I have put together steps required for BizTalk Server Disaster Recovery based on my experience of setting it for customers. The only supported way of backing up the BizTalk databases is by using the out-of-the-box Backup BizTalk Server Job combined the BizTalk Log Shipping for automatic restoration. Configure and activate the BizTalk Backup Job to generate the only supported BizTalk backup files and ensure you are able to restore them by planning and testing a disaster recovery plan. The success criteria for a BizTalk Disaster Recovery plan happen only when you test your scenario.

BizTalk Log Shipping – Prerequisites

• Hardware ready (with licensing) in DR environment (similar to PROD).
  •  BizTalk Servers configured in same BizTalk group as of Live.
    • When configuring the BizTalk Server at the disaster recovery site (including the disaster recovery Enterprise Single Sign-On Master Secret server) select No to the question “Is this the master secret server?” and select “Join” to the question “Do you want to create or join a BizTalk Server group?
    • After configuring the BizTalk Server run-time disaster recovery servers, create BizTalk host instances on the disaster recovery site that correspond to the production site host instances, but do not start these host instances
    • All BizTalk Server-related Windows services such as the BizTalk Host Instance and Rules Engine Service at the disaster recovery site should be set to “disabled” in the Services Manager to prevent the disaster recovery site from performing any processing.
  • SQL Servers
  • UNC share for data and transaction log backup
• Source and Destination SQL Server
  • Both server should have the same version of SQL.
  • Both servers should be at the same patch level.
  • Both servers can communicate to each other over the network.
  • Both servers should communicate with each other via MSDTC (Microsoft Distributed Transaction Coordinator).
  • Both servers should have the same location path for their MDF and LDF files.
• It is recommend that the data-log and transaction-log backup files are written to a highly-available UNC share. Ensure that an UNC share is created that is accessible by both the production and standby servers and that the necessary permissions are assigned. Check that you can read, write and delete files from this share using Windows Explorer on both servers.
• BizTalk Server admin user and SQL Server admin user is required. Create SQL Server security logins for the disaster recovery site that correspond to the production site so that in the event that a failover to the disaster recovery site is required, all required security logins are present on the destination system. Note- Make sure the user has non expiration password.
• Make sure drive letter(s) on the production site where the database files are stored match the drive letter(s) at the disaster recovery site where the database files are restored. So if the SQL Server database file group is located on G:\data in production, there must be a G:\data directory on the destination (DR) server or the restore will fail.
• The BizTalk backup and restore SQL Agent jobs should run as under an isolated user account – choose a Windows user account for this purpose and create a SQL Server login with sysadmin permissions for this account. Map this SQL Server login to the BTS_BACKUP_USERS database role in the BizTalk Server. Refer MSDN link for further detail.
• Ports required for communication.

Configuring Backup BizTalk Server Job for Backing up the BizTalk Databases

The first task in configuring BizTalk Log Shipping is to configure the SQL Agent Backup BizTalk Server Job on the production environment. The Backup BizTalk Server job ensures that all databases are backed up in a manner that allow them to be restored to this state. The job is installed onto the SQL server that has the management database, and is responsible for a number of tasks, including:

• Creating a full database backup at the specified interval
• Writing a transaction-log mark to the transaction-log and creating a backup of the transaction-log
• Clearing the backup history table, based on specified criteria.

Note: Sometime you may get the following error while creating the backup files at UNC location. "Query timeout expired". [SQLSTATE 01000] (Error 7412) Failed running sp_BackupAllFull [SQLSTATE 42000] (Error 50000). The step failed.” You can try resolving it by changing the following setting for the SQL Server connections as follows:

• Open SSMS
• connect to SQL Server
• open server property
• Change the remote query timeout from 600 seconds to <New Value > (New can be 900 seconds to begin with.)

Adding custom database to BizTalk database backup job

The custom database you want to backup must be in Full Recovery Mode. This can be set by right clicking on the database, select Properties and then select the tab page Options. Next you must execute following scripts, against all your custom databases that you want to back up, which you can find at C:\Program Files (x86)\Microsoft BizTalk Server 2013\Schema

• Backup_Setup_All_Tables.sql
• Backup_Setup_All_Procs.sql

This creates the necessary procedures, table, and role and assigns permissions to the stored procedure. Now open the adm_OtherBackupDatabases table from the BizTalkMgmtDb database and add a row for each of your custom database you want to add to the backup job. Fill the record as follows: When you have performed all steps the custom database are shown in the admv_BackupDatabases view. The next time you run the Backup BizTalk Server job, it will back up your custom databases.

Configuring BizTalk Log Shipping – The Restore Jobs

Log shipping is a process that allows SQL server to move transactional log from one SQL server to another standby SQL server. These logs then automatically applied in sequential order to the standby SQL server, which will now be slightly out of sync with the active server. So basically, during restore job following will be accomplished.

• Retrieving the backup history from the production environment;
• Restoring the data- and transaction-log backup files to the standby SQL Server in a non-operational state, allowing further transaction-logs to be restored; and finally
• Restoring all of the databases to an operational state, by restoring the next available transaction-log to their last log mark only when disaster recovery is invoked and the standby SQL Server environment is to be made operational.

