© Julian Soh, Marshall Copeland, Anthony Puca, and Micheleen Harris 2020
J. Soh et al.Microsoft Azurehttps://doi.org/10.1007/978-1-4842-5958-0_18

18. Migrating On-Premises Databases to Azure

Julian Soh1 , Marshall Copeland2, Anthony Puca3 and Micheleen Harris1
(1)
Washington, WA, USA
(2)
Texas, TX, USA
(3)
Colorado, CO, USA
 

In Chapter 17, we introduced database options and the benefits of consuming database services as a PaaS versus managing database servers.

This chapter covers the options that are available when migrating databases residing on database servers to Azure database PaaS. We mainly focus on migrating Microsoft SQL Servers to Azure SQL Databases, thereby eliminating the overhead of managing servers while realizing better service-level agreements.

We explore the migration tools, strategies, and options; going into detail about database migration is beyond the scope of this book. This is because database migrations usually impact applications, and a broader discovery to assess application dependencies should take place.

Bear in mind, however, that while the migration itself may be a complex activity, there are extremely very few scenarios where the needed capabilities can only be met if the databases remain on-premises. The benefits realized from having databases moved to Azure as a PaaS are worth the time taken to undertake such a migration.

Note

While we can only cover the core migration tools like DMA and DMS in this chapter, Microsoft has a comprehensive Database Migration Guide that provides step-by-step guidance for many migration scenarios, including third-party source databases. The guide is at https://datamigration.microsoft.com/.

Data Migration Assistant (DMA)

A good first strategy is to assess whether an on-premises database is a good candidate to migrate to Azure.

Microsoft provides a free tool called the Data Migration Assistant (DMA), which assesses the capabilities in a Microsoft SQL database to see if any issues need to be addressed before migrating the database to Azure SQL Database.

DMA tells you which features in use by the on-premises Microsoft SQL database will not be present in Azure SQL Database.

DMA is a good first step in the planning process to inventory and assess all the on-premises Microsoft SQL servers.

Not only is DMA an assessment tool, but it is also a migration tool that can copy the schema and the data from an on-premises database to Microsoft SQL Database; however, the Data Migration Assistant is designed as an assessment tool, and while it can serve as a data and schema migration tool, it is not designed to carry out an actual migration at scale. To execute migration at scale, please read the section on Database Migration Service later in this chapter.

We can also use DMA to migrate an on-premises SQL database to a Microsoft SQL Server on an Azure VM, but this scenario does not give you the benefits from using a PaaS, but we wanted to mention it anyway because there are scenarios where it is desirable to carry out this phased migration approach, for example, failing or aging hardware with databases that have migration blockers that may take time to remediate.

Hands-on: Setting up a Lab

In this exercise, you set up a lab environment that contains the following.

  • A virtual machine with SQL Server 2016 SP1 or later installed

  • A sample database that you can use DMA to analyze and migrate to Azure SQL Database

Let’s start the exercise.
  1. 1.

    Create a VM or use an existing VM and install SQL Server 2016 SP1 or later.

     
  2. 2.

    Go to our GitHub repo and download the Wide World Importers sample OLTP database (WideWorldImporters-Full.bak) from https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

     
  3. 3.

    Copy WideWorldImporters-Full.bak to the program filesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackup directory.

     
  4. 4.

    Launch SQL Management Studio and connect to the Microsoft SQL Server.

     
  5. 5.

    Right-click Databases and select Restore Database.

     
  6. 6.

    Select Device as the source of the restore.

     
  7. 7.

    Click the Device ellipses and select File as the backup media type.

     
  8. 8.

    Click Add and select the WideWorldImporters-Full.bak file. Click OK.

     
  9. 9.

    Click OK again to start the restore.

     
  10. 10.

    When the restore is complete, check that there is now a WideWorldImporters database with sample tables.

     

Hands-on: Using the Data Migration Assistant for Assessment

