Using SQL Mirroring with TFS

  • Article

Failover is a big concern for everyone using Team Foundation Server. What happens if the server hosting Data Tier of Team Foundation Server crashes? How can we get the whole system back up and running in least amount of time? There are various solutions to this problem. Traditionally we have recommended using Clustering for maximum availability. Clustering is a great solution, as it provides automatic failover that is transparent to end users. Another option is to use backup and restore mechanism of SQL Server. In this case users can take combination of Full and incremental backup of the Data Tier and if there is a failover of primary server you can easily recover the TFS Data Tier using the backed up data. This could be a very time consuming process.

Clustering provides automatic failover; Backup/restore provides long lead time recovering of server. SQL Mirroring can be used as a solution in between both of these options. SQL Mirroring provides warm standby but TFS does not support automatic failover with mirroring. Few manual steps need to be executed when the primary server fails with mirroring in TFS. We are working on a White paper to provide detailed steps on using SQL Mirroring for failover scenarios in TFS. We should have it ready within few weeks for everyone to use. Till then following are the high level steps required to use mirroring.

The process of configuring a VSTF 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
  5. Map Application Tier to talk to new Data Tier(Mirror Server)

 

More detailed Mirroring steps should be up on MSDN in next few weeks. I will keep all of you updated when that happens.