Collation change during upgrade

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Collation change during upgrade

Author

Topic

richard102
Starting Member

25 Posts

Posted - 2014-07-31 : 11:22:17

Greetings :)

I've been through the pain of upgrading sql 2000 databases to sql 2012 (via 2008r2) although the new box (sql2012) is not yet live.

I notice the COLLATION of the migrated user databases is SQL_Latin1_General_CP1_CI_AS and the server/system collation is Latin1_General_CI_AS.

QUESTIONS:

1) does it matter? they're almost the same

2) how would I have changed an individual databases (and contents) collation during the upgrade? (IE: backup / restore onto 2008r2 (autoupgrade), backup / restore onto 2012 (autoupgrade)). I'm happy to go through the pain again if best.

3) wouldn't it now be easier to just change the server and system databases to match the upgraded user databases (SQL_Latin1_General_CP1_CI_AS)?

4) and wouldn't 3) above ensure continued smooth operations with the rest of the environment? (lol looks like I'm talking myself into 3).

Richard

thanks

richard102

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-12 : 05:50:29

1) does it matter? they're almost the same

It matters if you need to cross query the old db for any reason and if you use link servers to cross query.

2) how would I have changed an individual databases (and contents) collation during the upgrade? (IE: backup / restore onto 2008r2 (autoupgrade), backup / restore onto 2012 (autoupgrade)). I'm happy to go through the pain again if best.

You can set the collation level during setup and also after by going to the db properties.

3) wouldn't it now be easier to just change the server and system databases to match the upgraded user databases (SQL_Latin1_General_CP1_CI_AS)?

In most cases yes, depends on what you need the old db for, keep the old db as is and copy it making a new db with the new collation for querying. Even after changing the collation level at db level you will still in most cases need to add the collate function to your queries, i.e.
Select [mycolumn]Collate SQL_Latin1_General_CP1_CI_AS as mycolumn

4) and wouldn't 3) above ensure continued smooth operations with the rest of the environment? (lol looks like I'm talking myself into 3).

As long as you use collate in your queries you can cross query.

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources