Post reply

Log Shipping Role Change Question

  • May 27, 2004 at 1:46 pm

    #85802

    We are testing a Log Shipping Role Change, and I am following the instructions in the Sql BOL (do a search on Role Change). The instructions are very straight forward, but I notice that the instructions assume that both your primary and secondary servers are up and operational. In a real disaster the primary would be destroyed and you would only have your secondary to promote to the primary position.  That being said, if my primary had been destroyed I assume I would only need to:

    • I can't run sp_change_primary_role on the old primary server because it has been destroyed.
    • I should run sp_change_secondary_role on the old secondary server to make it the primary.
    • I also suppose I wouldn't need to run sp_change_monitor_role because since I only have 1 server in a log shipping pair, I no longer have anything to log ship to, right? So if I'm not log shipping I don't need a monitor.
    • Then I resolve my logins.

    Would that give me a new functional primary server that is no longer in load status and read only status?

    Anyone else have any different ideas or anything else I should think of? For example what if I was provided a new secondary server that only had sql installed. I'm losing my mind thinking of the possibilites!

    Thanks!

  • May 28, 2004 at 7:53 am

    #508200

    I can sympathize with your confusion.  IMHO I believe MS blurred the lines between disaster recovery and re-implementing log shipping "the other way" to your replacement ("original primary") server.

    You are right in your bullet points.  You should only need to promote your secondary to primary, sync logins and (possibly) change your apps to point to the new server or change your secondary's IP address to assume the old primary's address.

    The second step - after replacing the destroyed ex-primary - would be to implement log shipping in the other direction and eventually have the new hardware assume the primary role again. 

  • May 28, 2004 at 1:00 pm

    #508274

    --5/28/2004 1:48PM

    --Clark Cruz

    There are two situations:

    1. Planned Failover

    2. Unplanned Failover

     

    Unplanned Failover

    In an unplanned role change like in the case of site destruction, recent transactions that are not backed up and copied to the standby server before the primary server fails are lost.  The goal here is to recover the secondary database as soon as possible.

    To promote standby server to primary when the primary server fails or is taken offline, disable the database restoration job on that server, execute the role change stored procedures, synchronize SQL Server logins and standby database user accounts, and then use the standby database as the new production database, and then redirect clients to the promoted standby server.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply