Recession is when your neighbor loses his job. Depression is when you lose yours.
– President Ronald Reagan
Recently a colleague informed me that a customer was thinking about using the Express edition of SQL Server for a mission critical workload. Express edition is the entry-level, free database version of SQL Server. It is ideal for learning and building desktop and small server data-driven applications. It is not ideal for mission critical applications. The customer wanted to save on licensing costs, but I cringed at the thought. Why? Because when a mission critical application fails the person responsible for the decision to use the Express edition may end up in a depression.
What is a Mission Critical Workload?
In this context, a mission critical workload is any workload that is extremely important or essential to the success of an organization’s operation. The failure of a mission critical workload significantly impacts an organization’s operations and likely leads to financial loss, human loss or injury, or both.
That seems like a high bar, but it is not as high as you think. For instance, suppose you have an intranet application that requires a database. Hundreds of employees use the application to do their jobs on a daily basis. The database fails and your application is offline for an entire day. What is the cost to the organization of losing a full days effort from hundreds of employees? More than the cost of an Enterprise edition license.
Do not misunderstand me. The SQL Server Express edition is very capable and may meet the performance requirements of your application. It has many of the features that the Enterprise and Standard editions have. However, it lacks the features required for High Availability and Disaster Recovery scenarios – a requirement for any mission critical system.
If you are running a mission critical workload on premises, do yourself a favor. Do not use SQL Server Express Edition. Avoid the possibility of a depression and invest in either the Enterprise or Standard edition.
More Reasons to Choose a Licensed Version over the Express Edition
You may want to carefully review each edition’s capabilities with regards to scale, performance, management tools, and manageability before choosing the free version over a version that requires a license.
For ease of analysis I have pulled out some of the important distinctions between Enterprise, Standard, and Express editions from Microsoft’s documentation which compares the full features sets.
|Maximum compute capacity used by a single instance – SQL Server Database Engine1||Operating system maximum||Limited to lesser of 4 sockets or 24 cores||Limited to lesser of 1 socket or 4 cores|
|Maximum compute capacity used by a single instance – Analysis Services or Reporting Services||Operating system maximum||Limited to lesser of 4 sockets or 24 cores||Limited to lesser of 1 socket or 4 cores|
|Maximum memory for buffer pool per instance of SQL Server Database Engine||Operating System Maximum||128 GB||1410 MB|
|Maximum memory for Columnstore segment cache per instance of SQL Server Database Engine||Unlimited memory||32 GB||352 MB|
|Maximum memory-optimized data size per database in SQL Server Database Engine||Unlimited memory||32 GB||352 MB|
|Maximum memory utilized per instance of Analysis Services||Operating System Maximum||Tabular: 16 GB|
MOLAP: 64 GB
|Maximum relational database size||524 PB||524 PB||10 GB|
|Online non-clustered columnstore index rebuild||Yes||No||No|
|Partitioned Table Parallelism||Yes||No||No|
|NUMA Aware and Large Page Memory and Buffer Array Allocation||Yes||No||No|
|Buffer Pool Extension||Yes||Yes||No|
|IO Resource Governance||Yes||No||No|
|Batch Mode Adaptive Joins||Yes||No||No|
|Batch Mode Memory Grant Feedback||Yes||No||No|
|Distributed Replay – Admin Tool||Yes||Yes||No|
|Distribute Replay – Client||Yes||Yes||No|
|Distributed Replay – Controller||Yes (Up to 16 clients)||Yes (1 client)||No|
|SQL Profiler||Yes||Yes||No 1|
|SQL Server Agent||Yes||Yes||No|
|Microsoft System Center Operations Manager Management Pack||Yes||Yes||No|
|Database Tuning Advisor (DTA)||Yes||Yes 2||No|
1 SQL Server Web, SQL Server Express, SQL Server Express with Tools, and SQL Server Express with Advanced Services can be profiled using SQL Server Standard and SQL Server Enterprise editions.
2 Tuning enabled only on Standard edition features
|Policy automation (check on schedule and change)||Yes||Yes||No|
|Performance data collector||Yes||Yes||No|
|Able to enroll as a managed instance in multi-instance management||Yes||Yes||No|
|Standard performance reports||Yes||Yes||No|
|Plan guides and plan freezing for plan guides||Yes||Yes||No|
|Automatic indexed views maintenance||Yes||Yes||No|
|Distributed partitioned views||Yes||No||No|
|Parallel indexed operations||Yes||No||No|
|Automatic use of indexed view by query optimizer||Yes||No||No|
|Parallel consistency check||Yes||No||No|
|SQL Server Utility Control Point||Yes||No||No|
|Buffer pool extension||Yes||Yes||No|
When to use SQL Server Express
If we do not recommend SQL Server Express for mission critical workloads, when do we recommend it be used?
Microsoft’s literature makes that fairly clear . . .
Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications. It is the best choice for independent software vendors, developers, and hobbyists building client applications.
. . . otherwise you are better off using one of the other editions.