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

Answer 1

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

Answer 2

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

Answer 3

Out put from query as requested -

allow_initialize_from_backup immediate_sync immediate_sync_ready
1 0 0

Thanks!

M