Changing collation

Question

Changing collation

SQLSlammer

Ten Centuries

Points: 1045
More actions
July 23, 2014 at 10:02 am

#293690

Hi,
I need a bit of advice, I am currently going through a migration to a new data centre and SQL server 2008R2- 2012 upgrades. I am relatively new to this job (but not being a DBA) and historically they didn't have a DBA before I turned up so you can imagine what state the database servers were in.
Currently our database servers are configured with the following collation SQL_Latin1_General_CP1_CI_AS and in our new environment I have elected to use Latin1_General_CI_AS, I know what is involved to change the collation (a bit of a ball ache).
My question is:
Am I signing myself for an unnecessary amount of work by changing to the correct collation, I know it's technically the right thing to do, but ultimately is it worth it given the massive task I have ahead of myself (100+ databases).
What would you do?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 1

You may very well be signing up for a lot of work (and troubleshooting afterward) that may not be necessary. What is reason for the change in the collation? Will it address some problem you are currently having?

lkennedy76 SSChampion Points: 13683 More actions · Answer 2

I agree with Matt, will it help solve an issue or is this a want not a must for the bosses?

MCSE SQL Server 2012\2014\2016

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

These two collations are effectively the same. They are both Case-insensitive, accent-sensitive, and have the same codepage, 1252.

select collationproperty('SQL_Latin1_General_CP1_CI_AS','CodePage')

select COLLATIONPROPERTY('Latin1_General_CI_AS','CodePage')

One is windows collation and one is SQL collation but I dont think one is more correct then the other unless your applications require it.

This KB article is specific to 2000/2005 but most probably still applies http://support.microsoft.com/kb/322112 .

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 4

If you decide to go ahead with the change, my recommendation would be to build empty databases with the new collation and copy the data across with a tool such as SQL Data Compare from RedGate. I had to go through a similar process a few months back (we had a variety of collations so standardising on one was worth it). Get an evaluation copy, try it on a couple of your bigger databases to see if the timings are practical and then decide.

Chris

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 5

Thanks for the replies, there is no technical reason I want to do this, just for standardisation reasons really and me being a bit anal.

Good to know that they resolve to the same code page, I think I might stick with SQL_latin1. Thanks for the Redgate advice I might consider that later down the line.