# Migrating On-Premises Databases to Azure

> Chapter 5 of the Courseiva DP-300 curriculum — https://courseiva.com/learn/dp-300/migrate-databases-to-azure

**Official objective:** 2.1 — Assess and migrate databases to Azure

## Introduction

Ignoring how to move a database to the cloud leaves you with a system that is expensive to maintain, vulnerable to hardware failures, and unable to scale when your app suddenly gets popular. For the DP-300 exam, you need to know the precise steps and tools Microsoft provides to relocate an on-premises SQL Server database to Azure safely and efficiently – whether you are taking a 'just copy everything' shortcut or carefully transforming it into a cloud-native service.

## The House Relocation Analogy

Your cramped, dusty garage, packed with boxes marked 'FRAGILE – CHINA'.

You are moving to a new house across town. You cannot just teleport the garage's contents. You must: plan which boxes are essential for your new life, hire a moving van, carefully load everything, drive it over, unload it, and then check that not a single teacup has cracked. That is a lift-and-shift move – it gets your stuff from A to B, but the organisation inside the boxes stays exactly as it was.

Now imagine you are also renovating the new house. You realise the dusty garage is going to be a home office instead. So you unpack the china, display it on a shelf, recycle the cardboard, and relabel everything with a colour-coded system. That is a modernisation move – you change how you store and use your belongings as part of the relocation. In IT, migrating an on-premises database – a database sitting on physical servers in your company's building – to the cloud follows the same logic. You can either lift-and-shift the database as-is (the 'moving van' approach) or refactor it into a fully managed service (the 'renovation' approach). The route you choose directly affects cost, performance, and how much maintenance you still have to do yourself.

## Core explanation

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:

- Pre-migration: You install DMA, run the assessment, and fix any compatibility issues. You also create your target Azure resource – either a virtual machine with SQL Server installed or an Azure SQL Database instance.
- Migration: You configure DMS with your source and target connection details, select the databases to move, and start the migration. DMS does the heavy lifting of copying data.
- Post-migration: After the cutover, you run validation queries to confirm all rows match. You then update your application's connection strings to point to the new Azure database instead of the old server.

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.

## Real-world context

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:

- The database uses the 'Full-Text Search' feature, which is fully supported in Azure SQL Database – no action needed.
- It uses a linked server to an old Oracle database. Azure SQL Database does not support linked servers, so Sarah must plan to refactor that code into an external table or an Azure Logic App integration.
- The database has a SQL Agent job that sends an email report every morning. Azure SQL Database does not have SQL Agent, so Sarah must decide whether to move that logic elsewhere.

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.

## Exam focus

DP-300 tests your practical knowledge of moving SQL Server to Azure. Expect scenario-based questions where you must choose the correct migration method, tool, or target. The exam does NOT ask you to write exact PowerShell commands – it asks for the concept and the right product name.

Key concepts they love to test:

- When to use Azure Database Migration Service vs. backup-and-restore. DMS is for minimal downtime (ongoing sync). Backup-and-restore is simpler but has higher downtime.
- The order of operations: always assess first with DMA, then fix compatibility, then migrate.
- Azure SQL Database vs. Azure SQL Managed Instance vs. SQL Server on Azure VM. Know the trade-offs: VM has full control but more management; Managed Instance is a balance; Azure SQL Database is fully managed but has compatibility limits.
- Cutover definition: the point when the new database becomes the primary and the old one is retired.
- Data Box scenario: if the database is very large (terabytes) or your internet is slow (sub-50 Mbps), Data Box is the correct answer because you ship a physical disk.
- Connectivity during migration: you need to open firewall ports (usually 1433 for SQL Server) and ideally use a private connection (VPN or Azure ExpressRoute) rather than the public internet.

Common traps:

- They present a scenario where the database uses features like Service Broker, CLR integration, or cross-database queries. Azure SQL Database does not support all of these – the correct answer is in many cases 'Azure SQL Managed Instance' which has broader compatibility.
- They ask about 'hybrid benefit' – you can reuse your existing SQL Server licenses on Azure to save money. That is called 'Azure Hybrid Benefit'.
- They offer 'Azure SQL Edge' as a target – wrong. Edge is for IoT devices, not for migrating your main business database.
- They name a tool 'Azure Data Factory' as the migration tool – it is an orchestration tool, not a database migration service.

Question patterns:

- 'You need to migrate a 500 GB SQL Server database with the least possible downtime. Which tool should you use?' Answer: Azure Database Migration Service with online migration.
- 'Your on-premises database uses SQL Server Agent jobs and linked servers. Which Azure target is most suitable?' Answer: Azure SQL Managed Instance.
- 'You have a 3 TB database and very slow internet. How should you get the data to Azure?' Answer: Azure Data Box.
- 'What is the first step in the migration process?' Answer: Run the Data Migration Assistant to check compatibility.

## Step by step

1. **1. Assess with DMA** — Install and run the Data Migration Assistant on your on-premises SQL Server to identify compatibility issues, unsupported features, and the recommended Azure target. Fix any blockers this tool reports.
2. **2. Set up the Azure target** — Create the Azure resource you will migrate to – either an Azure SQL Database, Azure SQL Managed Instance, or a SQL Server on Azure VM. Configure firewall rules and authentication.
3. **3. Choose your migration method** — Decide between offline backup-and-restore (higher downtime, simpler) or online migration with Azure DMS (minimal downtime, more complex). Your choice depends on how much downtime your business can tolerate.
4. **4. Execute the migration** — If using DMS, create a migration project, select the source and target, and start the transfer. During online migration, enable ongoing sync to catch changes. For backup-restore, copy the .bak file to Azure Blob Storage then run RESTORE.
5. **5. Perform the cutover** — Stop all writes to the source database. Apply any final pending changes via DMS or restore the last differential backup. Verify row counts and data integrity on the target.
6. **6. Update connection strings and decommission** — Edit your application configuration files to point to the new Azure connection string. After a period of parallel testing (typically 30 days), shut down the old on-premises server to save power and costs.

## Comparisons

### Offline Migration vs Online Migration

**Offline Migration:**
- Requires application downtime during the entire data copy
- Uses backup-and-restore or simple copy
- No ongoing sync – source must be stopped while data transfers

**Online Migration:**
- Allows application to stay running during data copy
- Uses Azure Database Migration Service with ongoing sync
- Cutover downtime is typically under a minute

### Azure SQL Database vs Azure SQL Managed Instance

**Azure SQL Database:**
- Fully managed PaaS – no OS or SQL Server access
- Fewer compatible SQL Server features (no SQL Agent, no linked servers)
- Lower cost per GB of storage

**Azure SQL Managed Instance:**
- Managed PaaS but with full SQL Server surface area
- Supports SQL Agent jobs, linked servers, cross-database queries
- Runs in its own VNet for better isolation

### Data Migration Assistant (DMA) vs Azure Database Migration Service (DMS)

**Data Migration Assistant (DMA):**
- Assessment only – detects compatibility issues
- Free tool that runs on-premises
- Does not move any data

**Azure Database Migration Service (DMS):**
- Performs the actual data transfer
- Paid Azure service (based on compute hours)
- Supports ongoing sync for minimal downtime

### Backup and Restore vs Azure Database Migration Service

**Backup and Restore:**
- Simple – just take a .bak and restore
- Requires application to be offline during restore
- No ongoing sync – any changes after backup are lost

**Azure Database Migration Service:**
- More complex setup but supports online migration
- Allows minimal downtime with ongoing sync
- Handles change tracking automatically

## Diagram

_Flowchart of the database migration process from on-premises SQL Server to Azure, showing assessment, method choice, transfer, and cutover._

```mermaid
flowchart TD
    A[On-Premises SQL Server] --> B{Assessment with DMA}
    B --> C[Fix compatibility issues]
    C --> D{Choose migration method}
    D --> E[Backup & Restore offline]
    D --> F[Azure DMS online sync]
    E --> G[Upload .bak to Blob Storage]
    G --> H[Restore to Azure target]
    F --> I[Configure DMS project]
    I --> J[Start migration + ongoing sync]
    J --> K[Perform cutover]
    H --> L[Update connection strings]
    K --> L
    L --> M[Decommission old server]
```

## Common misconceptions

- **Misconception:** You can simply copy the .mdf file to Azure Storage and attach it to an Azure SQL Database. **Reality:** Azure SQL Database does not allow attaching files. You must use backup-and-restore or a migration service. (People think the cloud works like a USB drive – plug and play – but Azure SQL Database is an isolated managed service that only accepts data through defined protocols.)
- **Misconception:** The Data Migration Assistant (DMA) actually migrates the data. **Reality:** DMA only assesses compatibility and identifies issues. The actual data transfer is done by Azure Database Migration Service or backup-and-restore. (The similar-sounding names cause confusion. Beginners mix up the assessment tool with the migration tool.)
- **Misconception:** An Azure SQL Managed Instance is just a bigger version of Azure SQL Database. **Reality:** Managed Instance is a separate service with a different architecture – it runs in a virtual network (VNet) and supports more SQL Server features like linked servers, Agent jobs, and cross-database queries. Azure SQL Database is smaller and more restricted. (Both are 'managed', so people assume they are the same product with different sizes, when in fact they suit different compatibility needs.)
- **Misconception:** You must migrate everything in one go – no hybrid setup is possible. **Reality:** You can run a hybrid deployment for a long period, keeping some databases on-prem and others in Azure while they talk to each other via linked servers or Azure Data Factory. (Beginners think the migration is an all-or-nothing cutover, but Azure supports gradual transitions.)
- **Misconception:** Lift-and-shift to an Azure VM is free – you only pay for the VM compute. **Reality:** You still need a SQL Server license on the VM (unless you use Azure Hybrid Benefit) and you pay for storage, backups, and data egress. (People see 'no hardware cost' and forget the software licensing and operational costs that still exist with IaaS.)

## Key takeaways

- The Data Migration Assistant (DMA) is only for assessment, not for migration – use Azure Database Migration Service for the actual data move.
- Minimal-downtime migrations require Azure Database Migration Service with ongoing sync, followed by a controlled cutover.
- Azure SQL Managed Instance supports more SQL Server features than Azure SQL Database, including SQL Agent jobs and linked servers.
- For very large databases (multiple terabytes) with slow internet, ship data physically using Azure Data Box.
- Always assess compatibility before migrating – DMA tells you which features will break in the target environment.
- Azure Hybrid Benefit lets you reuse existing SQL Server licenses on Azure to reduce costs.
- Backup-and-restore via Azure Blob Storage is the simplest method but requires your application to be offline during the restore.
- After migration, update application connection strings to point to the new Azure endpoint – this is often overlooked during cutover.

## FAQ

**Can I just upload my SQL Server database file (.mdf) to Azure and run it?**

No. Azure SQL Database and Azure SQL Managed Instance do not allow attaching .mdf files directly. You must use backup-and-restore (via .bak) or a migration tool like DMS.

**What is the difference between offline and online migration?**

Offline migration requires you to stop the application while the data copies – downtime can be hours or days. Online migration uses ongoing sync so the source can stay live until a brief final cutover.

**Do I need a VPN to migrate a database to Azure?**

Not strictly, but it is strongly recommended. The public internet route is vulnerable to interception. Most businesses use VPN or Azure ExpressRoute for a secure, private connection.

**What is Azure Data Box and when would I use it?**

Azure Data Box is a physical storage device that Microsoft ships to you, you copy your database onto it, and then ship it back to Microsoft. Use it when your database is very large (over 1 TB) or your internet connection is too slow for cloud transfer.

**Can I migrate an Oracle or MySQL database to Azure SQL?**

For DP-300, the focus is SQL Server to Azure SQL. Migrating Oracle/MySQL to SQL Server is possible but requires different tools (like Azure Data Factory or SSIS) and significant schema conversion, which is outside this chapter's scope.

**What happens to my SQL Agent jobs after migration to Azure SQL Database?**

Azure SQL Database does not support SQL Agent. You must reimplement scheduled tasks using Azure Logic Apps, Azure Functions, or Elastic Jobs. If you need SQL Agent, migrate to Azure SQL Managed Instance instead.

## Check your understanding

1. **What is the first step in migrating an on-premises SQL Server database to Azure?**

   Answer: Run the Data Migration Assistant (DMA) to assess compatibility and identify potential blockers.

2. **When would you choose Azure Data Box instead of a network-based migration?**

   Answer: When the database is very large (multiple terabytes) or your internet connection is too slow to transfer the data in a reasonable time.

3. **What is the key advantage of online migration over offline migration?**

   Answer: Online migration allows the source database to stay operational during the copy, resulting in very short cutover downtime (under a minute).

4. **Which Azure SQL target supports SQL Server Agent jobs and linked servers?**

   Answer: Azure SQL Managed Instance.

5. **What does 'cutover' mean in the context of database migration?**

   Answer: Cutover is the final step where you stop the old database and switch the application to use the new Azure database as the primary.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/dp-300/migrate-databases-to-azure