Well, I can't claim that you heard it here first, since it was announced today by Scott Guthrie (@ScottGu) at Connect(); // 2016 and also on the SQL Server team blog, but SQL Server 2016 Service Pack 1 is the build of SQL Server that will finally make just about everyone upgrade from all previous versions.
Why? Well, I hope you're sitting down:
I've long been an advocate of making the surface area the same across all editions, and restricting each edition to hardware/resource limits. The latter is already the case with Express Edition (1 CPU, 1 GB memory, 10 GB database size) and Standard Edition (24 cores, 128 GB), but features themselves simply don't exist there. While they did add some functionality around Availability Groups (limited support in Standard Edition, and the ability to forego a domain), many of the features that are currently Enterprise only don't seem to be Enterprise-y at all. Here are some reasons and consequences of putting so many critical features only in Enterprise, at least in my opinion:
- Data security seems to have taken a back seat in recent years. It should be in all editions; I was disappointed when Always Encrypted was released and it was restricted to Enterprise. The implicit message was that, if you're a small business, you don't need to be able to encrypt credit card data (they say the same with Transparent Data Encryption, but Always Encrypted is a much more valuable offering).
- Programmability features that moonlight as performance features – like data compression and partitioning – are arguably on the Enterprise side of things, even though I've always felt data compression's sweet spot is the small business who can't afford to just add memory, buy faster disks, or upgrade to Enterprise.
In current versions, when a vendor wants to take advantage of compression or partitioning, they have to consider that not all of their customers will be using Enterprise Edition. Similarly, customers want to use the same codebase in their QA, production, and development environments, but those may not all have the same edition either. Both vendors and customers also want to be able to use features that are available in both cloud and box, regardless of tier or edition. Currently, they either need to write multiple codebases, or simply surrender to the lowest common denominator.
- Adoption rates are slow because, in many respects, Standard Edition upgrades have been far from compelling in recent years, meaning many people are content with their 2012 Standard instances. They just don't really gain much from moving to a more recent version and, in many cases, the cost of testing a migration and dealing with regressions actually outweighs any benefits.
Colleagues like Brent Ozar and Steve Jones have had similar thoughts – arguing that the best features remain excluded from Standard and lower editions for revenue reasons, but this isn't the way it should be.
And now it's not.
| Feature | Express | LocalDB | |
|---|---|---|---|
| Always Encrypted | |||
| Change Data Capture | |||
| Columnstore 1 | |||
| Data Compression | |||
| Database Snapshots | |||
| Dynamic Data Masking | |||
| Fine-Grained Auditing | |||
| In-Memory OLTP 2 | |||
| Partitioning | |||
| PolyBase | |||
| Row-Level Security |
1. Limited to 32GB per instance on Standard Edition, 352MB on Express.
2. Limited to 32GB per database on Standard Edition, 352MB on Express.
I don't have any evidence of this, but I believe that due to slow adoption rates of SQL Server 2016 – and Standard Edition specifically – Microsoft has finally listened. Perhaps they've realized that making so many compelling features only available in Enterprise Edition doesn't work. Can't get blood from a stone, they say.
The table here shows the set of features now enabled outside of Enterprise Edition (some features were already there in Standard, but I didn't have a good way to illustrate that). It only has to be a table because of three features that don't work on Express and/or LocalDB (due to SQL Server Agent, permissions, or external dependencies).
That's my quick cheat sheet; check the documentation for the latest updates. I've tested most of the features and they work as you would expect – not to trivialize the effort, but they just removed the checks that blocked functionality based on edition. I thought I would trip them up by testing beyond the basics, like incremental statistics on partitioned tables and combinations like Columnstore over a compressed table, to see if they missed something. But no – everything in the table worked just as if I were running Enterprise Edition.
That said, the focus here was for a consistent programming surface area (CPSA). So no, you won't suddenly be getting all of the benefits of Enterprise Edition for free or at a substantial discount – see below for functionality that won't work. And some of the features above may still be scale-limited based on edition.
Nonetheless, this does provide a great solution to #2 above: Having many of these features work in all editions, even if they support different levels of scale, will make it easier for both customers and vendors to build solutions that work no matter where they're deployed. This is going to be a huge win, even beyond the pure benefit of finally being able to use things like Always Encrypted and partitioning everywhere.
What Still Differentiates Editions?
Essentially, if it was an Enterprise-level operational or availability feature before, or within a certain set of performance features, it will remain as such. The following is not an exhaustive list, but probably captures most of the big differences:
- Availability features like online operations, piecemeal restore, and fully functional Availability Groups (e.g. read-only replicas) are still Enterprise only. I wish the
REBUILD+(ONLINE = ON)syntax could work as a no-op in lower editions with a warning instead of returning an error, but I can't be too fussy here.
- Performance features like parallelism still won't work in Express Edition (or LocalDB). Automatic indexed view usage without
NOEXPANDhints, and high-end features like hot-add memory/CPU, will continue to be available only in Enterprise.
- Operational features like Resource Governor, Extensible Key Management (EKM), and Transparent Data Encryption will remain Enterprise Edition only. Others, like Backup Encryption, Backup Compression, and Buffer Pool Extension, will continue to work in Standard, but will still not function in Express.
- SQL Server Agent is still unavailable in Express and LocalDB. As a result, and as noted above, Change Data Capture will not work. Cross-server Service Broker also remains unavailable in these editions.
- In-Memory OLTP and PolyBase are supported in Express, but unavailable in LocalDB.
- Virtualization Rights haven't changed and are still much more valuable in Enterprise Edition with Software Assurance.
- Resource limits on downlevel editions remain the same. Seems the bean-counters still control the upper memory limit in Standard Edition, so this is still 128 GB (while Enterprise Edition is now 24 TB). Personally, I think this upper limit should make incremental gains with each new version (Moore's Law), because I feel that Standard Edition is expensive enough that its memory limits should never be so dangerously close to the upper bound of a well-equipped laptop. But I'll take what I can get, and when you are stuck on Standard Edition and scale is required, you can now use the above Enterprise features across multiple Standard Edition boxes or instances, instead of trying to scale up.
Summary
This is a bold move by Microsoft, and I can certainly see how this will increase Standard sales without cannibalizing Enterprise sales. Standard Edition customers can use these features both to consolidate their codebases and, in many scenarios, build solutions that offer better performance. And for those who already enjoy all the benefits of Enterprise Edition, this may not be all that interesting to you, but there are also a bunch of new features available across all editions of SP1. So regardless of what edition you're on now, you really have no excuse for holding on to that ancient version of SQL Server.
Am I alone in finding it breaks filestream and having to roll back to the last hotfix ?
While I agree that database security is probably the most important issue in the article mentioned above, I don't believe it is the job of the database. Unless a database server is disconnected from any possible outside source, the role of security lies within the network and not the database. The world doesn't need database programmers who are the most qualified in database security. It needs network and systems administrators who are the most qualified.
Security is best implemented from the outside in and not the inside out.
I don't believe that database speed or OLTB. These are the tools of the trade. I see the problem being not understand the industry one is working in and mis-representing or not understanding the data one is working with.
Of course, this is IMHO.
Heidi, plenty of people feel differently. Not all small businesses (the major portion of the Standard Edition target market) can afford to have a massive network infrastructure team. I've been to shops where the DBA is the developer and their boss is the IT department. Having a feature like Always Encrypted available to this type of business is a huge win because now they can:
(a) protect a single column, like a credit card or SSN, from end to end – which they had no chance of doing before this change.
(b) protect that column *from the DBA* – not that they don't trust the DBA, but in the past, the DBA with sysadmin had all the keys to all the castles, and could get to any data "protected" by TDE. Now features like Always Encrypted allow businesses to protect that data even from the people with sysadmin access to the database.
These are powerful things that you may not appreciate but I can assure you other businesses do. Brushing them away and saying "this isn't the database's job" is counter-productive – why not push for security capabilities at every possible layer you can get them? Are there not enough data disclosure stories in the news for you? :-)
It seems I am alone on this one.
I continue to have problem going from:-
13.0.2186.6 2015.130.2186.6 3205413 Cumulative update 3 (CU3) for SQL Server 2016
to
13.0.4001.0 2015.130.4001.0 Microsoft SQL Server 2016 Service Pack 1 (SP1)
My workaround is unchecking File Stream from the SQL Server Service before the Service Pack application and then rechecking it afterward.
I would post your setup logs to MSDN forums or open a ticket with Microsoft support. I can't troubleshoot individual problems with service pack upgrades, sorry.
I absolutely agree. Small companies do not have those resources yet are increasingly exposed to those same security risks of larger companies.
Thanks for the heads up. Most of my databases heavily rely on filestream and fulltext indexing. Currently I have my development running 13.0.1601.5. I did notice that a backup of a 2014 filestream database into two files does not restore to SQL Server 2016 as it produces an operating system error of 32 stating the file is locked. I changed my 2014 backups to a single file to move on.
As my SQL Server 2016 release point is lower than yours, I hope I do not run into the same situation.
>>>far from compelling in recent years, meaning many people are content with their 2012 Standard instances.
It's worse than that. I know many clients who are still on 2008 R2!
The big advantages in my opinion are adding features that many engineers implemented before hand. Such as advanced reporting and analysis. I don't think the new features are less than what they claim to be, I only wish to impart that those of us who have worked with databases since DB2 don't need the new features to be competent to do their jobs.
I lost a job at the University of Michigan not because I didn't have enough database experience, but because another applicant had experience in a new version of Crystal Reports. I find it disheartening that employers look more to experience of the latest greatest software rather than years of working with databases. FWIW.