SQL Server Express

By: Joe Gavin | Comments | Related: > Express Edition

Problem

SQL Server Express is a free version of SQL Server and this article will cover all of the aspects of the product you need to know to get started. What is it and what are the limitations? Is it free? When can we use it? How do I download and install it?

Solution

This tip will answer questions on SQL Server Express Edition.

What is SQL Server Express

Express Edition was originally called MSDE (Microsoft Data Engine or Microsoft Desktop Engine) in SQL Server versions 7 and 2000. Then the product was renamed Express Edition in version 2005 and the name continues. The edition has evolved as versions have evolved. It's a free entry level database server commonly used for small database applications.

Microsoft SQL Server Express Cost

The cost is $0.00 and it's free to download, distribute and use in production.

SQL Server Express Download

It can be downloaded from the one of the following corresponding links:

Install SQL Server Express

The install is almost identical to other editions. Installing SQL Server 2017 Express will walk you through the installation of a SQL Server 2017 Express Edition.

SQL Server Express Versions

As of this writing it's available in all supported versions which are 2012, 2014, 2016, 2017 and 2019.

SQL Server Express Limitations

Maximum database data file size is 10 Gigabytes.
CPU is limited to the lesser of 1 socket or 4 cores.
Maximum memory utilized per SQL Server instance is 1 Gigabyte.
It doesn't have Maintenance Plans, SQL Server Agent or Database Mail.
While technically the number of databases you could have on Express Edition is the same across editions at 32,767, it's highly unlikely the SQL Server service would even start at a fraction of that so realistically no more than just a few databases would be practical given memory and CPU limitations.

Microsoft SQL Server Express FAQ

Can I backup/restore databases, run DBCC checks, and perform index maintenance if I'm using Express Edition?

Yes. You can and should be doing all these tasks. However, given the lack of Maintenance Plans, SQL Server Agent, and Database Mail you would need to use another option like one of the following:

Patching SQL Server Express

You need to patch Express Edition just as you would with other editions.

Patching is the same regardless of the edition you have. For example, if you just installed any edition of SQL Server 2019 you will be at 'Release To Manufacturing (RTM)' level and would need to obtain the latest 'Cumulative Update (CU)' and / or 'Service Pack (SP)' depending on the version which can be found here: Latest updates for Microsoft SQL Server and apply it.

Can I backup a database on an Express Edition server and restore it to another edition and vice versa?

Yes, you can. A backup from any edition of SQL Server can be restored to any other edition. The rule for versions is still the same though, you can only restore to the same or higher version. For example, you can backup a database from SQL Server 2017 Standard Edition and restore it to a SQL Server 2019 Express Edition, but cannot backup and restore the other way from 2019 to 2017. Also, remember the max database size on Express is 10GB.

Is there any difference between SQL Server Express security and other editions?

There are no differences. You still have either a SQL authentication login/password and/or an Active Directory login with server and database level groups at a high level which is the same in all editions.

Is Microsoft SQL Server Express multi-instance?

Yes, as with other editions Named Instances are supported. Rather than your instance being named MachineName, they would look something like this: MachineName\SqlServerInstanceName1, MachineName\SqlServerInstanceName and so on.

Can I upgrade MS SQL Express to another edition if I outgrow it and have the need for more resources?

Yes, you can. While possible to upgrade it in place, it's advisable to build a new server and migrate the database to it. Here are some links to help you there:

SQL Server Express Edition High Availability and Disaster Recovery

While you could create your own scripts to backup and restore to another server there is no real HADR with Express Edition. And you are most likely going to use a higher edition if you have the need for HADR.

Where is SQL Server Express Edition used?

Express Edition is often installed along with a smaller scale or larger scale application where the database processing needs are low or as a free development / learning server.

Here's a short list of just some of the applications it's used with:

SAP Business Objects
Veeam backup
Optical Character Recognition (OCR) systems
Building security systems
Fax processing
Barcode Labeling applications
Shipper (UPS, etc.) applications

When would I use SQL Server Express?

If your database(s) can perform adequately with:

1 GB of memory or less
The lesser of 1 socket or 4 cores of CPU
You can guarantee the database data file on any database will not grow past 10 Gigabytes

SQL Server Express Minimum Requirements

If your database needs:

More than 1 GB of memory
More than the lesser of 1 socket or 4 cores of CPU
Any database data file that will grow larger than 10GB
If you need tools like Database Mail or SQL Server Agent

SQL Server Express Additional Considerations

Here's a list of some of things I've run across to look out for:

The SQL Server being installed without your knowledge, it's usually not patched, and sometimes it's an unsupported version – it's amazing what you'll find running the Microsoft Assessment and Planning Toolkit
Everything being installed on the C:\ drive due to whoever installed it just blindly clicking through the defaults which results in the system drive filling up
Although some of the applications that use Express Edition have some ability to do some maintenance functions like database backups, data archiving, etc. I've heard it said that the installed SQL Server Express Edition is 'self-managed' and there is no such thing
Care and feeding can be minimal, but you still need to do database backups, differential or transaction log backups (set recovery model to simple if you don't the recoverability of transaction log to prevent the log file from growing out of control), run DBCC checks, review the error log, etc.
A vendor app that removes logins from the sysadmin group - Connect to SQL Server when system administrators are locked out shows how to access as sysadmin if you need to as long as you have Windows Administrator rights
Lack of security implemented with the instance and in the databases

Next Steps

Here are links to several SQL Server Express Edition tips on MSSQLTips.com:

SQL Server Express Edition Tips

And here is some general SQL Server information at Microsoft Docs:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

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

View all my tips