In this exercise, you install the Data Migration Assistant and use it to assess the WideWorldImporters database.
  1. 1.

    Download and install the latest version of Microsoft Data Migration Assistant from www.microsoft.com/en-us/download/details.aspx?id=53595.

     
  2. 2.

    Launch the Data Migration Assistant. Then click + to start a new project.

     
  3. 3.

    Select Assessment as the project type and provide a name for the project. For this exercise, name the project WideWorldImporters-Assessment.

     
  4. 4.

    Select Database Engine as the assessment type, SQL Server as the source server type, and Azure SQL Database as the target server type, as shown in Figure 18-1.

     
../images/336094_2_En_18_Chapter/336094_2_En_18_Fig1_HTML.jpg
Figure 18-1

Creating a new project in DMA

  1. 5.

    Click Create.

     
  2. 6.
    You are presented with the different types of reports available, as referenced in Figure 18-2. Select both the Check database compatibility report and the Check feature parity report, and then click Next.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig2_HTML.jpg
    Figure 18-2

    Report types available in DMA

     
  1. 7.
    Provide the connection information, as referenced in Figure 18-3 for the Microsoft SQL Server hosting the WideWorldImporters database, and then click Connect.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig3_HTML.jpg
    Figure 18-3

    Provide the connection information for the Microsoft SQL Database Server

     
  1. 8.

    Select the WideWorldImporters database by checking the box next to it, and then click Add.

     
  2. 9.

    Click Start Assessment.

     

It may take a few minutes for the assessment to complete, but you have successfully initiated the assessment process. Next, we review the reports from the assessment.

Hands-on: Reading the Assessment Reports from the Data Migration Assistant

In this hands-on exercise, we explore the reports generated by the Data Migration Assistant from the previous exercise.

After the assessment is complete, you have the option to review the results.
  1. 1.

    Select SQL Server feature parity.

     
  2. 2.
    Review the impact and recommendations for each of the feature parity issues discovered as referenced in Figure 18-4. Pay close attention to the number of objects impacted because that is an indicator of the severity of the impacts.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig4_HTML.jpg
    Figure 18-4

    Review the assessment report

     
  1. 3.

    Repeat the same steps for the Compatibility issues report.

     

You might be wondering about the Upload to Azure Migrate option. We explore it next. Do not exit out of the Data Migration Assistant yet. Go to the next exercise.

Hands-on: Azure Migrate

Azure Migrate is a service that provides a central hub to manage all migration activities. It is not restricted to databases and includes servers, web applications, and virtual desktops. However, we are focusing on databases since that is what this chapter is all about. More information about Azure Migrate is at https://azure.microsoft.com/en-us/services/azure-migrate/ and in our GitHub repo at https://github.com/harris-soh-copeland-puca/azure-docs/tree/master/articles/migrate.
  1. 1.

    Log in to the Azure portal and deploy Azure Migrate. We assume that you are familiar with deploying services in Azure, and so we will not go into detail on how to do so.

     
  2. 2.

    Once an Azure Migrate instance has been provisioned, go to the resource, and select Databases.

     
  3. 3.
    Add a tool and provide information about the project, as shown in Figure 18-5. Click Next.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig5_HTML.jpg
    Figure 18-5

    Set up the migration project

     
  1. 4.
    Select Azure Migrate: Database Assessment, as shown in Figure 18-6, and then click Next.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig6_HTML.jpg
    Figure 18-6

    Azure Migrate Database assessment

     
  1. 5.
    Add a migration tool by unchecking the Skip adding a migration tool for now, as shown in Figure 18-7.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig7_HTML.jpg
    Figure 18-7

    Install a migration tool. Uncheck the box

     

Once the tool is installed, you have Database Migration Service deployed in your Azure subscription.

We cover the Database Migration Service later in the chapter. For now, let’s return to DMA and continue where we left off in the previous exercise.

Hands-on: Uploading an Assessment Report to Azure Migrate

  1. 1.

    In Data Migration Assistant, click Upload to Azure Migrate.

     
  2. 2.

    Select the subscription, and it should find the Azure Migrate Project created in the previous exercise.

     
  3. 3.

    Click Upload.

     
  4. 4.

    Go to Azure Migrate in the portal and select databases. Then click the refresh icon to see the report in Azure. As you carry out more assessments of other databases, uploading the reports to Azure Migrate gives you a centralized location for your reports.

     

Hands-on: Migrate Database Using Data Migration Assistant

