Azure Database Migration Services

Azure Database Migration Services, or DMS, is a fully managed Azure service that enables seamless migrations from multiple data sources to Azure databases.

Here are some examples of migrations that DMS can do:

• Migrate on-premises SQL Server to Azure SQL Database or SQL managed instance. Supports both online and offline migrations.
• Migrate Azure SQL Database to SQL database managed instances.
• Migrate an AWS SQL Server RDS instance to Azure SQL Database or SQL managed instance.
• Migrate MySQL to Azure Database for MySQL.
• Migrate PostgreSQL to Azure Database for PostgreSQL.
• Migrate MongoDB to Azure Cosmos DB Mongo API.

  Note

  Online migration is a migration with zero downtime or near-zero downtime during the cut-over to the migrated database.

Exercise: Migrating a SQL Server Database to Azure SQL Database Using Azure DMS

Note

In the exercise, the source database is on a SQL server on Azure VM. To migrate an on-premises database, site-to-site connectivity is required via VPN or Azure Express route. To find out more about it, please visit the following sites: https://docs.microsoft.com/en-us/azure/expressroute/expressroute-introduction

https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-about-vpngateways

The rest of the steps for the migration are similar to those of the exercise.

Follow these steps to migrate a SQL Server database on Azure VM to an Azure SQL Database:

1. Use Database Migration Assistant to find the compatibility issues in the source database and migrate the source schema to an Azure SQL Database. Before migrating the schema, make sure you have a blank Azure SQL Database already provisioned.

   The steps to assess and migrate schema are given in Activity: Using Database Migration Assistant.

2. The next step is to register the Microsoft.DataMigration resource provider. To do this, type Subscriptions in the search box and then select Subscriptions:
   

   Figure 2.28: Registering the Microsoft.DataMigration resource provider

   In the Subscriptions window, select the subscription in which you wish to create the Azure DMS instance:

   

   Figure 2.29: Subscription window

   In the selected Subscription window, type Resource Providers in the search box:

   

   Figure 2.30: Resource providers

   Click Resource providers to open the Resource providers window. Click Register against Microsoft.DataMigration if it's not already registered:

   

   Figure 2.31: Registering Microsoft.DataMigration

3. Log in to the Azure portal and type Azure Database Migration Services in the search box:
   

   Figure 2.32: Searching for Azure Database Migration services

   Click the Azure Database Migration Services link under Services. In the Azure Database Migration Services window, click Add.

4. In the Create Migration Service window, provide the details as shown here:
   

   Figure 2.33: Create Migration Service window

   Other than the name, subscription, location, and resource group, DMS requires a virtual network and a pricing tier.

   A virtual network allows DMS to connect to the target and source. For example, if your source is an on-premises SQL Server database, the DMS virtual network should be connected to the on-premises network either through a VPN or an Azure Express route.

   For the sake of the demonstration, the source is a SQL Server database on Azure VM. Azure VM and DMS are on the same virtual network in order to facilitate connectivity between them.

   DMS has two performance tiers, Standard and Premium. The Standard tier is free and is for one-time or offline migrations. The Standard tier comes with one, two, or four vCores.

   The Premium tier can be used for offline and online migrations. The Premium tier comes with four vCores.

   

   Figure 2.34: Pricing tiers

   Click Create to provision a DMS instance.

5. The next step is to create a new database migration project. To do that, open the AzDMS resource and click New Migration Project:
   

   Figure 2.35: New Migration Project

   In the New Migration Project window, provide the Project name, select SQL Server as the Source server type, and select Azure SQL Database as the Target server type.

   Click Create and run activity to continue:

   

   Figure 2.36: Creating a new migration project

6. In the next step, provide the source server details. The Source SQL Server instance name is the name or the IP of the source SQL server. The source server here is an Azure VM. The private IP of the VM is therefore used to connect to it:
   

   Figure 2.37: Source server details

