Take away SQL server access from your BizTalk support team using BizTalk360

Published on : Mar 15, 2013

Category : General

Saravana

Author

What’s the problem?

In pretty much every organisation that uses BizTalk server, the BizTalk administrator/operations team will require access to the SQL server instances for various things they need to verify either occasionally or periodically. Here are few things
  1. Custom SQL queries against certain databases (both BizTalk and custom ones)
  2. Health of all the SQL Jobs (for state, last execution)
  3. Checking the size of the database data and log files
  4. Checking Backup/DR log shipping health
When the organisation gets bigger and bigger, typically the SQL servers and databases will be managed and operated by dedicated database teams. In most cases database teams will not have the required BizTalk expertise and on the other hand they will be sceptical to grant access to the database to BizTalk team. I’ve personally seen teams struggling to come to terms with each other deciding on level of permission that should be granted to BizTalk team, creating new policies, and every time a new team member is added the same process needs to be repeated. There is also another problem, how can you trust the BizTalk people to use the SQL databases carefully. At end of the day, SQL server is heart of BizTalk, in-fact most of the real work in BizTalk is done at SQL server level and you shouldn’t mess around with it. Even if you only provide SELECT access to your BizTalk people, they can still create problems by creating SQL queries that can end up in dead locks. Ex: Imaging if there are 2 million records in your tracking message in/out table and someone write a vanilla select statement “SELECT * from dta_MessageInOutEvents” and keep executing it every 10 minutes from SQL management studio. Not every BizTalk person is a SQL expert, so there is a problem. BizTalk team will also need to have access to one more tool “SQL Server Management Studio” to run the queries and there is no easy option to share useful queries between team members. Individual BizTalk people will have their own set of useful queries stored somewhere in their desktops/laptops. Keeping all these issues in mind, we added capabilities in BizTalk360 and over multiple versions it’s been revised/improved and now you can pretty much take the complete SQL access from BizTalk team.

Custom SQL Queries

The idea behind Custom SQL Queries is to provide a secure platform to store a bunch of predefined queries and provide access (only selected queries if required) to the BizTalk team members to execute them. Individual users will not have any direct access to the SQL instances/databases, the queries will be execute under the context of a service account and BizTalk360 will provide the framework to control the security model. The queries will either be written or reviewed by an expert SQL DBA and stored in BizTalk360 (who have either Super user role or add query permission), the BizTalk team can then execute the queries for which they have permission. BizTalk360 comes by default with set of useful queries, you can also over a period build custom SQL queries that’s more appropriate to your organisation. Let’s first take a look at various things you can perform on a Custom SQL Query screen. You can access the custom SQL queries section by navigating from Data>Custom SQL Queries from left hand side navigation. Custom SQL Queries When the user navigate to this screen, they can choose one of the pre-stored queries from the drop down list, which automatically gets executed and result displayed in the result pane. The result grid will contain column headers corresponding to the column names specified in the query. As you can spot in the screen you can perform various actions
  • View/Edit/Delete Existing Query
  • Add New Query
  • Edit and Execute Query
  • Export Results to CSV format
Of course the user can perform those actions only when they have adequate permission to do so, which we will cover in later part of the article.

Managing Queries

BizTalk360 provides a platform to manage SQL queries within the BizTalk360 application. It comes out of the box with some useful SQL queries. The users can add their own queries (when there is adequate permission) or modify existing one and save it. As shown in the below picture, it’s standard SQL queries, you can put a friendly name ex: “Send ports to Specific Location”, specify the SQL instance name and Database name and store it. Manage Queries Once the queries are stored successfully, it’s visible in the drop down list for the user who have permission for the queries and they can execute them using BizTalk360 without having any real access to the SQL server. The queries will be executed under the service account credentials, the only account that will require access to the SQL databases. BizTalk360 also put’s some security measures in place, it will not allow any dangerous queries like INSERT/UPDATE/DELETE statements. When user tries to add such queries, an exception will be thrown and will not allow such queries.

Edit and Execute

Some time it may be required to edit the queries and execute them. Once you assign “Edit and Execute” or “Edit and Save” permission to the user, the user can edit the queries and execute them directly in BizTalk360 database. Some of the examples include, changing the date range, changing the where clause etc. Edit and Execute Queries

Export Result To CSV

The user can also export the results in CSV format by simply clicking on the “Export to CSV” button and providing the path, where you wanted to store the information. Will be useful, if you wanted to run a query and send the report to one of your colleagues.

Custom SQL Queries security model

Now lets take a look at the security model of custom SQL query in In BizTalk360. During the initial version of BizTalk360, the security model was very simple. Only super users can manage queries (add/edit/delete) and they will provide “Can Execute Custom SQL Query” permission to users or NT groups. This resulted in some challenges, example user will be able to execute all the queries configured in the environment, and also they can only execute queries and not able to manage it. Every time they need to contact the super user to add new queries. Based on the customer feedback, from version 6.0 we completely revamped the security model for custom SQL queries. As you can see from the following screen, now you can assign only certain queries to the users, and you have much better control of what type of access you can give to the users. Example: You can allow user to “Add Query”, “Delete Query” etc Security model for SQL Queries

Viewing the state of SQL Jobs

BizTalk server by default comes with bunch of SQL Jobs which are critical for the health of the BizTalk environment. From a BizTalk support perspective, it’s important to keep an eye on health of the SQL jobs. In BizTalk360 there is a dashboard for SQL instances that are related to your BizTalk environment. BizTalk360 will automatically pull the information based on your environment configuration and present a dashboard for all the SQL instances.  One of the sections of the dashboard is the SQL Server jobs as shown in the below picture. View state of SQL Jobs There is also an options to setup monitoring to make sure the jobs are in the correct state and to make sure it’s executing correctly without any errors.

Viewing BizTalk Database Data/Log File Sizes

One of the other reason BizTalk people will access SQL servers is to check the data and log files sized of BizTalk databases. As suggested by Microsoft the size of the Message Box database shouldn’t be more than 5GB, since message box is more of a transient database and once the processing is done the messages should be moved to BizTalk Tracking/BAM databases. In BizTalk360 we provide the data and log files size right in the UI, so the support person doesn’t require access SQL servers to get that information. Data and Log file sizes

Monitoring SQL Jobs (state/Outcome) and SQL Queries

In addition to all the above mentioned functionalities BizTalk360 also comes with ability to monitor SQL jobs both state of the jobs (enabled/disabled) and the success of the last outcome. Monitoring can also be set on outcome of database queries, example monitoring the row counts of spool table, host queue table, tracking data message in/out tables etc.

Backup/DR Visualizer

In BizTalk server the only supported way of taking back up and resorting the system is using the SQL Jobs that’s gets installed as part of BizTalk server configuration. Any other traditional approach or modern approach are not supported. There are various reasons behind the scene, why you should only use the supported Log Shipping (age old technique) mechanism to back and restore your BizTalk databases. One of the main challenges in backing up BizTalk databases is you need to maintain transactional consistency across all the BizTalk databases, which is difficult. The BizTalk team has made this task easy by providing out of the box SQL jobs, which will take care of this. BizTalk360 provides a single page view of how this configuration works, you can view the configuration of your Backup Job,  and health of all related items like whether Backup job is enabled, SQL Agent is running, Restore job is enabled, whether there are any errors from jobs, where the backup files are stored etc all in one place as shown in the below figure. Backup/DR Visualizer   Is there any other reason you access the SQL server directly from a BizTalk perspective, please leave your comments, we are always improving the product and if there is a genuine reason we can try to address it.