Friday, June 08, 2018

Why not use the Standard Edition of SQL Server?

The question invariably comes up - can't we just use the Standard Edition of SQL Server instead of the more expensive Enterprise Edition?

Here are some considerations:

1. Arguably the biggest advantage of Enterprise Edition is increased density - more VMs and more databases can be run on a given physical host using more cores and more memory.

2. Without Enterprise Edition you cannot compress your indexes, which means that more space for data pages will be consumed in memory, decreasing server density.

3. You may have experienced what happens when Standard Edition is forced as a solution - poor designs leading to increased customer risk and increased downtime.  A single outage having to do with indexing when online rebuilds were not available can justify the cost difference, if cost was the only consideration.

4. If HA and high performance and server density and additional failover options in the event of catastrophic outages are not needed then Enterprise edition may not be needed.

5. Enterprise Edition provides better storage performance.
Lego Superman prefers the more powerful
SQL Server Enterprise Edition.

6. Only 2 cores will be used when a batch mode query goes parallel.  This can affect application performance when querying the database.  Glenn Berry states the following:

  • Degree of Parallelism: Batch mode queries are limited to MAXDOP = 2 on Standard Edition. Enterprise Edition can use all of the cores that are present for the instance. This can be very significant on larger queries on typical, modern server hardware.
7. With Standard Edition - hard CPU limits:  If you have a server with more than 24 physical cores Standard Edition will not use the additional cores. (VM or Physical)  Standard Edition will use the lesser of 4 sockets or 24 cores.  I learned this from attending Glenn Berry's pre-con at SQL Intersection in March 2018.

8. With Standard Edition you will not be able to encrypt an entire database without changing application code(Transparent Data Encryption/TDE).  Security conscious customers will ask for this.

9. Enterprise Edition AlwaysOn Availability Groups can be configured to mitigate zero-day attacks by having replicas span multiple operating systems.

10. AlwaysOn Availability Groups provide synchronous or asynchronous readable replicas that can be used for up to the second reporting on production OLTP systems while preserving performance for external facing customers of those systems.

11. One of our team's preferred design patterns is using a CNAME pointing to AG listener.  Use of CNAMEs with AlwaysOn Availability Groups allows connection strings to remain unaltered for future parallel buildouts and additional replicas.

A valued co-worker put it this way: Our database production environments are established and maintained based on best in class solutions.

No comments: