BizTalk Server Backup, DR and Log Shipping – BizTalk360 can make your life easy

Note: This capability is added to our next release of BizTalk360 v5.0 due in 6 weeks time. One of the design goals for BizTalk360 is to abstract the complexities of some of the hard core concepts in BizTalk Server. We wanted BizTalk environments to be managed, operated and administered by someone who got very basic BizTalk knowledge, not a real BizTalk expert. In our experience only very few companies have dedicated BizTalk administrators. It may not be to feasible to have a dedicated BizTalk admin resource for various reasons like not enough work to keep the resource busy all the time, financial reason etc. In majority of the cases organisations choose to cross train either DBA’s or WinTel IT person to pick up some of the administrative BizTalk tasks. For us here at BizTalk360, the goal is to provide both experienced and new BizTalk people (operators, administrators) sophisticated tooling to make their day-to-day life easier. We already addressed some of the challenges like graphical message flow viewer,  fine grained authorization , graphical throttling analyser, Advanced Event Viewer  etc. On the same theme the next big module is BizTalk backup-disaster recovery visualization.

Introduction

SQL server especially the Message Box database is the heart of your BizTalk environment. All of your BizTalk servers in the group are typically state less and there is tons of logic in the SQL layer for the proper functioning of your BizTalk environment. For that same reason, Microsoft wants to treat SQL server as a black box when it comes to BizTalk server, its property of Microsoft and you are not supposed to do any traditional database maintenance activities in the SQL server. BizTalk server comes out of the box with predefined back up jobs and clear procedure on how to configure log shipping, standby server and restore procedures. In fact this is the only supported way of doing backup and restore in BizTalk server. There are very good documentation in MSDN and couple of great articles my Nick Heppleston about BizTalk backup/DR. At very high level this is his how it works

• You configure the backup job in the live environment, which takes periodic full back up and log backs and stored it in a highly available UNC path.
• You configure a stand by SQL server in a remote location, which got access to both the UNC shared drive and to the Live SQL Environment (as linked server).
• Both the environment maintains a bunch of SQL tables in management and master databases respectively keeping the history of backup and restore activities
• Standby environment reads the history from live environment, pick up the data and log files from UNC share and restores it on the standby environment WITH NO RECOVERY option.
• In the event of failure, you need to perform bunch of activities clearly documented in MSDN to bring the standby environment as live.

Challenges for BizTalk Administrators/DBA

In order to make sure the backup, log shipping and standby restore activities working correctly, a BizTalk administrator or DBA need to perform various checks periodically. There are too many touch points they need to verify, which includes

• The SQL agents are running correctly on both sides
• SQL Jobs are configured correctly and enabled
• No errors in the back up/ restore job history on live and standby environments
• Need to understand the adm_BackupHistory table to see whether backup are working as expected
• Need to understand the bts_LogShippingHistory table in standby environment to check whether restore is working as expected
• May need to look into the configuration of back up job to check the configuration (full backup frequency, log back schedule, UNC path etc)

All these steps just make BizTalk backup/log shipping/DR as an expert activity and it nearly impossible for a non-BizTalk person to understand all these things.

How are we resolving these issues?

BizTalk360 solves all these challenges by providing a BizTalk backup/disaster recovery visualizer as shown in the below picture. BizTalk360 understand the configuration and displays all the details in a simpler/graphical way.  Let’s take a look at each section

• Backup job configuration right in the UI
• Graphical view of Live/UNC/Standby environment
• Live and Standby backup/Log shipping, job history

Backup job configuration right in the UI

You can expand the pane “Backup SQL Job configuration (Live Environment)”, which will display your current backup job configuration in an easy to understand way. Behind the scene BizTalk360 parses the back up job configuration (all the job steps, parameters, schedule etc.) and present it in a nice UI.

Graphical view of Live/UNC/Standby environment

The next stage shows the graphical representation of your LIVE/UNC/Standby setting clearly pointing how the environments are configured. The live and standby environment clearly shows the health of important parameters like

• Whether SQL Agent are started
• Whether the backup/restore job is enabled correctly
• Whether there are any errors in job history

Note: One of the SQL restore job is supposed to be in stopped state, it’s only enabled during the real disaster. BizTalk360 will understand that configuration and ignores that job while calculating the health. On the UNC share box it shows the path, names assigned to full and log names in the backup job configuration.

Live and Standby backup/Log shipping, job history

As mentioned earlier it’s important to keep an eye on the backup/log shipping history records to see whether the back is working correctly and data/logs are restored correctly in the stand by environment. Some of the things to note

• You can visualize the databases being backed up/restored
• The frequency of full backup
• The frequency of log backup
• Last set of successful full backup records
• Last set of successful log backup records
• Last set of successful full restore records
• Last set of successful log backup records
• Notes clearly showing what you need to look for (ex: Restore backup set id must be live -1 to be healthy)
• Displaying the history records for backup/restore jobs.

BizTalk360 with this single view represent your whole backup/disaster recovery setup and helps you to keep an eye on the health of your DR plan. The important thing to note here is, anyone without any prior BizTalk knowledge can understand the settings. Thanks to BizTalk360. If you got any feedback please feel free to contact us, we want to make eveyone’s life easy. Nandri Saravana Kumar