SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Shipping Failover Test


Log Shipping Failover Test

Author
Message
MissTippsInOz
MissTippsInOz
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 Visits: 597
Hi

I'm currently running some DR testing and have failed over a couple of databases to our DR site. Generally the process was:

Tail log backup on ServerA with NORECOVERY
RESTORE with RECOVERY on ServerB
Ran sp_delete_log_shipping_primary_secondary and sp_delete_log_shipping_primary_database on ServerA
Created reverse log shipping on ServerB to log ship back to ServerA
All looks good and functions.....
BUT
I've got a log full of 927 errors on ServerA from 'sa': "Database 'MyDB' cannot be opened. It is in the middle of a restore."

What is trying to access it? Have I not successfully 'killed' log shipping on ServerA?

I'm sure (hopeful) this is stupid simple and I'm just being brainless.

Thanks

Clare

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56623 Visits: 17740
MissTippsInOz (2/13/2013)
Hi

I'm currently running some DR testing and have failed over a couple of databases to our DR site. Generally the process was:

Tail log backup on ServerA with NORECOVERY
RESTORE with RECOVERY on ServerB

Correct up to and including this point. When failing over Log Shipping scenarios, consult the following documentation

http://msdn.microsoft.com/en-us/library/ms178117(v=sql.105).aspx

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
MissTippsInOz
MissTippsInOz
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 Visits: 597
Sorry I gave general process when I should have been specific. All steps in that article are covered. Databases all appear in the correct log shipping system tables and don't appear anywhere they shouldn't. As far as everything I know and can see goes, I am running with reverse log shipping and only reverse log shipping. And yet..every two minutes I get the error in the log.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
MissTippsInOz
MissTippsInOz
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 Visits: 597
Just thought I'd add that I failed back this morning using exactly the same process and I'm not now seeing these errors occur on the secondary. Suspect that the errors may have been unrelated to log shipping in the first place, but still not got to the bottom of it.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56623 Visits: 17740
The link I posted does not tell you to run sp_delete_log_shipping_primary_secondary or sp_delete_log_shipping_primary_database for the role reversal!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
MissTippsInOz
MissTippsInOz
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 Visits: 597
You're absolutely right; it doesn't. However I did, and still believe its necessary, and ran them again on failback and have no problems so, rightly or wrongly, it doesn't help in getting to the bottom of my issue.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56623 Visits: 17740
MissTippsInOz (2/14/2013)
However I did, and still believe its necessary

The idea is to keep the 2 way configuration in place as the document states.

From the error it sounds like an agent task is trying to access the new secondary database. Can you post more detail from the logs please.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
MissTippsInOz
MissTippsInOz
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 Visits: 597
Literally all I have is a SQL Error Log full of:
Database 'DBname' cannot be opened. It is in the middle of a restore.
Error: 927, Severity: 14, State: 2.

I see nothing when I run profiler, no stored procedure calls just that exception approximately every 2 minutes. As I mentioned in my previous post I'm wondering if it is, indeed anything to do with log shipping. As far as both servers were concerned, log shipping was correctly configured in one direction with SPs and reports showing expected results. Having failed back I'm not now having this issue on the new (original) secondary despite following exactly the same process.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
louislamour2673
louislamour2673
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 512
Last night while migrating a 2TB database we came across the same issue:

Database 'DBname' cannot be opened. It is in the middle of a restore.
Error: 927, Severity: 14, State: 2.

A system process, SPID 46, with sa login and task as DBMIRROR was "connected" to the old database that is in restoring mode. How does that happen when I put it into Single User and then did a TAIL backup of the log and put it into restoring mode? Every two minutes in the error log SPIDS 14, 15, 16, 17 alternately keep trying to open the restoring database and generating that error. The migrate strategy we used was database log shipping to the new disk and last night we brought the secondary online as primary. This database also was mirroring to a partner server, I removed that at the very beginning. These are the steps taken to migrate the database:

--ensure most recent log shipped backup was restored
--via GUI remove MIRRORING from the primary
--put in SINGLE_USER mode
--via GUI, change name of db from Primary to Primary_OLD
--take TAIL of log backup, Primary_OLD goes into restoring mode
--restore TAIL to Primary_NEW WITH RECOVERY
--via GUI, rename Primary_NEW to Primary
--put back into MULTI_USER mode

So it is now successfully working on the new disk, but we have the lingering error every two minutes as noted. We have checked all the mirroring monitors/tables and even ran ALTER DB...PARTNER OFF etc and it all states there is no mirroring configured.

This will be resolved when we decide to drop the Primary_OLD database...but it is a little bothersome seeing that connection and the open errors when they should not be happening, would be nice to know what is causing this. Thanks.
pan123au
pan123au
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 498
Because you do "Tail log backup on ServerA with "NORECOVERY"".



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search