Migrate a SQL Server Database to Azure SQL Database

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.

Author
Recent Posts

Timothy Warner

Timothy Warner is a Microsoft Cloud and Datacenter Management Most Valuable Professional (MVP) who is based in Nashville, TN. Check out his Azure and Windows Server video training at Pluralsight, and feel free to reach out to Tim via Twitter.

Latest posts by Timothy Warner (see all)

Install Ansible on Windows - Thu, Jul 20 2023
Use Azure Bastion as a jump host for RDP and SSH - Tue, Apr 18 2023
Azure Virtual Desktop: Getting started - Fri, Apr 14 2023

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.

An in progress Azure Migrate database migration 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.

Data Migration Assistant report and upload to Azure Migrate

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.

Review database assessment results in Azure Migrate

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.

Transfer a local SQL Server database schema to Azure

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.

DMS hybrid workers mean you no longer need a VPN to migrate databases to Azure

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.

Configuring a DMS database migration

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.

Completed database migration project

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.

Verifying successful database migration

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: