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.