patternsqlMajor
What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions?
Viewed 0 times
preferwhataresqlversionsearlierreasonsobjectiveserverover
Problem
As Microsoft makes SQL Server version upgrades more frequently since the SQL Server 2005 or 2008 days, a lot of companies find it difficult to determine when an upgrade is a "must have!" and when an upgrade is a "nice to have"
In the spirit of a few earlier questions asking about reasons to prefer various newer releases of SQL Server over previous ones, what are some objective technical or business reasons a company might consider for an upgrade to SQL Server 2016 over an earlier version, even a later release such as SQL Server 2014?
(This question about SQL Server 2012 compared to SQL Server 2008, Or this one about SQL Server 2012 compared to SQL Server 2005 come to mind as examples of the spirit of this question. The answers to them also expand on some of the reasons here for companies starting on SQL Server 2008 or SQL Server 2005)
In the spirit of a few earlier questions asking about reasons to prefer various newer releases of SQL Server over previous ones, what are some objective technical or business reasons a company might consider for an upgrade to SQL Server 2016 over an earlier version, even a later release such as SQL Server 2014?
(This question about SQL Server 2012 compared to SQL Server 2008, Or this one about SQL Server 2012 compared to SQL Server 2005 come to mind as examples of the spirit of this question. The answers to them also expand on some of the reasons here for companies starting on SQL Server 2008 or SQL Server 2005)
Solution
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):
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
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):
- Group Managed Service Accounts are supported.
- Distributed Transactions are supported (with some caveats).
- Read intent connections can be load balanced among read only replicas.
- Three replicas can be involved in automatic failovers now.
- Encrypted databases can now participate in an Always On Availability Group.
- 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
Context
StackExchange Database Administrators Q#155839, answer score: 35
Revisions (0)
No revisions yet.