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.

Comments

  • Anonymous
    October 31, 2006
    PingBack from http://www.julian-kuiters.id.au/article.php/sqlserver2005-mirroring-tfs

  • Anonymous
    November 06, 2006
    One of our Program Managers, Sudhir Hasbe , has posted a draft of a whitepaper detailing how to use SQL

  • Anonymous
    November 24, 2006
    We continue to evolve and improve upon the TFS reliability and disaster recovery story. Fundamentally

  • Anonymous
    September 19, 2007
    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

  • Anonymous
    January 20, 2009
    こんにちは！ フォーラム オペレーターの服部清次です。 ^o^ ついに、アメリカではオバマ新大統領が誕生しましたね。 僕は、昨日の夜、テレビでオバマ新大統領の就任演説を見ようと頑張って起きていたのですが、直前に落ちてしまいました。。。