Major Changes for SQL Server 2016 Standard Edition

At today’s Connect() event, Microsoft announced some pretty major changes for SQL Server 2016 Standard Edition, which are implemented in SQL Server 2016 Service Pack 1 (which was also released today). Many very useful SQL Server features that were previously only available in Enterprise Edition will now be available in Standard Edition (and also in Web Edition and even Express Edition).

These include Row-level security, Dynamic Data Masking, Change Data Capture(*), Database Snapsnots, Columnstore indexes, Table Partitioning, Data Compression, Multiple Filestream containers, In-Memory OLTP, Always Encrypted, Distributed Partitioned Views, Polybase, and Fine grained Auditing.

There are some scalability limits for some of these features running on Standard or Web/Express Edition. For example, In-Memory OLTP is limited to 1/4 of the Edition buffer pool memory limit. Columnstore is also limited to 1/4 of the Edition buffer pool memory limit. These feature memory limits are in addition to the buffer pool limit for each edition.

In Standard Edition, Columnstore is limited to 2 DOP, and in Web/Express is limited to 1 DOP. Polybase worker compute nodes can be deployed on Standard, Web, and Express Editions, but still requires Enterprise Edition for the head node to scale out with (multiple worker compute nodes).

Keep in mind that you will need SQL Server 2016 Service Pack 1, to get these new features (and many other nice improvements, as detailed here).

The primary goal here is to provide application developers with an easy way to program an application in the same way using all of the application features of SQL Server 2016, regardless of which edition of SQL Server 2016 that the application may eventually be deployed on.

This is going to make it much easier for ISVs to use these features without requiring their customers to use Enterprise Edition. They will be able to simplify their deployment scripts by not having to check the Edition of SQL Server before they run DDL statements to create database objects. This is a very welcome development that should encourage many more organizations to move to SQL Server 2016.

Once you have an application using SQL Server 2016 Standard Edition, you can just do an Edition Upgrade to Enterprise Edition to get even more scalability and performance, taking advantage of the higher license limits in Enterprise Edition. You will also get the intrinsic performance benefits that are present in Enterprise Edition. Microsoft should consider publicizing some of these performance differences between Standard Edition and Enterprise Edition.

Note: Change Data Capture won’t be available in Express Edition, since it does not have SQL Server Agent.



Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.