There are a lot of misperceptions and misinformation about Microsoft’s SQL Server® Express edition. Some of these misperceptions are holdovers from the limitations of previous releases, and others stem from misunderstandings about the product’s features and licensing. In this article, I’ll tackle some of the myths about using SQL Server Express as a production database, and explain the differences between the various SQL Server 2014 Express editions and their limitations.
You can also watch this video where I demonstrate how to set up SQL Server Express as a production database:
Myths and realities
Before jumping into the details of SQL Server 2014 Express, let’s do away with some of the main myths concerning this edition of SQL Server.
Myth: SQL Server Express can’t be used as a production database.
Reality: While SQL Server is a completely free product, it is also completely capable of legally being used for production workloads. The licensing allows you to include SQL Server Express as a part of your own products, and many ISVs (Independent Software Vendors) do just that.
Myth: SQL Server Express is limited to a single user.
Reality: The predecessor of SQL Server Express, MSDE (Microsoft® Desktop Engine) used to have a workload governor that would reduce the performance of the database for multiple users. However, SQL Server Express has no such restrictions and is only restricted by the maximum capacities that I discuss in the Limitations section.
Myth: SQL Server Express is limited to 4GB of storage
Reality: This used to be the case with SQL Server Express 2008 and earlier. However, with the SQL Server 2008 R2 release, Microsoft increased the maximum database size in SQL Server Express to 10GB—that’s 10GB per data file for a database. You can have multiple 10GB databases.
Editions
There are four editions of SQL Server 2014 Express, all of them have both 32-bit and 64-bit versions:
- SQL Server 2014 Express LocalDB: First introduced with SQL Server 2012, SQL Server Express LocalDB is a lightweight version of SQL Server Express that is intended as a development tool. It runs as a local application—not as a service. It supports all of primary programmability features as the other SQL Server relational databases, but there is no required configuration. It is not intended to support production or multi-user workloads. The installer is called SqlLocal.msi.
- SQL Server 2014 Express: The SQL Server 2014 Express edition includes only the SQL Server database engine. This edition is a good fit for small scale production databases, and is often distributed by different ISVs along with their applications. SSMS (SQL Server Management Studio is not included with this edition. You can download SSMS separately, or you can manage SQL Server Express 2014 using the SSMS that comes with the SQL Server 2014 Standard, Business Intelligence, or Enterprise editions. The English language setup programs are called SQLEXPRE32_X86_ENU.exe and SQLEXPR_x64_ENU.exe.
- SQL Server 2014 Express with Tools: This version of SQL Server 2014 Express includes both the core relational database engine as well as SSMS. This version is best for developers who are creating and managing multiple small SQL Server databases. The English installers are called SQLEXPRWT_x86_ENU.exe and SQLEXPRWT_x64_ENU.exe.
- SQL Server 2014 Express with Advanced Service: This edition is the most full-featured member of the SQL Server 2014 Express family. It includes the SQL Server Express relational database engine, SSMS, the option to install LocalDB in addition to Full Text Search, and Reporting Services. This version is best for small application developers who need to include reporting capabilities with their applications. The English installer packages are called SQLEXPRADV_x86_ENU.exe and SQLEXPRADV_x64_ENU.exe.
Limitations
While the SQL Server Express editions can definitely be used for production databases, there are some important limitations that you need to be aware of:
- The relational database engine is limited to the lesser of 1 socket or 4 cores.
- The buffer cache of each instance is limited to 1MB of RAM.
- If you have the SQL Server Express with Advanced Services edition, the Reporting Services components are limited to 4GB of RAM.
- Databases are limited to 10GB of storage per database data file. FILESTREAM data does not count toward this limit.
- SQL Agent is not present.
For a more complete picture of how the SQL Server Express feature set compares to the full- blown editions of SQL Server 2014 you can check out Features Supported by the Editions of SQL Server 2014.
Express delivery
SQL Server Express is a great choice for small-scale, cost-effective multi-user database implementations. It can run on both Windows® desktop operating systems like Windows 7, 8, 8.1, and 10, as well as server operating systems like Windows Server 2008, 2008 R2, 2012, and 2012 R2. It is completely compatible with the Standard, Business Intelligence, and Enterprise editions of SQL Server 2014, and can be upgraded to any of these editions. Microsoft® SQL Server Express is employed by many Independent Software Vendors (ISVs) as a built-in database and it can be included in your own installation packages. You can download all of the SQL Server 2014 Express editions for free from Download Microsoft SQL Server 2014 Express.
I’d like to know the PRACTICAL limit of Express edition. I get the 10GB db, 1MB instance cache, 1 socket or 4 cores limits, but won’t responsiveness suffer before we reach 10GBs? How many rows I can expect to cope with? I know that depends on the size of the rows, queries, etc, but I need to make a decision before I spec and build, so an order of magnitude guess would be invaluable.
I think you meant 1 GB ? “The buffer cache of each instance is limited to 1MB of RAM.”
Hi Michael,
Thanks for the excellent post and information about how SQL Server Express can be used in a significant way. My experience shows that SQL Server Express works very well as a production database. In one application, we used this at over 1500 healthcare clinics for many years (it is still in use today). Each of the clinics processed many hundreds to many thousands of transactions each day.
Also, in terms of one of the limitations – there is no SQL Agent for job scheduling. SQLAutomate is a product that is available and is fully featured. While not free it is very low cost and another reason to stick with SQL Server Express as a production platform. Other options are of course the Windows task scheduler and using a master SQL Server environment (standard or enterprise).
Thank you for the excellent presentation. It points out what many of us have experience with SQL Server Express over time. It can certainly be used for significant production applications. We have used it at over 1500 healthcare clinics all with separate SQL Server Express instances installed to process millions of transactions each day.
You can easily get around the SQL Server Agent disadvantage by using one of the following:
1. Third party product such as SQLAutomate
2. Windows Task Scheduler
3. A Standard or Enterprise Edition of SQL Server with the master server capability installed
1 and 2 are very low cost or free. 3 is expensive unless you already have it available in your environment.
Hi have a question. Recently I talked to a Microsoft official and he said SQL Express cannot be installed in any client site where there are more than 10 employees. If there are more than 10 employees on client site, client cannot use express rather he has to purchase a license to use the tool as a database engine. Please throw some light on this aspect.
Hi,
Recently I talked to a Microsoft official and he said SQL Express cannot be installed in any client site where there are more than 10 employees. If there are more than 10 employees on client site, client cannot use express rather he has to purchase a license to use the tool as a database engine.
http://download.microsoft.com/download/6/4/A/64A1EC8F-F575-41E1-9D34-821FA9F98F8E/SQL_Server_2012_Licensing_Reference_Guide.pdf
You even may use SQL Express legally together with SSIS – look here
http://www.insidesql.org/blogs/klausobd/sqlserver/