Transactional Replication 

Configuration is not that easy. Replication is always table level. 
Lot of maintenance work. 
There is a latency and latency may increase in busy system. 
Need to configure it for each table. 
Not a high availability option.
You can use it as a DR strategy. 


High Availability and Disaster recovery strategy.
Built on top of Windows Failover Clustering framework. 
You can group several databases together as Availability Group unit and then can created multiple secondaries (up to 8 in SQL Server 2016) for that AG unit. 
There is one primary for reads/writes and secondaries for read only. 
You can offload backups to secondary replicas.
Smallest unit of automatic failover is AG. 
Automatic load balancing.

The Transactional Replication (TR) 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.


Last modified: September 3, 2019



Write a Reply or Comment