Video Screencast Help

Errors Changing SQL Collation

Created: 13 Feb 2012 • Updated: 01 Apr 2012 | 5 comments
This issue has been solved. See solution.

We currently run Enterprise Vault 9.0.2 and are looking at upgrading to 9.0.3.  For the last couple of upgrades we've been seing the 'mismatched collation' errors in the Deployment Scanner but have not had the opportunity to take the time out to resolve it and it has not prevented the upgrades.  As I currently have a period of down-time due to an unrelated issue I have been trying to correct the SQL Collation errors prior to upgrading to 9.0.3 by following the usual technote: http://www.symantec.com/business/support/index?page=content&id=TECH55063&actp=search&viewlocale=en_US&searchid=1329133685543.  For most Databases this has been working fine but for a couple of databases some unusual errors have been thrown up.  I'll need to retry following a restore on the Audit and Directory databases but I have the errorlog from one of the Vault Store DB's. 

I followed the process of copying the first part of the programmability script and executed that, then ran the change_collation.sql (We're using SQL 2005) in report mode and it came back operation successful.  After clearing down the TempDB tables I then executed the script in normal mode and after running for just under an hour it completed with the error:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SavesetIdentity_SavesetProperty". The conflict occurred in database "EVExchangeArchiveStore1", table "dbo.Saveset", column 'SavesetIdentity'.

I now don't know if the whole script completed or whether it has stopped after the error during the -- CREATE FK CONSTRAINTS section of the script.

Has anyone seen this issue before?  The script seems to be running fine against the other Vault Store DB's.  I'm going to try and re-run the script again tomorrow following a DB Restore but would appreciate any advice or comments on what could have caused this.

Thanks

Craig

Comments 5 CommentsJump to latest comment

JesusWept3's picture

just as a matter of interest, which database is it reporting the collation errors on?

The script i've used before was this:
http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx

They say to do a find and replace on $destdb, but thats a little stupid, as the definition is right at the top,
just change the following

From:
:SETVAR destdb IOFFICE2000
:SETVAR desired_collation Cyrillic_General_CI_AS
:SETVAR script_only 0

To:
:SETVAR destdb EnterpriseVaultDirectory
:SETVAR desired_collation Cyrillic_General_CI_AS
:SETVAR script_only 0
 

Make sure the collation matches the Master DB collation and most importantly run it in Cmd mode and make sure that all EV Servers are off and not trying to access the db

CraigNJMc's picture

Hi JesusWept 2,

Thanks for taking the time to comment.  The deployment scanner was reporting collation errors on all the EV databases, we migrated the databases to a new server to improve performance a year or so ago and the Server collation was different to the original server used when EV was first installed.

The script you've linked to is the same one that is referenced in the technote I linked in my post.  I've seen a number of Forum posts about various problems with the collation script and tried to follow the technote and other peoples advise as closely as possible.  All EV services are set to manual and have been stopped and I've checked SQL Activity Monitor to ensure that no other process are active agains the EV DBs.  The script has completed fine against 5 other EV DBs so far.

As I mentioned, the script ran in report mode (:SETVAR script_only 1) and returned success so in theory there shouldn't have been an issue.  The script had completed the dropping of the constraints, primary keys, foreign keys etc, updated the collation to match the server and done some of the rebuilds before it flagged up with the constraint error which I thought was a bit unusual given that it didn't flag that issue up in report mode.  As the indexes and other bits and bobs hadn't finished rebuilding and the DB was still in Single User Mode I opted to do a full restore from the backup taken earlier rather than try and perform and troublehooting on a partially built database.

The restores have taken up most of today so I'll try and run the script again on the remaining 3 databases tomorrow to see if they complete this time. *fingers crossed*

JesusWept3's picture

id ont suppose you have a spare server where you could move the database to that EV won't touch, perform the collation changes and then move it back?

Typically though to be honest if EVERY database is showing a mismatch on collation, your best bet would have been to rebuild the master database to match the collation of the EV databases, that is unless some of the EV databases had different collations OR you use the SQL Server for databases other than EV

For instance if your master is using Cyrillic_General_CI_AS and ALL of your EV Servers are using SQL_Latin1_General_CP1_CI_AS, then you would really want to change Master to use SQL_Latin1_General_CP1_CI_AS instead.

However that may not be doable if you have other databases using the same SQL Server or each of the databases has a different collation etc
 

SOLUTION
CraigNJMc's picture

Rebuilding the master database wasn't an option as it is a central shared SQL Server, not dedicated to EV otherwise that would have been the ideal solution.  Ironically, the SQL server was built to use SQL_Latin1_General_CP1_CI_AS, which is the preferred collation, but the original SQL Server was built with the default windows Latin1_General_CI_AS so the EV databases had inheritted that.  I'd rather have them on the SQL collation anyway. 

The Audit DB restore is at 90% so I should be able to kick off the scripts again when I get in tomorrow, I'm hoping this glitch was just a temporary issue as I've successfully converted the collation on 2 other Archive Vault Store DBs and the Journal Vault Store DB since then. 

CraigNJMc's picture

Progress Report

Well, I've tried the scripts on a couple of the databases I was having problems with again. 

On the Vault Store Database:

- Ran VaultStoreDB_8_Programmability.sql up to the first CREATE statement
- ran change_collation.sql with :SETVAR script_only 1 - successful operation
- Ran script to drop TempDB tables
- ran change_collation.sql with :SETVAR script_only 0 - operation completed with errors, in the exact same place as before:

-- CREATE FK CONSTRAINTS
...
ALTER TABLE [dbo].[SavesetProperty]  ADD CONSTRAINT [FK_SavesetIdentity_SavesetProperty] FOREIGN KEY ([SavesetIdentity]) REFERENCES [dbo].[Saveset] ([SavesetIdentity])
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SavesetIdentity_SavesetProperty". The conflict occurred in database "EVExchangeArchiveStore1", table "dbo.Saveset", column 'SavesetIdentity'.

On the Directory Database:

- Ran DirectoryDB_8_Programmability.sql up to the first CREATE statement
- ran change_collation.sql with :SETVAR script_only 1 - operation completed with errors:

-- TRY TO CHANGE COLLATION....
ALTER DATABASE EnterpriseVaultDirectory COLLATE SQL_Latin1_General_CP1_CI_AS
Msg 5075, Level 16, State 1, Line 1689
The object 'CK_RegisteredClientMailboxes_ClientOSType' 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 5075, Level 16, State 1, Line 1689
The object 'CK_RegisteredClientMailboxes_MailboxServerType' 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 1689

I'm still confused why the Vault Store Database reports operational successful when running the collation script in report mode but then throws up an error when running in normal mode.

I'm not sure how to procede with the Directory DB as othen than running the programability script and ensuring no EV services are running and no other locks on the Database.

Has anyone got any other thoughts?

I've attached the full output of messages for the 2 processes

 

AttachmentSize
EnterpriseVaultDirectory_Collation_Change.txt 70.25 KB
EVExchangeArchiveStore1_collation_change2.txt 45.61 KB