SQL Server 2005 Express Edition – Part 1

In order to address the diverse needs of its customers as well as to provide
balance between offered feature sets, hardware/software footprints, and
pricing, Microsoft has been consistently diversifying its database management
portfolio, delivering several options with each new release of its SQL Server
product line, with their own, distinct characteristics targeting specific
market niches. In the case of SQL Server 2005, the list of available choices
consists of the following:

  • Workgroup Edition – 32-bit data service database engine intended
    for less demanding environments, such as small businesses (as its inclusion in
    the Small Business Server 2003 R2 Premium Edition implies) or individual
    departments with non-critical, lower data volume requirements. It offers
    limited scalability bound by hardware restrictions that prevent the database
    engine from using more than 2 processors and 3 GB of memory, as well as by a
    lack of support for advanced high availability features, including database
    mirroring and failover clustering, or the ability to perform more involved
    maintenance operations (such as indexing or database restore) in the online
    mode. However, its resiliency can be increased by employing the functionality
    of built-in log shipping, replication (with up to 25 and 5 subscribers with
    merge and transactional replication methods, respectively), or Service Broker
    (handling guaranteed delivery-based asynchronous messaging).

  • Standard Edition – data service database engine available in both
    32-bit and 64-bit versions (for both x64 and Itanium systems) fits well into
    scenarios involving larger businesses and more critical departmental data
    stores. It is capable of taking advantage of up to 4 CPUs and all of the memory
    that is available to the host operating system (although rather insignificant
    at this point, a limit of 32 TB applies on 64-bit Windows platform with 64-bit
    version of the software), with up to 16 instances co-hosted on the same server.
    Its reliability and high-availability enhancements (compared with Workgroup
    Edition) include 2-node failover clustering and database mirroring (with
    transaction safety set to FULL, which allows High Availability and High
    Protection options but precludes High Perfomance mode – for more information on
    this subject, refer to a
    Microsoft TechNet article
    ). Business Intelligence functionality is provided
    by Integration, Reporting, and Analysis Services (accommodating data mining and
    warehousing needs).

  • Enterprise Edition – data service database engine intended for
    high-end, enterprise level, mission critical systems with full
    high-availability and scalability requirements (including high volume OLTP,
    data warehousing, or advanced analytics implementations). It can take advantage
    of the entire memory that is available to the operating system (with its both
    32-bit and 64-bit versions, without the 32 TB limit applicable to Standard
    Edition) as well as all of its processors, with up to 50 distinct instances
    running on the same server. However, the features that truly distance it from
    other editions consist of unique enhancements in areas of high availability,
    scalability, manageability, and business intelligence. Among the most relevant
    items in the first two categories are support for table and index partitioning,
    updateable distributed partitioned views, parallel indexing, online maintenance
    operations (page/file restore or indexing), database snapshots, and fast
    recovery. Improved clustering characteristics make it possible to deploy up to 8
    nodes per single failover cluster running Windows 2003 Server Enterprise
    Edition. In addition to the database mirroring options available in Standard
    Edition, it is also possible to have Transaction Safety turned off (which gives
    you the ability to choose among High Availability, High Protection, or High
    Performance modes, depending on your preferences). Manageability highlights
    include mirrored backup media sets and support for Hot Add Memory feature
    (which adjusts memory usage to take into account physical memory that was added
    while the server was running) in 64-bit version of the product (or 32-bit
    version with Address Windowing Extensions enabled and -h startup switch
    applied) running on Windows 2003 Server Enterprise or Datacenter Editions.
    Following release SQL Server 2005 SP2, Microsoft also announced additional
    licensing benefits in the area of virtualization, permitting unlimited virtual
    instances on physical servers that are running Enterprise Edition, regardless
    of virtualization platform (for more details refer to the Virtualization
    and Multi-Instancing
    article on in SQL Server section of Microsoft Web
    site). There are also numerous Business Intelligence features present
    exclusively in Enterprise Edition in the areas of Data Mining, Business
    Analytics (partitioned cubes, parallel partition processing, or proactive
    caching), and Integration Services (additional transforms such as Fuzzy Lookup
    and Grouping, Term Extraction and Lookup, or Data Mining Query).

  • Developer Edition – functionally equivalent to Enterprise
    Edition. Note, however, that it is subject to different licensing rules, which
    permit its use exclusively in development and test environments, or for
    demo/presentation purposes.

  • Enterprise Evaluation Edition – similarl to Developer Edition, it
    comprises all features available in the Enterprise Edition but is intended as a
    trial software only, with expiration date 180 days after its installation. It
    is available as a free download from the
    Download Center
    of the Microsoft Web site (following mandatory
    registration) or via evaluation DVD (for a nominal fee covering shipping and
    handling). Note that the trial software is not covered by Microsoft’s Customer
    Services and Support, so you might have to resort to the MSDN forums if you run
    into problems with its usage.

  • Compact Edition – unlike all previously listed editions, this one
    was not designed as a data service database engine (operating as a Windows
    service for multi-user access) but rather as an embedded one, which functions
    as part of an application (providing it with dedicated data store and running
    within its process space). It was released in November 2006, as the next
    evolutionary step following SQL Server 2005 Mobile Edition (which, in turn,
    originated from the SQL Server CE line). Its predecessors were intended
    strictly as embedded database engines on mobile devices (such as Smart Phones,
    Pocket PCs, or Tablet PCs running Windows Mobile or Windows CE operating system
    platforms), and could not be used independently on standard desktop computers
    without having either Visual Studio 2005 or a full version of SQL Server 2005
    already present. With this restriction eliminated in the current version (3.1),
    Compact Edition became the primary choice of developers looking for embedded, ligthweight,
    no-cost (its runtime component has a footprint of about 2MB and is freely
    downloadable from the
    Download Center
    of the Microsoft Web site), single-user database engine
    that can be easily distributed with custom applications to both mobile devices
    and desktops (and activated without requiring local Administrator privileges –
    assuming that relevant libraries are copied to application managed, user
    specific directories rather than registered in the Global Assembly Cache), is
    compatible with other editions of SQL Server 2005 product line (in terms of
    programming model as well as a replication partner), and integrates with Visual
    Studio 2005 (resulting in a greatly simplified development process, by
    leveraging its ClickOnce and DataDirectory substitution string functionality –
    for more information about these two features, refer to the Microsoft Knowledge
    Base article 920700).

  • Express Edition – plays a unique role in the SQL Server 2005
    products portfolio, due to its dual personality as either data service database
    engine (capable of operating as a service in a multi-user environment) or a
    host for embedded data store (satisfying local storage needs of a single-user
    application and running in the security context of that user’s account). It was
    intended as a replacement for Microsoft SQL Server 2000 Desktop Engine (MSDE)
    and, just like its predecessor, is suitable for less demanding environments (in
    terms of concurrency ratio and amount of data being processed), as well as for
    evaluation (as an alternative to SQL Server 2005 Enterprise Evaluation Edition
    we described earlier, without its enforced expiration date), training, or
    demonstration purposes. It also satisfies the needs of developers looking for an
    easily deployable, low cost data store that can be bundled with their applications.
    While it is no longer subject to the throttling mechanism present in MSDE
    (workload governor) that limited the number of simultaneous connections, its
    capabilities are affected by enforced single CPU usage (regardless of the
    number of processors accessible to the host operating system) and 1 GB
    restriction on the maximum size of the memory buffer pool (note that this rule
    does not apply to non-buffer pool structures, such as, connections or locks).
    As with Compact Edition, its maximum database size cannot exceed 4 GB.

    Note that while SQL Server 2005 Compact Edition has
    been designated as the platform for embedded applications running on mobile
    devices, picking an appropriate product for local data store on desktops might
    still be a bit problematic. When contemplating whether Express Edition should
    be your choice, take into consideration a variety of criteria. One of the most
    apparent is significant difference in sizes – with Compact Edition occupying
    less than 2 MB (both in downloadable, compressed format and on disk following
    its installation) versus over 36 MB download (in its most basic version) and
    185 MB expanded in case of Express Edition. This, however, is inversely
    proportional to functionality available in each, which makes the latter the
    right option if you plan to implement more advanced features, like those
    dependent on procedural T-SQL or Native XML (some of them, such as stored
    procedures, views, or distributed transactions are relevant in multi-user
    environment, and hence not applicable to embedded data stores). Express Edition
    also implements the full SQL Server 2005 role-based security model that is not
    recognized by its Compact counterpart (which, on the other hand, has the unique
    ability to password protect and encrypt its SDF database files). While both
    offer similar deployment options (allowing the database engine to be
    incorporated into application setup, making its installation transparent to
    users), Compact Edition allows completing entire process without administrative
    privileges (this is possible with the runtime operating in-process with the
    application, rather than running as a Windows service). However, since in this
    case the database engine is not registered as a separate, installed program,
    presence of its runtime components is not automatically detected by Windows
    Update, which introduces the need for extra maintenance procedures that provide
    patching functionality.

Following this lengthy overview of choices offered in the SQL Server 2005 product
line, we will take a closer look at its Express Edition. In our next article,
we will review its characteristics in more detail, as well as describe its
installation process and configuration options.

»


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