SQL Server 2005 Express and Workgroup: Scaled Back, Yet Feature-Rich

SQL Server 2005 includes two new editions, each of which provides a subset of the features in SQL Server 2005 Standard Edition. SQL Server 2005 Express Edition, which is free, is geared toward developers who want to build a database into their applications. SQL Server 2005 Workgroup Edition is a smaller-scale, lower-priced version of SQL Server 2005 Standard for small businesses and departments. Web Table 1 (http://www.sqlmag.com, InstantDoc ID 47721) compares the two editions' features. Let's take a closer look at what's in them.

What's in SQL Server Express?

SQL Server Express is the next generation of the Microsoft SQL Server 2000 Desktop Engine (MSDE). When coupled with any of the Visual Studio (VS) 2005 Express editions, SQL Server Express offers a robust development environment for Web and Windows applications.

Because it uses the same database engine as SQL Server 2005, SQL Server Express provides many features found in the other SQL Server 2005 editions, including rich database functionality (e.g., stored procedures, triggers); integration with the Common Language Runtime (CLR); native XML support; and integration with VS 2005. Unlike the other SQL Server 2005 editions, however, SQL Server Express provides no graphical management tools, such as SQL Server Management Studio. Microsoft will address this lack by providing a scaled-down version of SQL Server Management Studio. Another notable piece of missing functionality is SQL Server Agent (which is in MSDE), which means that SQL Server Express has no built-in support for task scheduling.

A developer or an ISV can use SQL Server Express to provide back-end databases in standalone and embedded applications. (Microsoft lets developers freely redistribute SQL Server Express.) Updates and patches for SQL Server Express are automatically distributed to users via Windows Update. Deployment is easy because SQL Server Express uses ClickOnce deployment. However, the SQL Server Express download is about 69MB and depends on Windows .NET Framework 2.0, so be patient if you have a slow connection. You can easily deploy databases by copying database files, similar to how you copy Jet databases. Additionally, SQL Server Express can act as a subscriber in both transactional and merge replication. SQL Server Express enables standalone reporting through its support of SQL Server 2005 Reporting Services controls (which are included in VS 2005).

SQL Server Express can function in a high-availability solution that uses SQL Server 2005 Standard or SQL Server 2005 Enterprise Edition. Database mirroring (scheduled to be generally available in the first half of 2006) is a new high-availability feature in SQL Server 2005 Standard and Enterprise in which log records are continually applied from one server (the principal) to another (the mirror). You can use an optional third server (the witness) to enable automatic failover. You can use SQL Server Express as the witness without paying any additional license fees.

In some circumstances you need a paid license to use SQL Server Express. For example, if SQL Server Express is a replication subscriber, it requires a license as a client of the publisher. Additionally, if you use SQL Server Management Studio to manage SQL Server Express, you need a client license.

Although SQL Server 2000 supports Microsoft Access databases as subscribers in merge replication, SQL Server 2005 doesn't support Access subscribers. Customers must upgrade their Access databases to SQL Server Express to enable merge replication.

Scalability and Performance

SQL Server Express supports one CPU, 1GB RAM, and a 4GB maximum database size. MSDE, by contrast, supports two CPUs, 2GB RAM, and a 2GB maximum database size. However, MSDE also contains a governor, which slows performance when more than eight operations are active concurrently. This means that SQL Server Express should provide greater scalability than MSDE for larger numbers of concurrent users.

SQL Server Express also provides Application XCopy, which lets you move, copy, or email a local database along with an application. In SQL Server Express, as in MSDE, AutoClose is enabled by default on all created databases. This means that when the last connection to SQL Server Express is closed, AutoClose disconnects the database engine from the database file and removes the file lock from the database file so that you can move or copy it via XCopy. AutoClose works well for standalone applications. But for applications that will potentially have many concurrent users, such as Web applications, you should turn off AutoClose to avoid the overhead of reestablishing database connections.

What's in SQL Server 2005 Workgroup?

SQL Server 2005 Workgroup is a full-featured database engine without the enterprise-class scalability and high-end business intelligence (BI) features of the costlier Standard and Enterprise editions. SQL Server 2005 Workgroup is priced as an entry-level database, but you can easily upgrade it to Standard or Enterprise if you need the additional features or capacity that those editions support. Customers who purchase SQL Server under Software Assurance (SA) can upgrade to a higher SQL Server edition by paying only the difference in cost between the SKUs. Like the Standard and Enterprise editions, SQL Server 2005 Workgroup is priced per CPU socket, not per processor chip. The new multicore processors, which contain two CPUs inside one package, are licensed as one CPU. Hyperthreaded processors are also licensed as single CPUs, as they are in SQL Server 2000.

If your application is processor or memory intensive, you anticipate many concurrent users, or you need to store more than 4GB of data, you should use SQL Server 2005 Workgroup instead of SQL Server Express. SQL Server 2005 Workgroup includes management features missing in SQL Server Express, such as SQL Server Agent and Database Tuning Advisor (DTA). You can access these features through SQL Server Management Studio, which comes with all SQL Server 2005 editions except SQL Server Express. SQL Server Agent lets you schedule database maintenance and backup jobs by using convenient tools and wizards in SQL Server Management Studio.

SQL Server 2005 Workgroup offers high availability without high cost. The product's backup-log shipping feature maintains a copy of a database on a passive failover database without requiring you to buy another database license. If the main server fails, the failover server uses the transaction log to pick up where the main server left off. SQL Server 2005 Workgroup doesn't support database mirroring or failover clustering, which are costlier high-availability approaches. The product supports replication, both as a subscriber and as a publisher. However, Oracle to SQL Server 2005 replication, a new feature, is available only in SQL Server 2005 Enterprise.

SQL Server Integration Services (SSIS) is the greatly enhanced replacement for DTS. Although SQL Server 2005 Workgroup lacks the tools to create SSIS packages, it has the SSIS runtime, so that it can run SSIS packages created on a SQL Server 2005 Standard or Enterprise system.

When Microsoft introduced the SQL Server 2000 Workgroup SKU in 2004, its goal was to provide a low-cost relational database without the integration, reporting, and analytical capabilities found in the SQL Server 2000 Standard and Enterprise editions. Microsoft has applied this philosophy to SQL Server 2005 Workgroup but has also included some BI capabilities. For example, SQL Server 2005 Reporting Services, which lets you develop, manage, and deliver reports, is included in SQL Server 2005 Workgroup as is Report Builder, the new end-user ad hoc query tool. One BI feature not in Workgroup is Analysis Services, the SQL Server component that provides multidimensional Online Analytical Processing (OLAP) and data-mining capabilities.

More Choices

SQL Server Express will benefit MSDE users who have been constrained by the lack of a robust, free management tool. Offering more features and better performance than MSDE, SQL Server Express is an appealing upgrade option. And SQL Server 2005 Workgroup provides a lower-cost SQL Server entry point for companies needing a subset of capabilities of the Standard and Enterprise editions.