SQL Server 2016 Service Pack 1 Changes Everything

SQL Server 2016 Service Pack 1Earlier 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):

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:

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.

About the Author

Tim Mitchell
Tim Mitchell is a data architect and consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or training? If so, contact Tim for a no-obligation 30-minute chat.

4 Comments on "SQL Server 2016 Service Pack 1 Changes Everything"

  1. Ellon Peterkin | December 12, 2016 at 4:25 pm | Reply

    Tim, have you found that although CDC is now supported with Standard Edition that some of the SSIS components often used with CDC still requires Enterprise Edition?

    • Hi Ellon – although I haven’t tested this, I suspect that is the case. The change impacted only the database engine and not any of the BI tools directly (including the SSxS stack). For now at least, the CDC components of SSIS are likely to remain Enterprise-only features.

  2. Nice entry Tim, very informative, I have found another helpful post for the same. see here: http://www.sqlmvp.org/sql-server-2016-service-pack-1-features/

  3. Great stuff thanks for sharing! The SQL Server 2016 Service Pack 1 is beginning to clear up. As I found another helpful post for the same see http://www.sqlmvp.org/sql-server-2016-service-pack-1-features/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.