December 7, 2006 at 8:53 am
Hi guys,
I am planning to upgrade one of our sql servers from 2000 to 2005. I ran the upgrade advisor already and everything looks fine so far.
I am not sure, but may be you guys can clarify. While running the upgrade, should sql 2000 be up and running / shutdown?
Also, anything I need to watch for?
Thx
Murali
December 8, 2006 at 5:13 am
Have you considered a full install instead of the upgrade? Create a new SQL 2005 instance, then upgrade the databases individually by restoring, or using DETACH/ATTACH. Ideally you would want to do this on a new server, but you could always create the SQL 2005 instance on the same server. After you have upgraded all of your databases, you then would uninstall the SQL 2000 instance.
If you are going to upgrade the instance, I would image the SQL installation package would take care of shutting down the instance when it needs to, similar to how the service packs are applied.
steve
December 8, 2006 at 7:45 am
if you are going to do the upgrade, make sure you have plenty disk space. My first upgrade, not sql 2005 system was on microsoft accounting. We have over 120 companies on it (I had to write lots of custom scripts because MS only supports to 99, but says unlimited, bug is reported, no fix, 1 year!). I had about 30 gig used and 40ish free. Upgrade died because of disk space...It used it all up!
Best bet, more work...backup databases and restore to 2005 like above!
December 8, 2006 at 8:11 am
If you shut down SQL 2000 the upgrade advisor wouldn't be able to connect to the databases.
If at all possible you want to do a fresh install of SQL 2005 on a new server. Or possibly on an existing server that has been completely wiped and rebuilt. Then move the databases over with a backup/restore or detach/attach. In theory you can install a new SQL 2005 instance on a SQL 2000 server and run them side by side, and while this would be better than upgrading the 2000 instance directly I would only do this on a test or development server. I'm too conservative to trust that procedure on a production server.
If you say this is impossible, that your one and only SQL Server must undergo an upgrade in place, then you have my sympathy. There are various ways this can go very badly and leave you with nothing functioning. This may be unlikely, but it is possible. Can you afford the risk?
December 8, 2006 at 9:40 am
Relying on the Upgrade Advisor may leave you with a broken database even if everything appears to check out.
To see why please see our page on the subject at http://www.innovartis.co.uk/topical/migratingto2005.aspx)
Malcolm
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
December 8, 2006 at 9:54 am
First let me clarify a few things first.
1. This server is a replica of the production server and not
the production server.
2. I have taken steps to do complete db backup on this
server from the database side.
3. I have taken steps to do complete os level backup after
shutting down sql 2000 associated services.
4. I am basically an Oracle DBA handed managing 300+
db on various sql flavors (7, 2000, 2005) and did
have applied sp4 patches on several sql2000 servers
including active-active clustered and non-clustered
servers.
5. I want to confirm should I have to keep sql2000 online
to do the upgrade / should I have to shut it down
before starting sql 2005?
6. I agree with all of you that step 5 is kind of risky.
I can install sql 2005 on a separate directory in the
same server and then can move db from sql 2000
databases to sql 2005 too which seems to be the
best suggestion so far.
7. If you had similar experience doing this, pls share.
8. I greatly appreciate your time and efforts to offer
suggestions in this regard.
Thanks
Murali
December 8, 2006 at 4:13 pm
There are two type of upgrades...
1. inplace upgrade in which you will not have any option to rollback...because your db are over written with 2005...only option will be reinstall sql 2000 and restore your good backup to rollback...
2. Side by side upgrade in which you have the option to rollbackup and use the old 2000 server...
Based on your first comment "1. This server is a replica of the production server and not the production server."...
I beleive you are doing side by side upgrade...
You can keep your sql 2000 up and running but make sure no transactions will go through otherwise you will endup with out of sync data with 2005.
Always side by side upgrade is best approach...
Read the following ...
SQL Server 2005 Upgrade Handbook
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
MohammedU
Microsoft SQL Server MVP
December 13, 2006 at 8:43 am
Just as a heads up warning (which I presume you already know - but just in case!), you really need to performance test the upgraded server. I'm experiencing a significant slow down on our test rig after the test upgrade (both ways tried in-situ and restore of a backup on a clean system). There are several articles about how to rebuild indexes etc etc after the upgrade to get performance back. But in my case this hasnt really helped out (to date!).
July 27, 2007 at 11:59 am
"..... Always side by side upgrade is best approach... "
I had been reading that the "Upgrade" was the easiest method, not side by side migration, because all your setting, logins, security, instance name, replication etc is all handled at once ... you don't have to recreate any of it on the new server.
I'm still trying to determine the best approach for us.
July 27, 2007 at 2:00 pm
One more thought - since you are going to be doing this 'side by side'... IF you have enought disk space. Rather than backup and then restore into new database...
1. Install SS 2k5
2. Detach the database(s) from SS2k
3. Copy the physical files over to the directory for SS 2k5.
4. (Re)attach to SS 2k5.
5. Reattach to SS 2k.
And now you can do a clean side-by-side comparison of two versions. You will probably have to synch up the logins in SS2k5, and then port over the jobs (maybe?) you have running in SS2k. You most likely will need to reschedule the existing jobs to start them up again. Just be careful that your machine is strong enough to handle the double load.
Enjoy the adventure!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply