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!
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.
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.
If we logon to the Primary Node/Replica using SQL Server Management Studio (SSMS) we can see the databases have been created.
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
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.
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…
Step 4: Select the databases you want to add to your availability group and click Next
Step 5: Select Join-only and click Next
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
Step 9: The wizard completed successfully – both databases were joined to the availability group
Step 10: In SSMS you should now see the databases as part of the availability group and you can startup TFS.
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.
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.