UPDATE 1: Unfortuately not everything is going well with the reports. We were finally able to track down that the issue was with the user account I was using to configure TFS. Evidently you need to be an administrator on the machine hosting the SSRS instance (not just an admin for SSRS). Without admin privileges you cannot access the WMI provider remotely which causes the configuration to fail.  Needless to say the network admins were not pleased. They tried to strip things down to the minimal privileges but nothing short of full admin worked.

UPDATE 2: We have run into another, more serious issue though, we can no longer create new team projects in any collection. I can create a new collection but creating any team project fails. It always fails when trying to create the SSRS reports and the error message is useless. The error message says it cannot connect to SSRS so the host name is bad, the connection timed out or the database is corrupt. Now I’m really annoyed. In a last ditch effort we are rebuilding the reporting databases. If this doesn’t work we may need to retreat back to the old server.

UPDATE 3: Someone from the TFS team responded that all you need to do to move the database is the following:

• Stop the collection
• Backup/restore the database
• Edit the collection settings to move the database

UPDATE 4: We finally resolve the team project creation issue. It turns out that moving the SSRS database and having TFS reconfigure to use it doesn’t restore all the permissions correctly. Thanks to this post I was able to run just the permission update scripts and we’re back in business.

I do see these options in TFS but I’m not sure whether that would include the configuration database (which got moved as well) or just the collection database. The next time I need to move the database I’ll have to try it and see.

Recently we needed to upgrade our TFS 2012 server to TFS 2013. The last time we upgraded TFS 2010 to TFS 2012 it was a nightmare so we took some more precautions this time. Alas they didn’t help. This upgrade proved to be just as big of a nightmare as the last time. Here’s my story of upgrading TFS based upon my 2 previous attempts.

Let’s start with our environment. We have the TFS 2012 application tier hosted on its own Windows Server 2008 R2 machine. When we first installed TFS 2012 we were running an older version of SQL that we couldn’t upgrade so we deployed SQL Server 2008 R2 to the TFS server along with SSRS. Later on we got a SQL 2008 R2 server up on another server so we moved just the TFS database to the new server. At the time we didn’t have any SSRS servers so we left the reporting DB on the TFS server. Our database servers are managed by our data group so we don’t have full permissions to the database.

As part of upgrading to TFS 2013 we have to upgrade our SQL server to 2012 because SQL 2008 R2 isn’t supported. Therefore we decided to break up the upgrade into 2 steps done about a week apart. The first step would be migrating our existing TFS and reporting databases to a new SQL 2012 database dedicated to TFS. Once it is on its own, dedicate server we can have better management of it. We would bring the current databases offline and let it bake for a week.

The second step would have us upgrade the TFS server to TFS 2013. We were doing an in place upgrade because: a) the server is more than sufficient, and b) we didn’t want to have to send out new URLs.

Problem 1 – Database Privileges

The first problem we ran into is with how TFS manages database permissions. Over the years we’ve had people come and go so I need to add and remove TFS administrators. Unfortunately this requires that you be a SQL admin. Why? I don’t know but it certainly causes the DB admins fits when I tell them I need SQL admin rights just to add a user to the admin console.

When upgrading TFS not only do you need full sysadmin rights to the SQL database but also SSAS (for reporting) and SSRS. This is understandable but it still doesn’t make the DB admins happy.

Problem 2 – Moving a TFS Database

The problem with moving the TFS database is you cannot do it in place. If you read the documentation for TFS it says you can.  It says to rerun the install (which should be really quick) and then when the configuration tool starts choose the option to upgrade. Alas the configuration options are all disabled.

The only real solution is to uninstall TFS and then reinstall it. Why can’t we have an option to move the database? Reinstalling TFS is a complete waste of time just to move the database. Even worse is that uninstalling TFS takes longer than installing it. Why can’t we access the configuration tool outside the installer? Looking at the command line it is actually the standard admin tool but with a parameter added so why can’t we have a shortcut in the Start Menu for it? Trying to reinstall TFS introduces the next problem.

