Log Shipping Failover Situation

Hi All,

I am little confused in the way how log shipping has been chnaged in SQL Server 2005. I read the whole of articles in BOL to find that microsoft has not given any procedures to handle the failover situation and the role change job. From my understanding i shall ask some quick questions.

1. For the failover MS says that restore any unrestored log files from the primary database to the secondary database and bring the secondary database up. My Question here is. What if my last log file is restored or the server where the primary database resides is unavailable due to some hardware or OS failure?

2. I have brought my secondary up restoring the last log file from the primary server. Now MS says that to again change the primary server to what it was it says to backup the database in secondary and restore it in primary and reconfigure the logshipping. Is this true are there no other alternatives to this?

3. What happened to the old procedures that were available with SQL Server 2000 that we used to do the role change? I find they are not available in 2005.

looking for answers to enrich myself thanks for all.

Cheers,
Sugesh Kumar R. MCDBA

Theory is when you know something, but it doesn't work.
Practice is when something works, but you don't know why.
Programmers combine theory and practice: Nothing works and they don't know why.

Hi All,

Even I am interested to know the solution to this. Tried to find the good old role change procedures but to no avail.

Thanks in anticipation.

Amit

|||Sugesh has rightly pointed out ....it seems there is no specific role change procedure available in sql 2005 as in sql 2000...any valuable suggestion in this regard from the MVPs or any1 will be greatly appreciated...we are fed up searching.|||

well here's what I thought of...It is not comparable to what the Stored Procedures used to do in 2000 but I guess will server to some extent...

1. Backup the log of the primary database giving the file name a specific name and with no recovery.

2. This will put the original primary in Recovering mode which will take the t-log backups from the would be primary.

3. Create a job to copy this file over to secondary server (xp_cmdshell will have to be enabled for this)

4. Restore this log on the secondary database with recovery.

This will convert the secondary database into primary and primary into secondary and of course log shipping will have to be set up again in the reverse way.

Suggestions on this are most welcome and awaited.

|||yes what you said is possible but how can you apply tran logs from the new primary server to old primary? you can only do it by reconfiguring log shipping and its jobs.......but in sql 2000 you had those sps i.e sp_change_primary_role something like that....if your primary is down you can bring your secondary up and just use those sps to perform role change.....so that ur secondary has become primary and once ur primary server is up and running you can once again rerun those sps to make ur original primary as primary and original secondary as secondary.....but those sps seem to be depriciated......|||

Deepak,

I completely agree with what you have said and that's why in my post i said that it is not comparable to what the original procedures used to do.

But since they have been deprecated now, we have to find some work around.

-Amit

|||anyways Amit thanks for the alternative you provided...it was indeed useful...as sugesh has pointed out no sps are present sounds bizzare.......friends come out with your suggestions it will greatly appreciated.........|||I have contradicting views to what Amit and Deepak has said. Let me post the exact brief reply sooner. But my question stills remians to be unanswered?