- Use Azure Bastion as a jump host for RDP and SSH - Tue, Apr 18 2023
- Azure Virtual Desktop: Getting started - Fri, Apr 14 2023
- Understanding Azure service accounts - Fri, Mar 31 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:
Subscribe to 4sysops newsletter!
- Tutorial: Migrate SQL Server to Azure SQL Database using DMS
- Migration guide: SQL Server to Azure SQL Database
- Overview of Data Migration Assistant
Quite informative.