Problem 4 – Reinstalling TFS

Have you ever tried to find the version of TFS that you originally installed with? In our case we were running Update 2 but every link to Update 2 will redirect you to Update 4. You have 2 options: keep looking or upgrade.

The only place I actually found Update 2 was by going back to MSDN Downloads. Why doesn’t MS provide easier access to the previous updates? Ideally we would just keep a copy of the update on the server but the full version is over 1 GB in size and our servers don’t have a lot of hard drive space (because they don’t need that much). Remember, the only reason you need the installer is so you can move the database. So I decided to go ahead and update to Update 4.

Problem 5 –  Restoring the Reporting Database

After restoring the database and installing Update 4 I started the configuration process to point it to the new database. The configuration tool found the database, confirmed it could be updated but it refused to work with the new SSRS instance. We had already copied the reporting database to the new server and restored the encryption key but it continued to fail with a TFS error saying the reporting server could not be found. The various suggestions it provided were all useless – firewall, permissions, bad name. We checked and rechecked everything but nothing.

At this point it was getting late and we needed TFS online so we decided to abort. We brought the original databases back online so I pointed our newly reinstalled TFS server to the new databases. It had no problem finding the database or SSRS instance. But it did let us know that we would be updating our database to Update 4. No problem. We let it do its validation checks and, beyond the firewall warning, it said everything would be great. We finished the configuration wizard, it completed the update steps and then began running the collection upgrades. At this point we got a failure in one of the scripts – “fails in WorkItemTrackingToDev11M55.sql’.

Problem 6 – TFS 2012 != TFS 2012 for Any Value of Update

Hmm, a quick google found this reference. Evidently Update 4 requires that you’re running SQL 2008 R2 SP1 even though TFS Update 2 does not.; Does MS even bother documenting system requirement changes for updates? Not everybody upgrades their servers every time a new update comes out.

Now we’re in trouble. We cannot upgrade our SQL database during an after hours TFS migration. For reasons unknown to anyone but the TFS team all the validations succeeded, the databases were partially updated but the update scripts failed. Why isn’t this caught back before it is too late? What is the purpose of running validation if you’re not catching things that will cause it to fail?

We ended up having to restore the databases to get them back to Update 2. By that time I had downloaded Update 2 from MS, uninstalled Update 4 and reinstalled Update 2. Now we were back up and running, at least for now. We went home defeated and annoyed.

Problem 7 – Reporting, Part 2

A few days later we were ready to move the databases again. This time we knew a reinstall of Update 2 was needed so we were ready. Uninstall, move databases, reinstall, run the configuration wizard. Again we ran into the problem where it could find the TFS database and even the Warehouse and Analysis databases but it refused to recognize the SSRS instance. We verified we could access SSRS but the installer refused.

We decided to risk it. We manually entered the reporting URLs and continued anyway. TFS did its configuration and everything checked out OK. But when we tried to run a report we got an error about the datasource. We verified the source was correct but the reports weren’t working. But this time we saw something about the databases needing to be rebuilt. Arg!! I just wanted to go home. We started the rebuild of the databases but it was taking too long to run so we left it and hoped for the best. Late that night we verified the rebuild was done and our reports were working. Success!!!

It was another brutal upgrade and all we really accomplished was moving the databases. We are still planning to upgrade to TFS 2013 but nobody is looking forward to it. Since the TFS upgrade won’t requiring moving servers or database we’re hoping it’ll be easier but experience says otherwise.

Final Thoughts

Is it always going to be this bad? I don’t believe so. I’ve run TFS in other places with the SQL database and application tiers on the same machine and I’ve had minimal trouble upgrading over the years. But I don’t believe the environment I described earlier is at all unique. So I’m confused as to why we have so many issues upgrading TFS when it is a standard use case. Why is it so hard to move a TFS database? Why do we have to uninstall/reinstall so much? Why does the installer do verification on the databases but still fail to catch obvious issues (like server versions)? TFS really has a long way to go before the upgrade process is smooth. Until then I’ll live in fear every time I need to update it.