Migrating SQL Server to Azure: Options, Tools and Pitfalls

For enterprise engineering teams, legacy Microsoft SQL Server deployments often become victims of their own success. Over years of operational growth, on-premises instances accumulate terabytes of business-critical data alongside complex dependency webs: bespoke CLR routines, linked servers, distributed transactions, and legacy SSIS packages. However, maintaining aging SAN storage, managing Windows Server patching cycles, and facing steep core-licensing renewals make maintaining on-prem infrastructure unsustainable.

Executing an on-prem to cloud migration for SQL Server workloads resolves these infrastructure bottlenecks, but picking the wrong target tier or migration method can create performance degradation and budget overruns. Successfully migrating server Azure environments requires an engineering-first evaluation of compatibility, throughput patterns, high availability (HA) needs, and cutover windows.

Here is a breakdown of your destination options, modern migration tooling, real-world cutover architectures, and the architectural pitfalls to avoid.

---

Choosing the Right Azure Target Architecture

When migrating your SQL database workloads to Azure, Microsoft provides three primary deployment targets. Each balances operational control against platform management overhead differently.

+-------------------------------------------------------------------+
|               Azure Target Architecture Options                   |
+-------------------------------------------------------------------+
|  1. SQL Server on Azure VMs (IaaS)                                |
|     -> Full OS control, custom drives, third-party agents        |
+-------------------------------------------------------------------+
|  2. Azure SQL Managed Instance (PaaS)                             |
|     -> Near 100% T-SQL compatibility, VNet injection, native agent|
+-------------------------------------------------------------------+
|  3. Azure SQL Database (DBaaS)                                    |
|     -> Hyperscale/Serverless, microservices, no instance overhead|
+-------------------------------------------------------------------+

1. SQL Server on Azure Virtual Machines (IaaS)

This approach represents a straight lift-and-shift. You run Windows or Linux VMs on Azure Compute while retaining absolute administrative access to the underlying OS, local file systems, and SQL Server registry.

• Best fit: Legacy applications requiring third-party software agents installed directly on the host, specific file-path dependencies (e.g., C:\LocalData\), or legacy features no longer supported in managed services (such as cross-database distributed transactions via MSDTC across unmanaged networks).
• Trade-off: You remain responsible for OS-level patching, manual cumulative updates (CUs), guest OS configuration, and high-availability clustering via Always On Availability Groups across Availability Zones.

2. Azure SQL Managed Instance (PaaS)

Azure SQL Managed Instance (MI) delivers near-100% surface-area compatibility with the SQL Server database engine while abstracting away operating system administration. It supports SQL Server Agent, native cross-database queries within the instance, Service Broker, CLR assemblies, and Database Mail.

• Best fit: Enterprise enterprise-resource planning (ERP) systems, off-the-shelf commercial applications, and monolithic databases needing lift-and-shift simplicity without OS maintenance.
• Trade-off: Longer deployment and scale-up provisioning times compared to single-database PaaS, higher baseline cost floor, and limited access to local file systems.

3. Azure SQL Database (DBaaS)

Azure SQL Database is a fully managed, multi-tenant or single-tenant cloud database service. Available in Single Database, Elastic Pool, and Hyperscale tiers, it decouples compute from storage completely.

• Best fit: Modern cloud-native microservices, multi-tenant SaaS applications, and greenfield workloads. The Hyperscale tier can scale up to 100 TB per database with rapid auto-scaling and near-instant point-in-time restores regardless of size.
• Trade-off: No instance-level features. You cannot use SQL Agent jobs natively (you must orchestrate via Azure Elastic Jobs or Azure Data Factory), linked servers are limited, and cross-database queries are restricted.

---

Tooling the Migration Pipeline

Modern tooling minimizes manual scripting and eliminates protracted cutover maintenance windows.

1. Assessment: Data Migration Assistant (DMA) & Azure Migrate

Before moving data, run the Data Migration Assistant (DMA) against your on-premises schema and performance baselines. DMA identifies:

• T-SQL syntactic breaking changes (such as deprecated joins or legacy storage keywords).
• Unsupported features across target tiers (e.g., FileStream, local Windows logins).
• Recommended SKU sizing based on real historical CPU, memory, and IOPS consumption.

