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.