Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Migrate SQL Server to Azure SQL Database offline using DMS

  1. Home
  3. Migrate SQL Server to Azure SQL Database offline using DMS

Go back to DP-300 Tutorials

In this, we will learn about how to Migrate SQL Server to Azure SQL Database offline using DMS. You may, however, transfer databases from a SQL Server instance to Azure SQL Database using Azure Database Migration Service.

Assess your on-premises database

Before you can migrate data from a SQL Server instance to an Azure SQL Database single database or pooled database, you must first evaluate the SQL Server database for any blocking issues that might hinder transfer. The following is a list of the stages that must be completed:

  • Firstly, in the Data Migration Assistant, 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.
  • Thirdly, in the Data Migration Assistant, 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.
  • Then, in the Add sources dialog box, select AdventureWorks2012, select Add, and then select Start Assessment.

However, for databases in Azure SQL Database, the assessments identify feature parity issues and migration blocking issues for deploying to a single database or pooled database.

  1. The SQL Server feature parity category provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps to help you plan the effort into your migration projects.
  2. The Compatibility issues category identifies partially supported or unsupported features that reflect compatibility issues that might block migrating SQL Server database(s) to Azure SQL Database.
  • Lastly, review the assessment results for migration blocking issues and feature parity issues by selecting the specific options.

Migrate the sample schema

Use DMA to migrate the schema to Azure SQL Database whenever you’re happy with the evaluation and that the selected database is a feasible candidate for migration to a single database or pooled database in 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.
  • Then, select Create to create the project.
  • Next, in the Data Migration Assistant, specify the source connection details for your SQL Server, select Connect, and then select the AdventureWorks2012 database.
  • After that, 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.
  • Then, 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.
  • Now, 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.
Dp-300 practice tests

Create an instance

  • Firstly, in the Azure portal menu or on the Home page, select Create a resource. Search for and select Azure Database Migration Service.
  • 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.
  • Then, select the location in which you want to create the instance of Azure Database Migration Service.
  • After that, select an existing virtual network or create a new one.
  • Next, select a pricing tier.
  • Lastly, select Create to create the service.

Create a migration project

After you’ve built the service, go to the Azure portal and access it, then create a new migration project.

  • Firstly, in the Azure portal menu, select All services. Search for and select Azure Database Migration Services.
  • Secondly, on the Azure Database Migration Services screen, select the Azure Database Migration Service instance that you created.
  • 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, select SQL Server. And, in the Target server type text box, select Azure SQL Database, and then for Choose type of activity, select Offline data migration.
  • Lastly, select Create and run activity to create the project and run the migration activity.

Specify source details

  • Firstly, on the Migration source detail screen, specify the connection details for the source SQL Server instance.
    • Make sure to use a Fully Qualified Domain Name (FQDN) for the source SQL Server instance name. You can also use the IP Address for situations in which DNS name resolution isn’t possible.
  • Secondly, if you have not installed a trusted certificate on your source server, select the Trust server certificate check box.
    • When a trusted certificate is not installed, SQL Server generates a self-signed certificate when the instance is started.

Specify target details

  • Firstly, select Save, and then on the Migration target details screen. Then, specify the connection details for the target Azure SQL Database, which is the pre-provisioned Azure SQL Database to which the AdventureWorks2012 schema was deployed by using the Data Migration Assistant.
  • Secondly, select Save, and then on the Map to target databases screen, map the source and the target database for migration.
    • However, if the target database contains the same database name as the source database, Azure Database Migration Service selects the target database by default.
  • Thirdly, select Save, on the Select tables screen, expand the table listing, and then review the list of affected fields.
    • Here. Azure Database Migration Service auto selects all the empty source tables that exist on the target Azure SQL Database instance. If you want to remigrate tables that already include data, you need to explicitly select the tables on this blade.
  • Then, select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity.
  • After that, 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.
  • Lastly, select Save, review the summary to ensure that the source and target details match what you previously specified.
Migrate SQL Server to Azure SQL Database offline using DMS DP-300 online course

Reference: Microsoft Documentation

Go back to DP-300 Tutorials

Menu