Newsletters




Major Feature Reconfiguration for SQL Server 2016 Service Pack 1


Bookmark and Share

One of the long-standing differentiators between SQL Server’s Enterprise Edition (EE) and Standard Edition (SE), besides price, were the large number of features available only in EE. This is also true for other lower editions of SQL Server such as Web Edition and Express Edition. But there’s big news in the air!

Microsoft has opened up many EE-only features as part of the SE, Web, and Express Editions!

What’s Included?

Microsoft’s intent with this raft of features was, in my opinion, to promote adoption of SQL Server 2016 Standard Edition and lower. In addition, Microsoft sought to provide application developers a consistent programming surface area (CSPA) so that ISVs could write their applications without worry about whether all of the programmability features of a given edition might or might be available on a customer’s SQL Server.

You can read all about the reconfigured features support at https://msdn.microsoft.com/en-us/library/cc645993.aspx. But to give you a quick recap, SE now supports: Always Encrypted, Change Data Capture, Columnstore Indexes, Data Compression, Database Snapshots, Dynamic Data Masking, Fine-Grained Auditing, In-Memory OLTP, Multiple Filestream Containers, Partitioning, Polybase, and Row-level Security.

Again, remember that the idea here is programmability, not scalability or high-performance. That means that Microsoft is limiting the amount of hardware resources you can throw at any of these features on SE or Web. So, if you want to run these features on a server with 128 CPUs and 1Tb of RAM, you’ll need EE.

Microsoft has also changed the way in which it describes and handles memory limits within down-level editions of SQL Server. In effect, SQL Server SE and lower have a greater amount of memory to work with while the spec sheet remains essentially unchanged. For a good overview of these enhancements, read this article by my colleague Aaron Bertrand, https://sqlperformance.com/2016/12/sql-server-2016/memory-limits-sp1.

What’s Left Out?

Enterprise Edition is a lot more than improvements in schema settings, programmability, and T-SQL coding features. So, broadly speaking, the features in EE related to uptime, high-availability, or operations are still going to be EE-only.

Here are a handful of examples. NUMA aware buffer allocation, Resource governor, Extensible key management, Peer-to-peer transactional replication, Oracle replication publishing, and Transparent data encryption are operational in nature and, thus, still EE-only. Non-locking online operations, piecemeal restore, online restore, hot adding of CPU and memory, and full Availability Groups features, being related to high-availability, and not available in SE and down-level editions. When running SQL Server Analysis Services, EE-only features include Star join query optimizations, Global batch aggregation, parallel query processing on partitions, Scalable read-only configurations, Polybase head node configuration, Advanced R integration, and standalone R Server.

What Next?

If you’re running older versions of SQL Server, I strongly encourage you to upgrade. SQL Server 2016 is a powerhouse! Full stop. If you’re assessing whether you might use SQL Server in your organization, then download the latest bits from https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016. And if you want to try it out without downloading it, you can actually spin up an Azure Virtual Machine with SQL Server 2016 already installed on it and start sandboxing right now at https://azure.microsoft.com/en-us/marketplace/partners/microsoft/sqlserver2016rtmenterprisewindowsserver2012r2/?wt.mc_id=sqL16_vm


Sponsors