Deciding Between Editions of SQL Server for Production

By: Daniel Farina |  |   Related Tips:More > Database Administration

Problem

You're asked to run a project that uses SQL Server as the database engine in your production environment with the least cost as possible. You know there are different editions of SQL Server, but how do you choose the right edition to make sure you have the needed features and keep the cost down.

Solution

Decisions are hard to make, especially if you don’t have relevant information. Sometimes the person that makes the decisions is not the person who knows the most. Let’s face it. We as database administrators don’t have the last word in the enterprise world. Not even regarding databases. The one with the last word is the one who signs the pay check, which in most cases is not the DBA.

Just to show you the picture, imagine yourself in a meeting with your company’s CFO explaining to him/her that you need the Enterprise edition of SQL Server. When your CTO asks about the price difference between the 2-pack of core licenses of SQL Server Enterprise $14,256 ($7,128 per core) and the Standard Edition which costs significantly less $3,717 ($1,859 per core) you must provide him with all the information in order to justify the $10,539 difference.

Editions of SQL Server

There are five editions of SQL Server:

• Express : This is the most basic of all SQL Server editions. It’s free to use in production, which makes it the best choice for independent software vendors, whose clients can’t afford the cost of a SQL Server license.
• Web : This edition is between the Standard and Express editions. It has features that Web hosting companies and Web VAPs can offer their customers to provide scalability, affordability, and manageability capabilities for small to large scale web properties.
• Standard : This edition enables database management with minimal IT resources.
• Enterprise : This is the most complete edition of all. With this edition you have unlimited virtualization and high service levels for mission critical workload.
• Developer : This edition has all the features of the Enterprise edition, but cannot be used in production environments.

These editions differ on features and also the resources they can utilize based on resource caps that are part of the edition.

Reasons to use SQL Server Express Edition

• If your application runs fine when capped to the lesser of 1 socket or 4 cores.
• Your application needs less than 1410 MB of buffer pool and less than 352 MB of memory for both Columnstore segment cache per instance and memory-optimized data size per database.
• If your database won’t be greater than 10 GB.

Recently I wrote the tip, Deciding to use SQL Server 2017 Express Edition that goes deeper into this matter and SQL Server Express Versions that will help you decide which version of SQL Server Express is right for you.

Reasons to use SQL Server Web Edition

• If your application runs fine when capped to the lesser of 4 socket or 16 cores.
• Your application needs less than 64 GB of buffer pool and less than 16 GB of memory for both Columnstore segment cache per instance and memory-optimized data size per database.
• You have the chance to use Log Shipping for high availability. Log Shipping allows you to automate the backup of transaction log files on a primary database server, and then restore them onto a standby server.  You can read how to implement it in this tip: Step By Step SQL Server Log Shipping . Additionally, you can take a look at our SQL Server Log Shipping Tips Category .
• Minimum replica commit availability group that enables users to set the minimum number of replicas that are required to commit a transaction before committing on the primary. Availability groups are a part of Always On technology, which you can get more information in this tip: What is SQL Server AlwaysOn? Additionally, you can take a look at our SQL Server Availability Groups Tips Category .
• You are limited to one controller for Distributed Replay. The Distributed Replay feature is available to help you assess the impact of future SQL Server upgrades. You can also use it to help assess the impact of hardware and operating system upgrades, and SQL Server tuning. If you want to implement this technology you can take a look at these two tips: Configure SQL Server Distributed Replay feature and Using the SQL Server Distributed Replay feature .
• The SQL Server Agent service is available, so you can schedule maintenance tasks like re-indexing and backups and monitor its execution status. You can get more information about SQL Server Agent on this tip: Getting Started with SQL Server Agent - Part 1 .
• You will be able to use Microsoft System Center Operations Manager Management Pack to monitor and administer your SQL Server instance.
• Database Tuning Advisor (DTA) is available for optimizing queries. Take a look at Ray Barley’s tip SQL Server Database Engine Tuning Advisor for Performance Tuning .
• You can administer servers by using Policy-Based Management. If you are new to this, a look at this tip: Using Policy Based Management in SQL Server . Additionally, you can dig into our SQL Server Policy Based Management Tips Category .
• Performance data collector. This is a feature introduced in SQL Server 2008 which can be used by database administrators to gather performance related data using built-in data collectors. For more information please take a look at this tip: Performance Data Collection and Warehouse Feature of SQL Server 2008 Part 1 .
• Able to enroll as a managed instance in multi-instance management. You can find more information about this in our SQL Server Central Management Servers Tips Category .
• Standard performance reports on SQL Server Management Studio ( Built in Performance Reports in SSMS ).
• Plan guides and plan freezing for plan guides. You can learn how to use this feature in this tip: How to use a SQL Server Plan Guide to Tune Queries .
• Automatic indexed views maintenance.
• Transact-SQL endpoints with Service Broker. You can read more about this in our SQL Server Service Broker Tips Category .
• Database mail to send e-mail messages. If you want to set it up I suggest that you take a look at this tip Setting up Database Mail for SQL Server and our SQL Server Database Mail Tips Category .
• StreamInsight Standard Edition. SreamInsight is a powerful platform that you can use to develop and deploy complex event processing (CEP) applications. For more information you can take a look at the following tip: Microsoft SQL Server 2008 R2 StreamInsight Overview .

