Configuring BizTalk 2013 Backup for Disaster Recovery
BizTalk Server 2013 disaster recovery procedures are built around BizTalk log shipping, a feature that was first introduced in BizTalk 2004.
BizTalk 2013 log shipping simplifies database restoration in the event of a disaster and is based on the principle of Microsoft SQL log shipping, it continuously applies transaction log updates to the disaster recovery site databases. However, the standard Microsoft SQL log shipping principle is not supported by BizTalk 2013, which comes with its own custom and improved log shipping methods.
How they are different and why you need them, will be explained with a little more detail in this article.
BizTalk Backup and Restore
MS SQL Mirroring
Biggest absentee to the party, MS SQL Mirroring
As pointed out in the Best practices, MS SQL mirroring is not supported. The setup and usage of this will work and your databases will sync up, however you will run into trouble after you perform a failover. BizTalk 2013 uses the MSDTC service for connecting to the principal server, which means that in case of a failover, the service will not be aware who the new principal service is, causing the service to commit some transactions but drop others.
More about this can be found on MSDN: Database Mirroring and Cross-Database Transactions.
MS SQL Log shipping
Guest of honor, MS SQL Log shipping
First of all, a little note on distributed transactions and transactional log markings. BizTalk 2013 uses this MS SQL feature to keep a consistent record of past transactions. This happens on all databases. In practice, this means that, every time a BizTalk 2013 backup job runs, new transactions are paused until the current transaction is completed. After completion the transaction log is marked, and a record is kept.
This comes in handy when using multiple databases to handle the load of larger BizTalk 2013 as well as in a high availability setup. For example, you have 3 databases performing the same function, taking jobs round robin and executing them. Without transactional markings, the other databases will not know which jobs are done by which database and they will not be able to sync nor reach a consistent state.
If you don’t keep a transactional log with markings of the accurate state across the 3 databases, you will not be able to restore them consistently, since there’s no general record (transaction mark) across these 3 databases.
Further information on mirroring and distributed transactions can be found on MSDN: Database Mirroring and Cross-Database Transactions.
The Backup and Restore Process
Now that you understand why we are using MS SQL log shipping, let’s dive in a little deeper.
After the installation of BizTalk 2013, you will notice that 4 MS SQL agent jobs have been created (1 on the principal server, 3 on the disaster recovery server):
The Backup job on the principal server, creates data- and transaction log backups, places the log markings and maintains an audit table containing a list of created backups (Date, type and location).
The ‘BTS Log Shipping – Get Backup History’ job checks the audit table for new backups and moves them locally in cache. The ‘BTS Log Shipping – Restore Database’ gets these backups and restored them using the MS SQL ‘NO RECOVERY’ option, putting the databases in a non-operational state, so that the transaction logs can be applied.
These 2 jobs run, by default every 15 min throughout the day to day operations of BizTalk 2013.
BizTalk 2013 Disaster recovery
During a disaster recovery the last job ‘BTS Log Shipping – Restore to Mark’ is activated. The most recent transaction log is restored using the MS SQL ‘WITH RECOVERY’ mode, putting the database in an operational ready state.
On the BizTalk 2013 (all nodes) you will have to run the 2 VBS scripts, ‘UpdateDatabase.vbs SampleUpdateInfo.xml’ to point the BizTalk 2013 databases to the new MS SQL server and the ‘UpdateRegistry.vbs SampleUpdateInfo.xml’ script, which updates the registry to reflect the new MS SQL server.
After this, the BizTalk 2013 services need to be restarted to pick up the changes and if everything went right, you should be up and running again.
Unfortunately, this process is the only way to fail back to the original server as well, which means that you will have to plan in some downtime.
MS SQL deep dive
By running the SQL query ‘select * from dbo.bts_LogShippingHistory order by BackupId desc‘, MS SQL will provide you with an overview of all the applied backup/transaction logs.Notice the first set of the full backup. (BackupType ‘db’) is restored (Green), then 15 minutes later, the first transaction log set is applied. This happens by running the ‘BTS Log Shipping – Get Backup History’ and ‘BTS Log Shipping – Restore Database’ SQL agent Jobs (Blue).
The most recent set of backups will not be restored immediately, there is always a 15 minutes gap of data between the state of the principal server databases and the disaster recovery databases. The final block of transaction logs will be applied in case of failover by running the ‘BTS Log Shipping – Restore to Mark’ (Red).