7. Click Save. DMS will connect and verify that DMS can connect to the specified source server. Upon successful connection, the wizard will move to the next step.
8. In the Select target window, specify the Azure SQL Database server and username and password. Make sure that the target Azure SQL Database contains the tables and the other objects from the source database:
   

   Figure 2.38: Migration target details

9. Click Save to verify and save the target server configuration.
10. In the next window, Map to target databases, map the source and the target database:
    

    Figure 2.39: Map to target databases

11. In the next step, select the tables to migrate. The default is All tables.

    Click Save to move to the Summary page.

    On the Summary page, verify the configuration settings, name the activity, and select the validation option:

    

    Figure 2.40: Migration summary

    The validation option allows you to validate the data after the database migration is done. Now select Do not validate my database(s):

    

    Figure 2.41: Validation option

12. Click Run migration to start the database migration.

    As the migration starts, you'll be redirected to the migration project status page.

    When the migration completes, the status is updated, as can be seen here:

Figure 2.42: Migration status

Determining the Migration Method

Once you have found and fixed compatibility issues, the next step is to select a migration tool or method and perform the actual migration. There are different methods available for various scenarios. The selection largely depends on downtime, database size, and network speed/quality.

Here's a comparison of various migration methods to help you correctly choose a migration method:

Figure 2.43: Determining the migration method

Migrating an On-Premises SQL Server Database to Azure SQL Database

Let's consider our example of Toystore Ltd. from the previous lesson. Mike has performed all the steps that he had to complete before he could migrate the SQL Server database to Azure. Now all he has to do is perform the migration using the tool of his choice. He selects SSMS. In this section, we'll see how to use SSMS to migrate a database to Azure:

1. Open SSMS. Press F8 to open Object Explorer. Connect to your SQL instance.

   Note

   A backup of toystore is available at C:\Code\Lesson02\toystore.bak.

2. In Object Explorer, right-click toystore database and go to Tasks | Deploy Database to Microsoft Azure SQL Database:
   

   Figure 2.44: Deploy database to Microsoft Azure SQL Database

3. In the Deploy Database wizard, click Next to continue:
   

   Figure 2.45: Deploy Database wizard

4. In the Connect to Server dialog box, provide your Azure SQL Server name, administrator login name, and password. Click Connect to connect to the Azure SQL server:
   

   Figure 2.46: Connecting to the Azure SQL server

5. In the Deployment Settings window, under New database name, provide the name of the Azure SQL Database to which you wish to migrate as an on-premises database. The Azure SQL Database edition and the Service Objective are automatically detected by SSMS.
6. Under Other settings, under the Temporary file name, SSMS displays the path of the exported bacpac file. You can change it if you wish to, or you can leave it as default. Click Next to continue:
   

   Figure 2.47: The Deployment Settings window

7. In the Verify Specified Settings window, review the Source and Target settings, and then click Finish to start the migration process:
   

   Figure 2.48: Verify Specified Settings

   SSMS checks for compatibility issues, and the migration process terminates because there are compatibility issues. Click Error, next to Exporting database, to view the error's details:

   

   Figure 2.49: View error details

   Here is the output showing a detailed description of the error:

   

   Figure 2.50: Description of the error

8. In the Error Details window, we can see that the migration was terminated because of unsupported objects found in the bacpac package. Click OK to close the Error Details window. The next step is to fix the errors.
9. Open C:\code\Lesson02\FixCompatibilityIssues.sql in SSMS. The script fixes the compatibility issues by commenting/correcting out the unsupported code within the stored procedures:

   USE [toystore] GO

   ALTER proc [dbo].[BackUpDatabase] As

   -- Backup command isn't supported on Azure SQL Database

   --backup database toystore to disk = 'C:\torystore.bak'

   --with init, stats=10 GO

   ALTER proc [dbo].[EmailProc] As

   -- Database mail isn't supported on Azure SQL Database

   --EXEC msdb.dbo.sp_send_dbmail

   -- @profile_name = 'toystore Administrator',

   -- @recipients = 'yourfriend@toystore.com',

   @body = 'The stored procedure finished successfully.',

   -- @subject = 'Automated Success Message' ;

   select * from city

