DBInsight's Blog

Transactional Replication VS Availability Groups for Read Only Workloads

The Transactional Replication (TR) is still one of my favourite parts of SQL Server, and provides some distinctive advantages over Always On Availability Group (AG) Read Only secondaries. The TR advantages include :-

  1. You don’t have to replicate everything when using TR, you can select tables, columns or even data subsets. You can also eliminate triggers, constraints and other definitions that add no value to a reporting implementation.
    (Always On Availability Groups mirrors everything in the database)
  2. TR sends deltas and therefore minimises data transfer traffic just like AG but TR has the flexibility of continuous or scheduled data transfers. AG mirroring has only continuous data transfer.
  3. TR allows you to move the reporting workload off to another server just like AG but
    • TR is available in SQL Server Standard Edition
      (AG is only available in the Enterprise Edition)
    • You can replicate to lower or higher edition/version of SQL Server instances
      (AG secondary servers can only be Enterprise Edition and same version)
  4. When using TR, you can add reporting specific indexes directly to the replicated (slave) database. With AGs you have to add all indexes to the Primary database.
  5. When using TR, you can add extra reporting objects (eg Summary tables, stored procedures, views, functions) directly to the replicated database.
  6. When using TR, you can run maintenance (index rebuilds) on the primary server independently of the replicated database. I.e. It doesn’t send a mass of transaction log changes to the replicated database. In addition, when using TR, you can run index rebuilds on the replicated database with impacting the Primary server.
  7. When using TR, you can configure database security on the replicated database independently of the primary database.

Posted in: Replication

Leave a Comment (3) ↓

Leave a Comment