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
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
m1tk4
Collation is only specific to the database, not the engine install. In fact, it can be even specified on a lower level (tables, etc).
ASKER
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
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
> There can be quite real problems with having similar but different collations between the server and the database.
Such as?
Such as?
ASKER
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
Problems such as code that uses temp tables now doesn't work because of the collation differences which won't naturally compare.
Regards
David
ASKER
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_
David adds:
I've followed that advice, and reinstalled the systems in question.
Regards
David
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_
David adds:
I've followed that advice, and reinstalled the systems in question.
Regards
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Vee_Mod:
re. Proposed solution "button" You are kidding, right? :) Why don't you have the "site engineers" disable it for now?
re. Proposed solution "button" You are kidding, right? :) Why don't you have the "site engineers" disable it for now?