Microsoft SQL Server Express Edition Limitations and Versions

What is SQL Server Express?

SQL Server Express Edition is a free edition of SQL Server. Microsoft provides it for educational purposes as well as minor use cases. Students, hobbyists, and startups can use this edition to implement projects that do not require advanced features of SQL Server.

This article will examine the functionality of the SQL Server Express edition. But before we proceed to explore it, we’ll try to understand the concept of versions and editions in SQL Server.

Microsoft SQL Server Versions and Editions

SQL Server is grouped as versions and editions when shipped. The SQL Server version releases come out every two or three years or so. A new version release brings numerous improvements to SQL Server in terms of feature set, performance capabilities, scalability, etc. The versions of SQL Server are named according to the year of release.

For each version of SQL Server, we also have a grouping called editions. Editions of SQL Server determine the functionality. Every edition has its feature set. Customers can choose the right edition and won’t pay for what they do not need. For example, organizations without high availability use cases would not need an Enterprise or Standard Edition of SQL Server.

Have a look at the below picture. It summarizes the SQL Server editions offered in version 2019:

The next picture shows the resource usage limits of the SQL Server 2019 editions.

A full description of the functionality of all editions is available on the Microsoft Website.

This article will focus on SQL Server Express Edition.

How to check SQL Server Version and Edition

You can check the version and edition you are running with the following code (@@VERSION is an inbuilt SQL Server configuration function):

-- Listing 1: Check Version and Edition of SQL Server
select @@version

On my computer, I have two editions of SQL Server. Now, I want to check their data. Let’s have a look at the outputs:

Note: Both instances are running SQL Server 2019, but one is the Developer edition, and the other one is Express Edition.

You can also retrieve similar information from SQL Server using the metadata function SERVERPROPERTY. It requires passing an argument to specify which precisely property of the instance we wish to retrieve.

The syntax for SERVERPROPERTY is as follows:

-- Listing 2: Check Edition of SQL Server Using SERVERPROPERTY
select SERVERPROPERTY ('Edition');

The detailed information about the SERVERPROPERTY function is also available on Microsoft Documentation.

SQL Server Express Edition Limitations

As mentioned earlier, one key reason for providing different versions of SQL Server is allowing customers to choose the right functionality for them and avoid paying for what they won’t need. Each “lower” edition has fewer features or less sophisticated functionality.

SQL Server Express Edition has the least features set. If you are a database administrator, you should be aware of the following limitations of this edition:

• The maximum database size is 10 Gb
• No support for the high availability configurations
• No support for the disaster recovery configurations
• No support for encryption
• No support for compression
• No support for database mail
• Limited support for performance tuning tools
• Limited support for management tools
• SQL Server Agent is not available

Let’s dig a little bit deeper into the above points. For instance, the Database Properties section of the Express edition misses the Mirroring and Transaction Log Shipping options. It is the limitation set for the edition. However, if you aren’t aware of this restriction, you might assume it was an error in the installation.

For disaster recovery, we need to clarify that an SQL Server Express instance can work as a database mirroring witness even though we cannot have such instances as mirroring partners.

An attempt to perform a backup operation on a SQL Server Express instance using the COMPRESSION option (see Listing 3) will return the error. Similar errors will occur if one attempts to use encryption.

-- Listing 3: Backup a Database on SQL Server Express

backup database WWI
to disk = 'E:\DriveG\Flash\WWI.bak'
with compression

The Object Explorer pane misses an SQL Server Agent (see Figure 6). It brings many implications for features requiring automation, such as database mail, operators, alerts, jobs, etc.

Finally, any attempt to exceed the 10GB limit for a database will return the error:

-- Listing 4: Increase File Size Beyond 10GB
USE [master]
GO
ALTER DATABASE [WWI] MODIFY FILE ( NAME = N'WWI', SIZE = 10486784KB )
GO

Why Use an MS SQL Express Edition?

As we see, there are significant limitations to the SQL Server Express functionality. You might ask what for would anyone use it at all.

Essentially, Express Edition is for low budget needs. SQL Server Express is FREE.

You can download the last version from the SQL Server 2019 Express page, and installing is straightforward. It takes less time than, say, Enterprise Edition since it is a much lighter edition.

Typically, one uses Express Edition for learning the basics of managing data on SQL Server or for testing and non-critical solutions. Such solutions don’t require high availability or disaster recovery. However, it makes sense to configure regular backups along with a documented procedure for recovery. It would not take too much time since the database sizes are limited to 10GB.

You can circumvent the 10GB limit of database size by implementing a simple archiving procedure that periodically exports the top tables in your main database (e.g., DB_Tran) to archive databases (e.g., DB_Archive_n). You stall deal with managing relatively large backup sets, as there is no compression available. Explore carefully compressing your backups at the operating system level after they are taken successfully.

Conclusion

SQL Server Express Edition provides a cost-effective option for DBAs who want to employ SQL Server for non-critical use cases. Despite its limitations, it can be quite useful for administrators who develop creative ways of circumventing such limitations.

However, no matter how small your critical databases are, it may not be a good idea to employ Express Edition for them. You still need to deal with other limitations beyond the database size. If you want to know how to check SQL Server storage information using stored procedures read our post.

References

1. ServerProperty
2. SQL Server Express Download
3. Editions and Supported Features of SQL Server 2019