Migrate SQL Server to Azure SQL Database using Database Migration Services (DMS)

The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. The Data Migration Service (DMA) lets you migrate on-premises SQL Server or SQL Database hosted in AWS or GCP migrate to Azure SQL Database. There are three simple steps require to migrate Database to Azure SQL. There are two migration approach you can take, Offline and Online migration.

  1. Assess on-premises SQL Server instance(s) migrating to Azure SQL database(s).
  2. Discover issues that can affect an upgrade to an on-premises SQL Server.
  3. Migrate an on-premises SQL Server instance to Azure SQL Server
Azure SQL Database Migration Steps

Prerequisites:

  • SQL Server sysadmin role
  • distributor role for source SQL Server
  • SQL Server Management Studio (SSMS)
  • Co-Administrator or Contributor role in Azure cloud
  • Source Database is SQL Server 2005 or later version
  • Target database is Azure SQL Database and Azure SQL Database Managed Instance
  • Azure Virtual Network (VNET) for the Azure Database Migration Service by using the Azure Resource Manager deployment model.
  • Ensure that your VNET Network Security Group rules don’t block the following communication ports 443, 53, 9354, 445, 12000.
  • Download and install the Data Migration Assistant v3.3 or later.

Prepare Target Database:

A single database has a defined set of compute, memory, IO, and storage resources using one of two purchasing models.

  1. Log on to Azure Portal
  2. Select Create a resource in the upper left-hand corner of the Azure portal.
  3. Select Databases and then select SQL Database.
  4. In the Create SQL Database form, type or select Database name, Subscription, Resource group, Select source>select OK.
  5. Under Server, select Create new.
  6. In the New server form, create Server name, location, admin Name and password
  7. Choose Select.
  8. On the SQL Database form, select Pricing tier. Explore the amount of DTUs and storage available for each service tier.
  9. For this quickstart, select the Standard service tier, and then use the slider to select DTUs (S0) and GB of storage.
  10. Select Apply.
  11. On the SQL Database form, select Create to deploy and provision the resource group, server, and database.
  12. Deployment takes a few minutes. You can select Notifications on the toolbar to monitor deployment progress.
  13. On the SQL Database page for your database, select Query editor (preview) in the left menu.
  14. Enter your login information, and select OK.
  15. Enter the following query in the Query editor pane. Select Run, and then review the query results in the Results pane.

SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName

FROM SalesLT.ProductCategory pc

JOIN SalesLT.Product p

ON pc.productcategoryid = p.productcategoryid;

Assess your on-premises database:

  1. In the Data Migration Assistant, select the New (+) icon, and then select the Assessment project type.
  2. Specify a project name, in the Source server type text box, select SQL Server, in the Target server type text box, select Azure SQL Database, and then select Create to create the project.
  3. When you’re assessing the source SQL Server database migrating to a single database or pooled database in Azure SQL Database, you can choose one or both of the following assessment report types: Check database compatibility, Check feature parity, Both report types are selected by default.
  4. In the Data Migration Assistant, on the Options screen, select Next.
  5. On the Select sources screen, in the Connect to a server dialog box, provide the connection details to your SQL Server, and then select Connect.
  6. In the Add sources dialog box, select AdventureWorks2012, select Add, and then select Start Assessment.

Create a Database Migration Services instance:

  1. In the Azure portal, select + Create a resource, search for Azure Database Migration Service, and then select Azure Database Migration Service from the drop-down list.
  2. On the Azure Database Migration Service screen, select Create.
  3. On the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.
  4. Select the location in which you want to create the instance of the Azure Database Migration Service.
  5. Select an existing virtual network (VNET) or create a new one.
  6. Select a pricing tier.
  7. Select Create to create the service.

Create a migration project:

  1. In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.
  2. On the Azure Database Migration Services screen, search for the name of the Azure Database Migration Service instance that you created, and then select the instance.
  3. Select + New Migration Project.
  4. On the New migration project screen, specify a name for the project, in the Source server type text box, select SQL Server, in the Target server type text box, select Azure SQL Database, and then for Choose type of activity, select Offline data migration or Online data migration.
  5. Select Create and run activity to create the project and run the migration activity

Specify Source On-premises SQL Database:

  1. On the Migration source detail screen, specify the connection details for the source SQL Server instance e.g. FQDN of SQL Server, admin name (domain name\username) and password.
  2. Click Encrypt Connection. Click Save.

Specify Target Azure SQL Database:

DMS Wizard
  1. On the Migration target details screen, specify the connection details for the target Azure SQL Database Server.
  2. Select Save, and then on the Map to target databases screen, map the source and the target database for migration.
  3. Select Save, on the Select tables screen, expand the table listing, and then review the list of affected fields.
  4. Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity.
  5. Expand the Validation option section to display the Choose validation option screen, and then specify whether to validate the migrated databases for Schema comparison, Data consistency, and Query correctness.
  6. Select Save, review the summary to ensure that the source and target details match what you previously specified.
  7. Select Run migration.
  8. After the migration completes, select Download report to get a report listing the details associated with the migration process.
  9. Verify the target database(s) on the target Azure SQL Database server.

Monitor & Cutover to Azure SQL Database:

Data migration using SQL Management Studio
  1. On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Running.
  2. Click on a specific database to get to the migration status for Full data load and Incremental data sync operations.
  3. When you’re ready to complete the database migration, select Start Cutover.
  4. Make sure to stop all the incoming transactions to the source database; wait until the Pending changes counter shows 0.
  5. Select Confirm, and the select Apply.
  6. When the database migration status shows Completed, connect your applications to the new target Azure SQL Database.