Convert Mirroring to Log Shipping

  • JeremyE

    SSCoach

    Points: 15443

    Comments posted to this topic are about the item Convert Mirroring to Log Shipping

  • Alistair Board

    SSC Journeyman

    Points: 86

    We have 2 servers that used to implement mirroring.

    In both access even though the mirroring was removed conventionally there appears to be a number of processes related to the mirroring that seem to persist.

    Notably the BROKER_TASK continues to be seen as a significant wait type when on similar systems which never had mirroring it is not seen at all.

    I have tried to help myself.

    On a test system I deleted broker related items and did remove the wait type and did not dame the server.

    However I was not comfortable or confident and was perplexed thinking that removing the mirroring should have put the system back to the sate it was in before mirroring was implemented.

    I am left feeling that the only way back to the pre-mirroring sate is to reinstall - tell me I am wrong, please.

  • JeremyE

    SSCoach

    Points: 15443

    You may have to delete the mirroring endpoint as well if there are no databases mirrored. There are various other internal tasks to SQL Server that use Service Broker also. For details on Service Broker waits see the blog below.

    http://blogs.msdn.com/b/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    Items 9 and 14 are missing from the screen shots. I assume you are referring back to the main 'Transaction Log Shipping' page in the properties view. It might be helpful to show the full screen shot. Thanks for the article.

    Can you please explain why you disabled the restore transaction log job, and when it would be necessary to leave it on?

  • JeremyE

    SSCoach

    Points: 15443

    The reason for disabling the restore transaction log job on the secondary was given in the 3rd paragraph of the article.

    "Once we convert the mirrored database to a log shipped database we can disable the restore job on the secondary and have the secondary available as a hot standby in case of an upgrade failure. This allows us to quickly backout of the upgrade by simply bringing the secondary online and pointing the application servers at it. Once the system is back online we can reestablish mirroring back to the original principal. If the upgrade is successful, we enable the restore job on the secondary, allow the secondary to catch up, and then reestablish mirroring."

    If you are not planning to use the secondary as a backout plan for a failed implementation then you can just enable the restore transaction log job and convert mirroring to log shipping.

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    So, this example is only intended for fail implementations? You plan on reverting back to mirroring when the implementation is complete?

    Or, did you mean to include a statement to come back later and turn on that job after the code implementation was complete?

    I guess what I'm not clear on is what the restore trans log job is for, in the big picture sense.

    After I have converted my system to TLS, is this restore TL job the only step I need to disable/enable for all future code implementation fail over tasks?

  • JeremyE

    SSCoach

    Points: 15443

    Dan Guzman - Not the MVP (8/19/2010)


    So, this example is only intended for fail implementations? You plan on reverting back to mirroring when the implementation is complete?

    Or, did you mean to include a statement to come back later and turn on that job after the code implementation was complete?

    Once the code upgrade completes successfully the LS restore job can be turned on to catch up the secondary. Then we can reestablish mirroring for high availability.

    The reason for switching to log shipping from mirroring is to have that "hot" standby copy ready on the mirror/secondary server which the application servers can point to. This brings the entire system back online faster in the event of a code upgrade failure. The traditional way of backing out of a code upgrade would be to restore the principal/primary database from backup. That may take several hours depending on the size of the VLDB creating a longer backout time.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the article. Good stuff.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • simon.murin

    SSC-Addicted

    Points: 449

    Well, wouldn't it be easier to suspend DB mirrorring session in this case?

    In case of upgrade failure: 1)turn off db mirrorring; 2) bring mirror db online.

    3 t-sql statements in total:

    1) ALTER DB SET PARTNER SUSPEND

    2) ALTER DB SET PARTNER OFF

    3) RESTORE DATABASE DB WITH RECOVERY

  • JeremyE

    SSCoach

    Points: 15443

    That should work as long as the transaction log on the principal is large enough to hold all the transactions during the upgrade.

  • Robert Davis

    One Orange Chip

    Points: 28027

    Interesting article. I've converted log shipping to mirroring lots of time but never considered any use cases for the reverse. Nice thoughts!!


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    So....

    Why not just fail over to the secondary full time, so that you can upgrade the primary offline. If all goes well, fail back to the primary, if not, the secondary keeps on running until the issues are resolved? No down time at all.

  • JeremyE

    SSCoach

    Points: 15443

    Dan Guzman - Not the MVP (8/20/2010)


    So....

    Why not just fail over to the secondary full time, so that you can upgrade the primary offline. If all goes well, fail back to the primary, if not, the secondary keeps on running until the issues are resolved? No down time at all.

    You would have to break mirroring or log shipping in order to have both primary and secondary databases online at the same time. How would you reconcile the difference between the primary which has the code upgrade applied and the secondary which has had user transactions running against it?

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    Duh. Yes, of course, you are correct.

  • sejal p gudhka

    SSCrazy

    Points: 2102

    Same also applies for LS in case of Recovery model is full, it can hold transactions till log file grows. Only +ve point of LS over log size is that it uses Bulk Load recovery model.

Viewing 15 posts - 1 through 15 (of 16 total)

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