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

3 Comments

  1. Gillian Ryder August 6, 2014

    Hi Rob, excellent summary – just what I was looking for! Thanks

    reply
  2. Nolan Le April 7, 2016

    Hi Rob,

    Thanks for posting. So in AG we cannot add extra tables, views and stored procedures?

    Thanks!

    Nolan

    reply
    • Rob Risetto April 7, 2016

      Hi Nolan,

      In an AG, you can add tables, views, stored procs etc to the primary database only which is then mirrored to the replica database. So in an AG primary and replica databases are always identical in structure. However, in transactional replication you can add extra objects (tables, indexes, stored, procedures) directly to the replicated database. So in TR the actual replicated objects are the same in master and replica, but in the TR replica you have any number of extra objects that are not in the master (publisher) database.

      regards
      Rob

      reply

Leave a Comment