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.
