• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

Setup SQL and choose the collation

Fellow Experts:

I'm after an opinion: In the help or BOL, Microsoft doesn't make any mention of the option of say picking the appropriate Windows collation, and updating other systems which may link to this new server.

For instance, later this year, we are planning to upgrade including new hardware one of our two production SQL Servers. Quite likely some old hardware will be reused to upgrade other servers, and this may include the second SQL server. (Obviously the associated test and development instances will need to be upgraded to SQL 2005 ... )

I am considering the idea of changing the collation of the database and objects. Why is this not discussed in the BOL and help setup? Yes it is likely to be involved, but as a one off - okay two off, practice and then live - it shouldn't be that onerous.

Thanks
  David
0
David Todd
Asked:
David Todd
  • 3
  • 3
1 Solution
 
m1tk4Commented:
Collation is only specific to the database, not the engine install. In fact, it can be even specified on a lower level (tables, etc).
0
 
David ToddSenior DBAAuthor Commented:
Hi,

I do beg to differ. Collation in SQL Server is set on the server, the database, the column, or the expression.

There can be quite real problems with having similar but different collations between the server and the database.

Regards
  David
0
 
m1tk4Commented:
> There can be quite real problems with having similar but different collations between the server and the database.

Such as?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
David ToddSenior DBAAuthor Commented:
Hi,

Problems such as code that uses temp tables now doesn't work because of the collation differences which won't naturally compare.

Regards
  David
0
 
David ToddSenior DBAAuthor Commented:
Hi,

I got the following opinion from another forum:

It doesn't make sense to me that MS recommends the Windows collation. I can't see any benefit in changing existing databases to the Windows collation but I know the amount of work involved. In the system I did I had to script the whole database and remove the collation settings from the script. Create a new database and copy all the data from the original database... 30GB of it. If you have more than one database it's a mission or if there is a lot of referential integrity built in you then must do the tables in the right order or add this in later.

I would also set the new project to SQL_Latin1.. If your business is like any other business you can be sure that sooner or latr you will be restoring databases with different collations onto the same server and then all # tables start causing problems. In the long run I will always recommend that unless you have a COMPELLING reason to use a different collation, stick to SQL_Latin1_General_CP1_CI_AS.

David adds:
I've followed that advice, and reinstalled the systems in question.

Regards
  David
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
 
m1tk4Commented:
Vee_Mod:

re. Proposed solution "button" You are kidding, right? :) Why don't you have the "site engineers" disable it for now?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now