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.
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
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
- 1.
Create a VM or use an existing VM and install SQL Server 2016 SP1 or later.
- 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.
Copy WideWorldImporters-Full.bak to the program filesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackup directory.
- 4.
Launch SQL Management Studio and connect to the Microsoft SQL Server.
- 5.
Right-click Databases and select Restore Database.
- 6.
Select Device as the source of the restore.
- 7.
Click the Device ellipses and select File as the backup media type.
- 8.
Click Add and select the WideWorldImporters-Full.bak file. Click OK.
- 9.
Click OK again to start the restore.
- 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
- 1.
Download and install the latest version of Microsoft Data Migration Assistant from www.microsoft.com/en-us/download/details.aspx?id=53595.
- 2.
Launch the Data Migration Assistant. Then click + to start a new project.
- 3.
Select Assessment as the project type and provide a name for the project. For this exercise, name the project WideWorldImporters-Assessment.
- 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.
- 5.
Click Create.
- 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.
- 7.Provide the connection information, as referenced in Figure 18-3 for the Microsoft SQL Server hosting the WideWorldImporters database, and then click Connect.
- 8.
Select the WideWorldImporters database by checking the box next to it, and then click Add.
- 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.
- 1.
Select SQL Server feature parity.
- 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.
- 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
- 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.
Once an Azure Migrate instance has been provisioned, go to the resource, and select Databases.
- 3.Add a tool and provide information about the project, as shown in Figure 18-5. Click Next.
- 4.Select Azure Migrate: Database Assessment, as shown in Figure 18-6, and then click Next.
- 5.Add a migration tool by unchecking the Skip adding a migration tool for now, as shown in Figure 18-7.
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.
In Data Migration Assistant, click Upload to Azure Migrate.
- 2.
Select the subscription, and it should find the Azure Migrate Project created in the previous exercise.
- 3.
Click Upload.
- 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
- 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.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.
Notice the tip at the bottom that recommends using Database Migration Service because it is faster. That is indeed the case.
- 3.
Click Create .
- 4.
Provide a connection to the SQL server, and then click Connect.
- 5.
Select WideWorldImporters, and then click Next.
- 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.
- 7.
Click Connect.
- 8.
Select the empty WideWorldImporters database created earlier, and then click Next.
- 9.
After the roles and schema have been generated, click Generate SQL script.
- 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.
- 11.
Click Migrate data.
- 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.
- 13.
Click Start data migration in Data Migration Assistant.
- 14.Note the progress of the data migration as the database is migrating to Azure, as referenced in Figure 18-12.
- 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.
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
- 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.For steps 3 to 7, refer to Figure 18-14 for reference.
- 3.
Select the subscription in which you want to deploy the Azure Database Migration Service, and then select Resource providers.
- 4.
Click Resource providers.
- 5.
Type migration in the search box.
- 6.
Select Microsoft.DataMigration in the Provider list.
- 7.
Click Register, and wait until the status changes from Registering to Registered.
- 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.
- 1.
Go to the Azure Database Migration Service and click + New Migration Project.
- 2.
Enter WideWorldImporters-migration as the project name.
- 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.
- 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.
- 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.
- 6.
Click Save.
- 7.
Click Create and run activity.
- 8.
Provide information for the following fields: Source SQL Server instance name, Authentication type, User Name, and Password.
- 9.
Check the Trust server certificate box.
- 10.
Click Save.
- 11.
Provide connection and authentication information for the WideWorldImporters-dms Azure SQL Database server as the target.
- 12.Select WideWorldImporters as the source database and reuse WideWorldImporters as the target database in the mapping, as seen in Figure 18-17.
- 13.
Click Save.
- 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.
- 15.
Click Save.
- 16.
Name the activity WideWorldImporters-dms-migration.
- 17.
Click the Validation option and select Validate my database(s), and then click Save.
- 18.
Click Run migration.
- 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.
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.