October 10, 2011 at 1:46 pm
SQL Experts,
I recently installed a SQL Server 2005 instance in order to migrate from physical machines to VM. We just discovered that the old instance uses a collations that differs from the new instance (default collation).
Old collation:
SQL_Latin1_General_Pref_CP1_CI_AS
New collation:
SQL_Latin1_General_CP1_CI_AS (default)
So, initially, all system databases were SQL_Latin1_General_CP1_CI_AS and all user databases were SQL_Latin1_General_Pref_CP1_CI_AS. Using tempdb with another collation could cause issues with joins and sorting etc, so I successfully changed it to SQL_Latin1_General_Pref_CP1_CI_AS (copied model from old server restored it to new server and restarted SQL Server service).
My concern now is, is it a problem to have master and msdb (along with the server) collations differ from the rest of the databases? Will jobs be affected? Will this introduce performance issues? Any other components affected?
All components of SQL Server are utilized in this installation (SSAS, SSIS, Notification Services, Brower, Agent, Full Text, Database Engine)
Here are the specs for the new server (which match that of the existing). Note, they do not wish to upgrade to SP4 at this time.
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
October 10, 2011 at 8:46 pm
Well, for this being a new instance, you should apply SP4 !
No doubt about that, since you have to test it all.
Collation could indeed be an issue if you have multi db queries (don't forget tempdb issues).
A problem arises when using tempdb (temporary tables, …) work area with explicit creation of the temp tables without specifying the column collation. (create table #temptable (….) )
This will use the collation of tempdb, which is the one specified at install time.
There is another collation specified than the one of the existing databases.
The error message shown is like:
msg:Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
There is no other sound solution than to reinstall the SQLServer instance using the wanted collation and to convert the databases created using the default collation.
This operation can be facilitated by installing a new instance, migrating all users, jobs, …
it all depends on the fact if your apps are already configured to use your current new sqlinstance.
There is an undocumented way of changing the default collation, but since it is undocumented I'll not promote it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 11, 2011 at 2:37 am
ALZDBA (10/10/2011)
There is an undocumented way of changing the default collation, but since it is undocumented I'll not promote it.
It's undocumented, but Paul Randal says it's safe and I tend to trust him. 😉
However, if something goes wrong, I'm afraid MS support wouldn't give a damn what Paul said.
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply