How to: Configure SQL Server Mirroring for the Team Foundation Data-Tier Server

  • Article

You can configure a second data-tier server to act as a SQL Server mirror of your principal data-tier server for Team Foundation. If you configure database mirroring, copies of all databases on the principal server are kept synchronized with the databases on the mirroring server. If your principal server becomes unavailable, you can manually swap the roles of the principal and mirroring servers so that the mirroring server becomes the principal server. For more information, see Mirroring the Team Foundation Data-Tier Server.

Note

Automatic failover to a SQL Server mirror is not supported for Team Foundation Server.

Required Permissions

To perform these procedures, you must be a member of the Administrators group and a member of the SQL ServerAdministrator group on the principal and mirroring data-tier servers for Team Foundation. For more information, see Team Foundation Server Permissions.

To prepare the principal data-tier server for a SQL Server mirror

  1. On the principal data-tier server, make a full backup of all the databases and the transaction log for Team Foundation Server. For more information, see How to: Back Up Team Foundation Server.

  2. Back up the encryption key for Reporting Services. Make sure that you store the key in a secure location on a different computer from Team Foundation Server and that you can access the key if you need it. For more information, see How to: Back Up the Reporting Services Encryption Key.

Install SQL Server to Act as the SQL Server Mirror

After you back up the databases, you must install a second data-tier server to act as the SQL Server mirror.

To install and prepare a SQL Server mirror

  • Install SQL Server on a server to act as the SQL Server mirror.

    For detailed instructions about how to make sure that SQL Server is installed correctly and operational, see the following topics in the installation guide for Team Foundation on the Microsoft Web site: 

    • "How to Install SQL Server 2005 for Team Foundation Server in a Dual-Server Deployment"

    • "How to Install SQL Server 2008 for Team Foundation Server in a Dual-Server Deployment"

    • "How to Verify SQL Server 2005 or SQL Server 2008"

    Important noteImportant Note:

    Be sure to install any required service packs as part of the installation.

Restore Data on the SQL Server Mirror

By using the restore tools that are provided with SQL Server, you must restore the data for Team Foundation onto the data-tier server that will serve as the SQL Server mirror.

Warning

You must restore all databases to backups taken at the same point in time or the databases become corrupted.

To open SQL Server Management Studio and view databases

  1. On the data-tier server that will act as the SQL Server mirror, click Start, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Management Studio.

    Note

    For more information, see "Implementing Restore Scenarios for SQL Server Databases" on the Microsoft Web site for either SQL Server 2005 or SQL Server 2008.

  2. Select Database Engine for the Server type. Select the appropriate Server name and Authentication scheme for the server. Provide a valid User name and Password if it is required by SQL Server, and then click Connect.

  3. Expand the Databases node to show the list of databases that make up the Team Foundation data tier. The list will be empty of Team Foundation databases until you follow the "To restore each database" procedure for Team Foundation databases.

Complete the "To restore each database" procedure for the following databases:

  • ReportServer

  • ReportServerTempDB

  • The configuration database for SharePoint Products and Technologies (STS_Config_TFS or WSS_Config)

  • The content database for SharePoint Products and Technologies (STS_Content_TFS or WSS_Content)

    Note

    The name of the database that contains data for SharePoint Products and Technologies will vary depending on the version of SharePoint Products and Technologies that is installed and whether the person who installed it customized the name. Additionally, if SharePoint Products and Technologies is installed on a separate server from Team Foundation Server, these databases might not be present on the data-tier server for Team Foundation. If they are not present, you must manage their backup, restoration, and configuration separately from Team Foundation Server. However, you should synchronize the maintenance of the databases to avoid synchronization errors.

  • TfsBuild

  • TfsIntegration

  • TfsVersionControl

  • TfsWarehouse

  • TfsWorkItemTracking

  • TfsWorkItemTrackingAttachments

  • TfsActivityLogging

Note

The name of the database that contains data for SharePoint Products and Technologies will vary depending on which version of SharePoint Products and Technologies is installed and whether the person who installed it customized the name of the database.

