Log Shipping Failover Test

  • 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

  • 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" 😉

  • 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.

  • 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.

  • 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" 😉

  • 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.

  • 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" 😉

  • 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.

  • 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.

  • Because you do "Tail log backup on ServerA with "NORECOVERY"".

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply