Deciding to use SQL Server 2017 Express Edition

By: Daniel Farina   |   Updated: 2018-06-28   |   Comments (4)   |   Related: > Express Edition

Problem

You need a database solution for your application, but you don't want to purchase additional database licenses if you don't need to. Before selecting a database platform, a colleague tells you about the SQL Server 2017 Express edition, but you have many questions prior to deciding to use SQL Server Express in production.  In this tip I will tell you about the basics of the SQL Server 2017 Express edition and things to consider to determine whether this is the right solution for your application.

Solution

We as IT professionals can be specialists in many different technologies and have different roles like Analyst, Developer, Project Manager or Database Administrator just to mention a few. Each of these roles has their own responsibilities, but there is one responsibility that is common to all of these roles. Whatever your role is, you as an IT professional must find amongst different options the solution that best meets the requirements and one that minimizes costs and maximizes benefits.

Imagine yourself in the role of a Database Specialist. If someone asks you for the best SQL Server edition to use for a project I assume you would say SQL Server 2017 Enterprise edition. But the answer to the question will probably change if you have a limited budget. In such case, the first thing that will come to mind is to use the Standard edition instead of the Enterprise edition, but even though the Standard edition works for most projects, is it really the right solution?

There are many DBAs that will laugh at you if you tell them you have a SQL Server Express running in production. They may think the Express edition is a joke, especially those new to SQL Server that do not know the evolution of SQL Server Express, but parrot other people’s opinions based on using the MSDE Engine in the past.  SQL Server Express has come along way.

History of SQL Server Express

SQL Server Express, which you can download from this link, https://www.microsoft.com/en-us/sql-server/sql-server-editions-express is a scaled down edition of SQL Server.  Its origins go back to the years of SQL Server 7 and SQL Server 2000, but in those days SQL Server Express was called MSDE (Microsoft SQL Server Data Engine). As a side note, some people say MSDE stands for Microsoft Desktop Engine, but in my opinion, they are wrong because MSDE is an application intended for data management which has nothing to do with the Windows Desktop.

In the late 90’s Microsoft had two database applications, Access with its JET engine and SQL Server that Microsoft bought from Sybase. Both were relational databases and ANSI SQL compliant, but there was one huge difference between them. SQL Server was (and still is) a native Client Server solution, but Access and its JET engine was a desktop only application. Basically, the main problem with the JET engine is that it does not provide remote access to the database other than by using a file share, because it does not allow network connections to the database. And of course, the remote machine that intended to connect to the JET database, needed to have Access installed. Furthermore, as you may guess from the previous comments, the JET engine did not allow multiple connections, not even from the machine that is running Access, which is the front end of the JET engine. The reason behind this is simple, a file can only be open for modification once at a time in order to maintain consistent information.

Therefore, we now have a free robust relational database option from Microsoft, the SQL Server Express edition.

SQL Server Express Features and Limitations

SQL Server Express is free to download and use, and it provides many of the features of the paid, full versions of Microsoft SQL Server (Enterprise, Standard and Web). However, it has technical restrictions that make it unsuitable for some large-scale deployments. Furthermore, the 2017 version of SQL Server Express includes features that were previously only in the Enterprise edition like In-Memory OLTP.

The difference between the full editions of SQL Server and the Express edition includes artificial hardware limits that prevent its usage on large scale applications with high concurrent access as well as some limitations in features.

The following list enumerates those differences.

• Maximum database size of 10GB per database is the same as in SQL Server 2016, SQL Server 2014, SQL Server 2012, and 2008 R2 Express (4GB for SQL Server 2008 Express and earlier, compared to 2GB in the former MSDE). The limit applies for the database data files, excluding the log files, but in some scenarios, users can access more data through the use of multiple interconnected databases. Just to remind you, the Standard and Enterprise editions allows databases up to 524 PB (Petabytes).
• No SQL Server Agent service to schedule processes (note: it was present in MSDE) .
• Single physical CPU or four cores are allowed
• Maximum memory size per instance is 1410 MB. It will run on a system with a higher RAM amount, but it will only use 1410 MB per instance of SQL Server Express.
• The Express with Advanced Services Edition has capped the maximum memory utilized per instance of Reporting Services to a limit of 4GB per instance of Reporting Services.  The Standard and Web editions have a limit of 64 GB and the Enterprise edition is limited to the operating system maximum memory permitted.
• Maximum Columnstore segment cache per instance is 352 MB. According to Microsoft, columnstore indexes are the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. But since you only can use 352 MB for Columnstore indexes, this is not very practical to use. If you are developing an application that uses Columnstore indexes, you will probably want to use the SQL Server Developer edition which has no limit for columnstore indexes because its only limit is that this edition cannot be used in production.
• Maximum memory-optimized data per database is 352 MB. According to Microsoft, In-Memory OLTP helps improve performance of OLTP applications through efficient, memory-optimized data access, native compilation of business logic, and lock- and latch free algorithms. The In-Memory OLTP feature includes memory-optimized tables and table types, as well as native compilation of Transact-SQL stored procedures for efficient access to these tables. As it happens with Columnstore Indexes, this feature is unlikely to be used.
• Other absent features in the SQL Server Express edition are Analysis Services and Integration Services.
• It does include the SQL Server Import and Export Wizard feature to allow data movement across different sources and destinations.
• The SQL Server Express edition allows us to use Reporting Services by downloading the installer from this link:  https://www.microsoft.com/en-us/download/details.aspx?id=55252.
• Something that is not a feature per se is that we can use the same tools we use on Standard and Enterprise editions like SQL Server Management Studio (SSMS), SQL Server Profiler, Import Export Wizard, sqlcmd and Extended Events Profiler. You can download SQL Server Management Studio from this link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.
• Also, as you can see on the screen capture below of the SQL Server Express edition installer, we can install Machine Learning Services (In-Database) with both Python and R Language.

