{"id":13855,"date":"2015-03-19T18:45:07","date_gmt":"2015-03-19T11:45:07","guid":{"rendered":"http:\/\/tom.ji42.com\/?p=13855"},"modified":"2020-11-05T13:46:03","modified_gmt":"2020-11-05T06:46:03","slug":"the-case-for-sql-server-2012-enterprise-vs-standard-edition","status":"publish","type":"post","link":"https:\/\/tom.tomwork.net\/?p=13855","title":{"rendered":"THE CASE FOR SQL SERVER 2012 ENTERPRISE (VS. STANDARD) EDITION"},"content":{"rendered":"

\"\"<\/p>\n

In celebration of theimpending SQL Server 2014 release<\/a>, and in recognition that a large percentage of my clients are on that cusp of the Standard\/Enterprise licensing decision for the currently available\u00a0version<\/em>\u2026let\u2019s talk about SQL Server 2012! Specifically, why Enterprise edition might be a huge advantage \u2013 or even an imperative \u2013 for your shop.<\/p>\n

One top 10 list of Enterprise advantages<\/h2>\n

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.<\/p>\n

Enterprise lets you use:<\/p>\n

    \n
  1. More than 64GB memory<\/b> 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.<\/li>\n
  2. Partition switching<\/b>. This allows you to very quickly load or archive huge chunks of data in an online operation.<\/li>\n
  3. AlwaysOn Availability Groups<\/b>, especially for offloading read-only workloads to secondary replicas (and, you know have multiple standby instances ready to recover you from a disaster).<\/li>\n
  4. Resource Governor. <\/b>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.<\/li>\n
  5. Columnstore indexes. <\/b>This new (ish) type of specialized index can massively reduce IO consumption. This is great news for data warehouses, that renowned resource hog.<\/li>\n
  6. Online reindexing<\/b> for tables with large data objects (like varchar(max)).<\/li>\n
  7. Data warehouse improvements <\/b>like <\/b>star join query optimizations, and parallel query processing on partitioned tables and indices.<\/li>\n
  8. Advanced Adapters and Advanced Transforms for SSIS<\/b>, including \u201cDimension processing destination adapter\u201d, \u201cData mining query transformation\u201d, and \u201cFuzzy grouping and lookup transformations\u201d.<\/li>\n
  9. Change Data Capture<\/b>, which lets you easily track inserts\/updates\/deletes on user tables. Fairly simple, efficient, auto cleanup, configurable.<\/li>\n
  10. SSRS data driven subscription<\/b>.\u00a0 You can dynamically determine your recipients, parameters, etc., each time the subscription runs.<\/li>\n<\/ol>\n

    Abbreviated list of SQL Server 2012 Standard vs Enterprise features<\/h2>\n

    You can get the full list of SQL Server features Supported by Edition on MSDN<\/a>; here again is an abbreviated list of some of the better\u00a0ones, IMHO<\/a>.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Feature<\/b><\/td>\nStandard<\/b><\/td>\nEnterprise<\/b><\/td>\n<\/tr>\n
    Per instance maximum compute capacity<\/td>\nLimited to lesser of 4 Sockets or 16 cores<\/td>\nOperating system maximum<\/td>\n<\/tr>\n
    Per instance maximum memory utilized<\/td>\n64 Gb<\/td>\nOperating system maximum<\/td>\n<\/tr>\n
    Database mirroring<\/td>\nSafety full only<\/td>\nHigh performance and full safety<\/td>\n<\/tr>\n
    AlwaysOn Failover Cluster Instances<\/td>\nNode support: 2<\/td>\nNode support: Operating system maximum<\/td>\n<\/tr>\n
    Database snapshot<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    AlwaysOn Availability Groups<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Online page and file restore<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Online indexing<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Online schema change<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Fast recovery<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Mirrored backups<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Hot Add Memory and CPU<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Table and index partitioning<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Data compression<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Resource Governor<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Partition Table Parallelism<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Multiple Filestream containers<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Transparent database encryption<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Parallel indexed operations<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Automatic use of indexed view by query optimizer<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Parallel consistency check<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    Integration Services \u2013 Advanced Adapters \/ Advanced Transforms<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
    SSRS Data driven report subscription<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Further Reading<\/h2>\n

    General:<\/p>\n