Implement an online migration strategy
In this we will learn about how to Migrate SQL Server to a single database or pooled database in Azure SQL Database online using DMS migration strategy. However, you can use the Azure Database Migration Service to migrate the databases from a SQL Server instance to Azure SQL Database with minimal downtime.
Assess your on-premises database
Before you can migrate data from a SQL Server instance to Azure SQL Database, you need to assess the SQL Server database for any blocking issues that might prevent migration. Using the Data Migration Assistant v3.3 or later, follow the steps described in the article Performing a SQL Server migration assessment to complete the on-premises database assessment.
To assess an on-premises database, perform the following steps:
- Firstly, in DMA, select the New (+) icon, and then select the Assessment project type.
- Secondly, 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.
However, 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
- Then, in DMA, on the Options screen, select Next.
- Next, 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.
- After that, in the Add sources dialog box, select AdventureWorks2012, select Add, and then select Start Assessment.
However, for single databases or pooled databases in Azure SQL Database, the assessments identify feature parity issues and migration blocking issues for deploying to a single database or pooled database.
- Lastly, review the assessment results for migration blocking issues and feature parity issues by selecting the specific options.
Migrate the sample schema
After you’re comfortable with the assessment and satisfied that the selected database is a viable candidate for migration to a single database or pooled database in Azure SQL Database, use DMA to migrate the schema to Azure SQL Database.
To migrate the AdventureWorks2012 schema to a single database or pooled database Azure SQL Database, perform the following steps:
- Firstly, in the Data Migration Assistant, select the New (+) icon, and then under Project type, select Migration.
- Secondly, specify a project name, in the Source server type text box, select SQL Server, and then in the Target server type text box, select Azure SQL Database.
- Thirdly, under Migration Scope, select Schema only.
- After that, select Create to create the project.
- Then, in DMA, specify the source connection details for your SQL Server, select Connect, and then select the AdventureWorks2012 database.
- Select Next, under Connect to target server, specify the target connection details for the Azure SQL database, select Connect. And then, select the AdventureWorksAzure database you had pre-provisioned in Azure SQL Database.
- After that, select Next to advance to the Select objects screen, on which you can specify the schema objects in the AdventureWorks2012 database that need to be deployed to Azure SQL Database.
By default, all objects are selected.
- Then, select Generate SQL script to create the SQL scripts, and then review the scripts for any errors.
- Lastly, select Deploy schema to deploy the schema to Azure SQL Database, and then after the schema is deployed, check the target server for any anomalies.
Create an instance
- Firstly, 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.
- Secondly, on the Azure Database Migration Service screen, select Create.
- Thirdly, on the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.
- After that, select the location in which you want to create the instance of the Azure Database Migration Service.
- Then, select an existing virtual network or create a new one.
- Here, the virtual network provides the Azure Database Migration Service with access to the source SQL Server and the target Azure SQL Database instance.
- Now, select a pricing tier.
- Lastly, select Create to create the service.
Create a migration project
After the service is created, locate it within the Azure portal, open it, and then create a new migration project.
- Firstly, in the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.
- Secondly, 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.
- Thirdly, select + New Migration Project.
- Then, on the New migration project screen, specify a name for the project, in the Source server type text box,. After that, select SQL Server, in the Target server type text box, select Azure SQL Database.
- In the Choose type of activity section, select Online data migration.
- After that, create Database Migration Service Project
- Then, select Save.
- Lastly, select Create and run activity to create the project and run the migration activity.
Run the migration
- Firstly, select Run migration.
The migration activity window appears, and the Status of the activity is Initializing.
Monitor the migration
- Firstly, on the migration activity screen, select Refresh to update the display. Do this until the Status of the migration shows as Running.
- Secondly, click on a specific database to get to the migration status for Full data load and Incremental data sync operations.
Perform migration cutover
After the initial Full load is completed, the databases are marked Ready to cutover.
- Firstly, when you’re ready to complete the database migration, select Start Cutover.
- Secondly, make sure to stop all the incoming transactions to the source database; wait until the Pending changes counter shows 0.
- Then, select Confirm, and the select Apply.
- Lastly, when the database migration status shows Completed, connect your applications to the new target Azure SQL Database.
Reference: Microsoft Documentation