SQL Server 2016 SP1 Standard Edition = Enterprise Edition!?
Last week Microsoft made a historic announcement regarding SQL Server: with the introduction of the Service Pack 1 the Standard Edition of SQL Server offers you the same features as the Enterprise Edition. You don’t trust me? Just look here.
This changes the whole game in the market for relational databases and is mostly a death blow against Oracle. In today’s blog post I want to talk a little bit more about that exciting announcement – whether there are still differences between Standard and Enterprise Edition, and in which cases the Enterprise Edition is still needed.
Standard Edition = Enterprise Edition
With the Service Pack 1 of SQL Server 2016 Microsoft makes all the development features of SQL Server available in the Standard, Web, Express and LocalDB editions:
- Change Data Capture
- Database Snapshots
- ColumnStore Indexes
- In-Memory OLTP
- Always Encrypted
- Multiple FILESTREAM Containers
From a developer’s perspective you no longer have to care which features of SQL Server you use in your database applications. Everything is available across all editions of SQL Server. This is amazing, *really* amazing! So the question is now, why do you still need the Enterprise Edition of SQL Server? Let’s have a look at that.
Standard Edition <> Enterprise Edition
The Enterprise Edition is still needed for the following 2 reasons:
- High Availability/Disaster Recovery
- To be able to use more Hardware Resources
For example if you want to deploy Always On Availability Groups, you still need the Enterprise Edition of SQL Server, because the Standard Edition only gives you Basic Availability Groups. Even Partial Database Availability is only supported in the Enterprise Edition.
And the most important key point is that the Standard Edition is limited to only 128 GB RAM and 4 CPU Sockets or 24 CPU Cores. If you want to throw more hardware at your workload, you will need the Enterprise Edition.
Let’s talk here a little bit more about the supported RAM. In the Standard Edition if you use In-Memory OLTP or ColumnStore Indexes, SQL Server will give you 32 GB of *additional* RAM (besides the traditional Buffer Pool memory) for each feature:
- 32 GB additional RAM for In-Memory OLTP
- 32 GB additional RAM for ColumnStore Indexes
This means that the maximum possible useable RAM in the Standard Edition is now 192 GB (128 GB Buffer Pool + 32 GB In-Memory OLTP + 32 GB ColumnStore), as long as you use In-Memory OLTP and/or ColumnStore Indexes. If your Memory Optimized Tables are larger than 32 GB RAM, all additional INSERT and UPDATE statements will just fail – ouch…
The story is different with ColumnStore Indexes, because SQL Server only holds up to 32 GB of your ColumnStore Indexes in RAM, and if you exceed that limit, then other ColumnStore data will just be discarded from the RAM. A Clustered ColumnStore Index will also give you great compression rates compared with a traditional RowStore Index, therefore the 32 GB of additional RAM for ColumnStore Indexes can store a lot of data in RAM.
AWESOME, AWESOME, AWESOME!!!
Thanks for your time,