10. Press F5 to execute the script. Repeat steps 1-10 to successfully migrate the database:
    

    Figure 2.51: Migrating the database

11. To verify the migration, connect to Azure SQL Database using SSMS and run the following query:

    SELECT TOP (1000) [OrderID]

          ,[CustomerID]

          ,[SalespersonPersonID]

          ,[PickedByPersonID]

          ,[ContactPersonID]

          ,[BackorderOrderID]

          ,[OrderDate]

          ,[ExpectedDeliveryDate]

          ,[CustomerPurchaseOrderNumber]

          ,[IsUndersupplyBackordered]

          ,[Comments]

          ,[DeliveryInstructions]

          ,[InternalComments]

          ,[PickingCompletedWhen]

          ,[LastEditedBy]

          ,[LastEditedWhen]

    FROM [toystore].[Sales].[Orders]

The following screenshot shows the output of the preceding code:

Figure 2.52: Verifying the migration

Congratulations! You have successfully migrated your SQL Server database to an Azure SQL database.

Activity: Using DMA

This section describes how to migrate a SQL Server database, such as the toystore database, to an Azure SQL database using DMA:

1. Open Microsoft Data Migration Assistant on your computer. From the left ribbon, click the + sign, as shown in the following screenshot:
   

   Figure 2.53: Microsoft Data Migration Assistant

2. In the resultant window, you will need to set these fields:

   For Project type, select Assessment.

   For Project name, type toystore.

   For Source server type, select SQL Server.

   For Target server type, select Azure SQL Server.

3. Click Create to create a new assessment project:
   

   Figure 2.54: Creating new assessment project

4. In the resulting Select report type window, select the Check database compatibility and Check feature parity checkboxes. Click Next to continue:
   

   Figure 2.55: Checking feature parity

5. In the Connect to a server window, do the following:

   For Server name, provide the SQL server name.

   For Authentication type, select Windows Authentication.

   Click Connect to continue:

   

   Figure 2.56: Connecting the server

6. In Add Sources, select toystore and click Add to continue.
7. Click Start Assessment to find compatibility issues.
8. DMA will apply the compatibility rules to find and list the compatibility issues. It tells you the features that aren't supported in the SQL Server feature parity section:
   

   Figure 2.57: SQL Server feature parity section

   According to DMA, you have one cross-database reference and one Service Broker instance, which aren't supported in Azure SQL Database.

9. Under Options, select the Compatibility issues radio button:
   

   Figure 2.58: Selecting compatibility issues

   DMA lists out the stored procedures that failed the compatibility test. To fix the errors, open C:\code\Lesson02\FixCompatibilityIssues.sql in SSMS and execute it against the toystore database.

10. In the top-right corner, click Restart Assessment:
    

    Figure 2.59: Restart assessment

    DMA will re-assess and notify you that there are no compatibility issues:

    

    Figure 2.60: DMA ascertains that there are no compatibility issues

11. To migrate the database, in the left-hand navigation bar, click the + sign.
12. In the resulting window, do the following:

    For Project type, select Migration.

    For Project name, type toystoremigration.

    For Source server type, select SQL Server.

    For Target server type, select Azure SQL Server.

    For Migration scope, select Schema and Data.

    Click Create to create a new assessment project:

    

    Figure 2.61: Creating a new assessment project

13. In the Connect to server window, do the following:

    For Server name, provide the SQL Server name.

    For Authentication type, select Windows Authentication.

    Click Connect to continue:

    

    Figure 2.62: Connecting to the source server

14. Select toystore from the list of available databases and click Next:
    

    Figure 2.63: Select toystore database

15. In the Connect to target server window, do the following:

    For Server name, provide the Azure SQL Server name.

    For Authentication type, select SQL Server Authentication.

    For Username, provide the Azure SQL Server admin user.

    For Password, provide the password.

    Clear the Encrypted connection checkbox.

    Click Connect to continue:

    

    Figure 2.64: Connect to target server

