Sql-server – Expanding AlwaysOn Availability Groups with Replication Subscribers

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 :

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

The first problem:

Error 21488

No active subscriptions were found. The publication must have active subscriptions in order to post a tracer token.

This happens when attempting to put in a tracer token without an active subscription. This error seems pretty obvious.

Error 18752

Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.

Make sure there are no other logreader agents running for the same publishing database.

Second part:

Second problem- How to maintain primary key value to avoid conflict between publisher 1 and publisher 2 to sync with node-1 because database are same ??

You'll either need to add a unique field (such as servername, etc) or configure some way of making each record unique. Generally, if not designed for at first, will require a schema change. Commons solutions are to use even/odd numbering in identity columns/id fields or using the source host name/database name.