locked
Is a standby SQL Server required when using BizTalk log shipping? RRS feed

  • Question

  • Hi!

    I am trying to get my head around BizTalk log shipping, and I am a bit confused on how it really works. Is it sufficient to write the logfiles to a fileshare, and in case of database disaster set up a new SQL server and then use the log files to restore the state of the database? Or do I need an installation of SQL Server that is standby and receiveing the logfiles in order to restore it?

    Thursday, December 3, 2015 9:01 AM

Answers

  • There is a distinction between High Availability (HA) and Disaster Recovery. High Availability in case of BizTalk SQL is achieved through the use of MS SQL Clustering (so if one server fails or requires H/W updates/patches, the services are failed over onto the other [passive] server).

    Disaster recovery is across sites and more associated with Business Continuity as opposed to HA.

    You can choose to configure only the Log Shipping portion. This ensures that there is ONE Complete Backup taken per day (00:00:00 Hours UTC) and then log shipping at regular intervals. You could choose to backup the last know good backup and subsequent logs over tape daily as a backup/restoration strategy where you restore the last know backup and then apply logs manually till the required interval.

    Also it would depend on what type of integration has been deployed over your BizTalk environment. If you deal with Long-running processes then there is a orchestration persistence across multiple business days which would be critical during a failure.

    Regards.

    • Marked as answer by Angie Xu Thursday, December 24, 2015 11:18 AM
    Thursday, December 3, 2015 10:06 AM
  • So, the only difference in using only the Backup Biztalk server job and log shipping is the configuration for log shipping done on the destination server? The restoration process seems to be exactly the same?

    #1, Yes.

    #2, At the SQL level, yes.  The database just see restores.  The difference being the continuously automatic part.

    A word of advice, you have to practice this a few times and write up some instructions.  It's not really complicated, but there's a lot of steps.

    • Marked as answer by Angie Xu Thursday, December 24, 2015 11:18 AM
    Friday, December 4, 2015 11:55 AM
    Moderator

