arabam.com: SQL Server Always On Migration with Zero Downtime

Migrating a production SQL Server instance to Always On Availability Groups without downtime is one of the most consequential infrastructure changes a database team can undertake. Done well, it eliminates scheduled maintenance windows, provides automatic failover, and unlocks read-scale performance. Done poorly, it risks data loss or extended outages during the cutover.

This playbook presents a five-phase rolling migration strategy that achieves zero visible downtime. It is based on patterns refined across SQL Server HA migrations in enterprise environments, including databases exceeding 2 TB under continuous production load.

When to Migrate to Always On

Always On Availability Groups (AG) are the right choice when your environment meets these criteria:

• You are running SQL Server Enterprise Edition (2016 or later; Standard Edition supports basic AGs with limitations)
• Your workload is read-heavy (70%+ reads) and would benefit from read offloading to secondary replicas
• Your business cannot tolerate scheduled maintenance windows — even 2-hour windows during off-peak hours
• You need automatic failover with an RTO under 30 seconds
• Your database size makes log shipping or mirroring impractical for modern RPO requirements

Prerequisites Checklist

Before beginning the migration, verify the following:

1. Windows Server Failover Clustering (WSFC) is installed and configured across all nodes that will participate in the AG
2. SQL Server version parity: All nodes run the same SQL Server version and edition. Mixed versions introduce unpredictable behavior
3. Network bandwidth: Sufficient bandwidth between nodes for synchronous replication. For a 2 TB database with moderate write activity, plan for sustained 100+ Mbps between replicas
4. Shared nothing storage: Each node has its own storage. AGs do not use shared disks
5. Application connection strings are parameterized and can be updated without application redeployment (ideally via config or environment variables)
6. Backup strategy is documented and tested. You will need a full backup + transaction log backups during the migration

The 5-Phase Rolling Migration Strategy

Phase 1 — Shadow Node Preparation (Week 1–2): Provision the new node in the same datacenter. Install the same SQL Server version and cumulative update. Join it to the WSFC but do not add it to any AG yet. Validate network connectivity, DNS resolution, and service account permissions. This phase has zero production impact.

Phase 2 — Baseline Backup and Restore (Week 3): Take a full backup of the primary database and restore it on the secondary node with NORECOVERY. Follow immediately with transaction log restores to minimize the catch-up window. Schedule this during your lowest-traffic period — not because it affects users (it does not), but to minimize the log chain gap.

Phase 3 — AG Formation (Week 4): Create the Availability Group with the existing primary as the primary replica and the restored secondary as a synchronous-commit secondary. Create the AG listener with a dedicated IP and DNS entry, but do not update application connection strings yet. Monitor synchronization state until the secondary shows SYNCHRONIZED.

Phase 4 — Application Rerouting (Week 5–6): Update connection strings in a rolling blue-green deployment to point to the AG listener. Route read-only workloads to the secondary via ApplicationIntent=ReadOnly in the connection string. This is the critical phase — use connection pool drain sequencing to avoid failed transactions. A well-executed reroute should complete in under 15 minutes with zero error spikes.

Phase 5 — Validation and Decommission (Week 7–8): Keep the original primary running as a monitor node for at least 14 days. Validate failover behavior with controlled tests. Only decommission after you have confirmed clean failover and failback cycles.

The key to a zero-downtime cutover is Phase 4 sequencing. Drain each application server's connection pool before switching its connection string. Never switch all servers simultaneously.

Monitoring Setup

Post-migration monitoring is not optional. Use SQL Server's sys.dm_hadr_database_replica_states DMV as your primary data source, and feed metrics into your observability platform (Prometheus + Grafana, Datadog, or equivalent). Set alert thresholds at:

• Redo queue size > 512 MB: Indicates the secondary is falling behind on applying transactions
• Send queue size > 256 MB: Indicates network saturation between replicas
• Synchronization health != HEALTHY for > 30 seconds: Requires immediate investigation
• Failover detection: Alert on any automatic or manual failover event

Common Pitfalls

• Forgetting read-only routing: Without ApplicationIntent=ReadOnly configuration, all queries hit the primary, negating the read-scale benefit
• Skipping failover drills: Run quarterly failover tests. An untested HA setup is not an HA setup
• Ignoring transaction log growth: During Phase 2–3, the primary's transaction log cannot truncate past the oldest unrestored LSN. Monitor log file size and ensure adequate disk space
• Network latency between replicas: Synchronous commit adds latency to every write transaction. If cross-datacenter replication is required, consider asynchronous commit for the remote replica

A properly executed Always On migration transforms your database infrastructure from a fragile single point of failure into a resilient, self-healing system. The investment in careful planning across these five phases pays for itself with the first avoided outage.