通过

Modifying Collation for BizTalk’s SQL server

  • 项目

Very sporadically I hear anyone asking about SQL Collation and modifying collation. There is very little documentation which talks about modifying collation for SQL server hosting BizTalk databases.
The following was added to BTS2010 & 2013 docs just last month. “BizTalk Server supports all case-sensitive and case-insensitive SQL Server collations except for binary collations. Binary collations are not supported”. The following documents were updated.

BizTalk Server 2013: Hardware and Software Requirements
BizTalk Server 2013: Preparing Your Computer for Installation
BizTalk Server 2010: The following files at https://www.microsoft.com/en-us/download/details.aspx?id=11503:
· Installing BizTalk Server 2010 on Windows Server 2008 R2 and 2008
· Installing BizTalk Server 2010 on Windows 7 and Windows Vista
· Installing BizTalk Server 2010 and BAM in a Multi-Computer Environment

The installation guide (only up to BizTalk 2009) mentioned “BizTalk Server does not support binary (case sensitive) collations”. However, there is no mention about modifying the existing collation of the BizTalk databases.
When you try to change the collation of a BizTalk database you would start getting messages which talks about object dependencies which does not allow the collation change. You may get messages as below:

Msg 5075, Level 16, State 1, Line 1
The column 're_ruleset.nVersion' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'x' cannot be set to Latin1_General_CI_AI.

Once you hit this message, any DBA would think of dropping the dependencies or certain columns to surpass this error. In SQL scenarios it is quite common to script out the dependencies and include them at a later stage.

After these changes, Would MS disqualify this environment from being supported? Is there any supported ways to make this change?

So here is the support statement for collation modification:
"It is not supported for customer to alter SQL collation once BizTalk databases are created on the SQL instance. This is because our database design causes a number of collation dependencies. The only supported route is to re-configure BizTalk Server (which re-creates the DBs)."

Written By
Jainath V R

Reviewed By
Shaheer AbubuckerMicrosoft GTSC, India