In this exercise, you use DMA to migrate the schema and data from the WideWorldImporters database residing on a Microsoft SQL Server to Azure SQL Database.
  1. 1.

    Create a database. Using the Azure SQL Database server from Chapter 17, create an empty database and name it WideWorldImporters. Assign it to the elastic pool.

     
  2. 2.
    Start a new Data Migration Assistant project, but this time select Migration as the project type, as referenced in Figure 18-8, SQL Server as the source server type, Azure SQL Database as the target server type, and Schema and data as the migration scope.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig8_HTML.jpg
    Figure 18-8

    Setting up a migration project using DMA

     
Note

Notice the tip at the bottom that recommends using Database Migration Service because it is faster. That is indeed the case.

  1. 3.

    Click Create .

     
  2. 4.

    Provide a connection to the SQL server, and then click Connect.

     
  3. 5.

    Select WideWorldImporters, and then click Next.

     
  4. 6.
    For the target server, enter the connection information and credentials to connect to the Azure SQL Database server in your Azure subscription, as shown in Figure 18-9.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig9_HTML.jpg
    Figure 18-9

    Credentials to the Azure SQL Database server

     
  1. 7.

    Click Connect.

     
  2. 8.

    Select the empty WideWorldImporters database created earlier, and then click Next.

     
  3. 9.

    After the roles and schema have been generated, click Generate SQL script.

     
  4. 10.
    Click Deploy schema, as referenced in Figure 18-10. The progress of the deployment is shown in a pane to the right. Take note of any errors so that you can remediate them separately after migration.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig10_HTML.jpg
    Figure 18-10

    Schema deployed to Azure SQL Database

     
  1. 11.

    Click Migrate data.

     
  2. 12.
    Optional step. If you go to the Azure portal and look at the WideWorldImporters Azure SQL Database using the Query editor, as shown in Figure 18-11, you see all the tables. If you run a query, there will not be any rows because you have only created the schema at this point.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig11_HTML.jpg
    Figure 18-11

    Viewing the WideWorldImports database in Azure SQL Database

     
  1. 13.

    Click Start data migration in Data Migration Assistant.

     
  2. 14.
    Note the progress of the data migration as the database is migrating to Azure, as referenced in Figure 18-12.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig12_HTML.jpg
    Figure 18-12

    Data migrating to Azure SQL Database by DMA

     
  1. 15.
    After the migration is complete, go to the Azure SQL Database for WideWorldImporters and see if the data is there, as shown in Figure 18-13.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig13_HTML.jpg
    Figure 18-13

    Data migrated to Azure SQL Database

     
Note

For some databases, especially complex and or enterprise databases, the recommended approach is to use the Data Migration Assistant to complete an assessment and move the schema. Then rely on Database Migration Service to move the data.

Azure Database Migration Service (DMS)

Azure Database Migration Service (DMS) is a fully managed service designed to migrate multiple database sources, compared to single database migrations using the Data Migration Assistant.

Azure Database Migration Service can migrate from many database sources, unlike DMA.

The Azure Database Migration Service does rely on Data Migration Assistant to generate an assessment prior to performing the migration and schema migration. Because Azure Database Migration Service can synchronize data to facilitate a cutover at a specific time, it is the preferred choice for most enterprise scenarios.

Enterprises use Azure Database Migration Service to initiate the migration of a production database to the new service. Data is moved, and when the move is complete, the data continues to be synchronized in both environments until a cutover is initiated.

Hands-on: Deploying Azure Database Migration Service

Before you can deploy an instance of Azure Database Migration Service, you need to activate the service by registering the Microsoft.DataMigration resource provider.
  1. 1.

    Go to the Azure portal and type subscriptions in the search box at the top of the portal. Select Subscriptions in the search results.

     
  2. 2.
    For steps 3 to 7, refer to Figure 18-14 for reference.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig14_HTML.jpg
    Figure 18-14

    Registering the Microsoft.DataMigration resource provider

     
  1. 3.

    Select the subscription in which you want to deploy the Azure Database Migration Service, and then select Resource providers.

     
  2. 4.

    Click Resource providers.

     
  3. 5.

    Type migration in the search box.

     
  4. 6.

    Select Microsoft.DataMigration in the Provider list.

     
  5. 7.

    Click Register, and wait until the status changes from Registering to Registered.

     
  6. 8.

    Once the Microsoft.DataMigration resource provider is registered, you can deploy Azure Migration Service by clicking Create a resource on the Azure portal. We assume that you are familiar with doing this by now, so we will not cover the steps on how to do it.

     

