Sql-server – What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions

There are a lot of reasons to consider an upgrade to SQL Server 2016. Most of the time, these reasons vary depending on the version of SQL Server one is coming from. This won't be an exhaustive list, but I'll give a few reasons that come to mind. Including one very big reason we just learned about with SQL Server 2016 SP1's release in November of 2016. Looking forward to see other answers for expansion or features I missed.

Big Reason: Many Features Available in Standard Edition Now

On November 16, 2016 Microsoft announced the release of Service Pack 1 for SQL Server 2016. Along with this announcement, they also announced that many features previously reserved to Enterprise Edition customers would be available in all editions of SQL Server - Standard, Express (with a few exceptions), Web in addition to Enterprise Edition.

This should serve to be a very compelling reason to upgrade for Software Vendors who develop and sell software which have a SQL Server backend – many features can now be available to their customers regardless of their licensed edition of SQL Server. This is also compelling for companies in sectors which must maintain auditing or other security enhancements that have been added to the product over time.

In my humble opinion, in over 17 years of working with SQL Server this makes SQL Server 2016 SP1 quite possibly the most compelling upgrade yet since I've started working with SQL Server in version 6.5. (Don't get me wrong, the 2000, 2005 and 2012 had their pros for their times also!)

Look at the list below, but just as a quick example of some of the features you can now use in Standard Edition, you have these: Compression, Change Data Capture, Fine Grained Auditing, Row-Level Security, Always Encrypted, Partitioning, Database Snapshots, In-Memory OLTP, Columnstore.

Specific Reasons For SQL Server 2016 New Features

I'm trying to keep the reasons here in this section to features introduced in SQL Server 2016 only. I'm also only describing core engine enhancements in this answer, not reporting, analytics, data integration, etc.

Security Related

Row Level Security - I have many clients looking to be able to implement security on a row by row basis. "User x can view data on this row, but not that row". Many of those clients have created functions and/or complicated view based structures to accomplish this. In SQL Server 2016 there are built in predicates and security policies available to aid here. You can read more about RLS here.

Dynamic Data Masking - This one sounds great on paper, but I wonder if it will prove to be less useful than the other two changes in the security area. Basically, you can dynamically mask, or obfuscate, data at query time based on the permissions of the caller. The amount of data to be obfuscated is something that is programmable. For instance, in the example Microsoft uses, you could mask all but the final four digits of a Credit Card number so a call taker in a call center could verify those final four digits. You can read more about that here.

Always Encrypted - Perhaps you have heard about some of the flaws or concerns over Transparent Data Encryption. Perhaps you don't want to use TDE for fear that someone with SA access can definitely decrypt if they wanted. Always Encrypted was released as a feature that puts the decrypt/encrypt ability in the hands of the application calling to SQL Server. Data at rest in SQL Server is encrypted. Data in flight heading to SQL Server is encrypted as a result of this approach also. You can read more about Always Encrypted here.

HA/DR Features

A few features came out here. One is the ability to use Always On Availability Groups in SQL Server Standard - the Basic Availability Group. This was necessary as Mirroring is deprecated and had no replacement. The functionality is limited to the number of replicas and the approach for synchronization. You can read more here.

That isn't the only change to Availability Groups (which, by the way, were introduced in SQL Server 2012). There are also a few other changes (you can see the exhaustive list with links to other changes here):

1. Group Managed Service Accounts are supported.
2. Distributed Transactions are supported (with some caveats).
3. Read intent connections can be load balanced among read only replicas.
4. Three replicas can be involved in automatic failovers now.
5. Encrypted databases can now participate in an Always On Availability Group.
6. Performance Improvements - part of the theme of SQL Server 2016 is "it's just faster" - that's the Microsoft Marketing spin, but it is proving out in the marketplace. Several improvements were made to Availability Group performance.

Installer Fixes Some Common Mistakes

The SQL Server installer starting in SQL Server 2016 fixes some common mistakes made when configuring TempDB. The setup GUI guides you through the best practice configuration now.

Query Store

A favorite among SQL Server MVPs and tuners. This feature is a tool to give you insights into how queries are executed by the engine. Especially useful in tracking performance issues caused by changes in query plans over time. This can be a tool to view, but also a tool to fix. Helps simplify a few performance tuning concepts. You can read more about that here.

