Starting Point
One of the country’s leading online marketplace platforms — with over 5 million monthly visitors and hundreds of thousands of active listings — ran its core database on SQL Server 2016 Standard Edition. The system was the single point of failure for the entire platform.
The risks were real:
- No high-availability setup: A single database server without failover. Any hardware failure meant complete platform outage
- Maintenance windows: Monthly patches required 2-4 hours of planned downtime — always at night, always with risk
- Performance limits: SQL Server 2016 Standard Edition was capped at 128 GB RAM and 24 CPU cores — the platform was approaching these limits
- Growing data volume: 2.8 TB database size with 15% annual growth
- No read scale-out: All read and write operations hit the same server — report queries competed with transactions
Challenge
The upgrade had to achieve multiple goals simultaneously:
- Zero downtime: The platform must not go offline during migration — not for a single minute
- High availability: Automatic failover on hardware failures in under 30 seconds
- Read scale-out: Redirect report queries to secondary replicas to offload the primary
- Maintenance without downtime: Patches and updates rolling, without platform interruption
- Data consistency: Synchronous replication for the primary replica, asynchronous for disaster recovery
Solution
Target Architecture
We implemented a SQL Server 2019 Always On Availability Group setup with three nodes:
| Node | Role | Location | Replication Mode |
|---|---|---|---|
| Node 1 | Primary | Data Center A | — |
| Node 2 | Secondary (Sync) | Data Center A | Synchronous |
| Node 3 | Secondary (Async) | Data Center B | Asynchronous |
- Node 1 + 2: Synchronous replication in the same data center — automatic failover in under 30 seconds
- Node 3: Asynchronous replication to a second data center — disaster recovery with RPO of a few seconds
- Read-only routing: Report queries automatically redirected to Node 2 or 3
Migration Plan
The migration from SQL Server 2016 to 2019 with simultaneous Always On introduction proceeded in 5 phases:
Phase 1: Infrastructure (2 Weeks)
- 3 new servers provisioned and hardened
- Windows Server Failover Cluster (WSFC) configured
- Network: dedicated VLANs for replication traffic
Phase 2: Side-by-Side Installation (1 Week)
- SQL Server 2019 Enterprise installed on all 3 nodes
- Compatibility level initially kept at 130 (SQL 2016) — for rollback safety
Phase 3: Data Migration (1 Week)
- Full backup of production database (2.8 TB)
- Restore on Node 1 (SQL Server 2019)
- Log shipping from old server to new — continuous synchronization
- Validation: checksum comparison of all tables
Phase 4: Cutover (4 Hours, Zero Downtime)
- Log shipping applied up to the last transaction log
- Application connection string switched to the new listener
- Always On Availability Group activated
- DNS switch to the new listener (TTL previously reduced to 60 seconds)
- Old server remained available for 72 hours as rollback option
Phase 5: Optimization (2 Weeks)
- Compatibility level raised to 150 (SQL Server 2019)
- Query Store activated for performance monitoring
- Read-only routing configured and tested
- Old servers decommissioned
Results
| Metric | Before (SQL 2016 Standard) | After (SQL 2019 Always On) |
|---|---|---|
| Availability | 99.7% (incl. planned downtime) | 99.99% |
| Failover time | Manual (30-60 min.) | Automatic (under 30 sec.) |
| Planned downtime | 2-4 hrs/month | 0 (rolling updates) |
| Report query performance | Baseline | +40% (read replica) |
| Unplanned outages (12 months) | 3 | 0 |
| Maximum RAM usage | 128 GB (limit) | 256 GB (Enterprise) |
| Disaster recovery RPO | Last backup (up to 24 hrs) | Few seconds (async replica) |
Lessons Learned
1. Raise Compatibility Level Gradually
We raised the compatibility level from 130 to 150 only 2 weeks after cutover — using Query Store to identify regressions. 3 queries showed worse plans and were manually optimized. Without this caution, we would have discovered performance issues in production.
2. Log Shipping as a Migration Tool
Log shipping isn’t just for disaster recovery — it’s the best tool for zero-downtime migrations. The new server stays synchronized at all times, and the cutover reduces to a DNS change.
3. Read-Only Routing Is Worth Its Weight in Gold
Redirecting report queries to the secondary replica improved primary performance by 40% — a side effect that became more important than the original migration goal.