Changing COLLATION on SQL Server 2008

Hi Guys,
I need to change the SQL COLLATION on all databases on our production server (SQL SERVER 2008)

1.  Can this be done without reinstalling SQL Server 2008?
2.  I read this blog and was wondering if anyone has used the commands and will collation be changes system wide for all dbs in the database instance?

http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx
3. Are there risks attached to changing COLLATION that I should know about?


Thanks so much,
John
johnmc33Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

geek_vjCommented:
>>I need to change the SQL COLLATION on all databases on our production server (SQL SERVER 2008)

Are you trying to do a server level collation change or user database(s) collation change?

If it is for only user databases, then it is pretty simple. However if it is server level collation change (ie master database collation change) then it requires rebuilding the master database.

>>Are there risks attached to changing COLLATION that I should know about?

This is a wide end question. Firstly, why are you trying to change the collation?

Collation change will typically change all the settings at SQL Server level like storing the data, data format etc

So, if you are absolutely sure about the collation change then only please proceed ahead.

If you need any clarifications, please revert.
0
cyberkiwiCommented:
Yes you do have to reinstall, but MS has provided a way to reinstall only changing the collation

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName

http://msdn.microsoft.com/en-us/library/ms179254.aspx

It is safe to use.

>> will collation be changes system wide for all dbs in the database instance?
No - you need to do this manually.  Not just the database collation, but also on all columns, and re-creation of all objects (proc, func, view) that have collation information.  See here for a way to do that and also a view of how big the task is.

http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation

>> 3. Are there risks attached to changing COLLATION that I should know about?
Not really, unless you have apps from suppliers that specifically use ascent insensitive or case sensitive collations and you go re-collating their db to some new server-wide default.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnmc33Author Commented:
cyberkiwi,

How do I find out current collation on my server ?
I will run the test and see what happens.

Risk: My client uses 1250 collation and we have built his server using Latin collation. He wants it changed. So my choice is ... reinstall SQL Server with correct collation and restore all databases.
or try to reinstall collations for each database ....

what do you think is best approach?

John

John
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

cyberkiwiCommented:
If by "uses 1250" you mean that his databases are already on that collation, then just change the server collation per the setup command.  However, if work has been done on the server for quite a while, you still cannot escape a rebuild/recollate of db objects, for example a create table without specifying collation uses the server default.

Restoring databases does not get rid of the problem - reinstall w/ collation change keeps all databases intact, but as usual, make full backups first - never trust computers.
0
cyberkiwiCommented:
--check collations of databases

select databasepropertyex('master','collation')
select databasepropertyex('mydbname','collation')

--check collations of columns

select o.name, c.name, c.collation_name, o.*
from sys.columns c
inner join sys.objects o on o.object_id = c.object_id
where c.collation_name is not null
  and o.type_desc not like '%System%'
  and o.type_desc not like '%INTERNAL_TABLE%'
0
johnmc33Author Commented:
Thanks,

I checked the collations ...

1.Master database has collation of SQL_Latin1_General_CP1_CI_AS ... this is what we've installed the database with.
2. checked one of the actual dbs and the collation is SQL_Latin1_General_CP1250_CI_AS ....  I restored these dbs to this SQL Server installation ...

client wants everything as SQL_Latin1_General_CP1250_CI_AS

So if I change collation using the setup command you detailed above ...  that should do the trick ?

John

John
0
johnmc33Author Commented:
If an instance of SQL Server is installed as  SQL_Latin1_General_CP1_CI_AS  collation but databases are restored as SQL_Latin1_General_CP1250_CI_AS  ....

which collation is used when requests are made to the db ?
0
cyberkiwiCommented:
Database default, unless it involves functions or temp tables - unless specified, the columns are returned collated server default; instant collation mismatch.
0
johnmc33Author Commented:
ok so I need to rebuild the master database as per command above correct?
0
cyberkiwiCommented:
That's correct
0
johnmc33Author Commented:
excellent support
0
johnmc33Author Commented:
Hi Cyberkiwi,

Sorry to bother you again.

Ran that command on the installation of SQL Server 2008 that needs collation changed ... I got this popup ...

Please go to the control panel to install and configure system components.

any idea why I would get this?

John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.