Hands-on: Using Azure Database Migration Service

After Azure Database Migration Service is deployed, you are ready to use the service to carry out the migration. Azure Database Migration Service uses the assessment from DMA to execute a migration.

Figure 18-15 shows the normal use of DMA as a precursor to using Azure Database Migration Service.
../images/336094_2_En_18_Chapter/336094_2_En_18_Fig15_HTML.jpg
Figure 18-15

The use of DMA and Azure Database Migration Service

For this exercise, you reuse the WideWorldImporters database assessment that you uploaded from the earlier exercise.
  1. 1.

    Go to the Azure Database Migration Service and click + New Migration Project.

     
  2. 2.

    Enter WideWorldImporters-migration as the project name.

     
  3. 3.
    Click the Source server type drop-down menu and note the different database sources that Azure Database Migration Service uses, as shown in Figure 18-16. Select SQL Server.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig16_HTML.jpg
    Figure 18-16

    List of source and target database sources that DMS can use

     
  1. 4.

    Click the Target server type drop-down menu and note the different database targets that Azure Database Migration Service can migrate to. Select Azure SQL Database.

     
  2. 5.

    Select Offline data migration for the type of activity. Azure Database Migration Service offers online or offline migration. With offline migrations, the database is stopped for the duration of the migration; application downtime begins at the time the migration starts. Online migrations carry out the migration while the database is not stopped until the final cutover. This limits the amount of downtime experienced by the applications.

     
  3. 6.

    Click Save.

     
  4. 7.

    Click Create and run activity.

     
  5. 8.

    Provide information for the following fields: Source SQL Server instance name, Authentication type, User Name, and Password.

     
  6. 9.

    Check the Trust server certificate box.

     
  7. 10.

    Click Save.

     
  8. 11.

    Provide connection and authentication information for the WideWorldImporters-dms Azure SQL Database server as the target.

     
  9. 12.
    Select WideWorldImporters as the source database and reuse WideWorldImporters as the target database in the mapping, as seen in Figure 18-17.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig17_HTML.jpg
    Figure 18-17

    Mapping the source and target databases in DMS

     
  1. 13.

    Click Save.

     
  2. 14.
    Select the tables that you want to migrate. As warned, the target table is not empty. This is to be expected since you used DMA earlier to migrate data. The existing data in these tables are deleted before the migration, as seen in Figure 18-18.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig18_HTML.jpg
    Figure 18-18

    Warning that table is not empty, and data will be deleted

     
  1. 15.

    Click Save.

     
  2. 16.

    Name the activity WideWorldImporters-dms-migration.

     
  3. 17.

    Click the Validation option and select Validate my database(s), and then click Save.

     
  4. 18.

    Click Run migration.

     
  5. 19.
    The migration activity’s progress is displayed in a new pane, as seen in Figure 18-19. Click Refresh to see the latest status.
    ../images/336094_2_En_18_Chapter/336094_2_En_18_Fig19_HTML.jpg
    Figure 18-19

    Status of DMS migration activity from pending to completed

     
Note

Since we used the WideWorldImporters database on Azure SQL Database with DMA, the tables, schema, and data were already there. When we reused the same database to migrate using Azure Database Migration Service, it deleted the data before the migration. If this was an empty database, the schema needs to be in place before migrating the data with Azure Database Migration Service. We can copy over the schema only first by using either DMA or Azure Database Migration Service.

Microsoft’s comprehensive and continuously updated documentation for the Azure Database Migration Service is at https://github.com/harris-soh-copeland-puca/azure-docs/tree/master/articles/dms.

Summary

This chapter was written to provide you with a good primer on the different Azure Storage options. As a reminder, comprehensive documentation for all Microsoft Azure services is on our GitHub repo at https://github.com/harris-soh-copeland-puca/azure-docs.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset