Sql-server – Replication Subscribers and AlwaysOn Availability Groups (SQL Server)

availability-groupsdistributed-databasessql servertransactional-replication

I have AlwaysOn Availability Groups configured with Replication Subscribers, and is currently running successfully with one publisher using the command below.

-- commands to execute at the publisher, in the publisher database:
EXEC sp_addsubscription @publication = N'yocc_pub', 
       @subscriber = N'repl_agl', 
       @destination_db = N'YOCCDB', 
       @subscription_type = N'Push', 
       @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;
GO

EXEC sp_addpushsubscription_agent @publication = N'yocc_pub', 
       @subscriber = N'repl_agl', 
       @subscriber_db = N'YOCCDB', 
       @job_login = null, @job_password = null, @subscriber_security_mode = 1;
GO

All commands complete successfully.

My first problem is "I want to add one more publisher " to the AlwaysOn subscriber but I am getting the error below:

sql server error-21488 sql server error-18752

Note:-

  • node-1 (alwayson primary node and replication subscriber)
  • node-2 (secondary replica and report server)
  • node-3 (publisher 1)/yocc_pub(work as distributor)
  • node-4 (publisher 2)/yocc_pub2(configure successfully but got error-21488,18752)
  • listener name – repl_agl
  • database – yoccdb

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 ??

Best Answer

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.

Related Question