Home / server / Questions / 75144
Accepted
MartW
Asked: 2009-10-17 05:16:15 +0800 CST

Fully Upgrade SQL Database

  • 772

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 :

  1. Would we lose any functionality by leaving the DB in compatibility mode for 2000?
  2. 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?
  3. Is there any way to make the upgrade disregard the existing collation references and use the server default throughout?
  4. 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.

sql-server

2 Answers

  1. Best Answer
    1. you will lose the ability to run the newer commands but you won't lose any functionality unless you upgrade the application using the database (which right now can't possibly be using the 2008 features)

    2.See books online about Compatibility level changes. Note that these are language interpretation changes so you still get the improvements in the query engine etc.

    3.Not that I am aware of. You can change it via alter database collation. You should see little difference if the collation is SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, but both have instances where they are faster or slower than the other.

    Latin1_General_CI_AS :- Latin1-General, case-insensitive, accent- sensitive, kanatype-insensitive, width-insensitive

    SQL_Latin1_General_CP1_CI_AS:- Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

    You can get more ideas from fn_helpcollations.

    4.you could do that but by default your database compatibility level will be 100 which may not work properly with your legacy application. It's best to see what the latest version of SQL server the app supports and set that compatibility level.

    • 2
    1. Yes, you'll lose all 2005/8 stuff if you leave the DB in 2000 compatibility mode
    2. There is a section in Books Online that talk about features that have been discontinued. Give that a look.
    3. Not as far as I know. Of course you could do a clean install of 2008 and migrate the DBs across, but that would have to be in a named instance.
    4. I vote for this idea. You have a full back out rollback plan then.
    • 0