We have a SQL Server 2000 server that was formerly running on SQL Server 7, with the result that the default collation is not what seems to be the default set later for SQL 2000 and up (it is SQL_Latin1_General_CP1_CI_AS rather than Latin1_General_CI_AS).
Anyway, we're now planning to upgrade to SQL Server 2008 at last. While the upgrade advisor shows no issues apart from one maintenance sp referring to some legacy system tables, I was wondering how to do a 'full' upgrade of the DB, rather than simply leaving the database in compatibility mode which what seems to happen if we either do an upgrade in place, or restore/attach the SQL2000 database to a SQL2008 instance.
So I have various questions :
- Would we lose any functionality by leaving the DB in compatibility mode for 2000?
- If we change the compatibility mode to 2008, what could break? Would there be other settings we'd need to change to make it fully a SQL 2008 DB?
- Is there any way to make the upgrade disregard the existing collation references and use the server default throughout?
- Or is it just better to script the database again from scratch and import the data afterwards?
Basically we want to upgrade, but make sure that this legacy DB/server can fully gain from the functionality of the newer versions of SQL Server.