August 26, 2011 at 3:38 am
Hi
We have a situation where we are using log shipping as technology for our remote site in Disaster Recovery Centre. We are backuping up every 5 minutes TRN logs and restoring into the secondary server. When we want to test the solution we are simply changing the roles - ba taking the last TRN backup on primary server WITH NORECOVERY and restoring into the DRC (Secondary) server, and it works fine. We are able then to switch the roles back and recover the primary db.
The problem what we have is with the transactional replication. We also have replication with another machine where we are pushing the transactions and when we are activating back the Primary node the replication is failing.
We are not interesting to enabled replication on the secondary server; we are leaving the distribution database on primary server, but at least to be able to continue working when primary database comes up again. It is not a must to work replication SECONDARYSRV – SUBSCRIBER only to continue working together with the transactions create on the secondary server from PRIMARYSRV – SUBSCRIBER.
We are getting the error below when restoring with recovery the primary server:
Error messages:
•The process could not execute 'sp_repldone/sp_replcounters' on 'SRVRAP'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
•Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. (Source: MSSQLServer, Error number: 18757)
Get help: http://help/18757
•The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
The process could not execute 'sp_repldone/sp_replcounters' on 'SRVRAP'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
We have marked the database as published, and then transactions that were created into the secondary server are not replicated to the subscriber.
Please suggest.
Regards
August 26, 2011 at 7:41 am
There are couple of gotchas in replication when failing over to secondary and failing back to primary.
1. You have to use KEEP_REPLICATION option when restoring log.
2. You might have to use synch with back up option. This one might not be needed in your case. Test this part out.
One draw back in using synch with backup option is that there will be high latency in your replication. The latency will be equal to the interval of your log shipping.
-Roy
August 26, 2011 at 7:55 am
Hi Roy,
I used once on the test environment KEEP_REPLICATION while restoring the log back into the Primary Server and it didn't worked.
When I used KEEP_REPLICATION on both sides - first Secondary and then Primary again it worked!
Thanks.
P.S. I have a case where I haven't used this command KEEP_REPLICATION and the database is recovered, is there any way that I can bring the replication up?
Regards
August 26, 2011 at 8:21 am
TBH, I am not sure if that can be done. I have not tried it. We have had our DB Server go down for an hour because of MSDB corruption. After restoring the MSDB our replication worked fine. But we have never tried moving to secondary and then bringing back the primary because we will lose way too much data. So cant say.
We have scripts that will drop replication and recreate everything from scratch. At that time we do not do any snapshots.
-Roy
August 31, 2011 at 6:06 am
shaban osmani-267622 (8/26/2011)
Hi Roy,I used once on the test environment KEEP_REPLICATION while restoring the log back into the Primary Server and it didn't worked.
When I used KEEP_REPLICATION on both sides - first Secondary and then Primary again it worked!
Thanks.
P.S. I have a case where I haven't used this command KEEP_REPLICATION and the database is recovered, is there any way that I can bring the replication up?
Regards
I'm really confused as to your setup, how many servers do you have? I "think" you have a dual role Publisher/distributor and a subscriber server. The Published database is logshipped to a DR server. Am I right?
So which server are you bringing online and breaking replication? The DR server doesnt appear to be replicated.
You can "rebuild" replication which I can help with provided the published data isnt being edited.
August 31, 2011 at 6:08 am
Also, is this in a development or test environment?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply