Migrate a SQL Server Database to Azure SQL Database

Created on January 22, 2023 at 4:50 pm

Category: Programming


Learn how to assess and migrate your on-prem SQL Server databases to Azure SQL Database by using Microsoft’s Data Migration Assistant and Azure Migrate products.

Your business plans to rehost its primary internal ASP.NET line of business (LOB) web application to Microsoft Azure. Currently, the data backend comprises a SQL Server database. Your task is to assess this database’s readiness for Azure SQL Database and then actually perform the migration. How do you begin?

Well, first, let’s assume your business has the following technical requirements:

  • The assessment and migration should minimize the need for additional hardware and/or software infrastructure.
  • The migration should ideally proceed in an online, replicated fashion with minimal downtime.
  • The Azure SQL Database should run at least as well as the on-premises source.

The good news is that Microsoft has a solid story for handling SQL Server database migrations to the Azure cloud. We’ll tackle the process in four phases:

  • Create an Azure Migrate project
  • Assess the local SQL Server database for Azure readiness with Data Migration Assistant (DMA) and upload the results to Azure Migrate
  • Transfer the local database schema to Azure SQL Database with DMA
  • Migrate the database data with Azure Database Migration Service (DMS)

Our prerequisites are as follows:

  • An Azure subscription
  • Data Migration Assistant (DMA) installed on our administrative workstation on-premises
  • A blank Azure SQL Database and associated virtual server in your desired region

Create an Azure Migrate project

Sign into the Azure portal, browse to the Azure Migrate blade, select the Databases (only) workload, and create a new project. Azure Migrate is a “one-stop shop” for several migration types, including:

  • Web apps to Azure Kubernetes Service (AKS)
  • Local virtual desktop infrastructure (VDI) to Azure Virtual Desktop (AVD)
  • Raw data upload via Data Box

For a SQL Server migration, you’ll use Data Migration Assistant (DMA) for assessment and Azure Database Migration Service (DMS) for data migration. The following screenshot shows my in-progress Azure Migrate project.

We’ll use Azure Migrate to organize and orchestrate our SQL Server-to-Azure SQL Database migration.

Assess the source SQL Server database and send the results to Azure

Install DMA if you haven’t already done so. DMA is a free Windows desktop application you can run from your Windows administrative workstation. DMA is useful in assessing your SQL Server database’s readiness for Microsoft Azure.

Specifically, DMA will identify the following issue types:

  • Migration blockers
  • Partially supported, unsupported, or deprecated features
  • Performance, security, or storage-related features you might want to consider for your postmigration Azure SQL Database

Open DMA and create a new assessment project. Specify SQL Server as your source server type and Azure SQL Database as your target server type. Authenticate to your local SQL Server and select the database(s) you need to assess.

As you can see in the next screenshot, DMA gives you a comprehensive report of feature parity and compatibility issues. What’s so cool about DMA is the tool provides detailed remediation advice, including auto-generated Transact-SQL scripts to accelerate your readiness process.

You can rerun the assessment as many times as you need until the tool gives you a proverbial clean bill of health. Next, click Upload to Azure Migrate, authenticate to Azure, specify your previously created Azure Migrate project, and proceed to the Azure portal for the next step.

Browse back to your Azure Migrate project, and you should see your assessment results. Hopefully, Azure Migrate lists your local SQL Server database as being ready for Azure SQL Database.

Transfer the database schema to Azure

I mentioned earlier that you’ll need to already have an Azure SQL virtual server and blank database online in your Azure subscription. Before we perform the data migration, we need to transfer the local SQL Server database schema to Azure SQL Database.

In DMA, start a new project. This time, select the Migration option and Schema only as your migration scope.

Step through the wizard, making sure to specify your Azure SQL Database as the target. Data Migration Assistant generates and then runs a T-SQL script that creates the schema structure in Azure for you.

Migrate the database

Although DMA can perform database migrations itself, as a single-user desktop application, it doesn’t have enterprise scale. For that, we need to revisit the Azure portal and create a new instance of the Azure Database Migration Service, or DMS for short.

Within that service instance, you’ll create a new migration project that will pick up where our prior work in Azure Migrate and DMA left off.

Historically, DMS required you to have either a site-to-site virtual private network (VPN) or ExpressRoute circuit between your source database server and Azure to facilitate port-based access between the environments.

Fortunately, DMS now offers a hybrid option that involves installing a hybrid worker service on your local SQL Server. This agent communicates on TCP 443, so it shouldn’t pose a problem for your perimeter firewalls.

As you’ll note in the next screenshot, your DMS project comprises a multistep activity with five steps:

  • Select the source SQL Server
  • Select databases to migrate
  • Select the target Azure SQL Database
  • Map schemas and database objects between sources
  • Configure migration settings

DMS attempts to perform an online migration; however, you’re likely looking at less than one hour of downtime during the cutover process.

In my lab environment, migrating the AdventureWorks 2016 database (admittedly small at 200 MB) took less than two minutes. Again, the point of DMS is its ability to run several large-scale data migrations in parallel.

Next steps

In the final screenshot, you can see my AdventureWorks database migration completed successfully, and I can interact with the database in Azure the same way I did when the database resided in my local environment.

You are now able to assess and migrate local SQL Server databases into Azure SQL Database. I encourage you to spend time “kicking the tires” in Azure Migrate to discover how to migrate other workload types.

For further learning:

Subscribe to 4sysops newsletter!

Connecting to lzomedia.com... Connected... Page load complete