3

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.

  • Is database A (on server A and Server B) same as database on Server C ? Meaning is A and C having same data, since C is subscriber of A ? or you are just replicating only few tables from A to C ? – Kin Shah Sep 16 '15 at 16:11
  • C has more tables but subscribes to all tables from A. – Nazeer Syed Sep 16 '15 at 16:13
3

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

  • Hi Kin, Thanks a lot for your quick response and sharing your knowledge. Especially the link with script to move the distributor database. Is it an added advantage to move the distributor to a seperate instance ? - Thanks – Nazeer Syed Sep 16 '15 at 18:21
  • @NazeerSyed it really depends, since if your server C is lost, then the likelyhood of bringing a new server and naming it as server C with same IP would take longer than to restore distribution database to a different server and setup replication. – Kin Shah Sep 16 '15 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.