Adventures upgrading SQL Server

Why is SQL Server so hard to manage? It tries to be everything for everyone. And mostly succeeds. But . . .

Trying to upgrade a server from SQL Server 2000 to SQL Server 2005. Al the docs say it should be easy, but it is not. First, shut down the client application and logged all the users off. Then I skipped two steps of questionable importance. I did not stop the services explicitly because I thought the update would, and I did not detach the dozen application databases from the system. I ran the 05 install, no errors, installed SP3 (Microsoft service pack 3 for SQL Server) and the latest BOL (Books on Line), started SQL Server Management Studio, entered a query to show the installed version, and it was still, after 90 minutes of installs, the old 2000. So I looked at the "Options" on a couple of databases and "90" (internal version of SS05) was not there. I had followed all the steps – why didn’t it work? Si I then reinstalled SQL Server 2005, and the services and internal version were the SS05. Ran the SP3 and BOL again. I though I was in good shape. Nope.

I tried to upgrade the databases to 2005 by running the Copy task to the same location, because that is what I understood the BOL to say. But it copied each database to the same name with “_new” appended, which meant the CLICK applications would not see them. A big waste of time I though, so I stopped the process. This left one of the most important large databases (11GB) locked. Even rebooting the server would not let SQL Server open the file. Thank the L*** we’re running all this on the latest version of VMware, and we had taken a “snapshot” before starting. We “reverted” to the original state of the server. In a couple of minutes 2 ½ hours of work were wiped out. Back to the drawing board.

Started the install all over again. Stopped the services, but did not do the detach the dozen databases. Ran the CLICK command “stopall” which stops all the services on the system used by the CLICK applications, including SQL, IIS, others, and their own services. After an hour of installing everything but Analysis Services again the services were still at SS00. No errors on the screen, and none in the event logs. What’s going on? So I did the install again, installing only the database server this time. This took maybe another 20 minutes. When it came up it was a SS05 system. The internal version "90" was available. So I installed SP3 and BOL. It seems to be working now. The applications are running ok. I went into each database manually and changed the version from 80 to 90. More testing after a break.

Next morning - the upgrade looks good. A couple of services needed to be restarted. The batch jobs, including transaction log backups, look ok. After restarting SQLSERVERAGENT they are running hourly.