SQL Server Express (Free But Not For Mission Critical Workloads)

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 Engine1Operating system maximumLimited to lesser of 4 sockets or 24 coresLimited to lesser of 1 socket or 4 cores
Maximum compute capacity used by a single instance – Analysis Services or Reporting ServicesOperating system maximumLimited to lesser of 4 sockets or 24 coresLimited to lesser of 1 socket or 4 cores
Maximum memory for buffer pool per instance of SQL Server Database EngineOperating System Maximum128 GB1410 MB
Maximum memory for Columnstore segment cache per instance of SQL Server Database EngineUnlimited memory32 GB352 MB
Maximum memory-optimized data size per database in SQL Server Database EngineUnlimited memory32 GB352 MB
Maximum memory utilized per instance of Analysis ServicesOperating System MaximumTabular: 16 GB
Maximum relational database size524 PB524 PB10 GB
Online non-clustered columnstore index rebuildYesNoNo
Resource GovernorYesNoNo
Partitioned Table ParallelismYesNoNo
NUMA Aware and Large Page Memory and Buffer Array AllocationYesNoNo
Buffer Pool ExtensionYesYesNo
IO Resource GovernanceYesNoNo
Automatic TuningYesNoNo
Batch Mode Adaptive JoinsYesNoNo
Batch Mode Memory Grant FeedbackYesNoNo
Management tools
Distributed Replay – Admin ToolYesYesNo
Distribute Replay – ClientYesYesNo
Distributed Replay – ControllerYes (Up to 16 clients)Yes (1 client)No
SQL ProfilerYesYesNo 1
SQL Server AgentYesYesNo
Microsoft System Center Operations Manager Management PackYesYesNo
Database Tuning Advisor (DTA)YesYes 2No

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)YesYesNo
Performance data collectorYesYesNo
Able to enroll as a managed instance in multi-instance managementYesYesNo
Standard performance reportsYesYesNo
Plan guides and plan freezing for plan guidesYesYesNo
Automatic indexed views maintenanceYesYesNo
Distributed partitioned viewsYesNoNo
Parallel indexed operationsYesNoNo
Automatic use of indexed view by query optimizerYesNoNo
Parallel consistency checkYesNoNo
SQL Server Utility Control PointYesNoNo
Buffer pool extensionYesYesNo

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.

Editions and supported features of SQL Server 2017

. . . otherwise you are better off using one of the other editions.


Leave a Comment