One or more Enterprise Vault (EV) database(s) contain Unmatched Default SQL Collation

Problem

Enterprise Vault depends on having a uniform SQL collation across the Master and all of the Enterprise Vault databases.In addition, all the columns within tables in a database will also have a collation (Column Collation) which  have to be uniform and match that of the Database too.

An inconsistent SQL Collation will affect both upgrades as well as certain daily operations depending on where the mismatch occurs.

Error Message

The Veritas Quick Assist or the Deployment Scanner performed prior to an Upgrade will detect Collation Mismatch Issues. If detected the Collation Issue has to be addressed prior to performing any upgrade

Cause

Solution

The second check has to be done per EV Database and is to check the Column Collation.

Run the following SQL query against each Enterprise Vault Database. If the query returns any records, there is a collation mismatch between the columns and the database itself:

SELECT
      collation,
      sysobjects.name tablename,
      syscolumns.name as columnname,
      syscolumns.xtype
FROM
      sysobjects
      JOIN syscolumns on sysobjects.id = syscolumns.id and sysobjects.xtype = 'U'
WHERE
      collation != convert(sysname,DATABASEPROPERTYEX(DB_NAME(), 'Collation'))

The method used to address the Collation Mismatch varies depending on several factors

• Whether the master database is using a Supported Collation
• The level of the Collation Mismatch.
• The number of Enterprise vault Databases with a mismatch collation
• Whether the SQL Server host other third-party databases

As a guidance, please review the following scenarios:

1. Database Only Level Mismatch
   For example, if all the EV Databases have the same collation but the master is different, the SQL Server can be rebuild with the right Collation or the databases restored to a SQL server with the right collation.  
   
   This kind of intervention is usually the responsibility of the SQL Database Administrator (DBA) and to ensure whether any other third-party Databases which may be hosted on the same SQL server will not e impacted by this change.
   
   If this is not an option (for example because there are other databases on the SQL Server ) and the master database is using a collation supported by EV, the alternative is to change the collation of the EV database(s) to ensure they match the master collation.
   
    
2. Database and Columns Level Mismatch
   A database with this level of mismatch will require the collation of the database to be changed to ensure it is uniform. It also has to be ensure that the collation being changed to matches the master database and is supported by EV
   
    

Changing the Collation of an EV Database is an intensive process which can take several hours to complete depending on the size of the database. This step requires all schema-bound objects dependencies to be dropped and recreated after the change (such as all  keys, constraints, indexes, computed columns)

Please contact Veritas Support Services to assist with this type of collation change.

Supported Collations

When selecting a Collation Name it not only has to meet the business needs  (such as whether it is Accent Sensitive) but also meet certain criteria to be supported by Enterprise vault:

• The collation has to be Case Insensitive ( CI Flag )  such as SQL_Latin1_General_CP1_CI_AS
   
• Enterprise Vault does not support certain double-byte supplementary characters in the U+10000 to U+10FFFF unicode range and does not support SQL collations that include the supplementary character (SC) flag. Please refer to the following MS Article

Note: For more information, please see the Enterprise Vault Compatibility list located in our Documentation Repository. 

Applies To

This issue can occur when moving EV databases from one SQL Server to another, either during EV upgrade or as a standalone operation.