In celebration of theimpending SQL Server 2014 release, and in recognition that a large percentage of my clients are on that cusp of the Standard/Enterprise licensing decision for the currently available version…let’s talk about SQL Server 2012! Specifically, why Enterprise edition might be a huge advantage – or even an imperative – for your shop.
One top 10 list of Enterprise advantages
Every shop has their individual needs and wants, and so every shop will have a different top 10 (or 8, or 17) reasons why Enterprise would benefit them over Standard. This is one list, based on a common set of needs I see at client sites, including performance, resource management, data warehousing, reporting, and change tracking.
Enterprise lets you use:
- More than 64GB memory per instance. This is an extremely important limit, especially for data warehouse servers, which feel about memory and CPU like marathon runners do about pancakes and syrup.
- Partition switching. This allows you to very quickly load or archive huge chunks of data in an online operation.
- AlwaysOn Availability Groups, especially for offloading read-only workloads to secondary replicas (and, you know have multiple standby instances ready to recover you from a disaster).
- Resource Governor. This allows you to customize limits on CPU and memory consumption. This is a HUGE deal, and shops are continuously surprised and disappointed to find that this is not a Standard feature. With Resource Governor, you can map a specific workload or set of workloads to a resource pool, to prevent it taking up too much of the system. These limits can be configured in real time, as they should be.
- Columnstore indexes. This new (ish) type of specialized index can massively reduce IO consumption. This is great news for data warehouses, that renowned resource hog.
- Online reindexing for tables with large data objects (like varchar(max)).
- Data warehouse improvements like star join query optimizations, and parallel query processing on partitioned tables and indices.
- Advanced Adapters and Advanced Transforms for SSIS, including “Dimension processing destination adapter”, “Data mining query transformation”, and “Fuzzy grouping and lookup transformations”.
- Change Data Capture, which lets you easily track inserts/updates/deletes on user tables. Fairly simple, efficient, auto cleanup, configurable.
- SSRS data driven subscription. You can dynamically determine your recipients, parameters, etc., each time the subscription runs.
Abbreviated list of SQL Server 2012 Standard vs Enterprise features
You can get the full list of SQL Server features Supported by Edition on MSDN; here again is an abbreviated list of some of the better ones, IMHO.
| Feature | Standard | Enterprise |
| Per instance maximum compute capacity | Limited to lesser of 4 Sockets or 16 cores | Operating system maximum |
| Per instance maximum memory utilized | 64 Gb | Operating system maximum |
| Database mirroring | Safety full only | High performance and full safety |
| AlwaysOn Failover Cluster Instances | Node support: 2 | Node support: Operating system maximum |
| Database snapshot | No | Yes |
| AlwaysOn Availability Groups | No | Yes |
| Online page and file restore | No | Yes |
| Online indexing | No | Yes |
| Online schema change | No | Yes |
| Fast recovery | No | Yes |
| Mirrored backups | No | Yes |
| Hot Add Memory and CPU | No | Yes |
| Table and index partitioning | No | Yes |
| Data compression | No | Yes |
| Resource Governor | No | Yes |
| Partition Table Parallelism | No | Yes |
| Multiple Filestream containers | No | Yes |
| Transparent database encryption | No | Yes |
| Parallel indexed operations | No | Yes |
| Automatic use of indexed view by query optimizer | No | Yes |
| Parallel consistency check | No | Yes |
| Integration Services – Advanced Adapters / Advanced Transforms | No | Yes |
| SSRS Data driven report subscription | No | Yes |
Further Reading
General:
- SQL Server features supported by edition:
http://msdn.microsoft.com/en-us/library/cc645993.aspx - InfoWorld article on great 2012 features:
http://www.infoworld.com/d/microsoft-windows/review-sql-server-2012-stands-tall-191706 - When to consider SQL Server Enterprise Edition
http://searchsqlserver.techtarget.com/feature/When-to-consider-SQL-Server-Enterprise-Edition
Per feature:
- Use more than 64GB memory per instance (recommended 256-512Gb memory).
http://msdn.microsoft.com/en-us/library/hh918452.aspx (data warehouse memory recommendations) - Partition switching.
http://technet.microsoft.com/en-us/library/ms191160(v=SQL.105).aspx
http://www.sqlservercentral.com/blogs/jamesserra/2012/07/05/sql-server-table-partition-switching/ - AlwaysOn Availability Groups:
http://msdn.microsoft.com/en-us/library/jj542414.aspx - Resource Governor:
http://technet.microsoft.com/en-us/library/bb933866.aspx - Columnstore indexes:
http://technet.microsoft.com/en-us/library/gg492088.aspx
https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/ - Online reindexing:
http://technet.microsoft.com/en-us/library/ms177442.aspx
http://technet.microsoft.com/en-us/library/ms190981.aspx - Data warehouse improvements:
http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/my-top-5-sql-server-2012-features-by-aaron-bertrand-guest-blogger.aspx - Advanced Adapters and Advanced Transforms for SSIS:
http://technet.microsoft.com/en-us/library/ms140144.aspx
http://www.mssqltips.com/sqlservertip/3013/introduction-to-the-dimension-processing-destination-in-ssis-2012/ - Change Data Capture:
http://technet.microsoft.com/en-us/library/cc645937.aspx
http://msdn.microsoft.com/en-us/library/bb895315.aspx - SSRS data driven subscription:
http://technet.microsoft.com/en-us/library/ms159150.aspx
http://jesborland.wordpress.com/2010/09/01/the-power-of-reporting-services-subscriptions-%E2%80%93-data-driven-subscriptions-%E2%80%93-windows-file-share/