Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 9, 2008

SQL Server 2005 Express Edition - Part 24 - Planning Upgrade of SQL Server 2005 Express Edition

By Marcin Policht

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.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM