none
Setting up transactional push replication to multiple subscribers

    Question

  • I currently have a publisher / distributor setup with transactional push replication to a database on a web server.  I am trying to introduce a second subscriber server via backup initialization.  I backup the database, restore it to the 2nd server and run the following script at the publisher / distributor


    exec sp_addsubscription @publication = N'WWW_Staging_Replication',
    @subscriber = 'WEB02', 
    @sync_type = N'initialize with backup',
    @backupdevicetype='Disk',
    @backupdevicename='G:\Backup\backup.bak'
    go

    and I get the follow error-

    Job 'SQL01-xxx-www_Staging_Replicati-WEB02-14' started successfully.

    Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.

    Msg 21397, Level 16, State 1, Procedure sp_MSsetupnosyncsubwithlsnatdist, Line 248

    The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup.

    The Subscriber was dropped.

    I disabled the distribution history cleanup job, and run the whole process again from backup - restore - run script and I still get the above error message.

    Are there other jobs I should disable for attempting to introduce a second subscriber?

    Kind Regards

    M

    Wednesday, May 30, 2012 8:48 AM

Answers

All replies

  • I currently have a publisher / distributor setup with transactional push replication to a database on a web server.  I am trying to introduce a second subscriber server via backup initialization.  I backup the database, restore it to the 2nd server and run the following script at the publisher / distributor


    exec sp_addsubscription @publication = N'WWW_Staging_Replication',
    @subscriber = 'WEB02', 
    @sync_type = N'initialize with backup',
    @backupdevicetype='Disk',
    @backupdevicename='G:\Backup\backup.bak'
    go

    and I get the follow error-

    Job 'SQL01-xxx-www_Staging_Replicati-WEB02-14' started successfully.

    Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.

    Msg 21397, Level 16, State 1, Procedure sp_MSsetupnosyncsubwithlsnatdist, Line 248

    The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup.

    The Subscriber was dropped.

    I disabled the distribution history cleanup job, and run the whole process again from backup - restore - run script and I still get the above error message.

    Are there other jobs I should disable for attempting to introduce a second subscriber?

    Kind Regards

    M

    Wednesday, May 30, 2012 1:36 PM
  • You should not have to disable any jobs. In fact enable the jobs and let them run for a bit, before taking another backup and trying again.

    A couple of questions - can you run this in your publication database for me?

    SELECT allow_initialize_from_backup, immediate_sync,immediate_sync_ready FROM dbo.syspublications


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, May 30, 2012 1:57 PM
  • Out put from query as requested -

    allow_initialize_from_backup immediate_sync immediate_sync_ready
    1 0 0

    Thanks!

    M

    Wednesday, May 30, 2012 2:10 PM