<< Click to Display Table of Contents >>

SQL Server 2008 R2 Express Limitations

This document explains the limitations of SQl Server 2008 Express Edition.


SQL Server 2008 Express: Express Manager, Management Studio, Downloads, & Limitations


SQL Server 2005 Express Edition is a scaled down version of Microsoft SQL Server 2005. Although the database server is limited in enterprise features, most of the limitations do not affect the database performance for what it is meant to do.


Performance Limitations in SQL Server Express

The SQL engine of SQL Server Express supports 1 CPU, 1 GB RAM and a 10 GB database size. This distinction gives SQL Server Express well defined cut-off points to differentiate it from other SQL Server 2008 editions.


1 CPU: SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. This limitation prevents the use of parallel query execution in SQL Server Express.


1 GB RAM: The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, etc. is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1 GB in total, but it will never use more than 1GB for the buffer pool. This limitation prevents the use of Address Windowing Extensions (AWE) with SQL Server Express.


10 GB Database Size: The 10 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server. There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and DTC is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Applications planning to use SQL Server Express are recommended to keep these changes in mind when designing their applications.


SQL Server Management Studio Express (SSMSE)

SQL Server Express version supplied with Custom Vantage Office includes management tools.


Limitation in Enterprise Features

The following SQL Server 2008 enterprise level features are not available in SQL Server 2008 Express Edition:


Bullet PointAnalysis Services (both OLAP and Data Mining)

Bullet PointIntegration Services (DTS successor)

Bullet PointNotification Services

Bullet PointReport Builder (although Reporting Services is included)

Bullet PointSQL Agent

Bullet PointDatabase Tuning Advisor

Bullet PointFull-text search

Bullet PointLog shipping


Enterprise Availability Limitations

Unlike other editions of SQL Server 2008, the Express edition does not support Fail-over Clustering or Database Mirroring.


Database Mirroring: Database Mirroring extends log shipping capabilities and enhances availability of SQL Server systems by providing automatic fail-over to a standby server.


Fail-over Clustering: Fail-over clustering is the ultimate fail-over mechanism SQL Server can provide. A SQL Server node in a cluster of nodes sharing one disk array can fail-over to another node without affecting the availability of the server cluster. The disk array where databases reside is central to the SQL Server cluster.


Networking Support in SQL Server 2008 Express

Only the shared memory on the local machine and TCP/IP is accessible by default for SQL Server Express, although the user can explicitly turn on other supported protocols such as Named Pipes. VIA and HTTP protocols are not supported in SQL Server Express. With only shared memory available by default, connections from a remote machine to SQL Server Express will fail unless the networking is turned on. To turn networking on, Use SQL Computer Manager to enable relevant protocols and start SQL Browser.


SQL Browser is used to identify the ports that named instances listen on. Since shared memory does not use it, this service is turned off in SQL Server Express by default. This means that the user will have to start this service so that network access can work.


Note One interesting fact is that SQL Browser listens on UDP port 1434. However, pre-SP3 versions of SQL Server 2000 holding port UDP 1434 may result in failure of SQL Browser name resolution, since they may refuse to give up the port. The workaround is to upgrade all SQL Server 2000/MSDE instances on the machine to SP3 or higher.


SQL Server Express Instances

Multiple SQL Server 2008 Express installations can coexist on the same machine along with other installations of SQL Server 2000, SQL Server 2005, SQL Server 2008, or Microsoft Desktop Engine (MSDE). In general, it is best that SQL Server 2000 instances be upgraded to SP3a or higher. The maximum limit to the number of SQL instances is 50 on the same machine. These instances must be uniquely named for the purpose of identifying them.


SQL Server Express by default installs as a named instance called SQLEXPRESS. This particular instance is to be shared among multiple applications and application vendors. We recommend that you use this instance unless your application has special configuration needs. Some configuration needs, such as the requirement of the Secure Socket Layer (SSL) authentication, affect the installation as a whole and hence need a separate named instance. In all other cases, the shared instance should suffice. Another advantage of using the shared instance is that the application vendors need not worry about installing SQL Server Express along with the application, which simplifies application installation.


Software Requirements


Microsoft Internet Explorer 8.0 SP1 or higher

Microsoft .NET Framework 3.5

Operating Systems

Microsoft Windows 2000 SP4 Professional

Microsoft Windows 2000 SP4 Server

Microsoft Windows 2000 SP4 Advanced

Microsoft Windows 2000 SP4 Data Center

Microsoft Windows XP SP1 Professional or higher

Microsoft Windows 2003 Server or higher

Microsoft Windows 2003 Enterprise or higher

Microsoft Windows 2003 Data Center or higher

Microsoft Windows Small Business Server 2003 Standard or higher

Microsoft Windows Small Business Server 2003 Premium or higher

Microsoft Windows 2008 Server or higher


Sources include the Microsoft website