Starting with today’s release of SQL Server 2016 Service Pack 1, Standard Edition now has a lot more of the features of Enterprise Edition. Here’s the list from Microsoft’s announcement post:
- Performance features – in-memory OLTP (Hekaton), in-memory columnstore, operational analytics
- Data warehousing features – partitioning, compression, CDC, database snapshots
- Some security features – Always Encrypted, row-level security, and dynamic data masking
The updated edition comparison page (Update 16 Nov – I’m hearing this grid isn’t quite right still) shows that these things still aren’t in Standard Edition:
- Full Always On Availability groups (multiple databases, readable secondaries)
- Master Data Services, DQS
- Serious security features – TDE, auditing
- Serious BI – mobile reports, fuzzy lookups, advanced multi-dimensional models and tabular models, parallelism in R, stretch database
Why would they be so generous? It’s simple: they need to drive SQL Server 2016 adoption. Lately, Standard Edition users just haven’t had a big reason to upgrade.
And now they do. If you’re on pre-2016 builds, it’s time to start having 2016 upgrade discussions with management. Go get ’em, tiger, and check out the full list of new and improved features.
31 Comments. Leave new
Dang. It figures the *ONE* feature we’re mandated to use (TDE) isn’t going to the lower-cost editions…
Would’ve been nice to tell the bosses in a year or two when we started migrating to SQL2016 that we would also be cutting licensing costs significantly…
Jason – I think that’s a smart move on Microsoft’s part. Lots of customers are required by their industry regulations to do various security stuff, which means Microsoft kinda has you over the barrel on that one. Hee hee!
Laugh it up fuzzball!
I don’t disagree that from an income point-of-view it is a smart decision on MS part. I just wish I could’ve been the bearer of good tidings later this year…
(currently running 5 servers w/4cores each of SQL2014 Enterprise, the math is left as an excersize for the reader)
I still don’t get the obsession with TDE when we have Bitlocker available. Why pay extra to only have the data files encrypted when you could encrypt *everything* on the disk? It seems TDE is *less* secure that Bitlocker (since master and msdb aren’t encrypted). And if you’re worried about transmission of a backup to a non-encrypted disk, .zip has supported AES-256 encryption for years now.
Oh, and does TDE encrypt FileStreams yet? Last I checked it didn’t. And there was some TDE-related problem I vaguely recall reading about with full-text indexes too. Then there’s the maintenance irritations I’ve read about. Isn’t there some painful stuff in relation to AG? And you have to remember to put install a copy of your encryption key(s) on every Sql instance you want to use the data.
Granger – because BitLocker doesn’t protect you if you do a backup to a UNC share, thumb drive, Azure, etc.
One correction, I think? It looks like auditing is in SE, based on how I read that chart.
Keep reading down two more cells in the chart. You can audit in Standard – but you can read the audits in Enterprise, the way that’s written. Guessing that means it’s like the old Management Data Warehouse used to work.
Hm, I wonder if “fine grained auditing” refers to the old C2 stuff. Interesting. Anyway, smart to keep SQL Audit in EE if that’s the case. That really is an enterprise feature IMO.
Assuming it hasn’t changed, you can do instance-level audit definitions in SE, but EE gives you more fine-grained control over database-level audit events. If you’re happy doing the filtering father down the pipeline (like in Splunk) SE really doesn’t lag far behind EE.
I just read the data platform insider post. My reading of it is they’re just closing the relatively small gap that existed before.
Hi Matt, will database level auditing available in SE with SP1? Have you tested it?
Thank you!
Is it me or has Stretch Database has sneaked out of Standard Edition?
Stretch Database is definitely in Standard Edition
CREATE OR ALTER (aka REPLACE) has finally made its way into SQL Server? Is this real life? Someone pinch me!!
Where are you seeing this? I can’t find it.
Hi Matt,
3rd bullet point in the link below:
https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/
They even acknowledged that we’ve been asking (begging!) for it for quite a while now 😉
Bummer that they’re sticking to their guns on the 128 GB limit for the relational engine.
This page has more detail https://technet.microsoft.com/en-us/windows/cc645993(v=sql.90)
It’s a little confusing because they talk about in-memory column store, but clustered columnstore indexes remain EE-only. Also, note that some features have special memory and parallelism limits in SE.
Snapshots and data compression are pretty exciting though.
I was wrong. This is what I get from trying to read a bunch of documents written in marketese rather than just test it out. Clustered column store is supported in SE, subject to the limitations of how much can be cached in memory.
With regards to “Full” Always on Availability Groups does that include having more than 2 nodes in Standard?
Troy – from what I’ve seen in the docs (which admittedly are still in flux from what I hear), Standard is still restricted to 2 AG members, with the standby not available for reads or backups.
Sounds like I need to work on building up a test environment and testing this stuff out and see what we really got with this new Service Pack
@BrandonM i’ll see your CREATE OR ALTER, and raise you a STRING_AGG!
https://msdn.microsoft.com/en-us/library/mt790580.aspx
=( disregard. there is no STRING_AGG in SQL2016.
the above link says (or will have said):
“Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. This function is available beginning with SQL Server 2016 SP1.”
however, i have tested this and it just ain’t so. looks like we gotta wait for vNext on that:
https://msdn.microsoft.com/en-us/library/mt788652.aspx
Hey mmiike,
The funny thing is, it looks like Intellisense already recognises the function, but the SP1-less 2016 engine doesn’t support it yet. Check this out: http://i.imgur.com/co7Is2L.jpg
I imagine the performance will be about the same as the XML black box method I’m using to concat rows into a string, but this should be more concise.
This is generally awesome news, I’m disappointed however that online index rebuild isn’t now in standard…. If performance features such as columnstore and in-memory tables are in standard to improve system performance – that’s great except index maintenance still has a potentially big impact ?
What does the following limitation mean: ‘In-memory columnstore [and Real Time Operational Analytics] are limited to one quarter of the edition buffer pool memory limit’. Does this relate to the reserved data of the table after a columnstore index has been created on it? What happens if the table grows beyond 32G (=128/4)?
I am looking at a database where we can get a >80% storage saving when we use columnstore indexes. The data is not yet big enough by far to test the 32G limit if any but if this is a limitation we might not use columnstore altogether or consider EE to be able to deal with future growth.
Do you know whether row and page compression are included in SQL Server 2016 Sp1?
How about temporal feature in SQL Server Standard SP1?
Make sure to read the posts from Microsoft carefully.
Thanks!
[…] Everyone on 2016 Standard Edition had patched to SP1 to get the free features […]