SQL Consolidation - Collation

matt_beatt
matt_beatt used Ask the Experts™
on
Dear All,

I am in the middle of doing a consolidation project.  Several of the databases i have marked for consolidation has a different collation from most of the others.  The others have standard SQL_Latin1_General_CP1_CI_AS but the others have Latin1_General_CI_AS.  What i was thinking about doing was after migration, running a script over my database to change the column collations to the SQL_Latin1_General_CP1_CI_AS collation and changing the database collation so that they are all the same.  My database uses the temp DB and was hoping this would help me avoid any problems in this area.  What i would like to know is what risks would i have, from a data and an application perspective.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> running a script over my database to change the column collations to the SQL_Latin1_General_CP1_CI_AS collation and changing the database collation so that they are all the same.

I don't think that as an easier process and the level of complexity grows if you have primary keys and foreign keys on some String columns. In that case, you need to

1. Drop Primary Keys and Foreign Keys
2. Change Collation
3. Create Primary Keys
4. Create Foreign Keys.

In order to do your Migration successful, you need to

* Script out both Data and DDL's using Database publishing Wizard

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

* Change the collation name in your DDL's either using Find and Replace or manually.
* Rerun the collation modified scripts to do the migration successfully.

Hope this clarifies

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial