Skip to main content
Marios PavlidisPrincipal DBA
Professional Services

SQL Server Always On AG — On-Premises to Azure VM Migration

Database Solutions Lead

Scope

  • Current-state assessment of on-premises FCI and AG topology
  • Azure VM sizing and storage configuration for SQL Server workloads
  • AG topology design for Azure (two-region, three replicas)
  • Migration procedure with minimal downtime
  • Azure Monitor and Log Analytics integration for alerting
  • SQL Server backup strategy for Azure Blob Storage

Outcomes

  • AG failover time reduced from 3-4 minutes (FCI) to under 60 seconds
  • Storage I/O throughput increased 40% through Azure Premium SSD placement optimisation
  • Centralised monitoring covering all SQL Server instances in Azure Monitor
  • Backup to Azure Blob with 99.999999999% durability, eliminating on-premises tape dependency
SQL Server 2019Always On Availability GroupsAzure VMsAzure Premium SSDAzure Load BalancerWindows Server Failover ClusteringAzure MonitorLog Analytics

Background

A professional services organisation operated SQL Server 2016 on a two-node Failover Cluster Instance (FCI) using SAN-backed shared storage. The environment had several limitations:

  • Shared storage was approaching end of life and required capital investment to replace
  • FCI failover times of 3-4 minutes exceeded the revised RTO target
  • DR was a separate SQL Server instance with log shipping, providing 15-30 minute RPO
  • No centralised monitoring — each instance was monitored ad-hoc

The business case for cloud migration was clear: eliminate shared storage refresh cost, improve HA characteristics, and establish a DR posture with near-zero RPO.

Architecture Decisions

Topology

Three AG replicas were designed:

  • Primary replica — Azure VM in primary region (East US), synchronous commit
  • Secondary replica — Azure VM in primary region (East US), synchronous commit, readable secondary
  • DR replica — Azure VM in secondary region (West US), asynchronous commit, readable secondary

This configuration provides:

  • Local HA: automatic failover between primary and secondary (synchronous) with zero data loss
  • DR: asynchronous replica in West US, maximum data loss bounded by network replication lag (measured at 2-8 seconds)

VM and Storage Sizing

SQL Server storage on Azure requires deliberate placement:

  • Data files: Azure Premium SSD P40 (2 TB, 7,500 IOPS, 250 MB/s throughput)
  • Log files: Azure Premium SSD P30 (1 TB, 5,000 IOPS, 200 MB/s throughput) — separate from data
  • TempDB: local NVMe SSD (D-series VM ephemeral disk) — TempDB is recreated on every restart and is appropriate for ephemeral storage

Separating data and log onto different disks is critical on Azure to avoid IOPS and throughput contention between the write-heavy log and the mixed I/O data files.

Backup Strategy

Backups were moved to Azure Blob Storage using BACKUP TO URL. Full backups daily, differential every 6 hours, log backups every 15 minutes. PITR window of 7 days for full databases.

Backup verification (RESTORE VERIFYONLY) was added as a SQL Agent job running nightly.

Migration Approach

  1. Provisioned Azure VMs and configured WSFC with Azure-compatible quorum (Cloud Witness)
  2. Installed SQL Server 2019 on all three replicas
  3. Created the AG in asynchronous mode, seeded databases from on-premises via RMAN Duplicate equivalent (backup-restore)
  4. Synchronised AG to current production state
  5. Switched applications to point to AG listener via Azure Internal Load Balancer
  6. Promoted Azure primary to synchronous commit, demoted on-premises to async
  7. Validated 24-hour production operation on Azure
  8. Decommissioned on-premises FCI

Monitoring Integration

Azure Monitor was configured with:

  • Custom log queries for AG health (via SQL query-based alerts)
  • Disk I/O latency alerts on data and log volumes
  • Autovacuum equivalent — index maintenance alerting via SQL Agent job status

All alerts routed to a shared operations channel.

Lessons Learned

Cloud Witness configuration is non-obvious: The WSFC quorum requires a Cloud Witness (Azure Blob) instead of a traditional file share witness. This must be configured before any failover testing — a cluster with no quorum will not complete failover.

Azure Load Balancer probe port must be open: The AG listener uses an internal load balancer with a health probe. If the probe port (default 59999) is not open in the Windows firewall on the SQL Server VMs, the listener IP is not reachable by applications even when the AG is healthy.

TempDB on ephemeral disk requires startup configuration: TempDB files placed on ephemeral storage are lost on VM restart. SQL Server recreates TempDB on startup, but the correct number of files and sizes must be pre-configured in the SQL Server startup parameters.