SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
thepotplants
thepotplants
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 428
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
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1917 Visits: 2729
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.
thepotplants
thepotplants
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 428
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?
thomas.tri
thomas.tri
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 473
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.
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1917 Visits: 2729
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.
thepotplants
thepotplants
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 428
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...
pikevillebastage
pikevillebastage
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
Ugh, I just finished the same type of install. I'm keeping my fingers crossed.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search