Upgrading TFS from SQLExpress

Back Story

Some time back, I setup Team Foundation Server (TFS) on a server machine more or less dedicated to the cause. This was to test drive it to consider it as a replacement for legacy source control, requirements management, deployment, etc. Since it was a trial, run, I opted for keeping setup simpler initially, reasoning that I could expand later if I so chose. As a result, I didn’t bother with Sharepoint setup initially, and I allowed the default installation of a database, which was SQLExpress.

Once I got used to the features of the basic installation, I wanted to test out the more advanced ones, but this has proven annoyingly difficult. Setting up Sharepoint and trying to retrofit it on existing projects was an enormous hassle, and I wound up having to delete my old projects and ‘recrate’ them with Sharepoint. Granted, these were playpen sorts of projects, but there was actual work in them and they were useful — just not primetime. So, losing them would be a hassle. And besides, it’s kind of hard to fully test a system without using it to do something useful.

After letting the dust settle a bit on that annoyance, I decided I’d switch from SQLExpress to SQL Standard to get the full benefit of TFS reporting services (via SQL reporting services). This was another huge pain point, and I’m going to document below what I had to do. Basically, it involved backing up all the SQL Express databases, installing SQL Server 2008 standard, and importing those backups. This guide is by no means comprehensive and there are a lot of moving parts, so this isn’t exactly a walk through, but hopefully it will help save someone at least some annoyance in this battle and maybe shave a little time off.

Steps

These steps were taken as I followed the instructions at this link (my thanks to the blogger, as I found this to be the most helpful guide). I documented below any place I had to deviate from these steps (or google around to figure out how to do one).

  1. Downloaded and burned SQL Server 2008 R2 as an ISO on DVD
  2. Got annoyed because the DVD+ I burned doesn’t seem to be recognized by the server’s disc drive. (Just a problem with the server’s hardware, I think)
  3. Copied and extracted the ISO and ran the install locally.
  4. Got annoyed because I got some weird error message, which I resolved by following the answer here from Russel Fields.
  5. Ran the installer, accepting the defaults until I got to the Feature Selection step. Here, I definitely need Analysis and Reporting Services, which is the point, so I checked those. But, I just went hog wild and checked everything since I’m learning the hard way with TFS to install random features first and ask questions later.
  6. Then I had to give account permissions for a bunch of things, but the install was fairly unremarkable.
  7. The “solid hosting” link I followed was generally accurate, but I did need to figure a few things out.
  8. It didn’t detail exactly how to perform backup and restore — I did this by using SQL Server Management Studio. This isn’t particularly intuitive. I had to go into the databases folder, right-click on each database, and choose “Tasks->Backup”. I had to repeat this for each database and then do a restore in the new SQL server for each database.
  9. When you’re actually doing the restore, what you want is “from device”. “Device” is disk, evidently.
  10. There was a lot of bad noise when trying to restore. I kept getting messages about files in use and suggestions to use the (nonexistent) with replace option. Since I didn’t relish the command line, I got around this bit of idiocy by adding a “1″ to the end of the target database name and then deleting the one after the succesful restore. Apparently management studio’s mind is blown if you want your new database on your new server to have the same name as the database you’re importing. A real corner case, I guess.
  11. I had trouble again when re-installing application tier. I kept getting a message about no usable databases. I resolved this by looking in the logins of the old and new database and basically re-creating the permissions from the old in the new. In my case, I needed to create some local login called “wssservice”, and that did the trick.
  12. After that, I had to actually know the password to the “wssservice” account, which was interesting because I wasn’t the one who had chosen this password and he wasn’t around at the time. I never figured this out, but I did use my admin credentials to reset it.
  13. Finally, when trying to reattach, I had to do this for some reason.
  14. And, that was it. After rebooting the server for good measure, and re-activating my automated builds, I was able to check in, check out, build, etc.

At the end of all of this, the lesson that I took away for future reference with TFS is when installing it, install all of the bells and whistles if there is any doubt. Adding functionality after the install is painful, so it’s better to install things if there’s any chance you’ll need them.