In the
first installment of our series, we briefly described the main
characteristics of the individual products in the SQL Server 2005 product line.
As we have demonstrated since that introduction, unique features incorporated
into the Express Edition (such as User Instances or ClickOnce Deployment) make
it a preferred choice when deploying single-user databases embedded into
third-party applications. However, there might be situations where its benefits
(including no licensing costs) no longer constitute an acceptable tradeoff to
its scalability or functionality limitations, forcing you to consider an
upgrade to a Workgroup, Standard, or Enterprise member of SQL Server 2005
family. We will review here the most common reasons that influence such a decision
(and continue our discussion focusing on implementation steps in the next
article).
Among the primary drivers behind an upgrade are the increased processing and
storage needs of your databases, associated typically with an expansion of your
user base or growing application requirements (beyond the ones projected during
the original implementation). While SQL Server 2005 Express Edition is not subject
to the throttling mechanism imposed by the Concurrency Workload Governor
present in its predecessor – Microsoft SQL Server Desktop Engine (MSDE) – its
processing capabilities are inferior in comparison with its full-featured
counterparts. For example, its restriction on a database size (set to 4 GB) is
not applicable to any other edition. Similarly, its ability to utilize physical
resources (on per-instance basis) is capped at 1 GB of buffer memory and a
single processor (incidentally, these numbers are lower than those assigned to
MSDE, which was capable of utilizing up to 2 GB of memory and 2 processors).
These boundaries are significantly expanded following an upgrade. In
particular, an installation of SQL Server 2005 Workgroup Edition can work with
up to 2 CPUs and 3 GB of RAM. With Standard and Enterprise Editions, the
processor limit is raised to 4 and 64, respectively (and all memory recognized
by the host operating system becomes available to the database engine). In
addition, in case of the latter, scalability and performance improvements also include
such features as table and index partitioning, indexed views, or parallel
Database Consistency Checks and index operations. Furthermore, both Standard
and Enterprise Editions run natively on Intel x64 hardware, significantly
increasing the amount of addressable memory (while SQL Server 2005 Expess
Edition can also be installed on the 64-bit platform, it relies on WoW
mechanism to interact with Windows).
Mediocre replication support might be another reason for an upgrade.
Unfortunately, SQL Server 2005 Express Edition capabilities have been
significantly restricted in this area, precluding it from functioning in a role
other than a subscriber. In addition, in the absence of the SQL Server Agent,
you are forced to resort to cumbersome or time consuming workarounds to
implement automatic synchronization (for more details, refer to an
earlier articles of this series). Effectively, if you want to configure
your SQL Server instance as a publisher or distributor, or your environment
relies on SQL Server Agent jobs for automation, you will need to install one of
the other editions. Your choice, in this case, will depend on the size and
complexity of the replication infrastructure. For example, with SQL Server 2005
Workgroup Edition, you might need to keep in mind the maximum number of allowed
subscribers when its instance is configured as a publisher (while there are no
restrictions in cases of snapshot replication, with transactional and merge
types, the limit is set to 5 and 25, respectively). When dealing with Standard
and Enterprise Editions, the constraints are dictated by the physical
characteristics of the underlying hardware. The latter also offers cross-platform
support, with Oracle-based publishers in transactional replication scenarios.
Availability and redundancy are other areas, in which Express Edition
characteristics are in a stark contrast with enhancements built into other
members of SQL Server 2005 family. Upgrading to SQL Server 2005 Worgroup
Edition will give you the ability to leverage log shipping, which offers
synchronization of a primary database and its replica (effectively,
facilitating a switch between them in case the former fails). However, since
this mechanism requires a manual intervention and does not eliminate the possibility
of data loss (this type of setup is vulnerable during failover), you might want
to take advantage of more robust solutions incorporated into SQL Server 2005
Standard and Enterprise Editions (which include database
mirroring and failover
clustering, with up to 2 and 8 nodes, respectively). The latter also boasts
a wide variety of other high availability features, such as:
-
online indexing (allowing execution of index data definition
language statements in parallel with modifications and queries against
underlying tables or views) -
restores (increasing availability by keeping the target database
online during the restore) -
fast recovery (permitting connections to a database during
restore or recovery) -
database snapshots (providing a read-only, "virtual"
copy of a database, representing its state at a particular time) -
snapshot isolation (for more information about this topic, refer
to our
earlier article).
While basic Reporting Services are available in SQL Server 2005 Express
Edition with Advanced Services (allowing it to generate reports against local,
relational databases), if you intend to take advantage of more advanced
functionality, you will have to resort to an upgrade. By choosing the Standard
Edition, you will gain the ability to report on non-relational, remote data
sources in a wider range of formats (compared with the set of options present
in Express or Workgroup Editions), as well as to designate subscribers, which
will receive reports according to an arbitrary schedule (typically delivered
via e-mail or copied to a local or remote file system). Additional usability
advantages can be realized through integration with SharePoint, which
incorporates reports into its Web sites (a feature missing from the SQL Server
2005 Express Edition with Advanced Services or Workgroup Edition). If you
decide to switch to Enterprise Edition, you will also benefit from performance
(through scale-out deployments) and functionality (data-driven subscriptions
and infinite clickthrough in ad-hoc reports) improvements.
As far as other SQL Server Business Intelligence-related components are
concerned, Analysis Services (comprising mainly of data mining, data
warehousing, and OLAP functionality) and Notification Services (handling
asynchronous, guaranteed delivery of notifications about data changes to a
designated set of subscribers) require at least the Standard Edition. While
their high availability and scalability mechanisms in such implementation are
fairly rudimentary, you can improve the situation by installing the Enterprise
Edition, which supports a large number of enhancements (such as parallelism,
advanced data mining tuning algorithms, proactive caching, or distributed partitioned
cubes, to name just a few). Similarly, SQL Server 2005 Express Edition does not
include Integration Services (although its built-in runtime component allows
package execution) and Workgroup Edition is merely capable of running the
Import and Export Wizard, but does not permit customizing data extraction and
loading activities. The ability to create new or modify existing packages (as
well as perform other SSIS related tasks) requires the presence of at least SQL
Server 2005 Standard Edition. Upgrading to Enterprise Edition will give you
access to a number of more advanced transforms (such as, for example, those
related to fuzzy logic or data mining). For more information, refer our series
of articles on this subject, published on the DatabaseJournal Web site.
Consistent management interface (including SQL Server 2005 Management
Studio, its Express equivalent, and Business Intelligence Development Studio
available in all editions) simplifies transition from database administration
and application development perspectives. At the same rate, though, you should
note that SQL Server 2005 Management Studio Express does not contain all of the
features included with other editions (such as the ability to manage SQL Server
Agent, linked servers, replication publishers and distributors, maintenance
plans, or other SSIS-based jobs), hence can not be used for managing their
operations. In addition, both SQL Server 2005 Standard and Enterprise Editions
include additional management utilities, such as SQL Server Profiler (allowing
you to monitor SQL Server 2005 behavior and statistics in real-time, which is
helpful with troubleshooting and performance tuning) and Database Tuning
Advisor (which offers recommendations regarding optimum index placement or
table partition changes based on data recorded via SQL Server Profiler).
Another of SQL Server 2005 Express Edition’s potential weaknesses is its
messaging functionality, which does not include Database Mail (for this, you
need at least Workgroup Edition). Its Service Broker implementation (which
offers asynchronous, guaranteed message delivery facilitating communication
between distributed SQL Server installations) requires at least one
full-fledged SQL Server instance (Workgroup, Standard, or Enterprise). Last,
but not least, with its inability to implement HTTP Endpoints, SQL Server 2005
Express Edition does not support Web Services, so if you intend to provide local
database access to SOAP-based clients, you will need to consider an upgrade. In
our next article of this series, we will review steps involved in a sample
implementation of such process.
