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:
Analysis Services (both OLAP and Data Mining)
Integration Services (DTS successor)
Notification Services
Report Builder (although Reporting Services is included)
SQL Agent
Database Tuning Advisor
Full-text search
Log 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