SQL Server 2016 Service Pack 1 Changes Everything

Earlier today, Microsoft announced the release of Service Pack 1 (SP1) for SQL Server 2016. Pay close attention to this announcement, because this is not a typical update. Service Pack 1 unlocks most all of the programmability features found in Enterprise Edition, making them available in lower-cost (and even free) editions of SQL Server. Folks, this is a huge change for the better, and will almost certainly change how you license SQL Server. Christmas has indeed come early this year.

With Service Pack 1 on SQL Server 2016, the following features are available in all editions (including Express and Local DB):

• Row-level security
• Dynamic data masking
• Database snapshot
• Columnstore
• Partitioning
• Compression
• Always Encrypted
• Fine grained auditing
• Multiple filestream containers

These features are available on all versions except for Local DB:

• In-memory OLTP
• Polybase

Finally, this feature is now available on Standard and Web edition:

• Change data capture

What Does This Mean For You?

These changes are huge for any organization that has made licensing decisions based on features. If you were buying Enterprise Edition to get partitioning, Always Encrypted, Polybase, or other programmability features, you can now use a less expensive version to get those same features.

Most of the clients I work with have data warehouses or data marts. The addition of common DW functionality such as partitioning, compression, and change data capture will certainly have an impact on those organizations. The inclusion of these features commonly used by data warehouses and data marts will be a welcome change. For once I’m actually looking forward to those licensing discussions with clients!

It is important to note that Enterprise Edition is still available, and it will still be a part of many workloads. Although the programmability features have changed, there are still some distinct differences between Enterprise Edition and the lower editions of the product. In particular, some of the availability options, transparent data encryption, and MDS/DQS are still limited to Enterprise Edition, as is the support for additional RAM and CPU cores. Also, this feature change only impacts the database engine: features of ancillary services such as SSIS, SSRS, and SSAS remain unchanged, so those features in the BI stack that required Enterprise Edition previously still do so. For a complete matrix of features and edition support, visit Microsoft’s updated documentation for SP1 feature support.

Other Changes

In addition to unlocking these programmability features, a number of other notable changes have been made:

• CREATE OR ALTER. This syntax change will allow you to create (if it doesn’t exist) or alter (if it does) a stored procedure, view, trigger, or view.
• Manual change tracking cleanup. A new stored procedure will allow manually cleaning up change tracking data on demand.
• Database cloning. Using a new DBCC command, you can create a clone of the metadata and statistics without the data itself. This seems very useful for dev and test scenarios.

This is just a taste of the numerous changes; check out the announcement blog for a comprehensive list of updates.

Next Steps

If you are currently running SQL Server 2016, start testing SP1 today. If you are running an earlier version of SQL Server and are considering an upgrade, there is no better time to do so. If you are considering a purchase of SQL Server, you can consider lower-cost options other than Enterprise Edition to get the same programmability feature experience.