4

I have a primary database in Amsterdam (AMDB), and a secondary in London (LNDB). I want to fail over from AMDB, to LNDB and then back again, without having to make another full backup (because the database is too large).

According to Changing Roles Betweeen Primary and Secondary Servers, and Failing Over to a Log Shipping Secondary this all looks possible. I've never done it before and I'm going to be doing it in production this weekend. (I'll be doing some test runs on non prod databases before hand.)

My question: Should I be doing anything else over and above the Microsoft documentation to ensure success? (I'm wondering if anyone else has some gotya's or extra steps they take.) I'm hoping for a reassuring answer such as "Yes this works every time we do it in production and it's cool" but will be very happy also with "No don't do that, make sure you do x/y/and z instead."

Edit: Please don't recommend I use Mirroring as the answer to this question - yes that's a great idea but it doesn't work for us because of licensing costs. We use standard edition (and this only supports Mirroring in synchronous mode). I would like to know if anyone has followed the steps in the MS documentation in prod or if there's a better alternative which an "experienced" dba would use.

4
  • 2
    Any particular reason not to use Database Mirroring instead? Feb 28, 2012 at 17:24
  • Its just that it's always been logshipped to the secondary; we haven't embraced mirroring yet. We'll definitely be getting involved with Mirroring soon and/or holding off until Denali to see what that offers. Feb 28, 2012 at 18:45
  • I'm with Remus. Log shipping doesn't have any inherent fail over/back functionality, requires manual steps. Mirroring does have this built in. Feb 28, 2012 at 21:03
  • @Eric. Thanks for the advice, but I've just checked and the reason why we use logshipping is because it's prohibitively expensive for us to use Mirroring; we'd need to upgrade from Standard to Enterprise because we need to run Mirroring in Asynchonous mode and Standard only supports Synchronous. Mar 1, 2012 at 14:50

2 Answers 2

2

For built-in failover / failback functionality, choose DB Mirroring over log shipping:

http://technet.microsoft.com/library/bb934127.aspx

You can follow a process to do this with log shipping, but manual steps are required. Mirroring is the best choice.

2
  • Fair point, but we use SQL Standard, and it's very expensive to use Enterprise. Mirroring is supported in Standard - BUT only in Synchronous Single thread mode. We would absolutely need asynchonous to use Mirroring. So it's a manual step for me. Any advice on the original question - all my testing seems fine so far. Mar 1, 2012 at 14:47
  • Ah I see, yes async mirror is usually the preferred way to implement mirroring. Apps having to wait for the sync is a perf killer. I'll jog my brain to see if I can come up with any other ideas for what you are planning. Mar 1, 2012 at 16:55
1

To answer my own question - Yes: the process as described in the links above worked well.

I did find that doing a tail log needed exclusive access to the database and I couldn't back up the tail log if the database was in single user mode. This was a problem in production because "all sorts" of things were connecting to the DB. The script I adopted below worked for me - it does a quick shuffle between single user and multiuser mode before the backup (note the use of "With Standby" to make the database queryable as well as ready for more logs in the fail back.)

--BACKUP TAIL    
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MyDatabase] SET MULTI_USER
BACKUP LOG [MyDatabase] TO DISK = 'C:\TransactionLogShare\TailLog2.trn' WITH STANDBY = 'C:\Standby\myDatabase.bak'

Then to bring it online on the other side was fairly straight forward:

--RESTORE TAILLOG
ALTER DATABASE [Liquid] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE LOG [Liquid_Logshipped] FROM DISK = 'C:\TransactionLogs\taillog.trn' 
ALTER DATABASE [Liquid_Logshipped] SET MULTI_USER

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.