Can’t synchronize TFS warehouse after upgrading SQL Server to 2008

A few days ago, I upgraded the database of my TFS from SQL Server 2005 to 2008. Then the warehouse of TFS can’t be synchronized. Finally, I got time to troubleshoot this issue today.

Symptoms

I tried to invoke the web service http://TfsServer:8080/Warehouse/v1.0/warehousecontroller.asmx?op=Run to synchronize the warehouse, then an 6 error message were logged in the Windows Event Log immediately in TFS server. One of them looks like this:

Detailed Message: Cube processing runtime error: \r\nMicrosoft.TeamFoundation.Server.AnalysisServiceConnectionException: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator. ---> Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1600.22'.
   at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
   at Microsoft.AnalysisServices.Server.Connect(String connectionString)
   at Microsoft.TeamFoundation.Warehouse.OlapCreator.GetServerEdition()
   --- End of inner exception stack trace ---
   at Microsoft.TeamFoundation.Warehouse.OlapCreator.GetServerEdition()
   at Microsoft.TeamFoundation.Warehouse.OlapCreator.CommonInit()
   at Microsoft.TeamFoundation.Warehouse.OlapCreator..ctor(String serverName, String databaseName, String relationalConnectionString)
   at Microsoft.TeamFoundation.Warehouse.AdapterScheduler.RunCubeProcess()

The other 5 error messages were similar to this one. I list their error message without call stacks here so that people can reach to this post so no matter which error message is used as the keyword to search, .

1. Detailed Message: Failed to load adapter Microsoft.VisualStudio.TestTools.WarehouseAdapter.Adapter. Exception Info: \n Microsoft.TeamFoundation.Server.AnalysisServiceConnectionException: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator. ---> Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1600.22'.
2. Detailed Message: Failed to load adapter Microsoft.TeamFoundation.WorkItemTracking.Adapter.Adapter. Exception Info: \n Microsoft.TeamFoundation.Server.AnalysisServiceConnectionException: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator. ---> Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1600.22'.
3. Detailed Message: Failed to load adapter Microsoft.TeamFoundation.VersionControl.Adapter.VCAdapter. Exception Info: \n Microsoft.TeamFoundation.Server.AnalysisServiceConnectionException: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator. ---> Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1600.22'.
4. Detailed Message: Failed to load adapter Microsoft.TeamFoundation.Build.Adapter.TeamBuildAdapter. Exception Info: \n Microsoft.TeamFoundation.Server.AnalysisServiceConnectionException: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator. ---> Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1600.22'.
5. Detailed Message: Failed to load adapter Microsoft.TeamFoundation.Warehouse.CommonStructureAdapter. Exception Info: \n Microsoft.TeamFoundation.Server.AnalysisServiceConnectionException: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator. ---> Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1600.22'.

Assessments

The error message indicates that TFS can’t connect to the Analysis Service after upgraded. I then lunched the SQL Server Management Studio 2008 and successfully connected to the Analysis Service. So the Analysis Service should work fine. Since the issue began occurring after upgraded SQL Server. TFS might still use OM from SQL Server 2005 to connect to SQL Server 2008.

To verify my guess, I restarted the IIS by running iisreset command and checked assembly binding information with fslogvw. In the tool, I found an item for binding assembly Microsoft.AnalysisServices for w3wp.exe, which is the IIS worker process of TFS web site. Here are the log:

LOG: Redirect found in application configuration file: 9.0.242.0 redirected to 9.0.242.0.
LOG: Post-policy reference: Microsoft.AnalysisServices, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
LOG: Found assembly by looking in the GAC.
LOG: Binding succeeds. Returns assembly from C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices\9.0.242.0__89845dcd8080cc91\Microsoft.AnalysisServices.dll.
LOG: Assembly is loaded in default load context.

Obviously, the OM from old SQL Server 2005 was used.

Solution

In the installation folder of TFS, I navigated to Web Services\Warehouse, There’s a web.config there. After TFS 2008 SP1 is applied, it adds an assembly binding redirection for Microsoft.AnalysisServices. At the time when I installed TFS 2008 SP1, the SQL Server was not upgraded yet. So the Microsoft.AnalysisServices was directed to the version 9.0.242.0, which is from SQL Server 2005.

On my another machine that SQL Server 2008 was already installed when applying the TFS 2008 SP1. This assembly was directed to 10.0.0.0. So the assembly redirection is supposed to be manually updated after upgrading the SQL Server.

In the web.config, I redirected this assembly to 10.0.0.0 and restarted IIS. The problem was resolved.

      <dependentAssembly>
        <assemblyIdentity name="Microsoft.AnalysisServices"
                          publicKeyToken="89845dcd8080cc91"
                          culture="neutral" />
        <bindingRedirect oldVersion="9.0.242.0" newVersion="10.0.0.0"/>
      </dependentAssembly>

 

[EDIT] The SetupWarehouse command also utilizes the assembly Microsoft.AnalysisServices when rebuilding the TFS cube. So after upgrading to SQL Server 2008, you will also want to configure the SetupWarehouse.exe.config in the same manner.