Temporal Tables

I often see a need to ask a question like "What did this data look like as of such and such a date". Temporal Tables are one method for doing that. Definitely have some warehousing use cases. See more here.

Columnstore Improvements

Three big to me improvements here among a couple others. One is updateable nonclustered columnstore indexes. SQL Server 2014 gave us updateable clustered, but now we have updateable nonclusted. SQL Server 2016 also allows columnstore indexes on In-Memory OLTP tables. And the ability to place a traditional B-tree nonclustered index on a clustered columnstore index now exists in SQL Server 2016.

Additional Feature Improvements in In-Memory OLTP

Several of the features that weren't supported inside of In-Memory tables or in natively compiled procedures are now available. Some additions (but see this article for more): UNIQUE Constraints, Check Constraints, OUTPUT clause in DML, Outer Join, Subqueries in the select list, SELECT DISTINCT, UNION/UNION ALL, and more.

General Performance Improvements

SQL Server 2014 made some improvements to performance in several key areas as each release does. SQL Server 2016 made additional key enhancements throughout the product. That includes eliminating for many customers some of the pain sometimes encountered when upgrading to SQL Server 2014 due to the cardinality estimator changes. Several changes up and down the engine were made to improve everyday performance. Microsoft's CSS team has been blogging about this on their blog. You can just search for "SQL Server 2016 it just runs faster". An example blog post for this series is here.

I've seen this played out in clients who have upgraded, and expect to see it more and more.

Some Cons

There are cons obviously to any upgrade. It could be because I do this for a living as a consultant, but I think the cons are all incredibly manageable and really just consider them "count the cost first" items to be aware of and manage around.

Upgrades In General - In general upgrades mean you are introducing new functionality. You need to be able to test them. Between the versions of SQL Server various features are deprecated. This doesn't mean they go away right away but it means they can in future releases, often three versions later. For instance the TEXT data type, or doing the outer joins in the where clause with the *= or =* syntax are not supported if you are coming from a database running in SQL Server 2000 compatibility mode. Speaking of compatibility modes - they only go back three versions as well. So if you have a database running in 80 (SQL Server 2000) or 90 (SQL Server 2005) compatibility modes, they can't go over into SQL Server 2016 like that. So if you've been avoiding doing that test and sneaking compatibility levels, it's time for a reckoning.

Again. That is easy stuff. There are tools out there to help capture and test data to see what breaks. There are perfmon counters that can help look for deprecated features.

Licensing - Two things here:

1. Core Based - if you are used to pre SQL Server 2012 licensing terms, CPU licenses were just that - CPU licenses. Since SQL Server 2012, CPU based licenses have been based on the core. So there is an adjustment period there. Also Enterprise for new licenses (with some exceptions that were made when 2012 first rolled out) must be licensed at the core level - no server + CAL for enterprise. The shining silver lining on this cloud here is what I said about SP1 allowing Enterprise features in non-enterprise SKUs. If you need more than 128GB of RAM. If you need the advanced scans performance improvements. If you need more than 32GB of RAM for a columnstore or In-Memory then you are looking at Enterprise. If you don't? And you don't need online index rebuilds? Standard may just be for you, and it may be more for you now with those features newly available to you.
2. Software Assurance (SA) - if you didn't buy SA, you have to buy new. So if you are running SQL Server 2005 and you are worried that you are out of mainstream support, not terribly far away from the end of extended support and wish you had the features now available, you can't just upgrade like a Service Pack. If you never purchased SA, then your upgrade is a new purchase. That's okay, it really is worth it. But it is something that has to hit the budget.

Summary

There are many more reasons, like JSON support, introduction of R, etc. But these are just some of the reasons I would consider an upgrade for. The most important item, again, being the ability to use many previously Enterprise Edition-only features in Standard, or even Express. I have seen great success stories among my clients and I'm expecting to see more.

And if you are using SQL Server 2005 or 2008, the list is even larger. AlwaysOn Availability Groups came out in 2012, Columnstore Indexes in SQL Server 2012, In-Memory OLTP in SQL Server 2014, etc.

Not to mention if you are still on SQL Server 2005 or 2008, you are out of mainstream support.