SQL Server Performance Forum – Threads Archive
Log Shipping fail over ( How to ?)Hi I am Pradeep. I have seen the posts on this site and they’ve been of immense help to me. I am using SQL Server 2000 Enterprise Edition. I’ve two servers in which I have configured Log shipping. The database in the secondary server will be in "Standby" mode even after removing the log shipping. I don’t know how to remove the read-only attribute from it. However I have detached the database and re-attached with the same files. Now the database is ready and working fine. However I feel that this must not be the correct fail over procedure. I reuqest u to please suggest me how to make the database in the Secondary server active when the primary server fails.
One more request. While doing the log shipping which would be the best option to select (No-recovery or Standby). Please help me . Thanks in advance, Regards, Pradeep
The correct way of doing it is using these proc’s: sp_change_primary_role
sp_change_secondary_role Use Books online for more information on how to use them. Alternatively a quick way to recoover the standby database is to run this: RESTORE DATABASE [MyDB] WITH RECOVERY
Thanks a lot for the reply. I will try the options which u have suggested. Can u please tell me while configuring the log shipping which would be the best option to select (No-recovery or Standby).
I usually choose Standby because between log restores I can access the database in read-only mode (which is sometimes handy if I want to check something). With No-Recovery you can’t access the database at all – it will always show as loading. I don’t know of any other differences. Simon
The only thing I’d add to Simon’s post (Hope you don’t mind Simon) is that if you use STANDBY mode, make sure you specify that users should be kicked off prior to the restore job running otherwise the restore job will fail because it cannot get exclusive access to the database. Karl Grambow www.sqldbcontrol.com
Here is some scripting I did to help in automating the process… –Run this portion first. It is used to disconnect all users from the database
–prior to running the role change. Users in the database can cause the sp_change_secondary_role
–procdures to fail. Exit SQL Enterprise manager before running the script to
–ensure the DB is not being used.
IF (select count(*) from master.dbo.sysdatabases where name = ‘DBname’) > 0
–Remove DB access
PRINT ‘ATTEMPTING TO DISCONNECT AND ROLLBACK TRANSACTIONS’
PRINT ‘Taking Database DBname Offline’
Exec(‘alter database DBname set offline with rollback immediate’) PRINT ‘Setting DBname into DBO Use Only’
exec sp_dboption DBname,’dbo use only’,true PRINT ‘Setting DBname database back online’
Exec(‘alter database DBname set online’) PRINT ‘Setting DBname back to Read Only’
Exec sp_dboption DBname,’read only’,true END
–Run this portion after the users are disconnected using the section above.
@terminate=0 –This is the 3rd section to run. It will bring the database out of read only mode.
–The database should now be production read. Open Enterprise manager and confirm.
PRINT ‘Setting DBname back to Read Only’
Exec sp_dboption DBname,’read only’, false —
I suppose some error handling would be nice to add, but at least this is a start. Also note the role change options I use may not suit everyone. As Simon has noted, see books online for options.
Its really nice to see your replies. It has been of great help to me. Hope you keep posting such good suggestions. Thanks and Regards, Pradeep