SQL Server Knowledge Sharing Network

Whenever any user (forum or usergroup) asks me about Upgrade gotchas and FAQs I will refer this SQLUpgrade-blurb post that I have made from my experience. Further I would like to enhance the topic of upgrade and things you need to watch about Collation settings, think the task when you have to perform the consolidation of databases from a 2000 version to 2005 version.

By default and as a best practice you must run through the SQL Server 2000 instance using Upgrade Advisor (problems you might get on that post) in order to get prior information on upgrade process. So when you think about default collation, the SQL will be selected with SQL_Latin1_General_CP1_CI_AS collation within 2000 version and change within 2005 version will be system databases running Latin1_General_CI_AS. This is where you need to careful to adopt the relevant collation setting for your master database and TEMPDB database! Yes, by default usage of when databases running SQL_Latin1_General_CP1_CI_AS they will be creating and referencing temp tables. As in documentation the TEMPDB is used more within SQL Server 2005, posts such as WhatToDo-WhenTEMPDB_Full will help you too.

Make sure you haven't changed any of the Windows locale to your local language, such as to UK that will have British English as language. As per the design the collation for tempdb always uses the default collation of the server. So in order to use with new collation settings within the upgraded environment you have an option to script out the databases schema having COLLATION setting turned on and edit the scripted text to use new collation, then reapply the changed script. When it comes to the data SSIS is only saviour to transfer the data between source and target database(s), though it is not an easy option but only way out if you have no other choice to change your code or ignore the changed collation settings.