To restore each database

  1. Right-click the database that you want to restore, point to Tasks, point to Restore, and then click Database.

  2. In the Restore Database dialog box, select From Device in the Source for restore area, and then click the ellipsis button (…).

  3. In the Specify Backup dialog box, complete the information to specify the location of the backup file, and then click OK.

    The first backup you apply must be a full backup, followed by the transaction log backups, in the order they were created.

  4. In the Select the backup sets to restore area, select the backup sets to restore.

  5. From the left navigation pane, select Options. In the Restore Options area, select the Overwrite the existing database box.

  6. In the Restore the database files as area, verify that the paths that are specified match your current database paths.

  7. In the Recovery State area, click Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY).

  8. Click OK to dismiss the Restore Database dialog box and restore the database.

  9. If you are applying additional transaction logs, follow this procedure for each log backup in the order they were created starting from the one made after the full backup. For more information, see "Applying Transaction Log Backups" on the Microsoft Web site for either SQL Server 2005 or SQL Server 2008.

Configure the Team Foundation Data-Tier Server to Use the SQL Server Mirror

As soon as you have restored the Team Foundation databases to the data-tier server that will serve as the SQL Server mirror, you must configure the original data-tier server to use the other server as a mirror.

Important noteImportant Note:

You must allow for TCP traffic on the appropriate database mirroring port between the data-tier server and the server that you want to use as a mirror. The default port for this is 5022. You can configure SQL Server to use another port for mirroring.

To configure mirroring from the principal Team Foundation data-tier server to the SQL Server mirror

  1. On the principal data-tier server for Team Foundation, click Start, click All Programs, click Microsoft SQL Server, and then click SQL Server Management Studio.

    The Connect to Server dialog box opens.

  2. In the Server type list, click Database Engine.

  3. In Server name, click the principal data-tier server.

  4. In Authentication, click the appropriate scheme.

  5. (Optional) If you are using SQL Server Authentication, type valid credentials in User name and Password, and click Connect.

  6. Expand the Databases node to display the list of databases that make up the data tier for Team Foundation. You must perform the following steps for the following databases in the order in which they appear:

    • ReportServer

    • ReportServerTempDB

    • The configuration database for SharePoint Products and Technologies (STS_Config_TFS or WSS_Config)

    • The content database for SharePoint Products and Technologies (STS_Content_TFS or WSS_Content)

      Note

      The name of the database that contains data for SharePoint Products and Technologies will vary depending on the version of SharePoint Products and Technologies that is installed and whether the person who installed it customized the name. Additionally, if SharePoint Products and Technologies is installed on a separate server from Team Foundation Server, these databases might not be present on the data-tier server for Team Foundation. If they are not present, you must manage their backup, restoration, and configuration separately from Team Foundation Server. However, you should synchronize the maintenance of the databases to avoid synchronization errors.

    • TfsBuild

    • TfsIntegration

    • TfsVersionControl

    • TfsWarehouse

    • TfsWorkItemTracking

    • TfsWorkItemTrackingAttachments

    • TfsActivityLogging

  7. Right-click the first database that you want to mirror and then click Properties.

  8. In Database Properties, in the Select a page window, click Mirroring. On the Mirroring page, click Configure Security.

    The Configure Database Mirroring Security Wizard opens. Click Next.

  9. On the Include Witness Server page, click No, and then click Next.

  10. On the Choose Servers to Configure page, click Next.

  11. On the Principal Server Instance page, review the default information to make sure that it matches the information for the original Team Foundation data-tier server, and then click Next.

  12. On the Mirror Server Instance page, in Mirror Server Instance, type the name of the Team Foundation data-tier server you set up to act as the SQL Server mirror, and then click Connect.

  13. In Connect to Server, select the appropriate Server name and Authentication scheme for the server. Provide a valid User name and Password if it is required by SQL Server, and then click Connect.

  14. On the Service Accounts page, in Principal, type the name of the Team Foundation service account (referred to as TFSService). In Mirror, type the name of the Team Foundation service account. These should be the same account. Click Next.

  15. On the Complete the Wizard page, review the information, and if all information is correct, click Finish.

  16. When the wizard has finished setting up the mirroring endpoints, click Close.

    The Database Properties dialog box opens. Click Start Mirroring.

    Note

    In some configurations, you might need to change the identity for the instances of SQL Server to the service account for Team Foundation and restore the transaction logs before you can successfully start mirroring the databases.

  17. Repeat steps 8-13 for every Team Foundation database. At the end of the process, all Team Foundation databases should be mirrored from the principal Team Foundation data-tier server to the mirrored Team Foundation data-tier server.

See Also

Tasks

How to: Fail Over to a Mirrored Data-Tier Server

How to: Back Up Team Foundation Server

How to: Back Up the Reporting Services Encryption Key

Concepts

Team Foundation Server Permissions

Other Resources

Mirroring the Team Foundation Data-Tier Server