Move data to the cloud with Azure Data Migration

Despite more than a decade of cloud migration, there’s still a vast amount of data running on-premises. That’s not surprising since data migrations, even between similar systems, are complex, slow, and add risk to your day-to-day operations. Moving to the cloud adds additional management overhead, raising questions of network connectivity and bandwidth, as well as the variable costs associated with running cloud databases.

Part of the problem is that we tend to think of cloud services as a replica of our on-premises systems. That leads to a mismatch with the cloud, where we take servers that are sized appropriately for a multiyear lifespan with additional capacity for any sudden increase in demand and move that specification straight to a cloud infrastructure. The result is often underused virtual machines and a compute budget that’s substantially higher than what’s actually needed. Instead of lifting and shifting entire infrastructures we need to consider taking advantage of cloud services, working with them as platforms not as datacenters.

Introducing Azure Database Migration Service

Moving a database from on-premises to the cloud doesn’t have to be hard, especially if you’re migrating like to like. Azure’s Database Migration Service can support much of the process for you, with a set of wizards that guides you through the migration process. Like most on-premises migrations, when you’re moving a database from server to server, you have the option of an offline migration, with any ensuing downtime, and an online migration. If you’re using data, then an online migration is the best option, as you keep downtime to a minimum at the point you cut over between the two databases.

At the heart of the process is the same Data Migration Assistant that you’d use to move data from one SQL Server instance to another, analyzing your database and making performance improvements as part of the migration. This requires a virtual network connection between your on-premises database and your Azure environment, using either a VPN or a high-bandwidth ExpressRoute connection. Once in place this needs to be configured as an Azure VNet with appropriate endpoints for the target database, the Azure service bus, and Azure storage.

Setting up Migration Service on-premises

Some of the requirements for setting up Azure Database Migration Service may seem a little like breaking all the security rules you’ve ever learned, especially around database networking. However, all the traffic will come over your VNet connection, so you will be inside your overall security perimeter, even if you’ve temporarily reduced some database security.

With the connection between your on-premises database and Azure configured, you can start to run a SQL Server migration assessment. Choose SQL Server as your source server and Azure SQL Database as the target before creating a project to hold your assessment results.

The migration assessment will offer two types of reports, one detailing database compatibility and one detailing feature parity. You can choose either, both, or none. I’d recommend using both; you need to see what changes need to be made to your source database, if there are any differences between the two platforms that may affect your application code, as well as any stored procedures or triggers.

When an assessment has completed its run, you’re presented with a list of issues that might need addressing. While most feature less-important parity issues, compatibility issues that might block a migration are detailed, along with possible mitigations. It will add details of possible Azure-specific features that are alternatives to on-premises features, along with guides as to how you might use them in your database.

Microsoft has recently launched Azure SQL Managed Instances as an alternative to Azure SQL. These are a PaaS implementation of SQL Server, and using them as a migration target can simplify the process of migrating databases to Azure, as there will be fewer compatibility issues. Like Azure SQL, and unlike using IaaS VMs to host SQL Server, there’s no need to define server compute and memory—Azure handles it all.

If there’s nothing more to do, you’ve prepared your data for migration and can move to the next step: migrating your database schema with Data Migration Assistant. Create a new migration project and chose to migrate your schema from on-premises to Azure. Connect to the two servers, choose the objects you want to migrate, and then generate the scripts that run the migration. Check the scripts and then run them to deploy your schema from your database to an Azure instance.

Setting up the Azure Portal

The next stage of a migration needs you to log on to the Azure Portal, where you add the Azure Data Migration Assistant to your account and create a service. This sets up an Azure Database Migration Service instance, connecting it to the VNet you’ve set up for your data migrations. As part of configuring the service you need to pick an appropriate pricing tier for your migration. If it’s a one-off use the free standard tier which supports up to four cores and offline migrations; premium pricing is available for migrations that need the least possible downtime, supporting online migrations on a four-core server for $0.37 per hour (with the first six months free).

With a pricing model in place, add connection details for the source server, using a fully qualified domain name on your virtual network. Do the same for your target Azure SQL instance. When you’ve set up the connections, map the database you want to migrate onto the target. The service will automatically choose the empty tables that exist on the target, which should match exactly, as you have already migrated your schema. The service offers validation options, and when you’ve completed filling out the migration settings, it will run your migration, reporting on issues that may have occurred during the process. You can monitor the operation from the service portal, keeping an eye out for errors.

When the migration is complete you can switch your application to point to the new cloud-hosted endpoint and continue with your cloud migration, having moved your data to a managed service. The Azure SQL service will scale as necessary, with georeplication reducing the risk of outages. You’re not limited to the bandwidth VPN or ExpressRoute connections; large migrations can take advantage of Azure Data Box to physically move tens of terabytes of data from your datacenter to Azure.