Reasons to use SQL Server Standard Edition

• If your application runs fine when capped to the lesser of 4 socket or 24 cores.
• Your application needs less than 128 GB of buffer pool and less than 32 GB of memory for both Columnstore segment cache per instance and memory-optimized data size per database.
• Backup compression. To learn how to take a compressed backup you can read this tip: SQL Server 2008 Backup Compression .
• Support for up to two nodes on Always On failover cluster instances.
• Basic availability groups  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups
• Encryption for backups. You can read my previous tip SQL Server 2014 Backup Encryption to get the details.
• Hybrid backup to Windows Azure (backup to URL). In the next tip you will get the required information to implement this solution: SQL Server managed backups to Azure .
• Clusterless availability group.
• Buffer Pool Extension, which gives us the possibility to use an SSD or Ram device to dynamically increase the Buffer Pool. You can get more information in my previous tip Increasing Buffer Pool in SQL Server 2014 .
• Heterogeneous subscribers which allow you to subscribe your SQL Server instance to non SQL Server sources. For more detailed info check out this tip: https://www.mssqltips.com/sqlservertip/2300/introduction-to-sql-server-heterogeneous-replication/ .
• Transactional replication to Azure. In order to take advantage of this feature I suggest you read this tip: Using Transactional Replication with an Azure SQL Database .
• SQL Profiler. This is a tool that provides a user interface to the SQL Trace utility which gathers events as they occur in your SQL Server instance. For more information I suggest that you read this tip Introduction to SQL Server Profiler and also visit our SQL Server Profiler and Trace Tips Category .  
• MDX edit, debug, and design tools.
• Send messages via the Service Broker.
• Create cubes without a database. You can read more about this here: How To Build a Cube Without a Data Source Using SQL Server Analysis Services .
• Auto-generate staging and data warehouse schema.
• Change data capture. This feature records the DML activity on a particular table. You can learn more about it in this tip: Using Change Data Capture (CDC) in SQL Server 2008 . Additionally, you can visit the SQL Server Change Data Capture Tips Category for more cool tips and tricks regarding CDC.

Reasons to use SQL Server Enterprise Edition