For estate-wide discovery, Azure Migrate provides holistic discovery across hundreds of physical or virtualized machines, identifying cross-server dependencies and mapping infrastructure to rightsized SKUs.

2. Orchestration: Azure Database Migration Service (DMS)

Azure DMS orchestrates offline and online migrations. For critical enterprise workloads where uptime cannot be compromised, DMS handles continuous transactional data synchronization via change data capture (CDC) or transaction log shipping, keeping the cloud replica hot until your final DNS cutover.

3. High-Volume Moves: Azure Log Replay Service (LRS)

When migrating large databases (multi-terabyte scale) to Azure SQL Managed Instance with minimal downtime, Log Replay Service (LRS) is the most robust approach. Powered by the same underlying engine as DMS, LRS allows you to ship full, differential, and transaction log backups directly to an Azure Blob Storage container. The Managed Instance restores logs sequentially in the background. Once your final transaction log is restored, you trigger a manual cutover with cutover windows measured in minutes rather than hours.

---

Top Technical Pitfalls (and How to Avoid Them)

Engineers who treat Azure targets as exact replicas of local iron often encounter post-cutover performance and cost issues.

1. Misjudging IOPS and Storage Throughput

On-premises, storage performance is often masked by large SAN caches and unthrottled local flash arrays. In the cloud, IOPS and throughput are metered by SKU and disk size.

• The Pitfall: Migrating to an Azure VM or PaaS tier where data disks hit throughput caps during peak reporting periods, leading to severe PAGEIOLATCH_SH and PAGEIOLATCH_EX waits.
• The Fix: Move beyond compute vCore count. Size Azure Managed Disks (Premium SSD v2 or Ultra Disks) specifically for your workload’s burst and baseline read/write patterns. For Managed Instance, place performance-critical workloads on the Business Critical tier to take advantage of local, low-latency NVMe SSD storage.

2. High Latency from Chatty Applications

Applications that run hundreds of queries per user request within an on-premises gigabit local network suffer when latency shifts from sub-millisecond local hops to 5–15 milliseconds across a wide-area network or hybrid link.

• The Pitfall: Migrating the database engine to the cloud while leaving application servers on-premises, causing query-heavy transactions to compound network round trips and drag down response times.
• The Fix: Migrate dependent application servers and database instances in synchronized waves. If a phased cutover is mandatory, deploy application gateways, utilize stored procedures to reduce round trips, or stage intermediate caching layers like Azure Cache for Redis.

3. Neglecting TempDB Configuration

In high-concurrency environments, tempdb contention (PAGELATCH_UP waits on allocation pages like GAM and SGAM) can quickly degrade database performance.

• The Pitfall: Provisioning an Azure VM without segregating tempdb onto local ephemeral storage, or selecting a low-tier PaaS instance that constrains tempdb throughput.
• The Fix: On Azure VMs, always store tempdb on the local ephemeral drive (D:\ drive on most Windows VM types), which runs on physical host NVMe SSDs. On Azure SQL Managed Instance, balance your temporary table workloads to match the documented tempdb limits of your chosen compute tier.

4. Overpaying by Ignoring Cost Optimization Features

Running an unoptimized database server Azure workload 24/7 on raw pay-as-you-go pricing leads to unnecessary cloud spend.

• The Fix: Apply Azure Hybrid Benefit (AHB) to repurpose existing on-premises SQL Server core licenses with active Software Assurance (SA). Combine AHB with 1-year or 3-year Reserved Capacity to cut compute expenses by up to 70–80% compared to baseline on-demand rates.

---

How Izan Technologies Helps

At Izan Technologies, we design and implement enterprise cloud modernization paths that minimize migration risk and eliminate infrastructure debt. Whether you are replatforming complex legacy SQL Server environments to Azure SQL Managed Instance, architecting low-latency transactional architectures, or migrating operational data directly into unified analytics platforms like Databricks and Snowflake, our data engineering specialists handle end-to-end execution.

Unlike consultancies that retain software assets or rely on proprietary, closed architectures, Izan Technologies ensures that our clients own 100% of the intellectual property (IP) created during the project—from Infrastructure-as-Code (Terraform/Bicep) automation to custom migration runbooks and data pipelines.

Ready to de-risk your cloud migration roadmap?

• Try our Smart AI DataStudio to explore intelligent data orchestration.
• Or, reach out directly and talk to an expert to design your migration strategy.