TFS Failover: SQL Mirroring

Some of you may have read by previous post on Mirroring with TFS at http://blogs.msdn.com/sudhir/archive/2006/10/30/using-sql-mirroring-with-tfs.aspx. This post is for Orcas or VS2008 release.

TFS leverages SQL Server for storing data. This allows our customer to leverage various failover options that come with SQL Server. SQL Mirroring is a great option. You can use clustering if you need automatic failover but if a small downtime is acceptable Mirroring is a good solution. TFS does not support automatic failover with mirroring. Few manual steps need to be executed when the primary server fails with mirroring in TFS. Following are the high level steps required to use mirroring.

The process of configuring a TFS deployment for mirroring can be defined in following steps

1. Backup databases from the main Data Tier Server(Principal server)
2. Restore backed up databases on mirror server.
3. Setup Mirroring for each database between Principal and Mirrored servers
4. Perform manual Failover to enable Mirrored Server as principal server.
   1. Manual failover when primary server is available can be performed using Management studio
   2. Manual failover when primary server is unavailable(Forced service Failover) can be performed using ALTER DATABASE command in TSQL Statements.

      ALTER DATABASE database_name SET PARTNER FAILOVER, where database_name is the mirrored database.

5. Map Application Tier to talk to new Data Tier(Mirror Server) using TFSAdminUtil RenameDT
6. Now you have the databases active but we store some messages in sysmessages table in Master DB and we also have few SQL jobs which will not get created by just restoring the databases. To restore these you have couple of options
   1. You can run repair on Application Tier machine which will fix the Data tier
   2. You can run TFSDB utility which is in Tools folder for TFS. This is quicker and much faster.

      TfsDb.exe repair /server:<servername> /property: "TFS_SERVICE_ACCOUNT=<TFSServiceAC>; TFS_REPORTING_ACCOUNT=<TFSReportingAC>; LCID=1033; VSTF_AS_INSTANCE=<ASInstance>; VSTF_AS_DATABASE=TfsWarehouse;VSTF_AS_ACCOUNT="

TFS Databases: TfsBuild, TfsIntegration, TfsVersionControl, TfsWarehouse, TfsWorkItemTracking, TfsWorkItemTrackingAttachments, TfsActivityLogging