SQL Server Express is a free lightweight edition of SQL Server with limited functionality, available for all versions starting from SQL Server 2005. You can use it for small data-driven applications and independent application vendors.
The Express edition restrictions are the following:
- Maximum relational database size: 10 GB
- Maximum compute capacity for SQL Server instance: Lessor of 1 socket or 4 cores.
- Maximum compute capacity for SSAS and SSRS: Lessor of 1 socket or 4 cores.
- Maximum Memory: 1410 MB
- Log shipping, Backup compression, Always On Availability Groups, Basic Availability Group, Online index create and rebuild, Accelerated database recovery, Encrypted backup – Not available
- Intelligent Database: automatic tuning, row mode memory grant feedback, the batch mode for row store – Not available
- Transparent database encryption, Heterogeneous subscribers, Peer to peer transactional replication, Transactional replication to Azure – Not available
- SQL Server Agent, SQL profiler, Database Tuning Advisor (DTA) – Not available
- Performance data collector, Standard performance reports, Parallel consistency check, Database mail, T-SQL endpoints – Not supported
The current article will explore the processes of downloading and configuring the Express SQL Server edition, as well as highlight some essential factors in its work.
Download SQL Server Express Edition
It is always recommended to use the latest versions of all products. In our case, the latest version is 2019.
Navigate to the official SQL Server download page and select the Express edition:
It will download the SQL2019-SSEI-Expr.exe installation file. Double-click on it to launch the setup.
In the installation type, you can choose from the Basic, Custom, or Download Media options. Select Download Media and save the ISO file on your system.
On the next page, you will be presented with several more options for the Express Edition package:
- Express core: It installs the SQL Server database engine only.
- Express advanced: If you require additional SSRS and full-text service, choose the advanced option.
- LocalDB: The LocalDB is a lightweight express edition that includes programmability features and can run in user mode. It is a zero-configuration installation, and the download size is only 53 MB. The primary scope is development activities.
In our case, we are going to use the Express Core option.
Click Download.
Once the ISO file is downloaded, launch SETUP.exe
In the Installation menu, select New SQL Server stand-alone installation…
Accept the License Terms and conditions.
Click Next, and it will perform a rule check, such as consistency validation for SQL Server registry keys, Windows firewall, computer domain controller.
As shown below, the Express Core edition has limited functionality. The database engine service is available for replication only. Besides, this edition does not include SQL Server Management Studio. You should download and install it separately.
You can name your SQL instance as you wish or go with the default name – SQLEXPRESS.
In the Server Configuration section, you can configure the service account, startup type, collation, and perform the volume maintenance task permission to the database engine. You can use this feature to enable instant file initialization.
Database Engine Configuration provides the following options:
- Server configuration
- Authentication mode ( Windows or Mixed mode)
- Specify SQL Server administrators
- Data directories
- TempDB configuration
- Memory: Here, you can choose the default or recommended memory configuration. You cannot configure it.
- Click Next, and it starts installing the SQL Server 2019 Express Edition.
It completes the SQL Server 2019 database engine service for express edition as shown below.
To connect with the SQL Server Express edition, download SQL Server Management Studio and connect it as below:
You can validate the version by the following query:
Select @@version
FAQ on SQL Server Express Editions
1. Is it possible to take a database backup on the Express edition?
Yes, you can take regular database backups for both system and user databases.
However, you cannot schedule backup jobs as the SQL Server agent is not available in the Express edition.
2. Can we use the database maintenance plans for the Express edition?
No, the Database maintenance plans are not available in this edition. When you connect to the Express edition, you won’t find the maintenance plans folders either.
3. Can we restore a database backup to the Express edition server?
Yes, we can restore a database backup file to the SQL Server instance with express edition. However, as regular backups, you cannot restore the backup to a lower-version instance. For example, if you take a backup on SQL Server 2019 instance, you can’t restore it on SQL Server 2017.
Also, as the Express edition maximum database size is 10 GB, you cannot restore extensive databases (10GB+).
4. Can we upgrade the Express edition to the Standard or Enterprise SQL Server instance?
Yes, SQL Server allows the edition upgrade from the Express to higher editions (and versions). The supported options for SQL Server 2017 are below:
- SQL Server 2019 Developer
- SQL Server 2019 Standard
- SQL Server 2019 Enterprise
- SQL Server 2019 Web
- SQL Server 2019 Express
You can refer to the Supported version & edition upgrades (SQL Server 2019) for more details.
5. Can we configure high availability and disaster recovery solution for the SQL Server Express edition?
No, we cannot. The SQL Server Express edition comes with limited functionality that won’t work for the following functionalities:
- Log shipping
- Always On failover cluster instances
- Always On availability groups
- Hybrid backup to Windows Azure
- Failover servers for disaster recovery
- Cluster-less availability group
6. Should we use SQL Server Express for production databases?
I wouldn’t recommend you this. The Express edition has limitations and does not offer the HADR solution. The maximum single database size of 10 GB is not feasible for production workload either. In many organizations, DBA won’t support the Express edition even if you install it.
Conclusion
This way, we’ve explored the use of the SQL Server Express edition along with its limitations. Despite these restrictions, the Express edition is suitable for learning SQL Server. Also, you can use the SQL Server Developer edition (free license) with full functionality for the development environment.
Tags: sql server, sql server edition, sql server express Last modified: September 17, 2021
