June 15, 2009 at 8:47 am
I'm preparing to upgrade a few instances of SQL 2000 to 2008. One DB in particular is over 1TB.
I've read through most of the info on the Microsoft site, and viewed the comparison charts extolling the benefits of side-by-side versus upgrade.
There are a couple of methods that I haven't seen mentioned, that I've been asked about. I'd like to get your opinions.
* What are the pros and cons of detaching the dbs, removing 2000, Installing 2008, and attaching the dbs?
* What are the pros and cons of detaching the dbs, upgrading to 2008, and then reattaching the dbs?
June 15, 2009 at 8:51 am
I'll probably the first to reply, as I know what some of the pros and cons are...
Regarding uninstalling 2000 and then installing 2008; the biggest con is that I now have to worry about remapping users. I see this as a huge headache.
A pro to this method would be a nice clean install the way it was meant to be. 🙂
Option 2, upgrading after detaching the dbs. Does this really buy me anything? The only pro I can see to this is that if the upgrade goes awry, I won't have to worry about restoring my database from backups, I'll just be able to install 2000 again, and attach the databases. I have to determine if that's less of a headache than trying to restore a > 1 TB database.
June 15, 2009 at 12:17 pm
Our plans are to perform most of our upgrades as side-by-side upgrades, meaning we install a new server with SQL Server 2008 and then backup & restore the 2000 databases to the 2008 server and upgrade that way. It's the most paranoid approach because the 2000 server and 2000 databases are never even taken offline.
If I had to do an in-place upgrade, I wouldn't remove 2000 and then upgrade because, as you say, you'll have to rebuild all kinds of settings, security, etc., that you'll want to maintain (although, it is an opportunity to clean things up, put the data on better drives, etc.) as part of doing an inplace upgrade. I'm not crazy about the detach/attach approach unless it also involves making a copy of the detached databases. I'm just a tad paranoid.
You are running the Upgrade Advisor right? Do you know about the Upgrade Assistant from Scalability Experts. It's worth checking out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2009 at 12:31 pm
Yes, I'm running the upgrade advisor as well as the Best Practices Analyzer tool for SQL 2000, but they don't really make any recommendations with regards to alternate ways to upgrade. I can't do the side-by-side installation because of the extra hardware that's required just isn't available to me.
June 15, 2009 at 12:41 pm
Grant Fritchey (6/15/2009)
Do you know about the Upgrade Assistant from Scalability Experts. It's worth checking out.
I saw where it was reference in the Upgrading from SQL Server 2000 to SQL Server 2008 white paper from microsoft. Other than that I don't know anything about it.
June 16, 2009 at 7:23 am
Hi,
We recently upgraded our production from 2000 TO 2008.
We did this by detaching all user databases, upgrading the engine and reattaching.
We don't have the resources for side by side which was my preferred choice.
It still worked fine, we have sql servers in India and Manila merging with each other and all we sure is that we rebuilt all the replication from scratch...everything else is working fine. We also reindexed with the new Index statements once everything was backup.
Our live environment is also running SQL 2008 on a virtual environment....we've had lots of fun with that but all seems to be working well now.
Regards
Graeme
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply