Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log Shipping Failover Test Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 3:11 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:03 PM
Points: 286, Visits: 579
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?
Post #1419754
Posted Thursday, February 14, 2013 2:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 6,416, Visits: 13,799
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"
Post #1419886
Posted Thursday, February 14, 2013 5:14 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:03 PM
Points: 286, Visits: 579
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?
Post #1419994
Posted Thursday, February 14, 2013 3:38 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:03 PM
Points: 286, Visits: 579
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?
Post #1420329
Posted Thursday, February 14, 2013 3:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 6,416, Visits: 13,799
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"
Post #1420331
Posted Thursday, February 14, 2013 4:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:03 PM
Points: 286, Visits: 579
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?
Post #1420334
Posted Thursday, February 14, 2013 4:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 6,416, Visits: 13,799
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"
Post #1420336
Posted Thursday, February 14, 2013 4:31 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:03 PM
Points: 286, Visits: 579
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?
Post #1420342
Posted Saturday, April 20, 2013 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 8, Visits: 399
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.
Post #1444721
Posted Monday, April 22, 2013 6:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 8:52 PM
Points: 14, Visits: 498
Because you do "Tail log backup on ServerA with "NORECOVERY"".


Post #1445232
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse