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:
- Ensure you already have provisioned an Azure SQL Database
- Install and Run the latest version of SQL Server Management Studio and connect to the Azure SQL Database.
- 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…
- In the Deploy Database Wizard, click Next, then click Connect to connect to the target / destination SQL Database.
- Enter the credentials to connect to the target / destination Azure SQL Server.
- 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 Complete the Wizard
- 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:
- Ensure you already have provisioned an Azure SQL Database
- Install and Run the latest version of SQL Server Management Studio and connect to the Azure SQL Database.
- 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…
- In the Deploy Database Wizard, click Next, then click Connect to connect to the target / destination SQL Server Database.
- Enter the credentials to connect to the target / destination SQL Server.
- Fill in details to specify the following fields:
– New database name
– File name for the temporary BACPAC Complete the Wizard
- Once the wizard has completed, the new (“target”) Database will be created as a copy of the Source Database.
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.
You can use Azure SQL Data Sync.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data