Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Any particular reason not to use Database Mirroring instead? –  Remus Rusanu Feb 28 '12 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. –  Daniel James Bryars Feb 28 '12 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. –  Eric Higgins Feb 28 '12 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. –  Daniel James Bryars Mar 1 '12 at 14:50
add comment

2 Answers

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.

share|improve this answer
    
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. –  Daniel James Bryars Mar 1 '12 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. –  Eric Higgins Mar 1 '12 at 16:55
add comment
up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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