SQL Server replication supports the automatic failover of the publisher, the automatic failover of transactional subscribers, and the manual failover of merge subscribers. The failover of a distributor on an availability database is not supported. In an AlwaysOn availability group, a secondary database cannot be a publisher. Re-publishing is not supported when replication is combined with AlwaysOn Availability Groups.
In this blog, I will explain the high-level steps to configure transactional replication for the database participating in Always on availability group(AAG).
- Source – SQL Server 2014 AlwaysOn with Active/passive synchronous replicas ( AAG Publisher)
- Target – SQL Server 2014 Replication Subscriber
- Distributor- SQL Server 2014, Remote distributor
Overview of the setup
The following sections build the environment described above:
– Configure a remote distributor
– Configure the Publisher at the original Publisher
– Configure Remote distribution on possible publishers
– Configure the Secondary Replica Hosts as Replication Publishers
– Redirect the Original Publisher to the AG Listener Name
– Run the Replication Validation Stored Procedure to verify the Configuration
– Create a Subscription