SQL Server Express


By:   |   Updated: 2020-08-25   |   Comments   |   Related: More > Express Edition


Problem

SQL Server is available in five editions; Enterprise, Standard, Web, Developer, and Express. We want to know more specifically about Express Edition. What is it?  What is the cost?  When can we use it? How do I install it?

Solution

This tip will answer questions on SQL Server Express Edition.

What is SQL Server Express Edition?

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.

Is Express Edition really free?

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

What versions of SQL Server Express Edition are available?

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

How do I obtain SQL Server Express Edition?

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

How do I install SQL Server Express Edition?

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.

What are the limitations of Express Edition?

  • 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.

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:

Do I need to patch Express Edition?

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

How do I patch Express Edition?

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 Express and other editions in the way security is implemented?

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.

Can I have more than one SQL Server Express Edition on a given Windows machine?

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.

Where will I find 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 Express Edition?

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

When would I not use Express Edition?

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

Can I upgrade Express Edition 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:

Does Express Edition come with High Availability / Disaster Recovery (HADR) functionality?

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.

What are some other things I need to look out for?

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:

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



Last Updated: 2020-08-25


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Getting Started with SQL Server 2012 Express LocalDB

Deciding to use SQL Server 2017 Express Edition

Installing SQL Server 2017 Express

Getting Started with SQL Server 2017 Express LocalDB

How To Schedule SQL Scripts On SQL Server Express Edition








get free sql tips
agree to terms