Team Foundation Server 2018 and SQL Server

Brian Harry

About 6 weeks ago, I wrote a post announcing the availability of TFS 2018 RC1.  In the comments, there ensued a sizeable conversation about the requirement for SQL Server 2016 or later (rather than also supporting SQL Server 2014).  It was good to get the feedback.  I read it carefully and we discussed it within our team.  I wanted to wrap up that conversation in a very open way with the conclusion we’ve reached.

We have decided to leave the requirement as it is (SQL Server 2016 or later) but will make some changes in our approach going forward.

Why require SQL 2016?

We decided to leave the requirement because we have taken dependencies on a number of features that are not available in previous versions of SQL Server.  Could we have possibly developed TFS 2018 without taking those dependencies?  Yes.  But we made the decision, internally, a year ago to take a dependency on SQL 2016 and all development that happened since then relied on that assumption.  Unwinding that assumption out of all the code that has been written would be quite a lot of work.

I’ll give just a few examples of SQL Server 2016 features that we have taken dependencies on:

  • Clustered ColumnStore Indexes – The plumbing for our new analytics capabilities relies on CCI.  The analytics service isn’t enabled in TFS 2018 but the code is in there and our hope is to enable it in a TFS 2018 Update.  CCI existed in SQL Server 2014, however, the implementation was immature enough that we could not use it.  We can rely on CCI in SQL Server 2016.
  • Changes in date/time computation – We were able to remove/simplify much of our date/time manipulation code due to improvements in SQL Server 2016.
  • Page compression – SQL Server 2016 introduced page compression in all editions and we were able to remove our code that conditionalized it.
  • Syntactic sugar – There are a bunch of new syntactic constructs, like “drop index if exists” or “create or alter” that we adopted to simplify our code.
  • And more…

Here’s a fairly comprehensive set of SQL Server 2016 improvements.

Again, I’m not saying it wouldn’t have been possible to avoid SQL Server 2016 features – we’d have to have planned to put off any analytics capabilities until TFS 2019 and left some more crufty code, but we could have done it.  We just didn’t – because we didn’t think we needed to and there was value in taking advantage of SQL Server 2016.  In retrospect, probably a mistake.

What are we going to do differently going forward?

The decision to take the SQL Server 2016 dependency was, almost, automatic.  We’ve had a long held policy of supporting the last 2 released major SQL versions.  That policy has served us well for 12 years.  We knew, when TFS 2018 shipped, both SQL Server 2016 and SQL Server 2017 would be released so, the answer was clear.  What this conversation has made us realize is that things have changed.  Like us, SQL Server has increased their release cadence and the last 2 releases of SQL Server happened in pretty rapid succession – resulting in the second shortest window of supported SQL Server releases in TFS’s history (only TFS 2013 had a shorter window and I don’t remember why).

Going forward, we will move to a “years” based model rather than a versions based model for our SQL Server support so we are less sensitive to release cadence.

  • We will support any major version of SQL Server released in the 36 months prior to a major TFS release.
  • We will support at least one major version of SQL Server released 24 months or more before a major TFS release.

SQL Cumulative Updates (CUs) won’t reset the clock or have any effect on the decision – only the major version release date will.  Similarly TFS Updates never change their prereqs so they will always support the same SQL Server versions that the previous major release did.

If we had applied these new rules to TFS 2018, we would have chosen to support SQL Server 2014 – not because it was shipped within the previous 36 months (it shipped 42 months prior), but rather because both SQL Server 2016 and SQL Server 2017 shipped less than 2 years before TFS 2018.  So the second rule would have faulted in SQL 2014 because it’s the first major release that has been in market at least 24 months.

I’m not saying we’ll never make a deliberate exception, but this will form the “default” assumptions and should hold most of the time.

But what about the standard support lifecycle?

So many people suggested using the “standard support window of 5 years” the metric that I want to comment on that.  I’ll try to explain – my point may be subtle so you may need to read this a few times.  Hopefully I can explain effectively.

The standard support lifecycle is the period in which a product is supported.  In that period, you can use it, we will provide support and key bug fixes/patches etc.  It only talks about your ability to rely on that version of that product.  It says nothing about what newer versions of other products will rely on.

It just doesn’t make sense for TFS to use the standard support window for SQL Server.  Let me demonstrate with an example.  Let’s say TFS 2025 was releasing 4 years and 11 months after SQL Server 2020 released.  If we used the “standard support lifecycle window of 5 years”, we’d support it.  One month later SQL Server would go out of standard support and you wouldn’t get the level of support/service on it that you will want/need for your mission critical TFS server.  I don’t think you’d want that.

By choosing a number more like 3 years, that gives you at least 2 years of standard lifecycle support on that combination of TFS/SQL Server. Hopefully that makes sense.


Based on the feedback in the blog comments on the other post, I know this is not going to make everyone happy.  I know it is going to cause some hardship for some people and inhibit some TFS 2018 upgrades.  I hate that.  Really.  Given where we are, however, I think this is the best trade-off I can make.  I’ll fix our guidelines so it’s better in the future but I’m going to have to live with the bed we’ve made for TFS 2018.

TFS 2018 is going to be a great release with a ton of nice new improvements.  I hope everyone can get to it as soon as possible.

I hope you understand.

Again, I really appreciate the feedback and we did think hard about it.  Let me know if there’s anything else I can do.


1 comment

Discussion is closed. Login to edit/delete existing comments.

  • Rod Falanga 0

    We’re currently at TFS 2015 Update 1. I’d like to move us to TFS 2018. I believe our TFS server is using SQL Server 2012. Anyway, it is not SQL 2016. I’ve talked to our DBA, and he’s willing to move it to SQL Server 2016. The question I have is should it be SQL Server 2016 Standard or Enterprise edition?

Feedback usabilla icon