Migrate a db from sql 2000 to SQl server cluster 2005

Question

Migrate a db from sql 2000 to SQl server cluster 2005

maverick-885272

SSC Enthusiast

Points: 100
More actions
September 19, 2008 at 2:17 am

#193813

Hello,
Is there anything I should take in consideration, since I migrate from a standalone server 2000 to a 2005 cluster?
Thanks, Pierre

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

If you're just talking about the database considerations, there are none.

Standalone to Cluster is not an issue for databases, the only differences are at the instance/server level and those things are the obvious cluster/standalone issues like shared resources, etc.

From 2000 to 2005 there are the well documented differences, but from loads of experience, let me just say to watch out for the schemas that automatically get created. Delete them. You'll want to recreate the logins, etc. Just like any other DB move.

I would use the backup/restore method because its the easiest.

Don't forget to set compatibility mode to 90 when you've restored on your 05 cluster.

Cheers!

~Craig

Craig Outcalt

MCITP, MCDBA

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Update all statistics after you move to 2005, run a DBCC updateusage and I would recommend a CheckDB as well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Also, if your system is setup like most systems - then all of your objects are owned by the dbo schema. If so, you want to modify all users default schema to dbo (and remove the user specific schemas).

If all of your code uses at least two part naming convention - the above changes won't make a difference. However, I have yet to see a system where the developers do in fact use two part naming in their code.

Here is a nice article about this issue: http://blogs.msdn.com/sqlprogrammability/archive/2007/03/23/upgrading-to-sql-server-2005-and-default-schema-setting.aspx

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

Johan Bijnens SSC Guru Points: 135266 More actions · Answer 4

- I would first make myself comfortable with SSMS.

- regarding databases:

- rebuild all indexes

- sp_updatestats

- dbcc update_usage (0) with count_rows

- switch db level to 90 if possible. ( after tests with 80 )

- Also keep in mind, with sql2005 cmdshell is shielded off double (you need to enable it if needed)

- A number of SQL2005's features are "off by default".

Don't just open them, unless you really have to.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

maverick-885272 SSC Enthusiast Points: 100 More actions · Answer 5

Great, I have an idea where to go now!!!

Thanks a lot all.

Maverick.