TFS Install with SQL Server 2016 AlwaysOn

If you’re curious about the steps to install TFS with SQL Server AlwaysOn I will be going through them and it’s really very easy.  This blog post assumes the availability group and listener is already up and running.

Windows Server 2016

One new features I loved was I could now use a Cloud Witness as part of my Windows Server Failover Cluster (WSFC) instead of a file share witness!

Configure Cluster Quorom – Cloud Witness

SQL Server 2016 AlwaysOn

If you were looking for a reason to upgrade your SQL Server AlwaysOn environment you may be interested to know that the AlwasyOn Availability Group in 2012 and 2014 will not failover if a database fails due to a log or data disk failure (for example).  SQL Server 2016 has enhanced the AlwaysOn health diagnostics with Database Level Health Detection which shows up during the creation of an Availability Group.   You’ll see the check box on the dialog where you name the Availability Group.

Specify Availability Group Name | Database Level Health Detection

Of course if you aren’t ready to upgrade quite yet the AlwaysOn support Team has posted a article where they describe how you can add a generic script resource to help detect  availability group drive health.

TFS 2015 Update 3

Let’s assume you already have the name of your SQL Server 2016 Availability Group Listener and you would like to install TFS, to start you must request that the account used to configure TFS is a member of the SysAdmin server role in SQL Server (on all nodes).  During configuration TFS will create the databases Tfs_Configuration and Tfs_DefaultCollection on the Primary node of the AlwaysOn Availability Group.

TFS 2015 Update 3 – Configuration Process

If we logon to the Primary Node/Replica using SQL Server Management Studio (SSMS) we can see the databases have been created.

Enter a captionSQL Server 2016 – Newly created TFS databases not part of the Availability Group

After I’m done the configuration of TFS 2015 Update 3 I turned the virtual machine off so I can backup those two (2) databases and restore them on the secondary node in my AlwaysOn Availability Group.

Step 1:  Take full database backups of both databases and copy them to the secondary node, here is s screenshot of the backup of Tfs_DefaultCollection from within SSMS

SQL Server – backup database Tfs_DefaultCollection

Step 2: Now that the two (2) database backups on on the secondary node we can restore them (WITH NORECOVERY) using SSMS directly to the seondary node.

Restore the databases on the secondary node making sure you select WITH NORECOVERY

Step 3: In SSMS connected to the Availability Group Listener expand the Availability Group you want to add the TFS databases to and right click on the Availability Databases node and select Add Database…

Availability Databases | Add Database…

Step 4: Select the databases you want to add to your availability group and click Next

Select the TFS databases to add to the availability group

Step 5: Select Join-only and click Next

Select Initial Data Synchronization – Join only

Step 6: Connect to Existing Secondary Replica and click Next

Step 7: Availability Group Validation Results are displayed (all skipped) and click Next

Step 8: Review your choices in the summary dialog and click Finish

Configure Cluster Quorom – Cloud Witness

Step 9: The wizard completed successfully – both databases were joined to the availability group

Results

Step 10: In SSMS you should now see the databases as part of the availability group and you can startup TFS.

SQL Server Availability Group Dashboard

NOTE: If you are using contained databases, you can configure contained users in the databases, and for these users, you do not need to create logins on the server instances that host a secondary replica.  For a non-contained availability database, you will need to create users for the logins on the server instances that host the availability replicas.

TFS 2015 Update 3 – Data Tier Summary

Make sure you don’t forget to add the Service Account used by TFS to the SQL Server instance on the Secondary Node.

My next post will be upgrading this install of TFS 2015 Update 3 to TFS15 RC2 all while connected to the SQL Server AlwaysOn Availability Group.