SQL Server 2016 SP1: Features Added to Standard, Web, Express, Local DB Editions

on November 16, 2016

Wouldn’t it be awesome if you could develop your application for a small SQL Server using the same features that you intend to use for scale?

And wouldn’t it be more awesome if you could start using bleeding edge features like In-Memory OLTP on some of your less-risky, smaller databases first?

that-time-sql-server-gave-you-a-pony

In the past, cost inhibited feature adoption

This has been tough in the past, because SQL Server limits which Edition can use specific programmability features. Enterprise Edition gets all the goodies, and Enterprise costs more:

  • Enterprise Edition is ~$7,000 USD/core (minimum 4 cores per socket, sold in 2 core packs)
  • Standard Edition is ~$1,800 USD/core (minimum 4 cores per socket, sold in 2 core packs / also available for purchase by Client Access License)

With these prices, developers cant’t usually afford to use Enterprise Edition features on new, small projects. And they were forced to introduce cutting edge EE features into the most critical databases first– because the least critical databases don’t get those licensing dollars. Software vendors often have to maintain two versions of their SQL Server codebase: one for Standard Edition customers, and one for Enterprise Edition customers.

That changes today.

Today is the Microsoft Connect() online developer conference. It’s also the day that SQL Server 2016 Service Pack 1 is dropping. SP1 includes some amazing licensing changes.

Download SQL Server 2016 SP 1 here.

Data Management Features added to Standard, Express, Web, and (mostly) Local DB Editions starting with SQL Server 2016 SP1

Developers will now be able to use far more features in production, whether or not they’re using Enterprise Edition. These features are now available in “lower” editions.

I’ve noted usage “caps” as I understand them from initial information:

  • Table Partitioning
  • Data Compression
  • Columnstore Indexes
    • Standard Edition limits DOP to 2
    • Web / Express limit DOP to 1.
    • Memory limited to 25% Buffer Pool limit for each non-EE edition (so you get 32GB for Standard Edition)
  • In-Memory OLTP
    • Memory limited to 25% Buffer Pool limit for each non-EE edition (so you get 32GB for Standard Edition)
    • Not in Local DB
  • Distributed Partitioned Views (writeable)
  • Multiple Filestream Containers
  • Change Data Capture
    • Not in Express or Local DB, as this requires SQL Server Agent, which is not present there
  • Database Snapshots
  •  PolyBase
    • Exception: not in Local DB

Security Features added to Standard, Express, Web, and (mostly) Local DB Editions starting with SQL Server 2016 SP1

At this time I don’t know of any limitations on these features, or differences from Enterprise. It’s very cool to see SQL Server making enterprise grade security features available to everyone, in every edition.

  • Auditing (some)
  • Always Encrypted
  • Row-Level Security
    • Has been available in Standard in 2016, now in Web, Express, Local DB
  • Dynamic Data Masking
    • Has been available in Standard in 2016, now in Web, Express, Local DB

Did the price for Standard Edition go up?

dev-environment

Nope. No pricing changes. Just wider feature availability!

Will people still need to buy Enterprise Edition?

Yes. Scalability limits for edition aren’t changing. Enterprise still gets you more memory and more CPUs to scale your workload.

Fine Print: not all features have licensing changes

The clever reader may have noticed that I haven’t mentioned AlwaysOn Availability Groups, Transparent Data Encryption, Database Mirroring, or Peer-to-Peer Replication. No licensing changes have been announced for those features, or for services like SSAS or SSRS.

This is a huge licensing improvement, and it’s targeted at how you architect your schema and your codebase, and how you secure your data. But it doesn’t touch everything.

Don’t worry, Developer Edition still acts like Enterprise

Note that these changes don’t apply to Developer Edition. It continues to have all the features of Enterprise Edition, and it’s free (aww yeah), but it’s only for non-production environments.

Seems like a great time to upgrade to 2016, doesn’t it?

Traditionally, managers like to wait until Service Pack 1 comes out before they get serious about upgrading to a new SQL Server version. You’ve got an especially good reason to talk about upgrades with this one!

PS: you try out SQL Server v.Next on Windows and Linux, too

There’s so much cool news today that I’m weirdly including this as a final note. The first preview of SQL Server v.Next is dropping today. You can run it on Windows. You can run it on Linux. You can run it in a Docker container on a Mac.

Download v.Next CTP1 it here.

Wowzers.