Fun with TFS and SQL Server SP1

  • [アーティクル]

After successfully conducting a pilot deployment of TFS to a single-server running Windows Server 2003 SP1 and SQL Server 2005 RTM, the time came this week to deploy to production. To make things interesting, we decided to roll-out a dual-server configuration running Windows Server 2003 R2 and SQL Server 2005 SP1.

The good news is that it worked... eventually. While Win2k3 R2 posed no challenges, SQL Server SP1 turned out to be somewhat "precious". The latest TFS installation guide recommends that SP1 be used in place of the Analysis Services hotfix. It also recommends that you read the preparation steps and installation instructions for the service pack. That's good advice, but it probably understates the need to become familiar with the SP installation process. We encountered a few issues:

  • The service pack throws up a couple of question/warning/information messages such as "Pending Reboot Files Found". These are benign and you can answer "Yes" to proceed.
  • There are other messages about various files being "in use". This was true, but they were being used by components of SQL Server itself. While it would have been nice for the SP to have sorted this out automatically, we simply shut down the offending services and continued. We probably didn't even need to do this.
  • After "successfully" installing the SP and rebooting, "Integration Services" failed to start. It turns out that this happened to us because our SQL Server was buried deep within an EDC and didn't have Internet access: https://support.microsoft.com/kb/918644. The solution... install the "Cumulative hotfix package (build 2153) for SQL Server 2005".

If you're following this, I'd recommend you make a cup of coffee at this point. The hotfixes don't take too long to install, but you've still got a lot of installing to do (of TFS etc). Before leaving the topic of hotfixes, expect to see similar question/warning/information messages that you saw when installing the SP. As before, these are benign.

If you're like me and go trawling through server logs looking for things that are broken, you might stumble across the following message in the SQL Agent log:

[298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)

This is a side-effect of SP1 and is benign.

Despite all of this, the rest of the TFS deployment went smoothly. Those of you that used the Betas would know that the TFS installation process has come a long way. Based on this experience, it may have actually overtaken SQL Server for ease of installation (well at least SP1).

The take-away from this experience... make sure you trial your deployment script to the letter, so you know what to expect when you deploy TFS for real. If I had followed my own advice, I would have saved a few hours diagnosing "possible" SQL Server issues.