Questions about upgrade of SQL Server 2000 to 2005

Last Post 02 Mar 2006 03:46 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
StuartCoffey
New Member
New Member

--
23 Feb 2006 12:57 PM
In general our database is about 335 GB.

1) It is my understanding that there are several ways to upgrade our production SQL Server 2000
databases to SQL Server 2005 and here are 3 that I can think of:
A) As a 2nd instance, move logins and other master, msdb, model objects over from SQL Server 2005,
detach/attach our databases, cleanup old SQL Server 2000 files and registry entries as needed.
B) Upgrade to SQL Server 2005 over the top of SQL Server 2000
(with our databases attached or detached and then attached in a subsequent step).
This allows the master, msdb, model in SQL Server 2000 to be upgraded.
Cleanup of all objects that did not get upgraded e.g. DTS and stored procedures.
I doubt there would be any cleanup needed since we don't have any local stored procedures,
SQL Server scheduled jobs and have very few DTS objects.
C) Install SQL Server 2005 on a newly built server and migrate SQL Server 2000 master, msdb, model
objects and recovery our databases on this newly built SQL Server 2005.

My questions are as follows:
1) What is the disadvantage of upgrading over the top of SQL Server 2000?
2) Is having the 2nd instance and migrating the databases over using detach/attach a good idea?
And is the cleanup of SQL Server 2000 a concern on a production server
e.g. should registry items be deleted?
3) Is there a better option than the 3 mentioned?
4) Will the upgrade of a 335 GB database take long e.g. are there any structual changes
made to the SQL Server 2000 user database tables that I should be aware of?

Of course I have backups for my server in case a fallback is required.
SQLUSA
New Member
New Member

--
02 Mar 2006 03:46 PM
C is the cleanest.

Probably that is your way to dba happyness as well.

It gives you an opportunity to do a few improvements on the platform as well.

Also, since you don't have dts-s and jobs, you don't have to worry too much.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/orderdoubleheader/
The Best SQL Server 2005 Training in the World


Acceptable Use Policy
---