4

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.

Improve this question

2 Answers 2

Reset to default
6

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).

Improve this answer
4

I would recommend you to use Always on availability group for server reasons.

  1. Database mirroring is deprecated as of SQL Server 2012 and will be removed on the future versions, so it is better to not use it on a new developments

  2. AOA group will give you a feature which you cannot get it on mirroring. Using AoA group, is like using a combination both clustering and mirroring.

  3. You can be able to query or perform reporting on from your secondary node which will minimize the load from a primary server.

  4. More managed failover. Unlike mirroring, on AoA you may not experience a single database failover.

  5. It has better performance and so on……

I do agree with you as you may need a little bit additional monitoring and setup tasks but it definitely worth it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.