The following discussion is intended for dedicated server customers who are evaluating Microsoft SQL Server 2005 EXPRESS version for their dedicated server versus Microsoft SQL Server 2005 Workgroup, Standard, or Enterprise versions: 

 SQL Server 2005 Express Edition is a scaled down version of Microsoft SQL Server 2005.  It includes some performance and features limitations that may or may not impact you based on your application and management needs.

Performance limitations in SQL Server Express

The SQL engine of SQL Server Express supports 1 CPU, 1 GB RAM and a 4 GB database size.  Unlike MSDE, SQL Server Express does not include a workload governor, instead, the limitation is as noted (cpu, ram, db size) and features.

• 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.
• 4 GB Database Size: The 4 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.

Features Limitations in SQL Server Express

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

• SQL Server Express does not ship with any management tools. However, you can download SQL Server Management Studio Express (SSMSE) basic management tools from Microsoft.
• Analysis Services (both OLAP and Data Mining)
• Integration Services (DTS successor)
• Notification Services
• Report Builder (although Reporting Services is included)
• SQL Agent
  • With no SQL agent, there is no job scheduling, include maintenance plans with database backups.
• Database Tuning Advisor
• Full-text search
• Log shipping
• IMPORT or EXPORT features (importing from CSV, excel, xml, etc)
• 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 2005 Express
  • Only the shared memory on the local machine is accessible by default for SQL Server Express, although the user can explicitly turn on other supported protocols such as TCP/IP and 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 Server Configuration Manager to enable relevant protocols and start SQL Browser.
  • SQL Browser is a new service in SQL Server 2005 that 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 2005 Express installations can coexist on the same machine along with other installations of SQL Server 2000, SQL Server 2005, 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.