Microsoft BizTalk Server requires and relies on a Microsoft SQL server. Realizing BizTalk’s dependency of a well performing SQL database is key to efficient management and maintenance of your BizTalk integration eco-system to tune and prevent hiccups and downtime.
The most important databases from a performance perspective are the Message Box (BizTalkMsgBoxDb) and the Tracking database (BizTalkDTADb). Performance of SQL jobs and stored procedures are probably the most common topics for companies, but there is also a very long list of other SQL parameters that can seriously impact the performance of your BizTalk including deadlocks, memory handlers and indexes as a few examples.
Proper and prudent monitoring of your BizTalk SQL requires monitoring a very long list of parameters that tend to change as your BizTalk environment develops and changes. As with all other critical enterprise IT applications humans cannot succeed with antiquated tools requiring manual alert configuration. Manual alert configuration assumes that you – as a technician – know what needs to be monitored from the business perspective or that you get that insight from the business (and keep that updated). Good luck with that!
So, for you to be able to properly manage and monitor BizTalk’s SQL database you need to be able to fetch all key performance parameters and use dynamic baselining (alerting thresholds that adapt to normal behavior – reflects business cyclicality). The only efficient solution is to use some form of machine learning and big data to automate the process.
On top of the dynamic baselining and anomaly detection you should look for correlation between individual anomalies on specific performance counters across SQL and across systems.
An example could be: anomaly detecting a significant spike in SQL jobs execution fails correlating with throttling on a message pattern in BizTalk and Message Box size increases and Message Box CPU time.
This sort of automated information will allow you to see triggering events and cause / effect and help you pinpoint issues in SQL that impacts BizTalk performance and ultimately business processes supported by BizTalk.
View the AIMS for SQL Recorded Webinar