First, you need to ensure that you are logged in as a member of BizTalk Server Administrator group. Now, run the following two SQL scripts on the Destination SQL Server to create the infrastructure for Log Shipping. These scripts are located in the Schema subfolder for BizTalk (%SystemDrive%\Program Files\Microsoft BizTalk Server 2013\Schema)

• LogShipping_Destination_Schema.sql –This creates required tables in the Master database on the server. These are used to store the list of databases being recovered, copies of back up history (taken from Management database), and the configuration of SQL Server Agent Jobs on the source SQL server.
• LogShipping_Destination_Logic.sql –This implements the artefacts required (including stored procedure and database roles).

Execute bts_ConfigureBizTalkLogShipping stored procedure. Before you execute this statement, you must enable the Ad Hoc Distributed Queries configuration option on the destination system.

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries',1; GO RECONFIGURE GO

exec bts_ConfigureBizTalkLogShipping @nvcDescription = '<MyLogShippingSolution>', @nvcMgmtDatabaseName = '<BizTalkServerManagementDatabaseName>', @nvcMgmtServerName = '<BizTalkServerManagementDatabaseServer>', @SourceServerName = null, -- null indicates that this destination server restores all databases @fLinkServers = 1 -- 1 automatically links the server to the management database In the command, replace <MyLogShippingSolution> with a meaningful description, surrounded by single quotes. Replace <BizTalkServerManagementDatabaseName> and <BizTalkServerManagementDatabaseServer> with the name and location of your source BizTalk Management database, surrounded by single quotes. Note: If we get error “Server is not found or not accessible” while executing the above stored procedure, check the DTC connectivity first and then you try to access the SQL server from DR server. There might be some firewall issue where only a particular port has been open to access the LIVE database server. In that case create an alias with same name as LIVE SQL database and then run the stored procedure. It should work now. Under SQL Server Agent ->Jobs, in SQL Server management studio, you will see that three new jobs has been created.

• BTS Log Shipping Get Backup History - The BizTalk Server Log Shipping Get Backup History job moves backup history records from the source to the destination. It is scheduled by default to run every minute.
• BTS Server Log Shipping Restore Databases - The BizTalk Server Log Shipping Restore Databases job restores backup files for the given databases for the source to the destination server. It is scheduled by default to run every minute.
• BTS Log Shipping Restore To Mark - The BizTalk Server Log Shipping Restore To Mark job restores all of the databases to a mark in the last log backup. This ensures that all of the databases are in a transactional consistent state.

Make sure the following two jobs are up and running:

• BTS Log Shipping Get Backup History
• BTS Server Log Shipping Restore Databases (WITH NORECOVERY)

And that the following job is disabled:

• BTS Log Shipping Restore To Mark (WITH RECOVERY)

The final step of the configuration process id to edit “SampleUpdateInfo.xml” on the BizTalk Server so that all database server information is updated with DR SQL server details. Open this file and replace all the instance of “SourceServer” and “DestinationServer” with the names of source and standby SQL server respectively. Log Shipping is configured now and you should see BizTalk DBs in restoring state on the DR site and you will see entries in the standby SQL Server master.dbo.bts_LogShippingHistory table.

Recovering from failure - Restoring the Databases using Log Shipping

If a failure occurs and your production SQL server is unavailable due to a catastrophic failure (such as site loss), the steps you need to do are as follows:

• Disable following jobs on the Destination SQL Server:
  • BTS Log Shipping Get Backup History
  • BTS Server Log Shipping Restore Databases
• Enable following job on the Destination SQL Server:
  • BTS Log Shipping Restore To Mark

Once this job is completed, go to the next step for pointing the BizTalk server to the newly restored BizTalk DB. To do this, run the following command on BizTalk Server:

• cscript UpdateDatabase.vbs SampleUpdateInfo.xml (Any one BizTalk Server in group)
• cscript UpdateRegistry.vbs SampleUpdateInfo.xml (All BizTalk Server in group)

Now restart all the BizTalk Services, including the ESSO service and Rule Engine Update service.

How to reconfigure Destination Database server for LogShipping

Run stored procedure master.dbo.bts_LogShippingClean, to clean up destination SQL server. After running this the whole database for which we are doing LogShipping will be deleted. Delete jobs manually which were created previously to perform LogShipping Run the following two SQL scripts on the Destination SQL Server to create the infrastructure for Log Shipping:

• LogShipping_Destination_Schema.sql
• LogShipping_Destination_Logic.sql

Note: This will clean everything from the DR DB site Execute bts_ConfigureBizTalkLogShipping. After executing this you will observer the jobs are recreated again and will be in the ‘running state”. Once you wait a while, you will see all BizTalk databases appearing under database with state as (restoring).

Related Links

http://social.technet.microsoft.com/wiki/contents/articles/5363.biztalk-server-disaster-recovery.aspx http://blogs.msdn.com/b/biztalknotes/archive/2012/01/10/biztalk-log-shipping.aspx http://blogs.biztalk360.com/biztalk-backup-disaster-recovery-log-shipping-is-the-only-official-option-why/ http://www.biztalkgurus.com/biztalk_server/biztalk_blogs/b/biztalksyn/archive/2012/05/08/biztalk-backup-disaster-recovery-log-shipping-is-the-only-official-option-why.aspx http://www.codeproject.com/Articles/529862/BizTalkplusServerplusDisasterplusRecovery