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…
   
4. In the Deploy Database Wizard, click Next, then click Connect to connect to the target / destination SQL Database.
   
5. Enter the credentials to connect to the target / destination Azure SQL Server.
   
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. Complete 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.
   
5. Enter the credentials to connect to the target / destination SQL Server.
   
6. Fill in details to specify the following fields:
   – New database name
   – File name for the temporary BACPAC
7. Complete the Wizard
8. Once the wizard has completed, the new (“target”) Database will be created as a copy of the Source Database.

About the Author

Chris Pietschmann

Chris Pietschmann is a Microsoft MVP, HashiCorp Ambassador, and Microsoft Certified Trainer (MCT) with 20+ years of experience designing and building Cloud & Enterprise systems. He has worked with companies of all sizes from startups to large enterprises. He has a passion for technology and sharing what he learns with others to help enable them to learn faster and be more productive.