Sometimes I am asked to justify the expense of Microsoft SQL Server Enterprise Edition license for organizations and systems I work with. Here's some of the stuff I say. I was motivated to collect these thoughts in part by the following blog post from @flashdba.
The Real Cost of Enterprise Database Software
http://flashdba.com/2013/09/10/the-real-cost-of-enterprise-database-software/
Certainly database licensing is one of the most significant cost components of a business critical database, whether the licensed product is Oracle, SQL Server, or another commercial database engine. And Microsoft and Oracle both have various tiers of licenses that should be weighed and applied to different systems.
First, an explanation of why this list won't fit your situation - your mileage WILL vary so just take this as food for thought. I work with a lot of organizations, each with numerous large databases across a variety of SQL RDBMS and noSQL platforms. The context for a discussion of SQL Server Enterprise Edition vs Standard Edition may seem a little unusual - these are organizations that have already selected SQL Server over Oracle for the project. I do my best to stay out of the conversations that lead to the Oracle/SQL Server decision, unless someone asks me questions I can answer about performance/scalability of the platforms or the applicability/value of a given feature exclusive to one platform or the other.
My own roots go back to particular noSQL engine on UNIX servers, with nearly every type of storage you can imagine worked into the systems. But these days I pay a lot more attention to SQL Server and Oralce RDBMS systems at the same organizations, performing workloads that are very closely tied to the noSQL systems I know and love. The database systems I'm concerned with occupy a narrow place in the taxonomy of databases. I share what I know, learn and argue. But there is a wide scope of database system design, workload, scale, and performance that is outside my daily concern and interaction. Your system is almost certainly more different from my systems than it is similar to them. But this may give you a new perspective on some of these features, or at least a collection of resources to read and share about them. :)_
***
The following two sources provide a good overview of SQL Server features per license edition. The contribution of selected Microsoft SQL Server Enterprise Edition features is explained below.
http://www.microsoft.com/en-us/sqlserver/editions.aspx
http://msdn.microsoft.com/en-us/library/cc645993.aspx
Server CPU and Memory scaling: While SQL Server Enterprise Edition supports the OS maximum amount of RAM and number of CPUs, SQL Server standard Edition is limited to 64 GB of RAM and the lesser of 4 sockets or 16 cores for CPU. These systems almost always enjoy more than 64GB of RAM on the database server, whether for query performance, to lower the overall rate of physical IO on shared storage resources, or both.
Enterprise Edition includes page compression for tables and indexes, and on this system I recommend using page compression throughout the database. On OLTP systems the CPU cost of compression may outweigh the benefits it provides. However, the primary workload profiles on this system are very different from OLTP workloads. The benefits of decreased logical reads for a given query plan, decreased physical reads for a given query, decreased total data network traffic, and decreased database data footprint on disk are significant on this type of system. As the database continues to grow in size, the decreased data footprint in comparison to an uncompressed database can be a significant benefit in the total amount of storage needed for long term backup retention. (Backup compression is expected to reduce the footprint even of page compressed databases on this system.)
http://technet.microsoft.com/en-us/library/cc280449.aspx
Online index operations, including online index reorganization and rebuild are available in Enterprise Edition. Managing index fragmentation is important the performance of this system, as well as to maintain good neighbor status on shared storage resources. I have not seen a successful implementation of a long term index maintenance strategy on a system like this utilizing exclusively offline index operations.
http://msdn.microsoft.com/en-us/library/ms177442.aspx
Partitioning is available in Enterprise Edition. In this system the main benefit yielded by partitioning selected tables is a reduction in total capacity needed for long term backup retention. Index maintenance can also be made less intensive, and in the future statistics maintenance will also be made less intensive through appropriate partitioning strategies.
http://msdn.microsoft.com/en-us/library/ms190787.aspx
Enterprise DBCC checkdb can perform parallel consistency check operations. As the database grows, without parallelism consistency time check will likely grow at least linearly in duration.
http://technet.microsoft.com/en-us/library/ms176064%28v=sql.110%29.aspx
Enterprise read-ahead is more capable than Standard. Enterprise read-ahead may issue single read requests up to 1 mb. Enterprise read-ahead can request up to 1024 pages ahead of query processing, while standard can only request 128 pages ahead of query processing.
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://blogs.msdn.com/b/psssql/archive/2010/03/24/how-it-works-bob-dorr-s-sql-server-i-o-presentation.aspx
More information on read-ahead can be found at the following link, which also mentions the Enterprise advanced scanning feature. Advanced Scanning allows multiple concurrent tasks to share a full table scan, reducing the total combined IO for the sharing queries/tasks. Advanced scanning is also sometimes referred to as "merry-go-round scanning."
http://technet.microsoft.com/en-us/library/ms191475%28v=sql.105%29.aspx
AlwaysOn Availability Groups are available with SQL Server Enterprise Edition. This allows continuous asynchronous database replication for the system. Some organizations have implemented workload isolation with this feature, and it is an important technology to consider in the availability and disaster recovery design.
Enterprise Edition also includes the online restore feature, including online page restore.
http://technet.microsoft.com/en-us/library/ms188671.aspx
Most of these ideas can be summed up in the following: maintaining acceptable performance of a such a system without Enterprise Edition features would be difficult; maintaining assurance of recover-ability, regulatory compliance, and mitigating litigation risk with integrity check, backup and recovery operations without Enterprise features would be nearly impossible.
No comments:
Post a Comment