Key takeaways from the blog
- About SQL Collation
- Why is SQL Server case insensitive?
- How do you check if a column is case sensitive in SQL Server?
- How do I change case sensitive in SQL Server?
- Why does BizTalk360 support SQL Collation?
- BizTalk360 is ready for BizTalk Server 2020!
As per customer feedback, BizTalk360 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. We have analysed the request and improved the support of SQL collation.
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 itself, supports case sensitive collation, to make it a consistent tool, it is so important that BizTalk360 also supports case sensitive collation.
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.
Why is SQL Server case insensitive?
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.
How do you check if a column is case sensitive in SQL Server?
To check if a column is case sensitive or case insensitive, you can execute the below query.
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<Name of the table that contains the column(s) you want to check>' If the output of the field contains ‘CI’, like in ‘Latin1_General_CI_AI’ then the column is case insensitive. If the output of the field contains ‘CS’, like in ‘Latin1_General_CS_AS’ then the column is case sensitive.
Note: 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.
How do I change case sensitive in SQL Server?
SQL Server collation can be changed in two ways:
- Changing the collation during the installation
- 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
- Collation
Note: To have case sensitive collation use the collation which holds CS_AS.
Why does 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.
Withitsmodern look and feel,BizTalk360 is ready for BizTalkServer 2020!
BizTalk360 helps you to manage/operate and monitor your BizTalk Server environments and is widely used by 650+ enterprise customers in 40+ countries across the world. Our team hasworked onthev10 releaseformore than one yeartocompletelyrefreshthe user interface.Also,based on the customer’s feedback fromourBizTalk360 feedback portal,several new features were developed.You can get a30 days trial version of BizTalk360and explore the product yourself or arrange for a personal demo of the product.
Conclusion
We always aim to improve the product by filling gaps in customer needs. Considering the same, we have improved the collation support from our release version 9.0 phase 2 on.
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.
