In celebration of the impending 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
http://sqlmag.com/sql-server-2012/tracking-changes-sql-server-2012 - 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/
Check out Minion Enterprise, our new enterprise management solution for centralized SQL Server management and alerting!
Work like a DBA.
Jen,
Nice review and comparison chart.
Thanks,
Thomas
Thanks!
Pingback: Dew Drop – March 25, 2014 (#1750) | Morning Dew
What about auditing and encryption?
Again, everyone has their own list. (And I did at least mention TDE in the table…)
I had two minutes to refresh my memory prior to a meeting and I am glad I read your post.
Can I setup a SQL 2014 mirror with primary Enterprise Edition and The Mirror Server running on Standard Edition?
Another must-have piece of equipment is a cell phone to enable easy
response to your customers. Will you need the cleaning
service to do a thorough cleaning for you.
Regardless of whether you have a 3 bedroom apartment home or a studio, your place will
get dirty.
Another must-have piece of equipment is a cell phone to enable easy response to your
customers. Finally, I think parents and grandparents need to make it clear to
students that keeping the apartment clean is a personal health issue.
These mops can be a bit pricey initially, but over time will add up to hundreds of dollars in savings on floor products, especially if you have many types of
flooring.
Great blog right here! Also your site a lot up very fast!
What web host are you the usage of? Can I am getting your affiliate hyperlink to your host?
I wish my web site loaded up as fast as yours lol
Pretty section of content. I just stumbled upon your website and in accession capital to say that I get in fact enjoyed account your blog posts.
Anyway I’ll be subscribing for your feeds and even I achievement you access consistently rapidly.
Pingback: The Marginal DBA | 36 Chambers - The Legendary Journeys: Execution to the max!
I have noticed you don’t monetize your website, don’t waste your traffic,
you can earn extra cash every month because you’ve
got high quality content. If you want to know how to make extra bucks, search for: Boorfe’s tips
best adsense alternative
Hello blogger, i’ve been reading your page for some time and I really like coming back here.
I can see that you probably don’t make money on your blog.
I know one simple method of earning money, I think you will like it.
Search google for: dracko’s tricks