June 3, 2010 at 8:37 am
I've done clean installs of sql server 2005, but not an inplace upgrade of sql server 2005. Obviously, you need to backup all of the databases on the instance(i.e. in this case the default) and the only instance of sql server. In the event that for some reason the upgrade goes bad in the middle of the installation, I wonder what state the default instance will be in?(i.e. sql server 2000 or sql server 2005). Would I have to re-install sql server 2000 as part of the fallback plan?
My other understanding with regards to an inplace upgrade is that I don't have to worry about orphaned users. If the upgrade is successful everything is basically in a working state(i.e. the dts packages still remain). I know that I will have to download a copy of dts designer to work with the legacy dts packages, but I think thats it.
Thanks in advance.
June 3, 2010 at 8:47 am
Your state if the upgrade installation fails = screwed.
I've watched someone try an in-place upgrade and the installation failed for various reasons (mostly due to permissions not being setup prior to starting installation). We had to restore the server (which actually had to occur 3 or 4 times due to the Windows Backup not applying correctly) and completely re-install SQL Server 2000 to get it back to the previous state. The installation when you started the setup for 2005 would prompt to "complete upgrade", since it had failed. It had issues with permissions and the scripts the upgrade was attempting to execute against the isntance.
With regards to orphaned users, this is not an issue in doing a side-by-side installation, just the steps involved in doing it. The copy/migrate wizards and scripts already available on Microsoft and SCC make these steps a sinch to complete. As well side-by-side lessens the downtime of the databases.
Each option has its issues and warnings. It is just a matter of in-place upgrade may cause need to do complete restore. Where side-by-side just screws up the installation of SQL Server 2005, not your original instance of SQL 2000.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 3, 2010 at 9:44 am
I totally agree with Shawn. We've never done an in-place upgrade because of the "no fall back option" issues. Side-by-side migration offers the ability to have the SQL 2000 instance standing by if a fall back is needed and the migration can be done in steps e.g. copy logins, jobs, packages, etc before copying databases. We're usually ready for new hardware by the time we upgrade to a new SQL Server version anyway.
Greg
June 3, 2010 at 10:46 am
I've got no problem piling on in this situation. The principal problem with the inplace upgrade is that if you hose the system, you have to rebuild. That means your production outage is greatly higher than it would be if you did a side by side upgrade. With a side-by-side, you can completely hose the upgrade and immediately be back online with minimal down time. That's just too huge to ignore for most enterprises.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply