SQL Server Performance

Log Shipping fail over ( How to ?)

Discussion in 'SQL Server Log Shipping' started by pradeep_adiga, Nov 17, 2005.

  1. pradeep_adiga New Member

    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
  2. simondm New Member

    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
  3. pradeep_adiga New Member

    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).
  4. simondm New Member

    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
  5. SQLDBcontrol New Member

    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
  6. biged123456 New Member


    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.
    Use Master
    IF (select count(*) from master.dbo.sysdatabases where name = 'DBname') > 0
    BEGIN
    --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
    GO


    --Run this portion after the users are disconnected using the section above.
    USE master
    GO
    Msdb..sp_change_secondary_role
    @db_name=DBname,
    @do_load=1,
    @final_state=1,
    @access_level=1,
    @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.
  7. pradeep_adiga New Member

    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

Share This Page