This post was originally published here
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.
Note:
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
COLLATE Latin1_General_CI_AI;
GO
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.
Conclusion
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.