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!!!