SQL 2000 --> SQL 2008

Question

SQL 2000 --> SQL 2008

SQLnbe

SSChasing Mays

Points: 601
More actions
August 6, 2009 at 1:41 pm

#140578

Is there any known Issue of moving SQL2000 DB files to SQL2008 ?!
We currently have SQL2000 and will be upgrading to a dedicated SQL2008 server soon to accommodate the growth and load.
Thanks
lee

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 1

Yes, there are quite a few known issues that you could run into. I would recommend running the Upgrade Advisor on your 2000 database before attempting to upgrade.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SQLnbe SSChasing Mays Points: 601 More actions · Answer 2

SQLnbe

SSChasing Mays

Points: 601

August 6, 2009 at 2:45 pm

#1035674

Thanks

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

I'd also recommend doing several tests on a separate box until you get it right. 😉 Once you get it right, schedule an outage so no one can change or insert any data, do the upgrade one more time on the test box, disable the original box, enable the test box as the "new" server, and then do your sanity checks.

In other words, I do NOT recommend an inplace migration on the original box.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 4

I'd recommend following;

1. Perform side-by-side upgrade.

2. run upgrade advisor.

3. DTS is depricated feature so you have an option to upgrade it to SSIS or download compatibility pack from microsoft to keep using your DTS on SQL 2000.

4. xp_sendmail didn't work so i had to configure dbmail.

5. Please note that once you have changed compatibility level from 8 to 10 you'll not be able to go back. so test, test and test....

Can think of above things from my experience.

Hope this helps.

Vivek

Vivek Shukla - MCTS SQL Server 2008

SQLnbe SSChasing Mays Points: 601 More actions · Answer 5

great , will keep that in mind .. now when you say upgrade advisor that would be the version that comes with sql2008 correct ?

thanks

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 6

Steve Jones - SSC Editor

SSC Guru

Points: 734552

August 17, 2009 at 9:33 am

#1040279

Yes, use the SQL Server 2008 version

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 7

yap.

http://www.microsoft.com/downloadS/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en

Vivek Shukla - MCTS SQL Server 2008

SQLnbe SSChasing Mays Points: 601 More actions · Answer 8

got it . I will start with this and go from there .

lee

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 9

also, have a look at the following. Its quite comprehensive.

http://www.sqlservercentral.com/articles/Upgrade/65872/

Good luck!

Vivek

Vivek Shukla - MCTS SQL Server 2008

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 10

Also, have a look at best practices for partition alignment. Server 2008 has special need.

http://msdn.microsoft.com/en-us/library/dd758814.aspx

Vivek Shukla - MCTS SQL Server 2008

SQLnbe SSChasing Mays Points: 601 More actions · Answer 11

will do , appreciate the help guys.

p.s. i'm still a grasshopper 😀

lee

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 12

we all are to the certain extent 🙂

Vivek Shukla - MCTS SQL Server 2008