SQL Server 2005 Express Edition – Part 2

In the
first installment
of our series, we provided an overview of SQL Server 2005
product line, pointing out intended usage and basic characteristics of each of
its editions, but concentrating on differences and similarities between Compact
and Express flavors, whose capabilities to some extent overlap (in particular,
in the area of desktop based installations). As we explained, the purpose of
SQL Server 2005 Compact Edition was to function as an application embedded
database engine (with its runtime executing in the application process space).
Its design was highly optimized for single-user scenarios (which resulted in
removal of features geared towards streamlining shared data access) with
emphasis on integration with Visual Studio 2005 (leading to eliminating some of
T-SQL constructs which can be replaced with .NET code), making it the
appropriate platform for mobile devices. Express Edition, on the other hand,
remains the primary choice for deployments of a full-fledged database engine in
less demanding environments (for both client/server and local data store
architectural models). It also constitutes a viable alternative to SQL Server
2005 Enterprise Evaluation Edition in testing, training, or presentation
scenarios (as long as you do not require more advanced enterprise level
functionality). Finally, it still competes with SQL Server 2005 Compact Edition
as a single-user desktop data store (appearing in a number of Microsoft
offerings in this category, such as, for example, Windows Vista Hardware
Assessment utility). We will explore here its features in more detail in order
to better understand its unique role in SQL Server 2005 portfolio.

SQL Server 2005 Express Edition constitutes a replacement for Microsoft
Desktop Engine (MSDE) 2000 and, as such, exhibits a number of similarities to
its predecessor (although the new version is not fully backwards compatible,
which presents some unique challenges during upgrades). Both deliver
performance and cost efficient data management solutions to individual users,
small businesses, and developers, who want to take advantage of benefits
present in other SQL Server editions but without the associated software and
hardware expenses. These expectations are satisfied thanks to lightweight
implementation and low cost. MSDE 2000 and SQL Server 2005 Express Edition are
freely downloadable and distributable (although distribution rights are granted
based on mandatory registration) and introduce additional charges only in less
common arrangements (for example, Client Access Licensing needs to be purchased
for each system that participates as a subscriber in a replication topology
where Per Seat licensing is used). Based on the same database engine as their
full-fledged counterparts, for the most part, they exhibit the familiar
behavior and can be managed in a similar manner (supporting an identical set of
programming APIs and T-SQL syntax), although there are exceptions and caveats,
which we will point out throughout our series. A high degree of compatibility
facilitates mostly straightforward and effortless upgrades to full SQL Server
editions within each of their respective versions.

On the other hand, while both of them allow for up to 16 separate instances
per server (with each represented by a separate SQLSERVER.EXE process,
dedicated buffer memory, its own set of binaries and registry entries, along
with a set of system and user databases), their scalability and performance
characteristics differ. In particular, despite increased maximum database size
(which was doubled, comparing with MSDE, from 2 GB to 4 GB) and removal of
throttling mechanism (imposed in MSDE by workload governor restricting the number
of concurrent operations that could be performed within a single instance), SQL
Server 2005 Express Edition is subject to lower than its predecessor limits (on
per instance basis) on supported buffer memory (1 GB vs 2 GB) and number of
processors (1 vs 2).

However, the most apparent distinction between them is availability of
graphical interface-based management tools. Following the release of MSDE 2000,
you were forced to resort to command line utilities (such as OSQL) or
programming methods (e.g. via SQL Distributed Management Objects) in order to
manage its configuration or activities. (Use of SQL Server 2000 Enterprise
Manager was possible as well, but required an existing, licensed installation
of the full-blown version of the product and a separate client access license
for each system running MSDE). In order to address this issue, Microsoft
developed freely downloadable, but officially unsupported SQL
Server Web Data Administrator
. Alternatively, you could also take advantage
of a number of third-party utilities (you can find some of the more popular
ones on the MSDE
Partners page
of the Microsoft Web site). With SQL Server 2005 Express
Edition, Microsoft decided to resolve these problems completely and not only
designed SQL Server 2005 Management Studio Express (a feature-limited version
of SQL Server 2005 Management Studio) but also offered for unrestricted usage
Business Intelligence Development Studio. The first one is available as a
separate (43.1 MB) download from SQL
Server 2005 Express Edition section
of the MSDN web site. It provides an
integrated graphical interface (closely resembling the full version of SQL
Server 2005 Management Studio), including Object Explorer and Query Editor,
allowing you to administer components and options incorporated within the
product, but missing others, present in the full version only. (For example,
Replication node displayed in the Object Explorer window contains the Local
Subscriptions subnode, but not the one representing Local Publications). Other
graphical utilities included with the installation of SQL Server 2005 Express
Edition (part of its
download
) are SQL Server Configuration Manager (implemented as Microsoft
Management Console with SQL Server 2005 Services, SQL Server 2005 Network
Configuration, and SQL Native Client Configuration snap-ins), SQL Server Error
and Usage Reporting, and SQL Server Surface Area Configuration (which we will
describe in more details in future articles of this series). Others that are
worth pointing out are command line-based SQLCMD and BCP (available also in
other editions of SQL Server 2005 and not well suited for working with features
specific to the Express Edition) as well as SSEUtil (command line utility,
available as a separate
download
and designed to accommodate unique requirements of SQL Server 2005
Express Edition).

