Consideration in consolidating 2x old SQL Server 2000 into 1x new powerful SQL Server 2008 ?

Hi All,

I'm in the process of consolidating the old Database server into SQL Server 2008 VM, what could be the things that I need to consider before I go ahead and attach detaching the DB instances into the new 2008 server ?

After I've done the restore for each instances, I'll set the compatibility level to SQL 2000, and run DBCC UPDATEUSAGE (0);
Who is Participating?
p_nutsConnect With a Mentor Commented:
Exactly.. But how many apps play by the rules.

The only way of knowing is to test. Start with the defaults in 2008 and change the level after testing if needed .

Also talk to the vendor. What do they support ..
Io as it will be running virtually. And I've had applications that wouldn't connect. So I'm always testing this and moving db by db.
Aaron ShiloChief Database ArchitectCommented:

i agree with p_nuts ive had problems to connecting with apps after moving to sql 2008 from 2000
you should do a POC first and see that you have no problem using your apps before migrating complitly
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
If you set the compatibility level to SQL 2000 then you have no advantage of SQL Server 2008. Please avoid this if possible.
Kobe_LenjouConnect With a Mentor Commented:
PatelAlpesh, this is not true. You do benefit from all the features and performance of SQL2008. Setting the compatibility level ONLY changes the way the engine handles certain situations that have drastically changed (for example default order: and a lot of other stuff.
Your database WILL be upgraded to the SQL2008 format internally, no matter what compat level you have.

The penalty of this low compat level is very small.
jjozAuthor Commented:
oh, so if I have a very old legacy application that can only uses the old SQL Server 2000 T-SQL, then I won't be able to use it eventhough I set the compatibility level to 80 --> SQL Server 2000 compat level ?
Kobe_LenjouConnect With a Mentor Commented:
On the contrary. The compat level 80 will make it work.

The compat level setting only changes the 'exterior behaviour' of the database.

You should however test this. If this app was written in a 'funky' way, it might just stop working.
By the way, if your app is playing by the rules it will just work, no matter what compat level you assign.
jjozAuthor Commented:
thanks !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.