January 26, 2011 at 8:08 am
Hi All,
To any of you DBA's out there ...have been reading up stategies of SQL2005 to SQL2008 upgrade which can be either "in place" or "side by side"
I have some questions and musings :
1) Looking at an "in place" upgrade does the new 2008 adopt the existing collation of the sql server it replaces ? Or does it install with its own default collation and magically upgrades the databases and all objects (I find it hard to believe its that smart..i might be wrong though)
2) In a "side by side" installation if you install with the default collation and you either restore backup of 2005 or re-attach of 2005 files which has a different collation we are going to have issues right ? Using the alter database command can change the collation of the database to the new one, but existing objects in the database still remain at the old collation right ?
So a massive exercise would have to be done for each and every object to upgrade the syscolumns table right ? and then everthing stored procs ..functions ,jobs etc. would all have to be impact tested...no mean feat
What is the general consensus out there ? what would be be the best strategy/course of action to adopt I suppose we could install SQL2008 with the existing collation of the databases you want to upgrade..but that sounds a bit defeatist
January 27, 2011 at 2:42 am
We generally use Windows collation on the newer SQL builds. There is no reason you can't have the same collation in SQL 2000/2005/2008 if you choose a windows collation.
This is an interesting read:
http://support.microsoft.com/kb/322112
"Windows collations are preferred unless there are backward compatibility issues or specific performance issues that require a SQL collation"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply