Sql-server – Availability Groups and Transactional Replication

availability-groupssql serversql server 2014transactional-replication

My client has a setup as shown below.

AG Group A ———> Server A (DB–> A) —-> Server B ( DB–> A)

Transactional Rep –> Server A ( DB–>A ) —> Server C ( DB–> B)

AG Group B ———> Server C ( DB –> B) —> Server D ( DB –> B)

where Server A -> Database A is configured for Transactional replication to Server C -> Database B.

The Availabilty Group and replication are configured fine and the publisher is redirected to the Availability Group Listener.

The distributor database is hosted on Server C ( subscriber).
To top it all the subscriber is part of another AG group so it has its own secondary AG replica in Server D (Database B).

I know in this situation transaction replication is not required at all as we could open secondary replica for Read within Availability Groups. ( client – disagrees to change).

Question is as follows:

If The Subscriber ( server C) crashes along with the distribution database, what options are available to redirect replication to a new distributor on replica server D ? Assuming both AG are synchronous and can be failed over together.

Your help and advise is much appreciated.

Best Answer

Distribution database should not reside on the servers that are part of AlwaysON availability group that the publishing database is (or will become) a member of.

Replication configuration is coupled to the SQL Server instance where the Distributor is configured; therefore the distribution database cannot be mirrored or replicated.

If you want to provide HA for distribution database, then you have to go for SQL Server Failover cluster. Thats the only option.

Your scenario is as below :

enter image description here

So if you loose server C, the only option to get distribution running on server D is to do a RESTORE.. with KEEP_REPLICATION from a good backup. You can use this script to restore your distribution database (with some changes as per your environment). I would go for a clean install of replication !

Make sure you script out your replication topology whenever your do any changes. You should have handy scripts of both drop and create, so in a disaster situation, you have scripts that will help easily create replication.

Also, since you are using always-ON with T-Rep, I would suggest you to enable TF 1448.

Trace flag 1448 enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled,, the log reader always waits for the synchronous secondary replicas. The log reader will not go beyond the min ack of the synchronous secondary replicas. This trace flag applies to the instance of SQL Server, not just to an availability group, an availability database, or a log reader instance. This trace flag takes effect immediately without a restart. It can be activated ahead of time or when an asynchronous secondary replica fails.

Reference : Configure Replication for AlwaysOn Availability Groups