Published on : May 23, 2017
Category : BizTalk Server Support
In case of disaster recovery and database restoration Log Shipping in the only supported method for BizTalk databases.
Key takeaways from this blog
What is BizTalk Server Log Shipping?
In case of disaster recovery, BizTalk log shipping can be used to restore BizTalk database backup files to a destination/standby BizTalk environment. Each time a backup is taken in the source system, the backup will be stored in a file location that can be accessed by the destination/standby BizTalk environment. In turn, the destination/standby environment continuously restores those backups.
When a disaster happens, at a maximum the last 15 minutes of the source system need to be restored in the destination/standby system.
Planning for Disaster Recovery
- Source and Destination SQL Server
- Both server should be at same patch level
- Both server can communicate to each other over network
- Both server can do MSDTC communication (use dtcping tool to check)
- Both server have same location for MDF and LDF files
- It is recommended that back-up and transaction-log files should be written to a highly-available UNC share, which is accessible to both the servers and should have good amount of space available.
- Make sure that Backup BizTalk Server job is configured properly and working fine.
Using BizTalk Backup Job and Log Shipping
- This job creates both full- and transactional-log backup sets of the BizTalk databases ready for restoring in a disaster situation
- It is also responsible for writing the transactional-log mark
- It will run by default every 15 minutes; however, this can be configured to a time interval that suits an individual’s (customer) environment.
- The job also maintains a backup history which is read by the standby environment to determine what backups are available for restoration.
- The history also provides auditing capabilities that allow the backup job to recover when only partial backup sets are created.
- The Backup BizTalk Server job performs three steps:
- Backup Full
- Mark and Backup Log
- Clear Backup History
- To be able to use the backups that are created by this job, for Log Shipping and Disaster Recover purposes, you need to store the backups in a file share that can be accessed by the Destination system.
Backup BizTalk Server Job Steps
- Backup Full
- Performs a full backup of the BizTalk databases.
- Performed if the ForceFullBackup flag has been set, or if the job executes in the scheduled window for a full-backup. The job default is daily full-backups.
- Done in Backup Sets
- Mark and Backup Log
- Performs a transactional-log mark and subsequent backup of the BizTalk database transaction-logs.
- It blocks any new transaction from happening and waits for in process transactions to complete.
- Clear Backup History
- Deletes the backup set history older than ‘n’ days.
- Default is 14 days.
- Does not delete the actual backup files.
How to Configure the Destination System for Log Shipping?
(Refer Configuring LogShipping for detailed information)
- Run following two SQL scripts on the Destination SQL Server to create the infrastructure for Log Shipping:
- LogShipping_Destination_Schema.sql
- LogShipping_Destination_Logic.sql
- Execute bts_ConfigureBizTalkLogShipping You can explain what are the parameters for this SP and what data we should feed in. Specially the Linked Sever one
- 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 following job is disabled:
- BTS Log Shipping Restore To Mark (WITH RECOVERY)
Update SampleUpdateInfo.xml on the BizTalk Server so that all database server information was updated with new server details. Your Log Shipping is configured now and you should see BizTalk DBs in restoring state on the DR site.
Recovering from failure
- 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 next step for pointing the BizTalk server to newly restored BizTalk DB.
- Run following command on BizTalk Server:
- cscript UpdateDatabase.vbs SampleUpdateInfo.xml (one per BizTalk group)
- cscript UpdateRegistry.vbs SampleUpdateInfo.xml (all BizTalk Server)
- Restart all the BizTalk related services on BizTalk Server
- Point BizTalk Administrator to new SQL Server This might not be obvious for many so you might want to give details here 😉
How to reconfigure the Destination Database server for Log Shipping
- To clean up the destination SQL server run stored procedure master.dbo.bts_LogShippingClean . After running this all the database for which we are doing LogShipping will be deleted.
- Delete jobs manually which was created previously to perform logshipping
- Run 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 observe the jobs were recreated again and will be in a running state. Wait for some time. You will see all BizTalk databases appearing under database with the state as (restoring)
