Solution to TFS Service not waiting for SQL Server 2008 databases to startup: Database Dependency Service sitting inbetween.

Question

If you are of the few customers who get the "SERVICE FAILED TO START" error for the TFS Service, then this may help your cause. Some users have reported that there is an event log for the TFS Service stating that "The configuration data for this product is corrupt".

Specifically this applies to Single Server (ATDT) deployments of TFS who regularly restart the server / virtual server. What happens is that the TFS Service tries to start and fails (after timeout) to start automatically upon bootup for either one of the following reasons:
(Manually starting TFS Service works fine however).

1. The SQL Server service (MSSQLSERVER) isn't started yet;
2. The SQL Server service (MSSQLSERVER) is started, but the databases aren't only yet (because their still being "recovered").

Note: This seems to only have became an issue with SQL 2008 SP1, moreover customers with TFS 2008 SP1 and SQL 2005 who upgrade to SQL 2008 SP1 seem to be the affected most.

For 1. the solution is easy - using the service configuration utility SC, you set the TFS Service to be dependent on the MSSQLSERVER. This way, when the server boots, it'll wait until the MSSQLSERVER has started, before starting the TFS Service. As an example:

sc config tfsserverscheduler depend = mssqlserver
sc config reportserver depend = mssqlserver
(the above entry is if you wish for SQL Server Reporing Services to also wait)

For some users this doesn't solve the problem entirely and the TFS Service still fails to start automatically and that because with SQL Server 2008 the database recovery process sometimes takes longer to bring the databases online, especially on "lesser" hardware (or as in our case a Hyper-V virtual server on an physical server that is aggressively shared by other virtual servers).

This would be issue 2. Unfortunatly I cannot alter the source code of TFS Service to retry its connection after some set time, before it fails to start. Bottomline, we were in need of a robust solution that would also work for other products with the same problem. Enter SQLDatabaseReady - essentially this is a service that we wrote that "sits in between" the MSSQLSERVER service and the TFSSERVERSCHEDULER service (or any other service you wish to use it for).

Simply put, you install and configure SQLDatabaseReady and with the help of the service configuration utility SC, you "chain up" a dependency between "successfully connecting the SQL Server databases" and whichever service you want to THEN start.

SQLDatabaseReady is a windows service that will attempt to start (service state = STARTING) and only when the databases (specified in the configuration for SQLDatabaseReady) are available and can be connected to, will it transition to a service state of STARTED. When this happens, any service that has been configured to depend on the SQLDatabaseReady service will begin to start (and find that they themselves can now connect to the same databases).

All you need to do is set whichever service you want to depend on the SQL.DatabaseReady service:

sc config tfsserverscheduler depend = SQL.DatabaseReady

Remember to edit the SQL.DatabaseReady configuration file (.config in install folder) and list the connection string(s) that you want it to verify is openable. Note this list is an all or nothing list. In other words, the service will only start once ALL of the connection strings can be opened.

In addition to the configuration, you are of course able to set global timeouts for when even the SQL.DatabaseReady service should timeout. You can even specify non-SQL Server connection strings (like Oracle) and you can of course make any service depend on the SQL.DatabaseReady service giving you a much wider and generic use for the utility.

How do I get it?

It's free.
It requires .NET 2.0 at minimum to install.
You may distribute it at will.
Download from http://www.silverskysoftware.com.au/SQLDatabaseReady.aspx

Hope it helps you as much as it did us!

---