Unmatached Default SQL collation in Deployment Scanner

[Steve710]

I am running the Deployment Scanner to upgrade from 8 Sp5 to 9.0.3  I get the following errors.  Since these are not related to the Directory Database, can they be ignored?  I'm told by Symantec that they need to match the master collation which is SQL_Latin1_General_CP1_Cl_AS but when trying thier documentation they provided I get an error on step #12 - http://www.symantec.com/business/support/index?page=content&id=TECH55063

Any thoughts or ideas are appreciated.

 DATABASE_SERVER: Database: EnterpriseVaultAudit - no mixed collations found.
 DATABASE_SERVER: Database: EnterpriseVaultDirectory - no mixed collations found.
 DATABASE_SERVER: Database: EnterpriseVaultMonitoring - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVConfiguration database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVConfiguration - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVDAExpedia database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVDAExpedia - no mixed collations found.
 DATABASE_SERVER: Database: EVNAEMail - no mixed collations found.
 DATABASE_SERVER: Database: EVNAJournal - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVVSGEUGroup_1_1 database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVVSGEUGroup_1_1 - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVVSGNAGroup_2_2 database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVVSGNAGroup_2_2 - no mixed collations found.
 DATABASE_SERVER\LYNC01: no Enterprise Vault SQL databases found on server.
 DATABASE_SERVER\LYNCDR: Unable to connect to server.
 DATABASE_SERVER: Database: EnterpriseVaultAudit - no mixed collations found.
 DATABASE_SERVER: Database: EnterpriseVaultDirectory - no mixed collations found.
 DATABASE_SERVER: Database: EnterpriseVaultMonitoring - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVConfiguration database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVConfiguration - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVDAExpedia database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVDAExpedia - no mixed collations found.
 DATABASE_SERVER: Database: EVNAEMail - no mixed collations found.
 DATABASE_SERVER: Database: EVNAJournal - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVVSGEUGroup_1_1 database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVVSGEUGroup_1_1 - no mixed collations found.
 DATABASE_SERVER: Unmatched default SQL collation: Server: SQL_Latin1_General_CP1_CI_AS; EVVSGNAGroup_2_2 database: Latin1_General_CI_AI.
 DATABASE_SERVER: Database: EVVSGNAGroup_2_2 - no mixed collations found.
 

[JesusWept3]

whats the error you get?

[Jeff_Shotton]

So the whole purpose of collations matching with the master database is if a transform needs to be done on text columns during the upgrade.

When the data is transformed in some way, the table is copied out into a temp table. Temp tables will take the master database collation. When a string match is then done to compare the original column with the temp table column, the compare operation will fail as you cannot compare text values(even if it looks the same) when the column collations are different.

Because support don't have a crystal ball to know what database modifications are coming up in future, the only advice can be to make your collations match so you dont hit a collation issue during upgrade in the future.

What you could do, is move the SQL_Latin1_General_CP1_CI_AS databases to another SQL server which has a default collation of SQL_Latin1_General_CP1_CI_AS during the upgrade, and then you could move them back afterwards. However, im guessing you just went through a SQL server consolidation exercise.

As Alex says, post the error. It might be something which is fixable.

Regards,

Jeff

[Steve710]

So would moving them to a server with the correct collation then moving them back change the default collation?  Then I could proceed with the upgrade after moving them back....  This would seem like the easiest fix...

The error received was when I ran step 12 in this document (in my case valutstoredb_8_programmability.sql  and was not pretty : 

here is part of the error

Msg 2715, Level 16, State 3, Procedure EVCreateArchive, Line 2
Column, parameter, or variable #1: Cannot find data type EVVaultID.
Parameter or variable '@ArchivePointVEID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVCreateArchive, Line 2
Column, parameter, or variable #2: Cannot find data type EVIdentity.
Parameter or variable '@ArchivePointIdentity' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVCreateArchive, Line 2
Column, parameter, or variable #3: Cannot find data type EVVaultID.
Parameter or variable '@VaultExistenceCheckVEID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVCreateArchiveFolder, Line 3
Column, parameter, or variable #1: Cannot find data type EVVaultID.
Parameter or variable '@ArchiveFolderVEID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVCreateArchiveFolder, Line 3
Column, parameter, or variable #2: Cannot find data type EVVaultID.
Parameter or variable '@ContainerArchiveVEID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVCreateArchiveFolder, Line 3
Column, parameter, or variable #5: Cannot find data type EVIdentity.
Parameter or variable '@ArchivePointIdentity' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVCreateArchiveFolder, Line 3
Column, parameter, or variable #6: Cannot find data type EVVaultID.
Parameter or variable '@VaultExistenceCheckVEID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageCollections, Line 3
Column, parameter, or variable #1: Cannot find data type EVSmallIdentity.
Parameter or variable '@PartitionID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageCollections, Line 3
Column, parameter, or variable #2: Cannot find data type EVIdentity.
Parameter or variable '@CollectionID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageCollections, Line 3
Column, parameter, or variable #3: Cannot find data type EVHSMType.
Parameter or variable '@HSMType' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageCollections, Line 3
Column, parameter, or variable #4: Cannot find data type EVPath.
Parameter or variable '@RelCollectionFilename' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageCollections, Line 3
Column, parameter, or variable #5: Cannot find data type EVFileFormat.
Parameter or variable '@FileFormat' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageCollections, Line 3
Column, parameter, or variable #6: Cannot find data type EVInteger.
Parameter or variable '@ReferencesMax' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageCollections, Line 3
Column, parameter, or variable #13: Cannot find data type EVInteger.
Parameter or variable '@RefCount' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageSavesetCollections, Line 3
Column, parameter, or variable #1: Cannot find data type EVSmallIdentity.
Parameter or variable '@PartitionID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure EVManageSavesetCollections, Line 3
Column, parameter, or variable #3: Cannot find data type EVInteger.
Parameter or variable '@Operation' has an invalid data type.

[Jeff_Shotton]

Not quite. With the SQL databases on another machine with the correct default collation (and suitable updates made to various tables to relocate those databases - see http://www.symantec.com/docs/TECH35744 ) then you would be able to upgrade worry free. The SQL collations issue is per server - different servers can have different collations, however you end up with issues if you were ever to consolidate - as per above.

Once upgraded THEN you could move back.

Regards,

Jeff

[Jeff_Shotton · Accepted solution]

What I think happened above is that the transform script did not actually complete properly and therefore missed objects when it finished. The problem is that these scripts do not end with obvious errors. The data types that you are missing are all listed in VaultStoreDB_8_Main.sql. These would all be in the database by default. Possibly you may be missing more objects, such as relationships between the tables.

The transform script essentially copies all data out into temp tables and rebuilds the entire database programmatically. If there are any problems along the way it just stops leaving you with half a database.

So...rather than start to post bits of code to 'correct' the errors, I suggest you get on the phone to support and they assist you with the process. I hope you have a database copy you can roll back to, or that this was an experiment rather than a live copy?

Regards,

Jeff

[Steve710]

I rolled back to a backup, I have a case open and will do the process with them tomorrow morning.

[GertjanA]

Hi steve, any luck in changing collation, can you advise what has been done?

Thanks

[Steve710]

Yes with the help of a Sr engineer at Symantec we fixed the databases that had collation issues using the steps in this document   

http://www.symantec.com/business/support/index?page=content&id=TECH55063

It should be noted that if the scanner shows your Discovery Accelerator db's have collation issues which mine did that you can ignore those 2 - in my case they are named Evconfiguration & EVDA...

Also they adivised I upgrade DA prior to Evault which I did and I had no issues...