16. In the resulting window, select the toystore database, and then click Next to continue:
    

    Figure 2.65: Selecting the toystore database

17. In the resulting Select objects window, you can select which objects to move to Azure SQL Database. Select all and click Generate SQL Scripts at the bottom of the window to continue:
    

    Figure 2.66: Generate SQL scripts

18. DMA will generate a T-SQL script to deploy the database schema. If you wish to save the T-SQL script, you can do so by clicking on the Save option in the Generated script section:
    

    Figure 2.67: Generating a T-SQL script to deploy the database schema

19. In the Script and Deploy schema window, click the Deploy schema button to deploy the schema to the Azure server. DMA will execute the T-SQL script against the Azure SQL Database to create the selected database objects:
    

    Figure 2.68: Creating database objects

20. Once schema migration is successful, click Migrate data.
21. In the resulting Selected tables window, you can choose what table data to migrate. Leave it as default, for this example, selecting all tables, and then click Start data migration:

Figure 2.69: Starting data migration

This migrates data from the selected tables in parallel and therefore can be used for large to very large databases:

Figure 2.70: Migrate the data from selected tables

Activity: Performing Transactional Replication

In this section, we will make use of the toy manufacturing company introduced in an earlier lesson as an example to understand how to migrate an SQL Server database to an Azure SQL database using transactional replication:

1. Open SSMS. Press F7 to open Object Explorer. In Object Explorer, click Connect to connect to your SQL server.
2. In Object Explorer, right-click the Replication node and click New Publication…:
   

   Figure 2.71: Creating a new publication

3. In the New Publication Wizard welcome screen, click Next to continue.
4. In the Publication Database window, select toystore as the database to be published. Click Next to continue:
   

   Figure 2.72: Selecting toystore as the database

5. In the New Publication Wizard, select Transactional publication. There are only two publication types allowed with Azure SQL Database as a subscriber. Click Next to continue:
   

   Figure 2.73: Publication and transactional window

6. In the Articles page, select all the objects to publish. Click Next to continue. If required, you can filter out objects that you don't want to migrate to an Azure SQL database here:
   

   Figure 2.74: Selecting all the objects in the Articles page

7. The Article Issues page alerts you that you should migrate all tables that are referenced by views, stored procedures, functions, and triggers. As we are migrating all the tables, we don't have anything to do here. Click Next to continue:
   

   Figure 2.75: Articles Issues page

8. Filter Table Rows lets you filter unwanted rows that you don't want to publish. As you are publishing all rows, leave it as default and click Next to continue:
   

   Figure 2.76: Filter Table Rows window

9. In the Snapshot Agent page, select the Create a snapshot immediately and keep the snapshot available to initialize subscriptions option. You can also schedule the Snapshot Agent to run at specific times:
   

   Figure 2.77: Snapshot Agent window

10. In the Agent Security page, select the Security Settings button:
    

    Figure 2.78: Agent Security page

11. In the Snapshot Agent Security page, specify the account for the Snapshot Agent to run on. You can either give the domain account that has permission to access the SQL Server instance and the database or you can choose it to run under the SQL Server agent service account, which isn't the recommended option.

    Under the Connect to the publisher section, select By impersonating the process account. The process account must have read and write access to the publisher database:

    

    Figure 2.79: Snapshot Agent Security window

12. Click OK to continue. You'll be taken back to the Agent Security page. Check the Use the security settings from the Snapshot Agent box, under the Log Reader Agent text box. The Log Reader Agent will run under the same account as the Snapshot Agent. You can choose different security settings for the Log Reader Agent if you wish to:
    

    Figure 2.80: Agent Security window

    Click Finish to continue.

13. On the Complete the Wizard page, under Publication name, provide a name for your publication. You can review the objects that are being published in this window.

    Click Finish to create the publication:

    

    Figure 2.81: Completing the wizard

