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!

snag-3682

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.

snag-3663

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.

snag-3697

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.

snag-3701

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

snag-3703

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.

snag-3710

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…

snag-3704

Availability Databases | Add Database…

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

snag-3705

Select the TFS databases to add to the availability group

Step 5: Select Join-only and click Next

snag-3706

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

snag-3712

Configure Cluster Quorom – Cloud Witness

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

snag-3713

Results

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

snag-3714

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.

snag-3715

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.

About wesmacdonald

Wes MacDonald is a Visual Studio ALM MVP, PSD, MCT and a Principal Consultant for LIKE 10 INC., a SharePoint Server, Visual Studio and Team Foundation Server Consulting Firm providing premium support and guidance services.

No comments yet... Be the first to leave a reply!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: