Setting up transactional push replication to multiple subscribers
-
quarta-feira, 30 de maio de 2012 08:48
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'
goand 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
Todas as Respostas
-
quarta-feira, 30 de maio de 2012 13:36
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'
goand 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
- Mesclado amber zhangModerator quinta-feira, 31 de maio de 2012 01:43
-
quarta-feira, 30 de maio de 2012 13:57
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
- Sugerido como Resposta amber zhangModerator quinta-feira, 31 de maio de 2012 01:43
- Marcado como Resposta amber zhangModerator sexta-feira, 8 de junho de 2012 01:41
-
quarta-feira, 30 de maio de 2012 14:10
Out put from query as requested -
allow_initialize_from_backup immediate_sync immediate_sync_ready
1 0 0Thanks!
M
.png)