14. The New Publication Wizard will now create the publication. Add the selected articles to the publication and it will start the Snapshot Agent:
    

    Figure 2.82: New Publication Wizard window

    Click + to complete the New Publication Wizard.

    In Object Explorer, expand the Replication node, and then expand Local Publications; the toystorepub publication has been added to the publication list:

    

    Figure 2.83: Check that toystore is added to the publication list

15. The next step is to create a subscription for the Azure SQL Database. Open Object Explorer, expand the Replication node, and right-click the Local Subscription option. Select New Subscriptions to continue. Azure SQL Database only supports push subscriptions:
    

    Figure 2.84: Creating a subscription for the Azure SQL database

16. In New Subscription Wizard, select Next to continue:
    

    Figure 2.85: New Subscription Wizard

17. In the Publication page, select the publication for which you wish to create the subscription. The toystorepub publication is listed under the toystore database. If it's the only publication, it'll be selected by default. Click Next to continue:
    

    Figure 2.86: Selecting the toystore publication

18. In the Distribution Agent Location page, select Run all agents at the Distributor, which in our case is the push subscription. Pull subscriptions aren't allowed with Azure SQL Database as a subscriber. Click Next to continue:
    

    Figure 2.87: Distribution Agent Location

19. On the Subscribers page, click the Add Subscriber button at the bottom of the window and select Add SQL Server Subscriber:
    

    Figure 2.88: Creating a subscription for the Azure SQL Database

    On the Connect to Server dialog box, provide the Azure SQL Server name and SQL authentication login credentials to connect to the Azure SQL server. Click Connect to continue:

    

    Figure 2.89: Connecting to the server

    The Subscribers page will now list the Azure SQL server in the Subscriber column and the toystore database in the Subscription Database column. Select the Azure SQL server if it's not already selected and click Next to continue.

20. In the Distribution Agent Security window, click (Options menu) to set the security option:
    

    Figure 2.90: Distribution Agent Security

    The distribution agent can run under the context of the domain account or the SQL Server Agent Service account (not recommended) for the agent. Provide a domain account that has appropriate access to the Distribution Server, which in our case is the same as the Publication Server.

    In the Connect to the Distributor section, select the default option (by impersonating the process account). You can also use a SQL Server login if you wish to.

    In the Connect to the Subscriber section, provide the Azure SQL server, SQL Server login, and password.

    Click OK to go back to the Distribution Agent Security page. It'll now show the selected security options:

    

    Figure 2.91: Connecting to the server

    Click Next to continue.

21. On the Synchronization Schedule page, in the Agent Schedule section, select Run Continuously and click Next to continue:
    

    Figure 2.92: Synchronization schedule

22. On the Initialize Subscriptions page, under the Initialize When option, select Immediately, and then click Next to continue:
    

    Figure 2.93: Initialize subscription

23. On the Wizard Actions window, select the Create the subscription(s) option and click Next to continue:
    

    Figure 2.94: Wizard Actions

24. In the Complete the Wizard window, review the subscription settings and click Finish to create the subscription. The wizard will create the subscription and will initiate the Snapshot Agent to apply the initial snapshot on the subscriber.

    Once the initial snapshot is applied, all of the transactions on the publisher will be sent to the subscriber.

    Click Close to end the wizard.

25. To verify the replication, in Object Explorer, right-click the Replication node and select Launch Replication Monitor:

Figure 2.95: Launch Replication Monitor

In the replication monitor, expand the My Publishers node, then expand the SQL Server instance name node. The toystorepub publication will be listed there. Select the toystorepub publication to check the synchronization health:

Figure 2.96: Replication Monitor

It may take time to generate and apply the initial snapshot depending on the database's size.

To further verify that the objects are migrated to Azure SQL Database, switch to SSMS and open Object Explorer if it's not already open.

Connect to your Azure SQL Database and expand the Tables node. Observe that all of the tables are listed under the Tables node:

Figure 2.93: Observing the Table nodes