Upgrading SQL Server 2000 to 2005. Best practice for a tidy install.

  • Hi All.

    I'm planning to upgrade a SQL 2000 instance to 2005, and wondering what approach would give the cleanest and tidiest install without "baggage" and clutter leftover.

    Edit: Project constraints: The server is (and will remain) windows server 2003.

    I'm happy enough with the db upgrade via detach/reattach, how about removing the old version of SQL Server?

    So far all links and references i have found refer to new features in 2005, and specific bugs and issues. but what I'm looking for is: X is a typical approach, Y is best practice, Z is a really bad idea.

    What are the options?

    1. Install 2005 to default directories, get a coffee and try not to think about it.:doze:

    2. Un-install SQL 2000 completely before installing 2005?

    3. Go crazy create a new windows server do a clean install, and migrate data. (Edit: not a viable option for me except as a last resort):unsure:

    4. Or do something clever that you're about to tell me... :blush:

    Edit: What i'm looking for is a generic conversation about difference options for upgrading from 2000 to 2005.

    Any advice or suggestions would be greatly appreciated.

    Cheers

    Pete

  • The cleanest install is always starting with a fresh OS and working up from there. I like Windows Server 2008 R2, even as a host of SQL Server 2005.

  • Hi, thanks for your answer 🙂

    Nadrek (8/16/2011)


    The cleanest install is always starting with a fresh OS and working up from there. I like Windows Server 2008 R2, even as a host of SQL Server 2005.

    Agreed, I would love to build a completely new server for this, but sadly the Application is only certified to run WinSvr2003 and SQL 2005. It would also mean re-installing the core application that also exists on that machine which is a painful process we want to avoid. I'm sorry I should have explained the project constraints a bit better in the original post. I have edited the OP to reflect that.

    Maybe my question should be more specifically about different approaches to upgrades/installations on a given server. I see I have 3 reasonable options, from lowest risk to highest risk.

    1. Upgrade/in place/over-write,

    2. Parallel install, (then un-install SQL 2000?).

    3. Uninstall 2000 -> install 2005.

    Option 1: How good a job does SQL Server do when upgrading? Does it work but leave a mess behind? are SQL 2000 objects/files left intact, or obsolete services running or are they cleanly removed?

    Option 2: Is this feasible? If I do a parallel install and then un-install 2000 components will it break anything? does anyone have any personal experience with this? Is it a cake walk or a nightmare?

    Option 3: If I un-install SQL2000 but leave the .mdf files intact, and then install 2005... How will 2005 handle this? Will it re-use the master database and retain the server and database logins? Or will it overwrite and destroy it. In short, will option 3 even work?

    Are there any other options I haven't considered? Am I being overly paranoid/pedantic?

  • Something to consider is how your 3rd party app interacts with the database. For example if you do a parallel install, the SS2005 instance will have an instance name other than the default and maybe a different port (assuming that SS2000 is using the default install on port 1433). So you will have to redirect your 3rd party app to the new instance.

    if you do the upgrade in place or from scratch you won't have those issues.

  • thepotplants (8/16/2011)


    Hi, thanks for your answer 🙂

    Nadrek (8/16/2011)


    The cleanest install is always starting with a fresh OS and working up from there. I like Windows Server 2008 R2, even as a host of SQL Server 2005.

    Agreed, I would love to build a completely new server for this, but sadly the Application is only certified to run WinSvr2003 and SQL 2005. It would also mean re-installing the core application that also exists on that machine which is a painful process we want to avoid. I'm sorry I should have explained the project constraints a bit better in the original post. I have edited the OP to reflect that.

    Are there any other options I haven't considered? Am I being overly paranoid/pedantic?

    Yes; use a drive image (Acronis Trueimage, Norton Ghost, Linux tools from the free Partedmagic ISO including simply copying partitions from drive to drive) of the server you have to create an identical machine* ON A SEPARATE NETWORK, then play with the options yourself! The clone starts with the same machine name, SQL Server instance name, IP address, etc.; that's why keep it off the main network (at first). Bonus - you can try over and over, and when you're ready, copy your data, unplug the old, plug in the new, and you're up.

    *Or buy the "restore onto different hardware" version of Acronis Trueimage or whatever and install onto different hardware.

  • Thanks to all who replied.

    This tale has a tragic end. well it would be the end.. if I wasn't still cleaning up. But as a warning (or amusement) to others who may follow in my footsteps... I shall share my woeful tale.. :doze:

    We decided that an in place upgrade was the simplest and most straight forward approach. (in part due to responses provided here.)

    For a variety of reasons ( I wasn't present) , the in place upgrade was aborted, and they (who shall remain nameless) performed an un-install of 2000 and install of 2005. :ermm: They reattached the databases and then bodged their way through the app upgrade, and got the app working sort-of...

    The following predicted things happened:

    1. Server logins were lost, passwords reset/changed

    2. Linked servers were lost.

    3. Automated database tasks & SQL agent jobs were lost.

    4. Some stored procs were lost or broken.

    I so I have spent all day playing SQL-wacka-mole. :angry:

    What I wasn't expecting was that the new server was installed with a different collation, from other SQL 2000 databases, which meant that once the logins and linked servers had been repaired, SP's containing queries which used remote tables in joins failed.. took me a while to fix those...

    Last but not least, as we had to reset a few passwords to get things working (we didn't know what the old one was) a few over looked database connections and applets have now stopped working as they contain hard-coded passwords :pinch:

    So it's 9:00pm... and i'm currently on the phone working with a developer who left here more than a year ago, trying to unpick their work and revive their app...

  • Ugh, I just finished the same type of install. I'm keeping my fingers crossed.

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

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