Migrating Databases Over to Azure SQL Database

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.