Migrating Databases Over to Azure SQL Database

By | In Blog, SQL Server | October 19th, 2016

In my previous blog post, Get Started with Microsoft Azure SQL Database, we explored how to open an Azure account, create a database and server, create a new Azure SQL Server-level firewall, and finally how to connect to Azure SQL Database via SQL Server Management Studio (SSMS). The most logical next step and the one that most people have immense of amount of interest in is how to migrate on-premise SQL Server databases over to the Azure SQL Database server. In this blog, we will quickly explore two of the most popular migration options. The first is “Deploy Database to Windows Azure SQL Database Wizard” in SQL Server Management Studio (SSMS), which is reserved for small to medium databases migrations. The second is “Exporting and Importing a BACPAC File,” which can be used for medium to large databases migrations.

In the following examples, we will migrate the “DVSQLSampleDB” on-premise SQL Server database over to our Azure SQL Database server.

Deploy Database to Windows Azure SQL Database Wizard

1. Open SQL Server Management Studio (SSMS) and connect to the on-premise SQL Server instance that contains the database you wish to migrate over to Azure SQL database server.

2. Right-click the database > Tasks > Deploy Database to Windows Azure SQL Database…

3. Click “Next” on the “Introduction” screen.

4. Click the “Connect” button to connect to your destination Azure SQL Database server. Rename the new database, if needed. Click the “Browse” button to specify the location in which to store the temporary file, and then click the “Next ” button.

5. Review the “Summary” page to verify the settings.

6. Click the “Finish” button to start the migration.

7. Review the “Results” screen. You should see all the boxes checked with green check marks. One of the most common failures is missing Primary Key in any of the tables in the database. Click the “Close” button.

8. Connect to your destination Azure SQL Database server, and you will see the newly migrated database.

Exporting and Importing a BACPAC File

1. Open SQL Server Management Studio (SSMS) and connect to the on-premise SQL Server instance that contains the database you wish to migrate over to Azure SQL database server.

2. Right-click on the database > Tasks > Export Data-tier Application…

3. Click the “Next” button on the “Introduction” screen.

4. Click the “Browse” button and then specify the local disk folder and name the BACPAC file. Note that you can also save to Windows Azure. Click the “Next” button to continue.

5. Review the “Summary” screen and then click the “Finish” button to start the export operation.

6. Review the “Results” screen and the click the “Close” button. But, you are not done yet. You will do the import in the next few steps.

7. Connect to your destination Azure SQL Database server.

8. Right-click on the “Database” folder > Import Data-tier Application….

9. Click the “Next” button on the “Introduction” screen.

10. Click the “Browse” button on the “Import Settings” screen to locate the BACPAC file you had exported earlier. Note that you can also import from Windows Azure if you had exported the BACPAC file there. Click the “Next” button to continue.

11. Rename the database in the “Database Settings” screen. Click the “Next” button to continue.

12. Verify the specified settings on the “Summary” screen. Click the “Finish” button to start the import process.

13. Review the “Results” screen and then click the “Close” button. As noted in the previous example, one of the most common migration failures is due to missing the Primary Key in any of the tables for the database.

14. Connect to your destination Azure SQL Database server, and you will see the newly migrated database using the “Exporting and Importing a BACPAC File” option.

Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

2 thoughts on “Migrating Databases Over to Azure SQL Database”
  1. Hi AM ,
    Please guide for me for below query
    I have one database server ( 8 core , 128 GB memory , four HDD ) , Total 6 DB in Production and Maximum size 500 GB each DB .I am using SQL 2008 edition and OS win 2008 .
    I plan to purchase one Azure SQl server and create Replication between Our Own location server and Azure .
    Please guide as for what is total cost for Windows 2008 + SQl 2008 and need three drive around 500 GB each .
    Our own server in UK .

    Thanks & Regards,