In this blog, we will take a detail look about the feature Secure SQL Queries which is available in BizTalk360 and we shall look how effectively it can be used in by the BizTalkies and what are all the things we can achieve by having this feature and how we made this feature so reliable for you.
Secure SQL Queries
Secure SQL Queries is a secure platform to store predefined queries and provide access to BizTalk members to execute the queries. BizTalk360, by default, comes pre-loaded with a set of queries and allows the users (with permissions) to build secure SQL queries that are more appropriate to the organization’s business.
Reliability of Secure SQL Queries
BizTalk Support members 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 member will have their own set of SQL queries and they manage it on their local machines. Which will basically result 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.
BizTalk360 allows you to save the SQL queries so that the queries can be saved under a centralized area and available for access to all the BizTalk members.
SQL queries are used for fetching the report from the BizTalk Database where admins are concerned to provide full access as it may have confidential information and interested to give privilege only for the authenticated BizTalk members. This can be handled by using the User Access Policy feature in BizTalk360 which allows to create a user and provide the permission for the saved Secure SQL Queries (only the enabled query will be listed for the users). Permission can be provided for the Secure SQL Queries as follows Add Query, Edit & Execute Query, Edit and Save query, Delete Query and Export Query.
At any point in time, there may be a chance of data loss or change may happen in the queries. To handle this sort of situation BizTalk360 provides you the feature Governance & Audit for Secure SQL Queries. If any operation like Create, Edit, Delete is performed against the Secure SQL Queries will be captured in the BizTalk360’s Governance & Audit section. So that we can find the details of the operation performed through the feature Governance & Audit for Secure SQL Queries.
Functionalities of Secure SQL Queries
In BizTalk360, you can query against the table’s data. Based on the customer’s requirement it allows you to store and execute the stored procedures.
In real-time when more users are involved, or remote connections are being established with the SQL Server, SQL authentication can be used. In shared servers where different users should have access to different databases and when a client connects to an instance of SQL Server on another computer than the one on which the client is running, in that case, SQL Server authentication is needed. Such user can select the Authentication Mode as SQL Authentication so that the user name and password can be provided, and the query can be saved and executed
Using the SQL Authentication Mode, you can get connected to the Azure Database by providing the Azure Database’s instance name, username, password for saving the queries.
Let’s glimpse on Secure SQL Queries in BizTalk360
Secure SQL Queries is implemented in the Operation, Monitoring, and Analytics section. Since most of our customers use this feature widely.
Secure SQL Queries in Operation
BizTalk360 comes with a pre-loaded set of queries. The users can choose one of these pre-loaded queries from the drop-down list. When the item is selected from the drop-down, the query automatically executes, and the result is displayed in the Query Results pane. It allows users to define and add/edit/delete your own queries. In which you can execute the query to check for the results.
Secure SQL Queries in Monitoring
You can monitor the Database Query results by configuring the query which returns the scalar value. If there is any deviation in the query results, then BizTalk360 will trigger an alert notification.
Secure SQL Queries in Analytics
The BizTalk360 Reporting feature includes a widget called Secure SQL Queries, where you can map the existing Secure SQL Queries to such custom widgets. The custom SQL widgets hold the top 100 records of the mapped query and send that as a report on the scheduled time. This feature can be powerful as it enables you to automatically receive reports based on SQL queries, without the need to requesting it manually over and over again!
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. Always try to write the query which returns the selective results.
Secure SQL Queries are designed to allow the users to execute only the ‘Select’ statement for queries and Execute command for Stored procedures. This will ensure the reliability of the data in the BizTalk Server and other databases.
Say, for instance, there may be a chance that the normal/a superuser runs a DELETE/TRUNCATE/DROP/UPDATE/INSERT commands through the secure SQL queries in BizTalk360. The above-mentioned commands are restricted by design to avoid data loss and secure the data.
Benefits of having Secure SQL Queries
- Single management tool for users to execute the queries
- No need for SQL Server Management Studio
- Central Query Repository – maintaining queries is much easier
- The end-users need not have direct access to the SQL database
- The queries will be executed in the context of the service account, therefore only the service account requires access
- Queries can be executed against any SQL instance/database
We are constantly improving on the features based on feedback in the customer forum. BizTalk360 will continue to provide more useful features in every release. Why not give BizTalk360 a try!
The post Effective Usage of Secure SQL Queries Feature in BizTalk360 appeared first on BizTalk360.
For those who spend most of their time working with Service Instance messages, ESB exceptions and Event Log entries, it might be good to know that BizTalk360 can ease you with the feature “Knowledge Base”. The Knowledge Base acts as a repository of articles that contain solutions to the common problems which are faced by users while working/supporting the BizTalk environment. One of the great advantages of the Knowledge Base is that you can store the information related to the exact error condition and share that with other users for reference.
In BizTalk360 you can create and refer KB articles for the following sections:
- Service Instances
- Event Log entries
- ESB Exceptions
- Throttling Data
Importance of having KB article
A knowledge-base is the one thing that can be instantly useful for both your support agents and customers. Support agents can refer to the knowledge base and answer the customer queries quickly, instead of spending time asking someone for guidance or trying multiple solutions. Customers can search for answers to their questions in the knowledge base, instead of contacting Support and waiting for a reply.
By having KB articles, it is very handy to document the solution to known issues, and most importantly it is fully customizable and easy to document. It’s a simple and intuitive user interface, through which the users will enjoy using this repository.
Let us consider a scenario that will help us to understand the importance of KB Articles a bit more. In an organization, not every user will be available on time for providing guidance. Some may be remote users, for whom the Knowledge Base will act as a self-service hub that will hold the referral documents or solutions.
Say for instance, while handling support, some issue has been raised and solved for a customer. After a while, another user has raised a support ticket for the same issue. In case the solution to the issue has been documented, this kind of situation can be handled much more effectively and less- time-consuming.
BizTalk related KB articles
When an issue occurs in BizTalk which ends up as a Suspended instance, it will hold an error code. By using this as a key point, you can document the solution for each issue. If needed, you can even use criteria like BizTalk application, Service name and (parts of) the Error Description to associate an article to an issue. Similarly, BizTalk360 will also let you document issues related to Event Log entries, Throttling data, and ESB exceptions.
Let me share with you a support case which we have handled recently. In a firm, we often used to upgrade our setup. While setting up the environment, we may face similar issues that occur frequently. Whenever any configuration related issue occurs it will be logged in the event viewer, along with the Description and Event Id. Say for instance, if the BizTalk server throws an “MSDTC” or “ENTSSO” exception while performing any operation. Using that Event Id, you can create a BizTalk360 KB article, through which you are providing visibility over the issue, which can be referred to in future cases.
Note: In the case of Event Log entries, BizTalk360 associates KB articles based on the Event ID. However, as the Event ID might not be enough to uniquely associate a KB article to a particular Event Log entry, you can also use properties like Event Log, Event Log Source, and few other properties. You can even take partial text from the actual Event Log entry description!
Similarly, BizTalk Server continually monitors for throttling conditions, calculates the severity of the throttling condition, and applies host throttling progressively depending on the calculated severity. To handle this, BizTalk360 contains an Analytics (Windows NT) service specifically to collect the throttling performance counter data from the various BizTalk servers in the environment. Throttling activity of a host instance is being monitored and at a point in time, the monitoring service plots the throttling state against, for example, the ‘System Memory’. Once the reason and a mitigation strategy are identified for the throttling, as a best practice, the information can be documented in a KB article.
Service instances can fail due to some error and BizTalk attaches the error with an error code and an error description. In the same way, other service instances can fail for the same reason as the previous one. I.e, if there is a suspended message, you can find the error code for the same. The error code 0xC0C01B4e represents routing failure messages.
With the BizTalk360 knowledge base, you can document the solution by referring to amongst others the error code and the error description. Once the Knowledge Base article is defined for one error code, event log, etc., it will be visible across all environments. In BizTalk360, whenever the issue occurs with the documented error code etc., then the user can see the KB article icon. By clicking on the properties (or eye icon), you can find the document under the KB article tab with the reason/solution you have created.
When creating multiple articles for the same error code, select the appropriate tags (Environment, Service Class, Application Name, Error Text, Host Name, Service Status, Service Name) for the error code. The tags will play the differentiator role to display the appropriate KB article.
Note: BizTalk360 allows super users to create KB articles specific to one environment. All the users in that environment will be able to see the articles.
Best practice of using the KB article
- Whenever an issue occurs and became resolved, make a practice to always document the solution in a KB article.
- Keep the Titles of the KB articles as straight forward as possible, the simpler, the more is it understandable. Ensure it is to the point and include the most important keywords.
- Formatting solution articles is extremely important. Clearly, differentiate your titles and subtitles. Split different sections using a horizontal line. Bold the action items in each step so it’s easy for the user to skim. Provide the step by step details of the solution.
- Refer to the link related to the article.
- Be descriptive with relevant points.
- Tag the article to have clear usage and better reference.
With the above-mentioned tips, you can make your KB articles a well understandable document.
With the Knowledge Base feature, we provide visibility over resolved issues and the same can be brought into the notice to all the users. With this feature, we can improve the visibility of issues and reduce time consumption in solving the issues which will lead to higher productivity.
The post An Effective Way of Using KB Articles in BizTalk360 appeared first on BizTalk360.
BizTalk360 always values customer feedback and we focus on improving the usability by adding new features and enhancing existing features as per customer requirements. Recently we have received a few support cases from our customer end about SQL Collation. So, we have analysed and improved it.
We are happy to share you that BizTalk360 has improved its standards by supporting case-sensitive collation. This will soon be available in the upcoming version 9.0 Phase 2. In the previous version of BizTalk360, we were supporting the general collation like Latin1_General_CI_AI for BizTalk360 Database. In this blog, we will see why we started supporting case sensitive SQL collation and what the benefits are of having it.
As you all know BizTalk360 is the one-stop monitoring tool to monitor BizTalk Server. Since BizTalk Server supports case sensitive collation, it is so important that BizTalk360 also supports case sensitive collation, to make it as a compatible tool.
About SQL Collation
A collation is a configuration setting that determines how the database engine should treat character data at the server, database, or column level. SQL Server includes a large set of collations for handling the language and regional differences that come with supporting users and applications across the world.
SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.
- CI refers to Case Insensitive
- CS refers to Case Sensitive
List of collation combination in SQL setup:
- SQL Server collation
- SQL Server and Database collation
- SQL Server, Database and Tables collation
- SQL Server, Database, Tables and Column collation
SQL has 4 levels of collation: server, database, table and column. If you change the collation of the server, database or table, then we don’t want to change the setting for each column. If needed, we can change the default collations at database/table/column level.
Example: If you change the default collation of a database, each new table you create in that database will use that collation, and if you change the default collation of a table each column you create in that table will use that collation.
To change the collation of SQL Server: SQL Server collation can be changed in two ways, one is by changing the collation during the installation and the other way is changing the collation after installation.
Changing the collation during the installation: while installing SQL server, you can find the Server Configuration section. Under this section, you can see two sections Service accounts and Collation. Click the Collation tab to verify the collation. The collation will have the default supported collation. Click on the Customize… button and select the required collation. Click Next and proceed further.
Note: To have case sensitive collation use the collation which holds CS_AS.
Why BizTalk360 supports SQL Collation
BizTalk Server supports all case sensitive and case insensitive SQL Server collations except for binary collations. So, it is important that BizTalk360 should support the same collations.
Collation refers to a set of rules that determine how data is sorted and compared. When BizTalk360 calls any functions or data from BizTalk Server which is of case sensitive collation, then it may lead to an exception.
Let’s consider that the BizTalk databases hold a case sensitive collation and BizTalk360 holds a case insensitive collation. In this case, when BizTalk360 gets integrates with BizTalk Server and tries to read the data, it is necessary to ensure all the procedure and column names should be exactly similar.
From the above picture it is clearly understandable that having a collation-based database, and when trying to fetch the data of an object, it will compare the case sensitiveness and provide the data. In case of uppercase and lowercase mismatch, it will lead to an exception.
Say for example, Invalid column name ‘nAdminHostId’ (or) Invalid object name ‘dbo.b360_ST_Schedules’ (or) Must declare the scalar variable “@expiryDateTime”etc.
Most of our customers configure BizTalk360 in the SQL Server instance where BizTalk Server is configured. In such cases, the below-mentioned scenario will add value for supporting collation.
Scenario I: BizTalkMgmtDb (Latin1_General_CS_AS) & BizTalk360 (Latin1_General_CI_AI)
Let us consider the scenario of having a SQL server with case insensitive SQL collation and case sensitive collation for BizTalkMgmtDb.
In this case, the BizTalk360 installation will be successful and BizTalk360 database will be created with case insensitive SQL collation since SQL Server holds a case insensitive SQL collation. But in the BizTalk360 application, you may face some exceptions getting popped up related to “Invalid column name ‘nAdminHostId”, in few sections like BizTalk Server, SQL Server, SQL Server instance, Analytics etc.
Scenario II: SQL Server (Latin1_General_CS_AS) & BizTalk360 Database (Latin1_General_CI_AI)
When creating a database in the SQL Server, by default the database will hold the collation of the SQL Server. In this case, the customer has the CS collation for the SQL server, thus all the BizTalk related databases hold the same case sensitive collation. In this case, when you try to install the BizTalk360 Db in the SQL server which holds Latin1_General_CS_AS collation, the installation will fail!
“ExecuteSqlStrings: Error 0x80040e14: failed to execute SQL string, error: Must declare the scalar variable “@environmentId”., SQL key: CreateTablesWin SQL string: ALTER PROCEDURE . @EnvironmentId uniqueidentifier”
The BizTalk360 database is created with a CI collation and BizTalk360 is installed by pointing the database to the CI collation DB. But, since the database was under the case sensitive collate SQL server, we had the same exception.
Query to create database with a case CI collation
CREATE DATABASE BizTalk360
In BizTalk360 v9.0 phase 2, the collation issue has been fixed across the application. We have revised the collation issue across each section in the application.
Checklist prepared to test SQL collation in BizTalk360
BizTalk360 supports many features like ESB, BAM, Azure etc. When having case sensitive collation for BizTalk360 Database, it is important that we look into all the sections of BizTalk360 (Operation, Monitoring, Analytics and Settings) to make it work correctly.
Say for example, BizTalk360 manages ESB exception data (EsbExceptionDb), in which it is important that all the column names, object names and procedures are exactly the same. We have nailed the compatibility of the BizTalk360 database with BizTalk Server database, ESB, BAM, Azure etc.
We always aim to improve the product by filling gaps in customer needs. Considering the same, we have improved the collation support in our upcoming release version 9.0 phase 2.
It is possible to have a combination of collation. Say, for example, SQL Server with the collation Latin1_General_CS_AS and BizTalk360 with Latin1_General_CI_AI. By achieving this, we have taken BizTalk360 to leading-edge technology in its standards.
The post SQL Collation Support in BizTalk360 appeared first on BizTalk360.
Recently, we released BizTalk360 v8.9. As always, each new release of BizTalk360 has completely new features in it, according to the customer feedback and suggestions we received. Keeping up with the business needs of our customers, version 8.9 has many beneficial features.
Along with the new features, we have enhanced quite a few features in BizTalk360. In our previous blog, we have covered few of these enhancements. In this blog post, I would like to provide a brief description about the following enhancements:
- ESB Service Name as a new filter option
- A new column “Resource Group” is introduced in Logic Apps (Operations & Monitoring)
- Data Monitoring Service Instances grouped by Error Description in Email Alerts
- Increased number of characters allowed in Alarm descriptions
- Ability to set Threshold Alert’s Start Time and End Time between 23.30 to 23.59 hours
- BizTalk360 v8.9 installer changes
ESB Service Name as a new filter option
In the ESB Exception Data feature, a query builder is available to fetch the precise data on a particular condition.
In version 8.9, we have added another filter property, namely Service Name. Choosing the Service Name in the filter properties will list the available service names as an option and the user can select the required option from the list. Thus, execution of the query will list the precise data in the grid. For even more detailed filtering, multi-combination of filter properties can be set.
We have implemented the same for ESB Data Monitoring; under the section Set Data Filter, the Service Name Property has been added. Selecting the Service Name in the filter will list the related options, so the Service Name can now be used for your ESB Exceptions Data Monitoring
Resource Group in Logic Apps
The Resource Group column has been implemented in the Logic Apps section, to have a clear picture over the Logic Apps being created under different Resource Groups in the same Azure Subscription.
In the previous version, all the Logic Apps under a subscription were listed. Quite often, a subscription will have different Resource Groups and each Resource Group can have their own set of Logic Apps.
There was an issue while display Logic Apps which are created in different Resource Groups, but with the same name. With the introduction of the Resource Group column, Logic Apps are now grouped by Resource Group in each configured Azure subscription.
Resource Group implementation is done in three areas, Operations, Monitoring and Data Monitoring.
Resource Group implementation in Operations and Monitoring
In the Operations section the Logic Apps are listed in the grid, based on their Azure subscription. This may lead to Logic App Name duplication, and to confusion in finding out to which Resource Group the Logic App is associated.
This is sorted in BizTalk360 v8.9, by adding the Resource Group column in the grid. Thus, once the Azure subscription is configured, the Logic Apps will be listed in the grid, along with the Resource Group to which they are associated. So, it will be easier to manage the Logic Apps by knowing their Resource Groups.
The Resource Group column is also implemented in the Monitoring Section, under Manage Mapping. When an alarm is set to monitor Logic Apps, it avoids name duplication, since Logic Apps are associated to the Resource Group.
Resource Group implementation in Data Monitoring
The implementation of Resource Groups in Data Monitoring helps in segregating the Logic Apps present under the associated Resource Groups.
In Logic App Data Monitoring, the Resource Group field is added under Set Data Filter. By filtering on Resource Group, only the associated Logic Apps will be listed and monitored.
Group By Error Description
In the Message Box Data monitoring section, the instance details were sent in the notification email, by grouping them based on the Error Code. In BizTalk360 v8.9, we have added the capability to group the instances by Error Description. This is done by implementing as a small feature under Settings / System Settings / Monitoring & Notification section, where you can find an ‘Enable Group By Description’ toggle button.
By default, Email notification errors are grouped by Error Code. Enabling this toggle button will result in grouping by the Error Description.
The Data Monitoring alert gets populated, grouping the instance details by the Error Description and list the count of it, along with its description.
As shown in the sample mail alert, having the grouping of description helps the user to have a very clear and precise clarity in the email alert for the suspended instances.
Increased the number of characters allowed in alarm descriptions
As you will probably know, the Alarm screen, in the Monitoring section, holds a Description field in which the user can feed up to 300 characters for providing a short description related to the alarm. This helps the customer to have a clarity over the alarm popped.
We now increased this limitation to 1000 characters, that in turn helps the customer to give a detailed description on the alarm. Exceeding the 1000 character will lead to a validation warning.
Hyperlink in Alarm Description
As an added advantage along with the 1000 characters, the Description field now accepts a Hyperlink and validates the HTML hyperlink tag. The field not only validates the hyperlink, it also validates basic HTML tags too.
Any syntax errors found in the hyperlink, will lead to a validation warning. By including the hyperlink tag, the customers can provide any necessary link which is related to the alarm.
We mainly have provided the hyperlink tag to comfort the user by allowing them to give more detail via a web page over the alarm created.
Threshold Alert’s Start Time and End Time increased to 23.59 pm
BizTalk360 has the capability to configure a threshold to trigger within certain time limits. The alarm configuration has a check field, “Set Alerts on Set day(s) and Time(s) only”. Once this check box is enabled, it will allow the user to access the Days Check and Time Selection, to precisely set the threshold time to trigger the alert emails during the particular days and times.
Any weekday can be selected, meanwhile timing for the day can be set by selecting the values in the Start Time and End Time dropdown boxes. The Start and End Time’s time value increases by 30 min, so any time can be set according to the need, under the condition “*Must be 60 mins later than start time”.
The lag was that, the Start Time and End time can be set only up to 23.30 pm. So, we were not able to fetch persisting violation details which occurred between 23.30 pm-23.59 pm.
As a solution to it, in the version 8.9, the End Time duration has been increased from 23.30 pm to 23.59 pm. By exceeding the End Time to 23.59 pm, the user will not find any lag in getting the violation alert. This way, the mapped artifacts of the Threshold alarm(s) can be monitored for the entire day.
BizTalk360 v8.9 Installer changes
The Installer is a facet of most software products. It is important to every Product organization to improve the installer from time to time, to provide a seamless installation experience. In our latest version, we made improvements in various aspects of the installer.
Rethinking the upgrade credential screen
The BizTalk360 installation package consists of three components:
- Web services
- Windows NT services (Monitoring, Analytics)
Users have the liberty to install the preferred components as per the business need. For an instance, the web component can be installed in a separate machine and the same counts for the other components.
Here is an interesting change we made in the Credentials update screen, for the upgrade scenario. What is the change? Previously, during the upgrade scenario, if all the components are installed in the same server, the user must provide passwords for all the components, even though all the components are using the same set of credentials. We have revisited this logic and made our installer to determine by itself whether all the installed components are using same credentials or different ones.
Let’s consider all the components are using the same credentials (User Name and Password). In that case, the installer will show only one section with the username and password. This reduces the user activity to enter the same password for the all the components.
Support for SQL authentication upgrade
Until the previous release, BizTalk360 did not support the upgrade, for the SQL authentication mode. In v8.9, the installer has been enhanced to support SQL authentication upgrade.
3.5 .NET dependency removal
For smooth functioning of the Azure services in BizTalk360, at a minimum .NET version 4.5 is recommended for BizTalk360 version v8.1 and later. The Installer blocks the installation if .NET 3.5 has not been installed in the machine. This restriction made users to install the .NET version 3.5. Now, this limitation has been removed.
Persisting config files during the update
To deploy BizTalk360 on HTTPS, your IIS must be enabled for HTTPS/SSL. To access BizTalk360 through HTTPS, there are few changes that need to be done in the BizTalk360 web.config file. Until our previous version, during the upgrade process, those changes were not persisted. After every upgrade, users had to do the changes again and again in the web.config files. As of the v8.9 upgrade, the changes made in the web.config files will be persisted.
We provided these improvements, hoping they will be helpful to you and make you feel easy while using BizTalk360. Happy migrating and try BizTalk360!!!