Migrating On-Premises Databases to Azure

Let us start with the basics. An 'on-premises database' is simply a database running on computers physically located inside your own office or data centre – the one your company owns and pays electricity for. 'Azure' is Microsoft's public cloud platform: a massive collection of servers and services located in data centres all around the world that you can rent by the minute. 'Migrating' means moving that database from your own servers to Azure's servers.

Why would you do this? Because managing your own servers is a headache: you have to buy hardware, replace failed disks, apply security patches, and keep an air-conditioned room running 24/7. By migrating to Azure, you offload most of that grunt work. But you cannot just copy a database file over the internet – data is delicate, connections can break, and the move must cause zero downtime for your users.

The three main migration strategies are: rehost, refactor, and rearchitect. 'Rehost' – also called 'lift-and-shift' – copies the database exactly as it is onto a virtual machine in Azure. You still manage the OS and SQL Server, but Azure handles the physical hardware. 'Refactor' means you change the database slightly so it can run on Azure SQL Database, a fully managed platform-as-a-service (PaaS) offering where Microsoft patches, backs up, and scales the database for you. 'Rearchitect' is a bigger change – you break the database into smaller services or use different storage technologies entirely. For DP-300, your focus is on rehost and refactor for SQL Server workloads.

The key tool for the job is the 'Azure Database Migration Service' (DMS). Think of DMS as a dedicated moving van that is designed to transfer databases with minimal downtime. It connects to your source SQL Server, reads the schema and data, and writes them to the target Azure database. It also watches for any changes made to the source while the move is happening (called 'ongoing sync') and applies those changes to the target, so when you finally cut over, users barely notice the switch.

Before you run DMS, you need an 'assessment'. This is where you evaluate your current database to see if it is compatible with the Azure service you want. Microsoft provides the 'Data Migration Assistant' (DMA) for this. DMA analyses your SQL Server and produces a report saying: 'These features you use are supported', 'These features will break and need fixing', and 'Your database is this size, and here is a recommended Azure service tier'.

The actual migration process breaks down into these stages:

A critical concept is 'cutover'. This is the moment you stop the old database and start using the new one exclusively. During a minimal-downtime migration, DMS keeps synchronising changes up to the very last second. When you are ready, you stop the source application, let DMS apply the final queued changes, and then redirect traffic to Azure. The total downtime can be under a minute.

Another important term is 'backup and restore'. For smaller databases or when you can tolerate a bit more downtime, you can take a full backup (.bak file) of your SQL Server, copy it to Azure Blob Storage (a cloud file-storage service), and then restore it onto an Azure SQL Database instance. This is simpler than using DMS but does not support ongoing sync.

Azure also offers 'Azure SQL Managed Instance'. This sits between a VM and Azure SQL Database: you get the full SQL Server surface area (like SQL Agent jobs and cross-database queries) but still have a managed service. Migrating to Managed Instance is similar to migrating to Azure SQL Database but with fewer compatibility blockers because it is closer to the on-premises product.

Finally, you must understand 'bandwidth' and 'latency'. If your internet connection is slow, copying a multi-terabyte database over the wire could take days or weeks. In that case, Microsoft offers 'Azure Data Box' – a physical storage device you ship to Microsoft, which they connect directly into their data centre. Effectively, you mail the data on a hard drive.

Sarah is a database administrator for a midsize retail company. Their on-premises SQL Server 2016 runs the entire product catalogue and inventory system. The hardware is eight years old, the SAN storage is nearly full, and the licensing cost for SQL Server Enterprise is eye-watering. Management has finally approved a move to Azure to reduce monthly costs and improve disaster recovery.

Sarah's first step is an assessment. She downloads the Data Migration Assistant (DMA) onto the existing server and runs it against the inventory database. The report flags three issues:

With the assessment done, Sarah's company chooses Azure SQL Database as the target – the team wants the lowest maintenance overhead. Sarah sets up an Azure SQL Database instance in the UK South region (close to their main office) with a Business Critical tier to keep performance snappy. She then provisions an Azure Database Migration Service instance.

Sarah configures DMS with the connection string for her old SQL Server (through a VPN tunnel to protect data in transit) and the connection string for the new Azure SQL Database. She selects the inventory database and starts a minimal-downtime migration. DMS begins copying historical data – 800 GB – which takes about 12 hours over their 100 Mbps connection. During that time, she keeps the on-premises application running.

At 10:00 PM on a Saturday, Sarah performs the cutover. She puts the front-end application into maintenance mode, stopping new writes. DMS applies the final set of changes that occurred during the copy. She runs a quick row-count query on both databases: they match exactly. She updates the application's connection string in the web.config file to point to the Azure SQL Database endpoint, then takes the application out of maintenance mode. Total downtime: four minutes.

Post-migration, Sarah uses Azure Monitor to watch query performance. She also sets up a geo-replicated secondary database in a different Azure region for disaster recovery. Finally, she decommissions the old SQL Server on-premises after a month of parallel testing. The migration saved the company thousands per month in licensing and hardware costs.