Completely remove an application tier from TFS 2012

If you ever wondered if it is possible to completely remove an application tier from the TFS Administration Console, here is an answer for you. Yes, but it is tricky and may result in a broken TFS installation. It involves messing with the catalog in the TFS_Configuration database.

As a disclaimer, this method is absolutely not supported. Not by me, not by my company, not by Microsoft. Use at your own risks.

So if you decide to reproduce the steps I am going to describe, please BACK UP your TFS installation (especially the Tfs_Configuration database).

Now, a little bit of context before starting, TFS uses a set of tables known as the catalog to keep track of the different pieces of your installation. It contains a lot of things including the databases, the servers, the web applications, etc.

Whenever you had a new piece to the installation (app tier, reporting tier, etc.) it goes into the catalog. Our problem is that for the moment there is no way to tell when you completely remove an app tier. So even if you uninstall an app tier, it stays in the catalog and eventually it will go out of sight thanks to the "Filter out machines that have not connected in more than 3 days".

Our goal here is to remove the app tier from the catalog itself. Unfortunately there is quite a bit of steps, and even more things to be aware of.

The first warning is that the steps below will produce the desired output only if you completely remove the server from the installation.

For example, consider a server APP-SERV1 hosting an app tier and another APP-SERV2 hosting an second app tier and SQL Server Analysis Services.

If you want to simply uninstall the app tier from APP-SERV2 but keep the SSAS on it, there is a high chance that this procedure will break your installation.

Actually this procedure removes the server from the catalog including all the other components that could have been part of the TFS installation (databases, reporting, datawarehouse, etc.).

Assuming TFS-APP is the server we'll remove, here is a high level list of the steps we will perform:
  • find the catalog resource for TFS-APP,
  • find the catalog resources for TFS-APP services,
  • find the catalog node for TFS-APP,
  • find the catalog nodes for TFS-APP services,
  • find the properties of TFS-APP,
  • find the catalog node dependencies for TFS-APP and services,
  • remove the items we found.

1. First things first, open a connection to your Tfs_Configuration database.

2. Then we start by looking for our server in the tbl_CatalogResource and tbl_CatalogNode tables:

SELECT 'Resource >'
     , r.*
     , 'Type >'
     , t.*
     , 'Node >'
     , n.*
  FROM [Tfs_Configuration].[dbo].[tbl_CatalogNode] n
  JOIN [Tfs_Configuration].[dbo].[tbl_CatalogResource] r
    ON r.Identifier = n.ResourceIdentifier
  JOIN [Tfs_Configuration].[dbo].[tbl_CatalogResourceType] t
    ON t.Identifier = r.ResourceType
 WHERE r.DisplayName = '{SERVER}' -- Replace with your server name

3. Note the value of the PropertyId, ParentPath, ChildItem, ResourceIdentifier fields. They will be used to identify and then delete the relevant data.

4. Select the properties associated with the catalog resource from the tbl_PropertyValue table.

SELECT * 
  FROM [Tfs_Configuration].[dbo].[tbl_PropertyValue]
 WHERE ArtifactId = '{PropertyId}' -- Replace with the value from step 3

5. Select the relevant items from the tbl_CatalogNodeDependency, we are looking for a) the items TFS-APP depends on, b) the items that depend on TFS-APP and its children:

-- a)
SELECT *
  FROM [Tfs_Configuration].[dbo].[tbl_CatalogNodeDependency]
 WHERE ParentPath = '{ParentPath}' --Replace with value from step 3
    AND ChildItem = '{ChildItem}' -- Replace with value from step 3

-- b)
SELECT *
  FROM [Tfs_Configuration].[dbo].[tbl_CatalogNodeDependency]
 WHERE RequiredParentPath LIKE '{ParentPath}{ChildItem}%' -- Replace with values from step 3

6. The records returned by a) will be removed in the end.

7. From the results returned by b) note the values of the RequiredParentPath and RequiredChilditem

Warning: If the app tier was the only TFS component installed on the server, you should only have the "WebApplication" value  in the AssociationKey field, otherwise it means you had other components like databases or datawarehouse installed on the server. In this case, I recommend you make sure you understand the implications. For example, make sure the reports and warehouse were already migrated to another server (check in the TFS Administration Console).

8. Select the APP-SERVER children:


SELECT 'Resource >'
     , r.*
     , 'Type >'
     , t.*
     , 'Node >'
     , n.*
  FROM [Tfs_Configuration].[dbo].[tbl_CatalogNode] n
  JOIN [Tfs_Configuration].[dbo].[tbl_CatalogResource] r
    ON r.Identifier = n.ResourceIdentifier
  JOIN [Tfs_Configuration].[dbo].[tbl_CatalogResourceType] t
    ON t.Identifier = r.ResourceType
 WHERE r.ParentPath = '{RequiredParentPath}' -- Replace with value from step 7
   AND r.ChildItem = '{RequiredChildItem}' -- Replace with value from step 7

9. Note the values of PropertyId, ParentPath, ChildItem, ResourceIdentifier.

10. We have everything we need to start the actual removal of the server.

-- Delete property values
DELETE FROM [Tfs_Configuration].[dbo].[tbl_PropertyValue] WHERE ArtifactId IN ({PropertyId}) -- Replace with values from steps 3 and 9

-- Delete dependencies for TFS-APP
DELETE FROM [Tfs_Configuration].[dbo].[tbl_CatalogNodeDependency]
 WHERE ParentPath = '{ParentPath}'
   AND ChildItem = '{ChildItem}' -- Replace with values from step 3

-- Delete dependencies to TFS-APP and services (Web application)
DELETE FROM [Tfs_Configuration].[dbo].[tbl_CatalogNodeDependency]

 WHERE RequiredParentPath LIKE '{ParentPath}{ChildItem}%' -- Replace with values from step 3

-- Delete nodes
DELETE FROM [Tfs_Configuration].[dbo].[tbl_CatalogNode]
 WHERE ParentPath = '{ParentPath}'
   AND ChildItem = '{ChildItem}' -- Replace with values from step 3 and 9

-- Delete resources
DELETE FROM [Tfs_Configuration].[dbo].[tbl_CatalogResource]
 WHERE Identifier IN ({ResourceIdentifier}) -- Replace with values from 3 and 9

11. Open the TFS Administration Console, you should now see only the active app tiers in the Application Tiers section even if you uncheck the "Filter out..." checkbox.
If the list is empty, it means the catalog is broken and TFS cannot rebuild the dependencies. This can happen if you forgot to delete a dependency on a deleted node.
To find the source of the problem, open the Event Viewer, you should see some TFS Service errors with the following message:
TF246024: An error occured while attempting to build catalog nodes. A catalog node is missing the following path to its parent: [parent path].

One possible solution would be to find the tbl_CatalogNodeDependency record with the RequiredParentPath set to the value in the error message, and then delete the record.

Popular posts from this blog

Handling exceptions the right way in WCF (part 2)

Adding a delay before processing Textbox events

Change the deployment URL of a ClickOnce application