Select Page

Migrating Databases Over to Azure SQL Database

JP Chen | | October 19, 2016

Migrating to Azure

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.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS