Share via

SQL Server Database migration to Azure SQL DB – using SSMS. Comparison of SSMS, SSDT, SAMW, SQL Server 2016 Upgrade Advisor.

  • Article

If you are planning to migrate an on-prem SQL Server to Azure, there are a few key considerations:

 

Migration will involve both migration of the schema and data from your source SQL DB to Azure SQL DB (PaaS). There may be some re-engineering required if your workload is not compatible with Azure SQL DB. See guidelines and limitations above.

 

Below are details on use of SQL Server Management Studio 2016 Migration Wizard. This approach is best used for SQL DB migration where the DB is less than 1TB in size and where there is persistent (good) connectivity between your source and Azure SQL DB. Alternate approaches such as BCP, SQL Server replication, manually migrating SQL DB or any other means will be covered in future posts.

 

Pre-reqs:

  • SSMS 2016
  • If needed, ensure SQL Server DB has SQL auth enabled (including create SQL login to enable migration of DBs)
  • Open inbound/outbound ports 1433 to SQL Server DB

 

Migration Steps:

  1. In my example, I’ll migrate my Azure IaaS VM SQL Server Database to Azure SQL Database. The same steps apply if you migrate an on-prem SQL Server DB to Azure SQL DB.
  2. I’ll need to prepare my IaaS VM SQL Server for migration
  3. Ensure inbound and outbound network firewall rules enable port 1433. You need to enable this so that SSMS can access and process migration.

sql1

3a. Go to Azure Portal and select the IaaS VM running SQL Server you wish to migrate * (only required if you are migrating an IaaS VM running SQL Server DB; if its an on-prem VM, log in to the Windows Server and open firewall rules enabling inbound/outbound ports 1433)

3b. From settings, click on Network Interfaces

3c. Next click on Network Security Groups

3d. Select the security group that applied to the IaaS VM with SQL Server to be migrated

sql2

3e. Set both the Inbound and Outbound security rules to enable port 1433

sql3

 

4. Next we’ll log in to SQL Server to begin migration process through SSMS 2016:

sql4

5. To begin the migration process, Select the DB you wish to migrate to Azure SQL DB by right clicking then selecting Tasks -> Deploy Database to Microsoft Azure SQL Database. Note you can also migrate an on-prem SQL Server to an IaaS VM SQL Server however consider benefits of Azure SQL vs managing your own VM.

sql6

6. This will open the Deploy Database wizard. The first step of the migration wizard will be to connect to your Azure subscription (Azure SQL DB) destination where you’re migrating your on-prem SQL DB to

6a. SSMS deployment database wizard will then validate and perform both the schema and data migration from your source and migrate your database to Azure SQL SB.

sql8 sql9

  1. You are notified once the operation is complete.

sql10

Here we have completed the migration of an SQL Server 2008 R2 database from an VM to Azure SQL DB.

SQL Server Management Studio is a very easy tool to perform simple migrations of SQL Server DB to Azure SQL DB if you do not require schema changes and your source DB is compatible with Azure SQL DB.

In summary some tools allow edit of schema to correct any incompatibilities, tools vary between developer audience vs SA audience, and finally some tools provide compatibility analysis vs some tools just to BCP/migration of schema and/or data.

SQL migration tools comparison

cheers

Joe