Migrating Team Foundation Server Databases

This article will be helpful if you are planning to move the databases used by your TFS deployment, to a new hardware. The databases to be migrated will depend on your deployment of TFS, and the components for which you choose to move the databases. This may include:

TFS Databases

Ø Configuration database (Tfs_Configuration)
Ø Collection Databases (Tfs_<CollectionName> there can be multiple databases, depending on the number of collections you have)
Ø Warehouse (Tfs_Warehouse, if Reporting is configured with TFS)
Ø Analysis Databases (Tfs_Analysis, if Reporting is configured with TFS)

The names of these databases may be different in your deployment of TFS.

Reports Databases

Ø ReportServer
Ø ReportServerTempDB

The names of these databases may be different based on your configuration.

SharePoint

Ø SharePoint Configuration Database
Ø Central Administration Content Database
Ø Content databases
Ø Service Application Databases (if any)

All the services (TFS, SharePoint and Reporting) will be running on the existing machines, only the backend databases will be moved to a different SQL server or an instance.

Below are the versions of products that have been used for the preparation of this blog

  • Team Foundation Server: 2013
  • SQL, Analysis and Report Server: 2012 with SP1
  • SharePoint: 2013

 

Permissions Required

  • Servers: Administrators (local Administrators group or equivalent)
  • TFS: Team Foundation Administrators and Admin Console Users
  • SQL Server: sysadmin
  • Analysis server: should be a member of the server administrators group.
  • SharePoint: should be a member of the farm administrators group

 

Migrating TFS Databases

The migration process will involve the backup and restore of the databases to the new SQL server and then running commands to remap and register the new location of the databases with TFS application tier. The below article provides a step by step description on migrating the TFS databases for Team Foundation Server 2010 version, and has been used as a reference for writing this blog https://msdn.microsoft.com/en-us/library/ms252516(v=vs.100).aspx

Points to be kept in mind

  • The migration activity will require an outage window, depending on the size and number of databases. So plan accordingly and notify the users in advance.
  • The TFS commands used in this article should be run from an elevated command prompt, in the below location:
    “Drive:\Program Files\Microsoft Team Foundation Server 12.0\Tools”
  • Below are the descriptions for the parameters used in the commands that should be run during the migration process.
    • / DatabaseName : Specifies the name of the new SQL server that hosts the databases for TFS, in addition to the name of the configuration database.
    •   / SQLInstances: ServerName1,ServerName2: Specifies the name of the server that is running SQL Server, in addition to the name of the instance if you want to use an instance other than the default instance. If you are specifying more than one server, you must use a comma to separate multiple pairs of server and instance names.
    • / AnalysisInstance: ServerName: Optional. Specifies the name of the server and instance that now hosts SQL Server Analysis Services.
    • / AnalysisDatabaseName: DatabaseName: Optional. Specifies the name of the Analysis Services database to be used with TFS if you have more than one such database on the server that you specified with the / AnalysisInstance option.

 

Steps for migration

  • Stop the services that TFS uses. Run the below commands to stop the TFS services and ensure that users will not be able to connect to TFS during the outage window:
    • IISRESET /stop
    • TfsServiceControl quiesce

clip_image002[4]

  • Backup the TFS databases which you are planning to migrate to the new SQL server. Connect to the SQL server Database Engine that currently hosts the TFS databases from SQL Management studio and backup the below databases by right clicking on the database and then choosing Backup from the Tasks option:

clip_image004[4]

    • Tfs_Configuration
    • Tfs_<CollectionName>
    • Tfs_Warehouse

The backups for these databases will be .bak files.

Connect to the Analysis services which hosts the Analysis database for TFS from SQL Management studio and backup the below database:

    • Tfs_Analysis

The backup for the analysis database will be an .abf file

  • Restore the databases to the new SQL server to be used with TFS by connecting to the target SQL server from SQL management studio. Right click on Databases and choose Restore and select the database to be restored by browsing to the physical location of the backup.

clip_image006

Connect to the analysis services from SQL management studio and restore the Tfs_Analysis database as well.

  • The databases in my case were:
    • Tfs_sreetfs12Configuration – the configuration database
    • Tfs_sreetfs12_DefaultCollection –collection database
    • Tfs_1305_Warehouse – warehouse database
    • Tfs_1305_Analysis – analysis database

The new database server to which I have restored my databases:
Sreerajtfs12 – default instance, Analysis instance was also on the same SQL server machine in my case, but shouldn’t necessarily be on the same server

  • Run the below command to remap the TFS databases to the new location. The RemapDBs commands will redirect Team Foundation Server to its databases, when they are restored or moved to a new location.

TFSConfig RemapDBs /DatabaseName: ServerName;DatabaseName /SQLInstances: ServerName1,ServerName2
[/AnalysisInstance: ServerName]  [/AnalysisDatabaseName: DatabaseName]

It is recommended to first run the command with the /preview option. This verifies the configuration and gives us a preview of the configuration changes that will be done while running the command. This is very helpful as it warns us of possible failures that can happen during the command execution. In case the preview fails with errors, resolve the errors first, before you proceed with running the command.

Running the command with preview:

TFSConfig RemapDBs /DatabaseName: sreerajtfs12;Tfs_sreetfs12Configuration /SQLInstances: sreerajtfs12
/AnalysisInstance: sreerajtfs12 /AnalysisDatabaseName: Tfs_1305_Analysis /preview

clip_image007[4]

Proceeding without preview, as no errors where reported while running with preview. If you receive any errors during preview, please resolve them before proceeding.

clip_image008

For more information on the RemapDBs command, refer to this link: https://msdn.microsoft.com/en-us/library/vstudio/ee349262.aspx

  • Run the TfSConfig accounts command to change the ownership of the moved databases to the current user. Make sure that the logged in account is a member of the TFS Administrators group in the TFS server and the sysadmin group in SQL server
    TFSConfig Accounts /ResetOwner /SQLInstance: ServerName /DatabaseName: DatabaseName

I ran the below command:
TFSConfig Accounts /ResetOwner /SQLInstance: sreerajtfs12 /DatabaseName: Tfs_sreetfs12Configuration

clip_image009[4]

This command will change the ownership of all the databases used by TFS.

  • Update the service account for TFS and the TfSReports or the data sources account, by running the below accounts command. Even if you are using the same account that was previously being used, this information must be updated in order to make sure that the accounts are eligible to be used with the new server.

Please note that in case you are using more than one application tier machine, make sure you run these commands on each of those machines.
TfsConfig Accounts /add /AccountType: ApplicationTier /account: AccountName
/SQLInstance: ServerName  /DatabaseName: DatabaseName

I ran the below command to update the TFS service account:
TfsConfig Accounts /add /AccountType: ApplicationTier /account:< AccountName>
/SQLInstance: sreerajtfs12 /DatabaseName: Tfs_sreetfs12Configuration
-where <AccountName> is the name of the TFS service account

clip_image010

The below command updated the TfsReports account:
TfsConfig Accounts /add /AccountType: ReportingDataSource /account:< AccountName>
/SQLInstance:sreerajtfs12 /DatabaseName: Tfs_sreetfs12Configuration
-where <AccountName> is the name of the Report reader account

clip_image011

For more information on the Accounts command, refer to this link: https://msdn.microsoft.com/en-us/library/vstudio/ms253107.aspx

  • Run the RegisterDB command to update name of the server that now hosts the configuration database for TFS, with the Application Tier. This will redirect the application tier to the new location of the databases.
    Please note that in case you are using more than one application tier machine, make sure you run this command on each of those machines.
    TFSConfig RegisterDB /SQLInstance: ServerName /databaseName: DatabaseName

I ran the below command:
TFSConfig RegisterDB /SQLInstance: sreerajtfs12 /databaseName:Tfs_sreetfs12Configuration

clip_image012

For more information on the RegisterDB command, refer to this link: https://msdn.microsoft.com/en-us/library/vstudio/ms252443.aspx

  • Restart the Team Foundation Server services so that users will be able to connect to the server. In order to do this, run the below command:
    TFSServiceControl unquiesce

clip_image013

 

Migrating Reports Databases

  • Backup the reportserver and reportservertempdb databases from SQL management studio.

clip_image015

  • Restore the reportserver and reportservertempdb databases from SQL management studio to the new SQL instance.
  • Open Reporting Services Configuration Manager
  • On the Database page, click Change Database. Click Next.
  • Click Choose an existing report server database. Click Next.

clip_image017

  • Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.

clip_image019

  • In Database Name, select the restored report server database and click Next.

clip_image021

  • In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.

clip_image023

  • Click Next and then Finish.

clip_image025

  • Validate the migration of Report Server database by browsing the URL for the Reports site. The site should open up fine and should show up the folders and reports for the team projects.

clip_image026

 

Migrating SharePoint databases

If you are planning to migrate the SharePoint databases as well, please refer to this article which provides the detailed steps to perform this operation.

Validate the migration

  • Once the migration of the databases is completed, validate the migration:
    • Check whether the changes are getting reflected in the Administration Console (in the Data Tier Summary tab, check if the details of the new SQL server is updated).

In my case, before the migration, owk2012u3 was the original SQL server and sreeratfs12, the new SQL server.

Before migration:

clip_image028

After migration:

clip_image030

  • If the changes reflect properly, bring down the original SQL server offline.
  • Check the connectivity to TFS server from Visual Studio.
  • Run a work item query and compare the results before and after the migration.
  • Create a new Team Project in one of the collections and verify that it succeeds.
  • Verify that the source control operations are working fine by going a check-out and check-in.
  • Browse the web access URL for Team Foundation Server.
  • Also check whether the Reports and SharePoint sites are functioning as expected.

 

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