This post was originally published here
This blog is a part of the series of blog articles we are publishing on the topic “Why we built XYZ feature in BizTalk360”. Read the main article here.
Why do we need this feature?
In a BizTalk Server support scenario, one of the common things that happen is people accessing the BizTalk System and some custom databases regularly using tools like SQL Management Studio to run some reporting queries. Some of the system databases will include BizTalk Message Box (BizTalkMsgBoxDb), BizTalk Management (BizTalkMgmtDb), BizTalk Tracking (BizTalkDTADb), Business Activity Monitoring – BAM (BizTalkPrimaryImport). BizTalk Administrator and support people access these databases for a variety of requirements such as to check tables like spool or host tables in Message Box, Message In-out table in tracking database, looking for some values in BAM database tables, cross reference data, check for large messages etc.
What are the current challenges?
Performance Impact: Some of the BizTalk databases can grow extremely bigger. It’s not uncommon to have over 1 million records in certain tables (ex: MessageInOut table in Tracking Database). Executing some queries like just a plain “SELECT * ..” on those tables without any query optimization or locks can have serious performance impact during business hours.
Productivity Improvement: BizTalk Support Team will typically have a bunch of standard SQL queries they run on a regular basis to get some reporting on BizTalk databases. Each individual team members will have their own set of SQL queries and they manage it in their local machines. This basically results in two things, useful reporting queries are not shared between team members and maintaining the queries in SQL files and accessing different files for different environment is not productive.
No consolidated tooling: If BizTalk Administrator or support person needs to run some reporting queries, they need to use an external tool like SQL management studio.
What is the current limitation in standard BizTalk Admin Console?
The standard BizTalk Admin Console does not come with any capabilities related to accessing SQL data. This leaves the support people to rely on external SQL tools like SQL Server Management Studio (SSMS). Tools like SSMS are designed for DBA’s and Developers and it’s not particularly friendly when it comes to pure support and management – a level of technical expertise is required to use SSMS.
How BizTalk360 solves the problem?
We understood the practical challenges of not having an integrated SQL data access tool along with BizTalk Administration and the challenges it exposes (as explained above). This resulted in building “Secure SQL Queries” platform in BizTalk360.
The principles are quite simple — a DBA or someone who understands SQL well writes optimized SQL queries and store them with friendly names in BizTalk360. Ex: “Depth of Message Box Spool Table”. Then, assign who will have permissions to run the queries. May be not all the queries are required for every support person. BizTalk360 can then work like a reporting tool for these custom SQL queries and display them in the web interface directly.
This approach eliminates people from directly running non-optimized queries in production environments and it also simplifies having access to SQL data within a single web console instead of switching tools.
One of the biggest advantage is that useful SQL reporting queries in your BizTalk environment can be centrally stored and shared between team members.
NOTE: The platform is very generic; you can store and execute SQL queries across any databases and not necessarily just BizTalk Server databases as long as the service account has right privileges to access the data.
What is the business value?
The business benefits are very obvious. Providing a secure web based platform for accessing SQL sever data (which is critical to the day-to-day operations of any BizTalk Server solution) increases the productivity of support/BizTalk Admin people and makes it extremely secure. In general, you can keep your database administrators (DBA’s) happy in the organisation.
Get started with a Free Trial today!
Download and try BizTalk360 on your own environments free for 30 days. Installation will not take more than 5-10 minutes.
