2

This question already has an answer here:

We are going the create our Sql-Servers new on new hardware / VM's and think about which Sql-Server Version to go with. We are actually using Sql Server 2008 R2. We have already done a project for testing everything for any compatibility issues between 2008 and 2014 - and have fixed all issues in application code as well as in the db schemas to be ready for 2014.

Now the question for simply dismissing 2014 and directly go to 2016 came up. We are not sure.

I have ran SQL 2016 Upgrade Advisor - it said "ready for upgrade (2016)" and raised around 500 warnings for deprecated ntext, text and image columns.

  • Are there any special additional downsides of going to 2016 instead of 2014?
  • Are there new compatibility issues? AFAIKS there have been no new deprecations in 2016 that are not yet in place of 2014...
  • What do we have to consider?

marked as duplicate by Philᵀᴹ, mustaccio, LowlyDBA, Erik, Andriy M Sep 15 '16 at 18:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • One thing that may matter is SQL 2016 requires Windows 2012 or newer. If you already planned to use Windows 2012 R2 then this isn't an issue. – Antoine Hernandez Sep 15 '16 at 19:43
5

The is some information directly from MS that you might find useful: Breaking Changes to Database Engine Features in SQL Server 2016 and Discontinued Database Engine Functionality in SQL Server 2016, which indicate that officially there is very little by way of breaking changes. Those pages link to similar information about older versions too.

As a general rule I recommend going for the latest version for new installations once that version has been released long enough for any major bugs to have been found by someone else and fixed by MS, and SQL2016 had a large enough beta release that anything major should have been found already. But I would also recommend a full regression check of the applications to cover the possibility of:

  • reliance in your application on undocumented/undefined behaviour that may differ in the newer version: changes to officially undefined behaviour won't be listed in places like the pages linked to above.

  • performance issues due to changes in the query planner: the new carnality estimation method in 2014 generally lead to noticeable improvements but in a some use cases they had the opposite effect and hints are needed to push the query planner back towards the optimal plan, I'm not aware of such a significant change in that area in SQL2016 but there will no doubt be smaller changes that could have a detrimental effect for a minority of queries/statements.

  • new and interesting bugs as yet undiscovered.

As is sounds like you have recently performed a large scale regression test, you might not be able to justify (to the business) doing so again this soon in which case sticking with 2014 would be the safer option.

  • I recommend going for the latest version for new installations once that version has been released long enough for any major bugs to have been found by someone else and fixed by MS. Although many have this philosophy, I think testing one's actual workload is a better approach than relying on others to find issues with the version that may or may not apply. RTM SQL versions are better than they were 20 years ago when the "not before SP1" policy originated. – Dan Guzman Sep 15 '16 at 10:51
  • @DanGuzman: While things are greatly improved over the problems that some experienced in the past, in part because of the extended public access beta periods (and in the case of SQL Server some of the features that are new in "on prem" editions having been battle-tested in Azure first) I still prefer to be paranoid and not upgrade immediately unless there are specific fixes/features that I want to make immediate use of. This is for all products, it isn't intended as an anti-MS sentiment at all. We certainly agree on "testing one's actual workload", as detailed in the latter half of the answer. – David Spillett Sep 18 '16 at 19:51
3

I cant speak for your compatibility, but general performance and security and longevity is always better with the latest version. (you'll most likely get another 2 years of support for a version 2 years more recent)

The best thing I can advise is getting a dev licence (which by comparison cost next to nothing) for 2016 and use that to test your system to see if there are any issues and how easy they are to fix beyond the general these things shouldn't be used any more.

There's also the question of time, since you've done a full sweep of 2014 it will be far quicker now to upgrade to that, but there's also you should now know all of the tests that need to be performed, so a second compatability test with a new system should go much smoother (and you've already got the fixed up to 2014 bypassing 2012 which had a lot of changes) so the number of required changes from your prepared system and what you need will be (in theroy) substantially less than the set you have already done.

EDIT: SQL Developer edition can now be used for no cost with a visual studio subscription or visual studio dev essentials https://www.microsoft.com/en-gb/cloud-platform/sql-server-pricing

Not the answer you're looking for? Browse other questions tagged or ask your own question.