If you’re still on SQL Server 2008/2008 R2, you’re missing out on some great features. It’s no secret that Availability Groups are my favorite feature since SQL Server 2012.
Back in the day, I was supporting complex environments with aggressive SLAs. On one particular system, I had Failover Clustering for HA, Asynchronous Database Mirroring for DR and Transactional Replication for reporting. Management and users were constantly complaining about the data latency in their reports. The distribution cleanup job was struggling to keep up, so I was having to manually clean this data up. Due to the database design, all articles were in one publication. Initializing the subscriber took 5-8 hours and caused blocking on the publisher for 30-60 minutes. When we finally upgraded to SQL Server 2012, I was able to reduce the complexity of the environment by using Availability Groups for our HA, DR and reporting needs.
Transactional Replication is a great tool for your reporting needs, but it does have its downsides. Latency and troubleshooting are at the top of that list.
Availability Groups certainly has its downsides too. It gets better with each new version – more replicas and stability in 2014, Basic Availability Groups (Standard edition) in 2016, Distributed Availability Groups in 2017 and more synchronous replicas in 2019 (not sure why you’d want this though).
If you’re struggling with Transactional Replication like I was and are using Enterprise edition, it’s time to plan an upgrade.
