SQL Server Performance

Role Change and FailOver

Discussion in 'SQL Server 2005 Log Shipping' started by Deepak Rangarajan, Jul 3, 2007.

  1. Deepak Rangarajan New Member

    Hi All,
    I am little confused in the way how log shipping has been changed 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?

    ---maybe i can restore with recovery to bring secondary online

    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.

    following links i have gone through,

    http://msdn2.microsoft.com/en-us/library/ms178117.aspx ---Role change in sql 2005

    http://msdn2.microsoft.com/en-us/library/ms191233.aspx ---Fail over in log shipping

    http://msdn2.microsoft.com/en-us/library/ms175475.aspx ---Troubleshooting Orphaned user

    looking for answers to enrich myself thanks for all.



    Regards
    Deepak
    SQL DBA
  2. satya Moderator

    1. If you have restored the transaction logs on the secondary server then you will be able to recover until that time. Say you have restored 11:30am log to secondary server, but primary crashed at 11:40 then you may lose the transactions that are committed on the primary server, still you can try brining up the primary server to see whether it can auto recover the transactions.

    2. Yes

    3. The articlehttp://msdn2.microsoft.com/en-us/library/ms178117.aspx you have mentioned deals with role changes.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Deepak Rangarajan New Member

    Hi Sathya,<br /> Really thanks for your prompt response..........few more doubts are there ! ! !<br /><br /<a target="_blank" href=http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx>http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx</a> ---SQL 2000 role change steps...<br /><br />if u observe the above links,you can see the following procedures namely,<br /><br />1.sp_change_primary_role <br />2.sp_change_secondary_role <br />3.sp_change_monitor_role <br /><br />but unfortunately no such procedures exist in SQL 2005 refer the available procs,<br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms175106.aspx>http://msdn2.microsoft.com/en-us/library/ms175106.aspx</a> ---SQL 2005<br /><br />refer sp_change_log_shipping_primary_database ---but it changes only the primary dbs settings ..........<br /><br />similarly <br />sp_change_log_shipping_secondary_database and <br />sp_change_log_shipping_secondary_primary are available but u can change roles.....sounds bizzare [<img src='/community/emoticons/emotion-6.gif' alt=':(' />!]<br /><br />also no procedures available for changing the monitors role.......[:0]<br />I personally feel that MS needs to enhance log shipping features in SQL 2005,coz u hav the role change features i.e SPs available in SQL 2000 but nothing in SQL 2005<br />usually in a latest version of a product they will enhance its features but in this case they seem to have demoted it is'nt it ???????<br /><br />Pls correct me if am wrong as i am only a newbie......[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /> <br /><br /><br /><br /><br /><br /><br /><br />Regards<br />Deepak<br />SQL DBA
  4. satya Moderator

    quote:After you have completed the steps above for the initial role change, you can change roles between the primary database and the secondary database by following the steps in this section. To perform a role change, follow these general steps:

    Bring the secondary database online, backing up the transaction log on the primary server with NORECOVERY.

    Disable the log shipping backup job on the original primary server, and the copy and restore jobs on the original secondary server.

    Enable the log shipping backup job on the secondary server (the new primary server), and the copy and restore jobs on the primary server (the new secondary server).
    THe specified SPs still exists on SQL 2005 too, check books online (updated) for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. Deepak Rangarajan New Member

  6. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] I should have corrected in stating it, those have been renamed as different as sp_change_log_shipping_primary_database <br />in 2005, the workout is same.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  7. Deepak Rangarajan New Member

    Hi Sathya,
    EXEC msdb.dbo.sp_change_primary_role
    @db_name = 'Pubscopy',
    @backup_log = 1,
    @terminate = 1,
    @final_state = 3,
    @access_level = 1

    is available in SQL 2000 for performing role change.......

    EXEC master.dbo.sp_change_log_shipping_primary_database
    @database = N'AdventureWorks'
    , @backup_directory = N'c:LogShipping'
    , @backup_share = N'\tribecaLogShipping'
    , @backup_retention_period = 1440
    , @backup_threshold = 60
    , @threshold_alert = 0
    , @threshold_alert_enabled = 1
    , @history_retention_period = 1440
    ,@monitor_server_security_mode = 1

    is available in SQL 2005 and it is only used to alter the primary db settings and not for role change.......refer
    http://msdn2.microsoft.com/en-us/library/ms187351.aspx


    Regards
    Deepak
    SQL DBA
  8. satya Moderator

    Im bit confused here, let me check back and will confirm here.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. Deepak Rangarajan New Member

    Sathya thanks for responding my queries patiently...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] <br />but i seriously feel MS has gone back and not provided any procedures......guys lets discuss this seriously ........cool<br /><br />Regards<br />Deepak<br />SQL DBA
  10. Deepak Rangarajan New Member

    Sathya,
    any update ? ? ? .......i am waiting for the solution......

    Regards
    Deepak
    SQL DBA

Share This Page