For years it’s been difficult to migrate a database that was built in SQL Server to Microsoft Azure SQL Database. Originally, the T-SQL support in Azure SQL wasn’t the same as SQL Server, which caused a lot of pain over the years. The T-SQL support in Azure SQL has been greatly updated / enhanced over the years, but at times a migration is still necessary. Fortunately, SQL Server Management Studio includes the “Deploy Database to Microsoft Azure Database Wizard” which has built-in support to migrate a database from SQL Server to Azure SQL Database. This tool actually works BOTH ways, from Azure to SQL Server and SQL Server to Azure!

Download SQL Server Management Studio

Before attempting to perform a database migration to/from Azure SQL Database, you’ll want to ensure you have the latest version of SQL Server Management Studio (SSMS) installed.

It can be downloaded for free from the following url:

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Once you have the latest SQL Server Management Studio installed, you’re ready to migrate!

Migrate from SQL Server to Azure SQL Database

To Migrate a Database from SQL Server to Azure SQL Database, you’ll need to follow these steps:

  1. Ensure you already have provisioned an Azure SQL Database
  2. Install and Run the latest version of SQL Server Management Studio and connect to the Azure SQL Database.
  3. In SQL Server Management Studio (SSMS), right-click on the SQL Server database that needs to be migrated, then click Tasks, and finally click on Deploy Database to Microsoft Azure SQL Database…
    ssms_open_dbmigrationwizard
  4. In the Deploy Database Wizard, click Next, then click Connect to connect to the target / destination SQL Database.
    ssms_open_dbmigrationwizard_2
  5. Enter the credentials to connect to the target / destination Azure SQL Server.
    ssms_open_dbmigrationwizard_3
  6. Fill in details to specify the following fields:
    – New database name
    – Edition of Microsoft Azure SQL Database
    – Maximum database size (GB)
    – Service Objective
    – File name for the temporary BACPAC
  7. ssms_open_dbmigrationwizard_4Complete the Wizard
  8. Once the wizard has completed, the new (“target”) Database will be created as a copy of the Source Database.

Migrate from Azure SQL Database to SQL Server

To Migrate a Database from SQL Server to Azure SQL Database, you’ll need to follow these steps:

  1. Ensure you already have provisioned an Azure SQL Database
  2. Install and Run the latest version of SQL Server Management Studio and connect to the Azure SQL Database.
  3. In SQL Server Management Studio (SSMS), right-click on the Azure SQL Database that needs to be migrated, then click Tasks, and finally click on Deploy Database to Microsoft Azure SQL Database…
  4. In the Deploy Database Wizard, click Next, then click Connect to connect to the target / destination SQL Server Database.
    ssms_open_dbmigrationwizard_2
  5. Enter the credentials to connect to the target / destination SQL Server.
    ssms_open_dbmigrationwizard_3
  6. Fill in details to specify the following fields:
    – New database name
    – File name for the temporary BACPAC
  7. ssms_open_dbmigrationwizard_4Complete the Wizard
  8. Once the wizard has completed, the new (“target”) Database will be created as a copy of the Source Database.

Posted by Chris Pietschmann

Chris is a Microsoft MVP and has 15+ years of experience building enterprise systems both in the cloud and on-premises. He is also a Microsoft Certified (MCSD) Azure Solutions Architect. He has a passion for technology and sharing what he learns with others to help enable them to learn faster and be more productive.

3 Comments

  1. […] Migrate Between Azure SQL Database and SQL Server (Chris Pietschmann) […]

    Like

    Reply

  2. Once the database is migrated, do you have any recommendation to transfer data between databases?

    In my on premise database, I know what records have changed / added to a table. After migrating the on premise database to Azure, I’m looking for ways to sync my on premise table to the Azure table. I don’t want to upload the entire on premise database to Azure again.

    Like

    Reply

  3. […] Migrate Between Azure SQL Database and SQL Server by Chris Pietschmann […]

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s