We have two data centers with a 2 ms ping time between them connected by a site to site VPN. In the primary data center we have 2 servers, in the secondary data center we have a single database server. All server are identical in spec and configuration running Windows 2012 R2 and we have two SQL Server 2016 Enterprise licences. One for the main site + fail over and one for the secondary site.
The question is what technology should we use? always on availability groups with synchronous replication between the two nodes in the main data center and asynchronous replication to the secondary data center. The other option is to use mirroring in the main data center and then log shipping to the secondary data center. The only caveat is that we need to use transparent data encryption (TDE) hence the enterprise licences.
Our ops team is currently 2 people and growing but no fully assigned DBA as yet, I know how to setup both technologies but have only used mirroring and log shipping in production.
What would people recommend, the always on seems great but I have read some articles saying you need a lot of DBA resource to look after it where mirroring and log shipping is a lot easier.
Best Answer
If you were referring to Database Mirroring when you say mirroring (as opposed to SAN mirroring), then don't. It has been deprecated since 2012 now so it is a potential candidate for complete removal in the future releases.
Go with Always On Availability Groups. The difference in DBA overhead should be mostly the same. What is different, IMHO, is the learning curve. Log shipping is dead simple to learn, implement and operate. That's largely because it was originally created by MSIT DBAs (decades ago) as a simple, flexible DR solution which was subsequently productized. DB Mirroring applies the same concepts with a few powerful enhancements (e.g. shipping blocks instead of backups gives a sizeable perf boost).
Always On AG extends these concepts but added dependencies that users now need to learn to use and manage (e.g. WSFC). It's not rocket science but may take some effort to pickup if you're not already familiar with them. However, once you figure it out, the benefits are pretty significant (grouping resources, readable secondaries, much higher transfer perf are among the top ones).