SQL Server 2005 Express limitations and Express Manager
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 4 GB database size. This distinction gives SQL Server Express
well defined cut-off points to differentiate it from other SQL Server
2005 editions. Unlike MSDE, SQL Server Express eliminates the confusion
created by the workload governor.
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.
SQL Server Management Studio Express (Express Manager)
SQL Server Express does not ship with the same management tools
provided in other commercial editions. Rather, an Express
version of the SQL Server Management studio is available as a
separate download from Microsoft.
Limitation in enterprise features
The following SQL Server 2005 enterprise level features are not
available in SQL Server 2005 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 2005, 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 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.
Software requirements
Microsoft Internet Explorer 6.0 SP1 or higher
Microsoft .NET Framework 2.0
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
|
