SQL Server 2005 Express Edition - Part 1

Monday Jun 25th 2007 by Marcin Policht

Marcin Policht offers an overview of choices offered in the SQL Server 2005 product line, as an introduction to a new series on the Express edition.

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

Mobile Site | Full Site