SQL Server Enterprise Edition Features You Didn’t Know You Could Use

Features Supported by the Editions of SQL Server

Microsoft has very nice documentation covering which features are available for each version and edition of SQL Server. I refer to these pages fairly often, usually on Mondays when I can’t remember if some particular feature is available in Standard Edition (SE) or if it is Enterprise Edition (EE) only.

If you zoom in on the Enterprise Edition features for any category you’ll notice a very rich set of amazing things that you only get when you ride the “Starship Enterprise” (a.k.a. Enterprise Edition.) Let’s take a look at a select handful of Enterprise Edition features in SQL Server 2012:

Cross-Box Scale Limits

Maximum Compute Capacity Used by a Single Instance

Operating System maximum: However big you build your server, that’s how much CPU you can use. It sounds good, but be warned; due to changes in licensing in SQL 2012 it’s desirable to have as few CPU as possible. Fewer and faster is a good rule of thumb. We’ll get into why a little later in this post. Compare this to Standard Edition where the limit is the “lesser of 4 sockets or 16 cores”.

Maximum memory utilized (per instance of SQL Server Database Engine)

Operating system maximum. Like CPU, Enterprise Edition will use as much RAM as is available. Compare to Standard Edition where the limit is 64GB. (Note SQL2014 SE allows 128GB).

High Availability

Online indexing – Rebuild indexes online without blocking the ongoing workload. It’s not perfect, the rebuild process still takes some locks as it finalizes and merges the rebuilt data back into the ‘live’ database, but it sure beats the Standard Edition option where the lock persists for the duration of the rebuild.

AlwaysOn Availability Groups – A comprehensive solution to both HA and DR allowing segregation of OLTP, DSS, and maintenance workloads and may put five nines in reach. To get something similar out of Standard Edition (or any pre-AOAG versions) you’d need to employ clustering, database mirroring, replication, log shipping, andor some combination of these and perhaps other technologies. That is certainly a challenge, and any given solution might have its drawbacks, but note it is possible to achieve HADR without AOAGs.

Mirrored backups – It’s convenient to regularly back up database to a local drive for easy and quick access just in case they are needed quickly. Of course it’s a risk to only keep them there since if the whole box goes down those backups go with it. A typical practice is to backup databases locally, then immediately copy them off to a remote drive. That’s a two-step process and it can be tricky to manage the OS-level file copy through SQL Server. But if you have Enterprise Edition it is possible to send a copy of the backup to two separate destinations – one local and one remote – in parallel, which handily solves the problem.

Scalability and Performance

Table and index partitioning – This is a great feature to help manage tables with many rows since it enables you to access, load, remove (age-out), or maintain sub-sets of table data. The table still looks and acts like a unit, but behind the scenes groups of rows are segregated into different partitions. Data can be moved in and out of a single partition efficiently, and indexes and data compression can be maintained on the partition level. In some cases the SQL engine is able to optimize query executions by eliminating partitions that are not relevant to the query. In Standard Editions the closest corollary is  partitioned views which may provide some performance optimizations, but none of the management benefits of partitioned tables.

Data compression – Enterprise Edition supports row and page compression of data. Data compression not only saves space on the physical disk but also can reduce IO. When data is compressed, there is more data per page and so less page reads are required to access a given set of data. Additionally, since the data remains in the compressed configuration after the physical read, the over-all buffer pool usage is reduced which is a good thing – the more data that fits in the buffer pool, the less SQL Server will need to access that data via slow physical reads.

Resource Governor – An enterprise level environment typically serves a diverse set of users. In a well configured SQL Server each user, or group of users, has a particular security profile – some are allowed only to read data from specific views, others to read and write to a set of tables, yet other to execute stored procedures, etc. You can think of the Resource Governor along the same lines, but rather than security constraints, the Resource Governor enforces resource constraints. One type of user might only have access to 30% of the overall CPU, another group to 10% of memory, and so on. Resource Governor enforces a maximum (and minimum) percent of resources for each user type.

These are the Voyages of the “Starship” Standard Edition

After this overview of EE features I hope you are getting the idea of some of the power available in Enterprise Edition. But this also points out just what Standard Edition doesn’t provide. No data compression, no online indexing, no partitioning, etc. Very often I see Enterprise Edition installed and in use, but with a minimum or none of the EE features enabled. If the features above, or the others described in the Microsoft documents, are not necessary for your operations then Standard Edition is your ticket. You still get some valuable tools; in fact it has plenty of great features that suffice for many implementations, and you may be able to work around SE shortcoming with some creative development or implementation of combined features (like the HADR example above). In short, if you don’t need Enterprise Edition, don’t use Enterprise Edition.

It matters because as we all look towards inevitably upgrading versions beyond SQL 2008, we are faced with Microsoft’s new SQL Server licensing scheme. Licensing is going from socket based to core based. The 2008 EE server that has been running along doing its work on 4 quad-core CPUs might suddenly quadruple in licensing costs upon upgrading to SQL2012 EE. Now is the time, before upgrading to 2012+, to down-edition to Standard Edition where possible.

To wrap it all up, this highlights the need to review the current SQL environment for version, edition and work load. Consider consolidating critical production database to perhaps one or several high caliber EE servers with lots of RAM. Then find ways to employ those expensive EE features. Some EE features may initially look like ‘a solution in search of a problem’, but it’s worth a close study to determine how they might be useful. In general these EE features work great and can be critical in getting and keeping enterprise level databases running tip-top. Meanwhile, move less-critical, less-used database to Standard Edition instances. For edges cases, find ways to get by without EE features when you can. Not only can you save licensing costs with this effort as we migrate to SQL 2012+, but you’ll end up with a well thought out, easier to manage and more organized SQL Server environment.

Here are some helpful references you can use:

Features Supported by the Editions of SQL Server 2014

Features Supported by the Editions of SQL Server 2012

Features Supported by the Editions of SQL Server 2008 R2

If you enjoyed this overview, you can gain even more insight through our SQL blog posts (link that whole thing to that blog category since they are already in the blog) and extensive Resource section. For more information on how we might best support you and your organization with custom solutions tailored to your needs, please contact Datavail.