Weekend Jobs. I give in, I'll never make a DBA but at least Virtual Server loves me.

So regular readers will know that I tend to do home sysadmin tasks at the weekend. This one was no exception, apart from it being my birthday on Sat, so I took the day off ;). Sunday, a different matter, apart from the slight hangover. So what started out as a simple yet cunning "spur of the moment" plan turned into the nightmare from hell - plus a chance for a Monday morning blog rant (sorry!)

Mission: Upgrade SQL 2000 SP4 machine to SQL 2005.
Operatives: Me, IT Pro, Licensed to install.
Agents: MOM 2005 SP1 and WSUS.
Enemy: SQL Server 2000 & 2005
Undercover Contact: Virtual Server 2005 R2

True, maybe I have been over-doing Bond on the XBox recently, but yes, it really was like that. The enemy without doubt was SQL Server. Fortunately, MOM, WSUS and SQL 2000 for me are all Virtual Machines running under VS2005 R2. Hence, a quick save state and copy VHD's to a safe location was my first task.

This should be a piece of cake - will all be done before breakfast I think as I put the SQL DVD in VS host machine and start going through the install. So the first thing it told me was that my SQL VM didn't have enough memory. No problem fixing that one (although make mental note to start dropping hints to wife & kids to buy me 4GB of RAM for Christmas!) Next, I needed IIS as a pre-requisite. Again, no problem. Then it tells me it won't install to compressed NTFS drives - tedious, but still not hard to fix. Start the installer. Realise I messed up the original SQL installation (hence another reason why I'll never be a DBA!) by using the domain admin account as the service account for both SQL and the Agent. Hummmm. Forgot about that - in fact, I dug through my notebook and I did put a reminder to fix that one day, but long since forgotton. Breakfast long since been and gone by this point.

Service accounts should be easy to fix? A quick search found a KB article for the ultra complex way of changing the SQL Server and Agent Service account without using SQL Enterprise Manager. Hummm (again) - maybe not. Would anyone seriously do it that way? Insane. Read KB again feeling resigned to having taken on more work than I wanted to, but notice glimmer of hope when it mentioned you can doing it using Enterprise manager. This was hidden somewhere near the top of the article - I didn't spot it first time reading through. Tip of the day - always read KB articles from the start, even if 99 times out of 100, the useful information starts half way through ;). Dig through the SQL 2000 online help to find out how to do it using Enterprise Manager. Much easier, and now much more secure - using a standard domain user account :) Realise this really is going to be a long day afterall.

Start the SQL 2005 install again and find it craps out half way through. Some obscure error (should have written it down) which I couldn't find anything useful on the Internet about. Left machine half corrupt. Doh! Not to worry, our "Undercover Contact" for the mission is Virtual Server 2005 R2, so a quick copy back of the VHDs, repeat all of the above and a while later am ready to reinstall SQL 2005. Not going to be caught out this time, another quick save of the VHDs first.

Repeat above and craps out again. Try installing new Virtual Machine with just SQL 2005 - maybe it just didn't like the upgrade? Nope, same problem on a fresh VM. Don't believe that's possible even though I'd just seen it, so prep another fresh VM and try to install SQL 2005. Find the DVD won't read. Great. That explains the install failures. A while later, another copy of SQL 2005 downloaded and ready to roll burned to DVD.

So we're back on SQL 2000 box this time, go through the installer again, and it completes (eventually!). Great, reboot. However, a few nagging thoughts as its rebooting - why did it not ask me if I wanted to remove the existing SQL 2000 and upgrade the databases? Why did it not ask me the installation path?

Reboot completed and I find that both SQL 2000 and SQL 2005 appear to be installed. Worse, SQL 2005 is installed to the C Drive which now has 100MB of free space. Shout and swear at machine. Don't think it heard me. Thinking it would be quicker to revert back to the saved VHD rather than uninstall, the under cover contact, Virtual Server, saves the day and I run the installer again. Aha, paths for installation are under the advanced options. Change to D drive. Cunning and obvious? Not! Notice that it says something about existing databases during the install wizard, ignore totally assuming that's fine and am convinced it's going to do the upgrade from 2000 so click next and go through the install again. Surely it can't be that naive not to think I want both 2000 and 2005 installed together? More nagging thoughts as the reboot takes place once more post install. Notice have significantly more space on C drive. Good. Notice SQL 2000 and SQL 2005 are both installed. Aaaargh. Maybe I should have read the readme file or that dialog afterall? Too late now - had to take kids to the Microsoft Childrens Christmas Party. The entertainer, Mr. Lolly, was good!

...and so the story continues later in the evening. Where was I? Oh yes, SQL 2000 and SQL 2005 both installed. I know, I'll just uninstall SQL 2000. Asks loads of questions about whether I want to keep common components. Get bored clicking "no I don't want that obscure DLL which looks like it's in some SQL 2000 path", and after what felt like the 5,000,000th time, click "no to all". I said the machine wasn't listening earlier - still the case. It should have taken the hint - yes, I am uninstalling!!! After having clicked that, wonder if it was the right thing to do? Now be honest here - you do that too, don't you? Thought so. I just love pre-MSI un-installers. During reboot ponder that SQL 2000 (un)installer was written in VB4, 5 or 6. Surely not VB3? Conclude whatever it's written it, my guess probably isn't far from the truth. Oh well. At least the 2005 installer looked nicer.

Another reboot and seems OK. SQL 2005 running, SUSDB and OnePoint databases appear online and OK. Time to fire up the WSUS and MOM boxes to make sure they work. Well, to be honest I never got as far as the MOM box - the WSUS box was complaining about database connectivity. Spent far too much time before giving up and reverting back to the VHDs I saved at the beginning of the day. At least they still work and I did want some "proper" weekend relaxation afterall!

So at least this gives me something to work on over Christmas when I want to escape for a few hours. I'm just very glad to have my undercover contact, Virtual Server 2005 R2 to hand. Maybe between now and Christmas I'll look at the readme file for SQL 2005. It was much harder to upgrade than I thought it would be and I'm now convinced - I truly never will make a DBA.