Why did we build the Secure SQL Queries capability in BizTalk360?

|  Posted: January 29, 2018  |  Categories: BizTalk360

Secure SQL Queries BizTalk360

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.

Download a PDF version of this article.

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 a 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 on 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 the 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.

Download a PDF version of this article.

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. Maybe 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.

secure-sql-queries-biztalk360

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 advantages is that useful SQL reporting queries in your BizTalk environment can be centrally stored and shared among 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 server 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.

Download a PDF version of this article.

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.

BizTalk360 30 Days Free Trial

Author: Saravana Kumar

Saravana Kumar is the Founder and CTO of BizTalk360, an enterprise software that acts as an all-in-one solution for better administration, operation, support and monitoring of Microsoft BizTalk Server environments.

Back to Top