• You have no limit on compute capacity and are allowed to use all the CPU’s available on your system.
• Also you have no limits on buffer pool size, Columnstore segment cache and memory-optimized data.
• Always On Availability Groups. You can use up to 8 secondary replicas, including 2 synchronous secondary replicas.
• Online page and file restore that allows you to recover a partially corrupted database. In this tip you will see how it’s done: Using Page Level Restore as a Disaster Recovery Procedure in SQL Server 2005 .
• Online indexing: Rebuilding SQL Server indexes using the ONLINE option .
• Resumable online index rebuilds. For more information take a look at this tip: SQL Server 2017 Resumable Online Index Rebuilds .
• Online schema change that allows you to modify tables online like in this tip: SQL Server 2016 Online ALTER COLUMN Operation .
• Fast recovery that saves time in the initialization of databases that needs to perform a recovery.
• Mirrored backups that will allow you to create 2 to 4 identical copies of a database backup.  You can see how to do this on the next tip: Mirrored Database Backup Feature in SQL Server 2005 and SQL Server 2008 .
• Hot add memory and CPU. If you don’t know how to do it don’t worry, we have a tip for that: How to hot-add a vCPU to a virtual SQL Server .
• Online non-clustered columnstore index rebuild.
• Resource Governor. This feature allows you to specify limits on resource consumption like memory, CPU and IOPS. For more information take a look at our SQL Server Resource Governor Tips Category .
• NUMA Aware and Large Page Memory and Buffer Array Allocation.
• Automatic Tuning.
• Batch Mode Adaptive Joins.
• Batch Mode Memory Grant Feedback. For more information take a look at this tip: SQL Server 2017 Adaptive Memory Grant Feedback for Stored Procedure Performance Improvements . 
• Transparent database encryption for data security: SQL Server 2008 Transparent Data Encryption getting started .
• Extensible key management (EKM), using the Microsoft Cryptographic API (MSCAPI) provider for encryption and key generation.
• Oracle publishing.
• Peer-to-Peer transactional replication. For more info you can read this tip: Overview of Peer-to-Peer Transactional Replication in SQL Server 2008 .
• Transactional replication updateable subscription.In this tip you will see an example of how to implement this technology.
• Distributed partitioned views. This allows you to use tables that reside in different databases which reside on different servers or different instances. For more information take a look at our SQL Server Views Tips Category .
• Parallel indexed operations: Reduce Time for SQL Server Index Rebuilds and Update Statistics .
• Automatic use of indexed view by query optimizer.
• Parallel consistency check. For more information take a look at this tip: SQL Server 2016 DBCC CHECKDB with MAXDOP .
• SQL Server Utility Control Point. You can get more information about this on the next tip: Introduction to Utility Control Points in SQL Server 2008 R2 .
• Advanced R integration. R is a programming language and free software environment for statistical computing. For more information you can read the tip Getting started with R in SQL Server . Additionally, if you need to set it up I suggest that before starting you take a look at this configuration guide: SQL Server 2016 R Services: Guide for Server Configuration .
• Advanced Python integration: Installing Python and Running Python Scripts from SQL Server SSMS . 
• Machine Learning Server (Standalone). For more information and installing instructions take a look at the following tips: Machine Learning Services – Installation and Configuration ; Basics of Machine Learning with SQL Server 2017 and R and How to setup Machine Learning Services in SQL Server 2017 CTP2 .  
• Polybase head node. PolyBase is a technology that accesses data outside of the database via the T-SQL language. You can get more information about it in this tip: Introduction to PolyBase in SQL Server 2016 - Part 2 .
• Automatic star join query optimizations.
• Scalable read-only Analysis Services configuration. For more information take a look at this tip: Clustering SQL Server Analysis Services Part 1 .
• Parallel query processing on partitioned tables and indexes that improves query performance on partitioned tables.
• Global batch aggregation.
• StreamInsight Premium Edition with High Availability.

Next Steps

• For more tips about SQL Server 2017 visit our SQL Server 2017 Tips Category .
• Take a look at the changes introduced on SQL Server 2017 by reading this tip: Overview of Database Engine Changes in SQL Server 2017 .
• Take a look at my previous tip Deciding to use SQL Server 2017 Express Edition for more information about the Express Edition of SQL Server.

Last Update: 2018-08-23

About the author