To take advantage of features such as SQL Server Agent (which was present in
MSDE 2000 but did not make it to the current release), Analysis Services, or
Integration Services, you need a full version of SQL Server 2005 (and its
Management Studio). A fairly complete subset of Reporting Services features as
well as Full Text Search are included in SQL Server 2005 Express Edition with
Advanced Services (also available for download from referenced earlier SQL Server 2005
Express Edition section
of the MSDN web site).

The replication capabilities that we mentioned before constitute another
difference between MSDE and its successor, with the latter capable of operating
as subscriber in merge, snapshot, or transactional replication topology (MSDE
could subscribe to merge and transactional replication only). On the other
hand, MSDE can function as merge replication publisher, while SQL Server 2005
Express Edition does not handle publishing at all. Support for replication (in
the role of a subscriber) means that SQL Server 2005 Express Edition can be
deployed in distributed environments with a central database residing on an SQL
Server 2005 Standard or Enterprise instance. Individual, remote users can
operate independently (even while disconnected from the central office) while
periodic synchronization ensures that data remains consistent across all
systems (keep in mind that such arrangements require an additional SQL Server
2005 Client Access License on the publisher for each of its subscribers, just
as it did in scenarios involving MSDE).

Another factor that needs to be taken into consideration when comparing MSDE
with SQL Server 2005 Express Edition is operating system support. While the
former can be installed on older Windows platforms (such as Windows 9x), it’s
no longer supported with newer releases (starting with Vista). The latter is
available on Windows 2000 SP4 (both Server and Workstation), Windows XP SP2
(Home and Professional), Windows Server 2003 SP1 (including Small Business
Server 2003 SP1) or later, as well as Vista (its functionality is dependent on
the presence of .NET Framework 2.0, which, obviously, was not required for MSDE
installation).

What distinguishes SQL Server 2005 Express from other editions, is a small
footprint, set at 36.4 MB in the basic form (without Advanced Services) of its
32-bit implementation (and 55.3MB for its 64-bit equivalent), which by far
exceeds the size of SQL Server 2005 Compact Edition, but remains considerably
lighter than the full version. As you might expect, reduction in size required
eliminating functionality outside of the scope of intended usage scenarios,
including primarily enterprise level features, such as clustering, log
shipping, or database mirroring. In addition (as mentioned earlier), you will
not be able to incorporate into your installations Analysis Services,
Notification Services, Integration Services (although it is possible to execute
DTS packages, by using downloadable
DTS Runtime, as well as to point to Express Edition databases as sources and
targets for data extraction and loading), or SQL Profiler (you can, however,
use Profiler included in another, full-fledged database engine instance on the
same computer to capture activity of the SQL Server 2005 Express Edition). Note
also that SQL Server 2005 Express Edition can take advantage (as a client) of
SQL Service Broker, which provides guaranteed delivery, asynchronous messaging.

Among advantages of SQL Server 2005 Express Edition are its improvements in
the area of database deployment, including support for XCopy methodology (which
allows you to copy applications with their databases using any standard file
transfer method, without the need for detaching them from a source and
attaching at a destination) as well as integration with Visual Studio-based
ClickOnce mechanism (making possible to launch download and setup of an
application along with database engine and associated database with a single
click). Both features are further enhanced by the innovative concept of user
instances, which allow non-administrators to manage their own, independent
copies of the database engine within the security context of their own accounts
(we will cover them in more detail in a future article).

Application and database development is simplified through integration with
programming standards, leveraging Microsoft Visual Studio, with its intuitive
graphical interface and rich debugging capabilities. Full .NET support allows
developing stored procedures in managed code and provides access to
programmability features available in other editions.

In the next installment of this series, we will take a closer look at the
process of installing and configuring SQL Server 2005 Express Edition.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles