Configuring TFS with the databases spread across different SQL server instances

This article will be helpful, if you are planning to do a hardware based move or an upgrade of TFS version with new hardware , and you have the TFS databases (the configuration and collection databases) spread across multiple instances of SQL servers.

Normally when we perform a hardware based move or an upgrade of TFS version,we restore all the TFS databases to a single SQL server instance and then run the configuration wizard for TFS, Upgrade or Application Tier only, which ever is applicable. TFS assumes that all the TFS databases have been migrated to the database server that we specify at the beginning.

But if you are planning to keep one or more of the collection databases on a separate SQL server instance than the instance that hosts the configuration database, you will have to run the RemapDBs command, and specify the SQL instances that the new TFS will be using, before TFS can get past the readiness checks.

I have captured two scenarios here, one is a hardware based move where we will be running the Application Tier only wizard and the other is an Upgrade scenario.

Notes:

1. All the screen shots have been captured on TFS 2013 update 2 version.
2. TFS 2010 databases were used to upgrade, to TFS 2013 update 2 version in the example.
3. I am using two SQL instances in the demo, sreerajtfs12 which hosts the TFS configuration database and some of the collection databases, and owk2012u3 server, which hosts one of the collection databases, in both the cases(Hardware based move and Upgrade).

Hardware based move- Running the Application Tier Only wizard

In this scenario, I am trying to migrate the existing TFS databases to two separate SQL server instances, in the manner mentioned in the Notes section above, and then run the Application Tier only wizard to configure the Application-Tier machine.

• The configuration and some of the collection databases were restored to sreerajtfs12 server.
• One of the collection databases were restored to the server owk2012u3.
• Installed TFS on the new application tier machine, and ran the Application-Tier Only wizard.
• Specified sreerajtfs12 as the SQL server instance and the setup automatically listed the available TFS 2013 Update 2 configuration database. Selected the listed configuration database and proceeded.

• The Readiness Checks failed as shown below, saying that the database connection strings are not valid, and I have to run the RemapDBs command to correct it.

Steps to run the RemapDBs command

Permissions required:

• Local administrator on the TFS Application tier server.
• The account should be a member of the sysadmin security group on the SQL servers involved.
• Open up an administrative command prompt and navigate to the below location: <Drive>:\Program Files\Microsoft Team Foundation Server 12.0\Tools
• Run the RemapDB command:
           TFSConfig RemapDBs /DatabaseName:ServerName;DatabaseName /SQLInstances:ServerName1,ServerName2
  Where:
  • ServerName: SQL Server which hosts the configuration database
  • DatabaseName: Name of the configuration database (usually Tfs_Configuration)
  • ServeName1, ServerName2.. : SQL servers which hosts databases for the TFS deployment, separated by comma.
  • For more details on the command, please refer the below link: https://msdn.microsoft.com/en-us/library/ee349262.aspx
• The command I ran was as below:
     TFSConfig RemapDBs /Databasename:sreerajtfs12;Tfs_sreetfs12Configuration /SQLInstances:sreerajtfs12,owk2012u3

Here sreerajtfs12 hosted the configuration and some of the collection databases and owk2012u3 hosted one of the collection databases.

Running the command with /preview switch will prompt the changes that will be made once the command is executed. It is highly recommended to first run the command with the preview switch, and make sure preview doesn’t show errors, before running the command. In case preview gives errors, you must resolve them before proceeding.

TFSConfig RemapDBs /Databasename:sreerajtfs12;Tfs_sreetfs12Configuration /SQLInstances:sreerajtfs12,owk2012u3 /preview

Running the command without /preview switch:

• Once the command completed successfully, re-ran the readiness checks, and this time it completed successfully. TFS is now ready to be configured.

Upgrading TFS with databases restored to multiple SQL instances

The procedure to be followed is the same in both the scenarios. Below are the screen shots captured for the Upgrade wizard.

• Restored TFS 2010 databases in the same fashion as in the previous example.
• Chose Upgrade wizard and ran the readiness checks.
• The Readiness Checks failed as shown below, saying that the database connection strings are not valid, and I have to run the RemapDBs command to correct it.

• Ran the RemapDBs command as mentioned in the Steps to run the RemapDBs command section.
  • First with preview:

• • Proceeding without preview as no errors were reporting during the preview:

• Re-ran the readiness checks, and it completed successfully.

• Configured TFS and the Upgrade process completed successfully.

Content Created by – Sreeraj Rajendran
Content Reviewed by – Romit Gulati