I'm a database developer, not a DBA, but the task has fallen to me to write a brief white paper on our options for configuring a database mirror for web service APIs to use as its source, in order to take load off our primary, transactional database.
I listen to the Brent Ozar podcasts, and I hear how "always on availability groups" are pitched as being the "right" answer for this. I also know that the older SQL Server "database mirroring" technology is officially deprecated.
So I suggested AGs, and our DBA seemed to be on-board, but then he came back and said that implementing this would require the creation of failover clusters and would require lots of extra $$$s and licenses.
I have tried to read the literature about how availability group licensing works, and I get lost very quickly.
Can anyone point me to a "SQL Server Replication for Dummies" type post that compares options and costs for building a second database replica/mirror that can be actively queried for reporting purposes, specifically:
- Nightly automatic backup/restore
- Log shipping (will that work here?)
- Database mirroring
- Always On Availability Groups
If so, I'd be forever grateful.
If it matters, our production DB version is SQL Server 2014 Enterprise Edition, but we will be updating to SQL Server 2017 Enterprise Edition this fall.
Thanks!
Best Answer
First on AGs. To have a readable AG replica, both the primary and secondary SQL instance must be Enterprise Edition. For SQL 2016 and before you also must have a Windows Server Failover Cluster (although it doesn't need any shared storage). So there's no extra hardware required; the Windows Servers just need to be able to talk to each other, and be in the same Domain.
For SQL 2017 and after you can create an AG replica without a Failover Cluster (and therefore without automatic failover). See Read-scale Availability Groups.
If your secondary SQL instance is not Enterprise Edition, you can use Backup and Restore (eg Log Shipping), or Transactional Replication to maintain a second, readable copy of your database. With any backup-based solution the replica will not be near-real-time, and you will have to briefly disconnect the readers to restore the latest backup.
With SQL 2016 and later, there are additional features to enable you to run reporting workloads directly on your production database. This uses features including, READ COMMITTED SNAPSHOT isolation, Resource Governor, Updatable Columnstore Indexes with Compression Delay, and optionally In-Memory OLTP, called collectively Real-Time Operational Analytics.