3

I'm using SQL Server 2012 and trying to implement transactional replication. Im using the system stored procedures to create the publications and subscriptions. I was successful in creating these things, but when i check the replication monitor, it shows "Uninitialized subscription".

When I check the synchronization status on the subscription, i found this log

Date 6/20/2012 7:36:33 PM Log Job History (HYDHTC0131320D-PublisherDB-PublicationOne-HYDHTC0131320D\MSS-ReplicationSubscri-7C1D7509-C8A6-4073-A901-0433A2B6D2D3) Step ID 1 Server HYDHTC0131320D\MSSQLSERVER2 Job Name HYDHTC0131320D-PublisherDB-PublicationOne-HYDHTC0131320D\MSS-ReplicationSubscri-7C1D7509-C8A6-4073-A901-0433A2B6D2D3 Step Name Run agent. Duration 00:07:41 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message 2012-06-20 14:14:13.986 Copyright (c) 2008 Microsoft Corporation 2012-06-20 14:14:13.986 Microsoft SQL Server Replication Agent: distrib 2012-06-20 14:14:13.986 2012-06-20 14:14:13.986 The timestamps prepended to the output lines are expressed in terms of UTC time. 2012-06-20 14:14:13.986 User-specified agent parameter values: -Publisher HYDHTC0131320D -PublisherDB PublisherDB -Publication PublicationOne -Distributor HYDHTC0131320D -SubscriptionType 2 -Subscriber HYDHTC0131320D\MSSQLSERVER2 -SubscriberSecurityMode 1 -SubscriberDB ReplicationSubscriberDB -Continuous -XJOBID 0xDFE51AEC7F9E3F42A450CE8874B662CD -XJOBNAME HYDHTC0131320D-PublisherDB-PublicationOne-HYDHTC0131320D\MSS-ReplicationSubscri-7C1D7509-C8A6-4073-A901-0433A2B6D2D3 -XSTEPID 1 -XSUBSYSTEM Distribution -XSERVER HYDHTC0131320D\MSSQLSERVER2 -XCMDLINE 0 -XCancelEventHandle 000005F8 -XParentProcessHandle 00000560 2012-06-20 14:14:13.986 Startup Delay: 619 (msecs) 2012-06-20 14:14:14.606 Connecting to Subscriber 'HYDHTC0131320D\MSSQLSERVER2' 2012-06-20 14:14:14.656 Connecting to Distributor 'HYDHTC0131320D' 2012-06-20 14:14:14.671 Parameter values obtained from agent profile: -bcpbatchsize 2147473647 -commitbatchsize 100 -commitbatchthreshold 1000 -historyverboselevel 1 -keepalivemessageinterval 300 -logintimeout 15 -maxbcpthreads 1 -maxdeliveredtransactions 0 -pollinginterval 5000 -querytimeout 1800 -skiperrors -transactionsperhistory 100 2012-06-20 14:14:14.683 Agent message code 21040. Publication '' does not exist.

How do I solve this issue?

Improve this question

4 Answers 4

Reset to default
3

I received the same error. My fix was to explicitly define the Job_login and job_password which I had as null to start with.

EXEC sp_addpullsubscription_agent @publisher = @publisher, @publisher_db = @publicationDB, @publication = @publication, @distributor = @publisher, @job_login = $(Login), @job_password = $(Password);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

It seems there is an error in your replication setup scripts.

I suspect the error is in the call to sp_addpushsubscription_agent (if it is push subscription) or sp_addpullsubscription_agent (if it is pull subscription). Specifically the @publication parameter is wrong as the Distribution Agent is stating that the specified Publication " does not exist.

Please review your script and try again.

Improve this answer

1 Comment

ya you are rite, i still could not solve the issue, so moved to push publication. thanks for you answer
1

I faced same problem and fixed by doing the following :

  • -Subscriber Job owner is same as Publication User

  • -Subscriber User has been added to the subscriber users list and added to the sysadmin server role

Improve this answer

Comments

0

Just a few notes as I managed to get a pull subscriber up and running with a backup initialisation:

  1. Make sure you have the agent account of the publisher, distributor and subscriber as sysadmin logins across all instances, example subscriber must be on the distributor and publisher.
  2. Create linked servers for all components on each instance, example distributor and publisher on the subscriber.
  3. Then take a full backup of the source database and restore without recovery on the subscription instance.
  4. Create the publication component on the publisher database (sp_addsubscription) and make sure you have @sync_type = N'replication support only'.
  5. Then take a diff backup of the source database and restore with recovery on the subscription instance.
  6. Then create the pull subscription on the subscription instance (sp_addpullsubscription), note that if the source is a mirrored instance, use the original instance value here (@publisher). If you the principal on the "mirror" instance, meta data will be created correct and the distributor contains the logic to connect the log reader to either of the mirror instances.
  7. Now you will have a problem with primary keys etc failing as the distributor started capturing data before the differential backup ... no problem. Stop the subscriber job and then add a new profile to the distributor agent with setting the -SkipErrors parameter to "2601:2627". This will skip all primary key violation transactions and continue processing. Remember to select this agent profile then and hit "OK".
  8. Restart the subscription job and monitor the subscription as it start to catch up on the transactions.
  9. When caught up, stop the job, change the agent profile back to the default and restart the job.

Hope this help anyone still struggling with pull subscribers based on backup initialisation ... note that at no point did I use any backup configurations within the replication configuration. Also stated that the pull subscription must not initialise (@immediate_sync = 0).

Here is the scripts:

-----------------BEGIN: Script to be run at Publisher 'DB001\OLTP'----------------- use [DB1] go exec sp_addsubscription @publication = N'DB1', @subscriber = N'DB002\OLTP', @destination_db = N'DB1', @sync_type = N'replication support only', @subscription_type = N'pull', @update_mode = N'read only' GO -----------------END: Script to be run at Publisher 'DB001\OLTP'----------------- -----------------BEGIN: Script to be run at Subscriber 'DB002\OLTP'----------------- use [DB1] exec sp_addpullsubscription @publisher = N'DB001\OLTP', @publication = N'DB1', @publisher_db = N'DB1', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0 exec sp_addpullsubscription_agent @publisher = N'DB001\OLTP', @publisher_db = N'DB1', @publication = N'DB1', @distributor = N'DB003\DIST', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20170327, @active_end_date = 99991231, @alt_snapshot_folder = N'\\DB001\Replication', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0 GO -----------------END: Script to be run at Subscriber 'DB002\OLTP'-----------------
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.