Sunday, 17 May 2015

A design consideration: SQL Server AlwaysOn Availability Mode for TFS

There are a few bits of any Team Foundation Server deployment which are either taken for granted, or completely ignored. One of them is:

Why shall I choose a Synchronous-Commit Availability Mode for the TFS Data Tier using SQL Server AlwaysOn?

If you are running an AlwaysOn Availability Group, you should know that you have a choice on how to write your Transaction Log – hence your database integrity. My suggestion is to run Team Foundation Server’s data tier in a Synchronous-Commit Availability Mode.

The reason is very simple: TFS relies heavily on the Transaction Log, and it is the only way to guarantee a point-in-time recovery. Moreover, if you run AlwaysOn you cannot have any database in the Availability Group set without the Full recovery mode.

So you must use the Transaction Log, and it is critical for the Team Foundation Server recovery – using the Synchronous-Commit mode ensures that each Secondary Replica in your Availability Group has an updated copy of the Transaction Log, synchronised at the same time as the Primary Replica, for immediate failover and service continuity.

You will face a really minor increase in the RTO (a few seconds, generally) and a performance loss, but we are talking about something you won’t be able to realise in general usage, and you are ensuring your service has an excellent reliability.

Posted by at

4 comments:

  1. Giulio Vian18 May 2015 at 07:45

    Totally agree. That's exactly the configuration we use on our main TFS instances.

    ReplyDelete
  2. Unknown19 April 2018 at 13:30

    I'm currently trying to move our TFS databases to an availability group. Do I only need to run the TFSConfig command using /sqlalwayson on the tfs_configuration database alone?

    ReplyDelete
    Replies
    1. Matteo Emili19 April 2018 at 13:54

      No, I believe you need to use that switch on every operation that affects a database which is part of an Availability Group. If you look at the docs (https://docs.microsoft.com/en-us/vsts/tfs-server/command-line/tfsconfig-cmd), every TFSConfig command has that switch.

      Delete
  3. Unknown19 April 2018 at 15:17

    Thanks for the reply! Apologies, I was a little unclear... I'll be using the TFSConfig RegisterDB command as "Use RegisterDB to update name of the server that hosts the configuration database" covers what we're attempting to achieve. For example, we are moving the databases from OURSERVER\SQLINSTANCE to AVAILGROUP.OURDOMAIN.COM

    ReplyDelete

Subscribe to: Post Comments (Atom)