Reasons You Would Use SQL Server Express Edition

By now, after reading the differences of the Express edition against the Standard and Enterprise editions, you may be asking yourself about the motives that will make you choose to use the Express edition of SQL Server instead of some other solution.

• It’s Free. SQL Server Express is free for production usage. In opposition to the SQL Server Developer edition which is also free to download and use but cannot be deployed in production environments.
• Fully supported by Microsoft including patches and updates.
• It’s built on SQL Server. It may seem obvious, but what I want to state is that any project or development done on SQL Server Express is 100% compatible and ready to be deployed on any of the paid editions of SQL Server.
• Backup and Restore databases.  You have the ability to backup and restore databases the same way as with any of the paid editions. Furthermore, you can restore backups taken from Standard or Enterprise editions, but it’s limited to those databases that don’t use features only available on those editions. For example, you won’t be able to restore a 100GB database to an Express edition.
• It’s scalable. Software creators can sell their applications to different sized companies by not forcing their customers to pay for the Standard or Enterprise edition meanwhile they still have the chance to do so in the future.
• Data security. With SQL Server Express you can take advantage of Row-Level Security, Always Encrypted, basic and fine-grained auditing and Dynamic Data Masking features.
• You can use the same tools for monitoring that you use on Enterprise and Standard editions.
• Includes features you need. SQL Server Express 2017 includes the security features I previously mentioned and also In-Memory OLTP and Columnstore Indexes, Table and index partitioning, Data compression, Stretch Database to Azure amongst others that make this product worth to be considered. You can use Database Snapshots and Database Recovery Advisor for high availability and disaster recovery. You can also use the Express edition as a witness on a Database Mirroring configuration and save money by not using a paid edition.
• You don’t have a limit for concurrent connections. The only limit you may get is OS related. For example, if you are using a home user version of Windows you will be limited by the number of connections allowed by the OS. But if you install SQL Server Express on a Server edition of Windows you won’t be limited on concurrent connections.
• You can easily upgrade. You have the chance to upgrade to Standard or Enterprise editions very easily.

Reasons You Would Not Use SQL Server Express Edition

• No SQL Server Agent. SQL Server Agent is a service that is used to schedule and automate routine tasks. SQL Server Express does not include SQL Server Agent which is very helpful for scheduling processes. Of course, there are ways to emulate its behavior like using Windows Task Scheduler or if you are running SQL Server Express on Linux, the Cron daemon. But it will make SQL Server Express less scalable. The reason behind it is because if you have an application that runs on SQL Server which uses SQL Server Agent and you want to port it to SQL Server Express, you will need to rewrite the jobs and adapt them to the new scheduling mechanism.
• You hate Microsoft products. It's not a joke, there still are people mostly in the open source community that when you show them a Microsoft product, their only reason for not using it is because Microsoft made it. Just look what happened recently when Microsoft acquired Github that there was a campaign to move the repositories to Gitlab (http://www.itprotoday.com/open-source/why-open-source-software-moving-gitlab-after-microsoft-github-deal) without any real argument. I mean, nothing changed on Github that may justify the extra work of migrating repositories.
• You need an embedded database in your application’s binaries. Although SQL Server Express is a lightweight edition of SQL Server it is not embeddable into an application’s source code.
• Resource limitations. As I previously expressed, SQL Server Express limits the maximum database size to 10GB, the memory to 1410 MB and regarding CPU, it only allows the lesser of one CPU socket or four cores.
• You need SQL Server Integration Services.
• You need SQL Server Analysis Services.
• You need features that are not included in Express Edition.

Feature Workaround

There are some missing features that can be emulated by using additional software and a little bit of ingenuity.

Database Mail

• SQL Server Express does not include database mail, but you can replace it by using a CLR stored procedure like explained in this tip: Send Email from SQL Server Express Using a CLR Stored Procedure.

SQL Server Agent

• As mentioned, SQL Server Agent is not included in the Express edition.  You can use the Windows Task Scheduler (or the CRON daemon in Linux) in conjunction with SQLCMD or PowerShell. You can see an example of how to do this by taking a look at this tip: Scheduling a SQL Server PowerShell script using the Task Scheduler.

Database Size Limit

• SQL Server Express limits the databases to 10GB, but it is possible to split the data into different databases by moving tables and creating views, but be aware of the Limitations When Working with SQL Server Views.

SQL Server Management Studio

• As mentioned, SQL Server Management Studio is now a separate download.  You can download the full featured SSMS and use it to manage your SQL Server Express instances.

Next Steps

• Stay tuned to the SQL Server Express Edition Tips Category for more cool tips and tricks regarding SQL Server Express.
• SQL Server Express does not include shortcuts for backup handling and automation. So if you are in need then you can read the following tip: Automate SQL Server Express Backups and Deletion of Older Backup Files.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips