SQL Server 2005 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 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 (SSMSE)
SQL Server Express does not ship with any management tools. However, you
can download basic management tools from Microsoft. For
streamlined management with fast and efficient user interface,
download a copy of Teratrax Database Manager. Teratrax Database Manager provides a number of enhanced
productivity features.
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 (See Teratrax Job Scheduler)
- 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
Sources include the Microsoft website
|