All replies

  • Hi Jose ,

    Yes you require to have a disaster SQL server(stand by) in place in the network and both SQL server should be enabled to do MSDTC transaction .

    Both the sql server should beof same version . Do check DTC transaction through DTCPing and DTC Tester

    Its been very well explained in MSDN documentation as well as some personal blog

    Refer TechNet article http://social.technet.microsoft.com/wiki/contents/articles/5363.biztalk-server-disaster-recovery.aspx?tduid=(1b3adfaef23435ef570d330ec7fdbd76)(256380)(2459594)(TnL5HPStwNw-60EosUXNOMAUQr.cgqSSGg)()

    MSDN blog post http://blogs.msdn.com/b/biztalknotes/archive/2012/01/10/biztalk-log-shipping.aspx

    And http://gautambiztalkblog.com/2015/06/02/biztalk-server-disaster-recovery/

    Thanks

    Abhishek


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Thursday, December 3, 2015 9:17 AM
  • The later option is a better and preferred option.

    BizTalk Log shipping has two components. The first one being the shipping of logs which as you put it, ends in writing of logs to a file share. The second portion is the application of the logs on the DR setup. This is listed under configuration of the recovery environment and creates the jobs necessary to retrieve the logs shipped for the database and its application on the recovery instance. As part of BizTalk DC/DR Setup and Recovery Procedure your should have a standby environment setup to receive and apply the shipped logs from the primary environment.

    Regards.


    • Edited by Shankycheil Thursday, December 3, 2015 9:26 AM edit
    Thursday, December 3, 2015 9:21 AM
  • Thanks for the quick response!

    I was thinking about how you do this with a customer that does not have demands for high availability and with a small budget: could you not set up a new SQL server and configure it for log shipping after the disaster has happened, if you have the log files in a safe place? Or, is it better in this case not to use log shipping at all, since they do not need to preserve the state of the databases? And if one does not use log shipping, how do you go about setting up new databases for BizTalk server?

    Thursday, December 3, 2015 9:29 AM
  • There is a distinction between High Availability (HA) and Disaster Recovery. High Availability in case of BizTalk SQL is achieved through the use of MS SQL Clustering (so if one server fails or requires H/W updates/patches, the services are failed over onto the other [passive] server).

    Disaster recovery is across sites and more associated with Business Continuity as opposed to HA.

    You can choose to configure only the Log Shipping portion. This ensures that there is ONE Complete Backup taken per day (00:00:00 Hours UTC) and then log shipping at regular intervals. You could choose to backup the last know good backup and subsequent logs over tape daily as a backup/restoration strategy where you restore the last know backup and then apply logs manually till the required interval.

    Also it would depend on what type of integration has been deployed over your BizTalk environment. If you deal with Long-running processes then there is a orchestration persistence across multiple business days which would be critical during a failure.

    Regards.

    • Marked as answer by Angie Xu Thursday, December 24, 2015 11:18 AM
    Thursday, December 3, 2015 10:06 AM
  • Hi,

    High availability (HA) can be achieved by clustered SQL with Active/Passive configuration is most of the cases  and having multiple BizTalk Application server connected to the same BizTalk group.

    The way you cluster servers both SQL and BizTalk application server depends on the throughput and the load which your farm can get .

    On other side disaster Recovery (DR ) setup deals with deeper cause when your primary BizTalk server farm goes down and to continue with transactional process you have replica exists which can act as your primary farm in case any disaster .

    Thanks
    Abhishek 


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Thursday, December 3, 2015 10:54 AM
  • It depends on how fast they need to recover.

    Log Shipping provides a recovery windows of several minutes but requires a live SQL Server that continuously loads the shipped logs.  It also requires at least one standby BizTalk Host Computer.

    If the recovery windows can be counted in 'administrative time', meaning hours, then restoring the latest backup set (full backup plus intervening logs) will have the same effect.

    For perspective, the SQL process is the same, the Log Shipping target is merely 'restoring' the source 'backups' as they happen.

    So, if they don't have any tight SLA's or budget for a stand-by site, Log Shipping is not an option.  The Backup BizTalk Sever job and manual restore is what you do.

    Thursday, December 3, 2015 11:53 AM
    Moderator
  • Thank you all for valuable responses!

    In this case, with a small solution that does not have long running orchestrations (in fact, no orchestrations at all), with no budget for an extra SQL Server and with a allowed down that can be counted in hours, log shipping does not seem to be the right choice.

    However, when I try to search for "manual restoration of databases" or "backup biztalk server job", everything I find is about log shipping. How does one do a recovery manually with this job?

    Thursday, December 3, 2015 3:54 PM
  • Refer to https://msdn.microsoft.com/en-in/library/ee378546.aspx

    While this talks about movement of databases to a new server, it would be applicable in your case in two parts. You would use the process specified in Step 4 to backup the databases and then follow the rest of the rest of the steps during your restoration situation.

    Regards.

    Friday, December 4, 2015 5:11 AM
  • Thank you!

    So, the only difference in using only the Backup Biztalk server job and log shipping is the configuration for log shipping done on the destination server? The restoration process seems to be exactly the same?

    Friday, December 4, 2015 9:50 AM
  • In the case of a standby server, the second part would address the auto restoration on the standby. In your case the restoration will have to be done manually. The backup process is the same.

    Regards.

    Friday, December 4, 2015 9:56 AM
  • So, the only difference in using only the Backup Biztalk server job and log shipping is the configuration for log shipping done on the destination server? The restoration process seems to be exactly the same?

    #1, Yes.

    #2, At the SQL level, yes.  The database just see restores.  The difference being the continuously automatic part.

    A word of advice, you have to practice this a few times and write up some instructions.  It's not really complicated, but there's a lot of steps.

    • Marked as answer by Angie Xu Thursday, December 24, 2015 11:18 AM
    Friday, December 4, 2015 11:55 AM
    Moderator