1

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

1

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.

  • I already define identity column/id like below publisher 1 - seed value = 1,increment by = 2,publisher 2 - seed value = 2 increment by = 2 and it will work good.then i need to add a unique field (such as servername, etc) or not ? – Gulrez Khan Jul 26 '16 at 5:22
  • If you're using the identity field like that (evens and odds) then you shouldn't need anything else but will be limited to just the 2 publishers. – Sean Gallardy Jul 26 '16 at 6:33
  • yes you are right...but when i add third publisher in future then i planned for some step like below publisher 1 - seed value = 1,increment by = 3,publisher 2 - seed value = 2 increment by = 3,publisher 3 - seed value = 3 increment by = 3 Is i am going right way..? – Gulrez Khan Jul 26 '16 at 8:24
  • What will you do with all of the current identity values? Your best solution is to add the name of the source system as part of the uniqueifier on the source system. Then you don't run into these issues. – Sean Gallardy Jul 26 '16 at 21:06
  • yes this is the exact stage where i am confused..please explain me with a example to how to implement "name of source system as part of uniqueifier on the source system". – Gulrez Khan Jul 27 '16 at 6:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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