• Illustration: Graphical Management Tool for Express Edition
• Illustration: Database Mirroring Reduces Unplanned Downtime
• Sidebar: Clustering or Mirroring?

SQL Server 2005 SP1 delivers two new features that did not make the product's Nov. 2005 release. Database mirroring, included in higher-end editions of SQL Server 2005, gives administrators a simpler failover solution than the Microsoft Cluster Service (MSCS) used to support redundancy and failover in previous versions. However, SQL Server 2005 continues to support clustering, so organizations will need to decide which method best meets their requirements. In addition, the SP delivers some bug fixes, and SQL Server's free, entry-level Express Edition gets a new management utility and a pair of features previously available only in higher-end editions.

Service Pack Fixes Broad Problems

Arriving about six months after the Nov. 2005 release of SQL Server 2005, SP1 contains a relatively small number of fixes—the Knowledge Base article outlining SP1 lists 47 documented fixes. (In contrast, SQL Server 2000 SP1 was released seven months after SQL Server 2000 shipped and contained nearly 300 bug fixes.) Documented fixes correspond to bugs for which hotfixes were released. Other bugs of a less severe nature are also fixed in SP1; however, Microsoft has not provided any public documentation for these fixes.

Not surprisingly, the majority of fixes in SP1 resolve bugs in the product's most heavily used feature areas—more than half the bugs occurred in SQL Server's database engine or Analysis Services. Although most of the fixed bugs are of moderate severity and would likely not have resulted in production problems (most would have been caught by users during application development or testing), some problems could have caused run-time errors or performance degradation. For example, several fixes resolve problems that could cause queries or stored procedures to run slowly, fail unexpectedly, or fail to run. A bug in Integration Services causes a memory leak when one of the built-in data transformations is used. In addition to the documented bug fixes, SP1 includes a number of performance tweaks.

Very few fixes documented in SP1 relate to bugs in SQL Server 2005's new features, such as integration of the .NET Common Language Runtime (CLR) and user-defined data types (UDTs), probably because these features have not yet been widely deployed by customers. Thus, customers could find that subsequent hotfixes and service packs contain fixes for bugs found in those features as they work their way into heavy testing or production use.

Mirroring Supports Automatic Failover

Database mirroring is a database redundancy and failover solution that is similar to a previous SQL Server feature called log shipping. Both mirroring and log shipping provide for database redundancy by maintaining a copy of a production database on a standby machine. To maintain synchronization between production and standby databases, mirroring (and log shipping) periodically copy the production database's transaction logs (which record database changes, such as data inserts, updates, and deletes) to the standby machine and apply the changes recorded in those logs to the standby database.

However, database mirroring improves on log shipping in one important way: administrators can configure it to automatically failover from a production database (referred to as the principal database) to the copy of that database on the standby server (the mirror database). Log shipping, on the other hand, requires operator intervention to bring a standby database online if the production database fails. Thus, if configured for automatic failover, mirroring promises administrators less unplanned downtime than does log shipping.

(For more information about database mirroring, see the illustration "Database Mirroring Reduces Unplanned Downtime".)

An Alternative to Clustering

Prior to SQL Server 2005, building automatic failover solutions for SQL Server databases involved using the Microsoft Cluster Services (MSCS), a Windows service that uses clusters of computers to provide application redundancy—when one machine in the cluster fails, its applications restart on another.

Although SQL Server 2005 continues to support MSCS, most customers will find database mirroring a simpler, less costly failover solution. An MSCS-based failover strategy requires specialized, expensive clustering hardware, including shared storage: the actual database resides on a common storage system (such as on a storage area network) that is shared by the nodes in the cluster. In addition, machines in the cluster must use specially designed and certified network and server hardware. Database mirroring has no such requirements. Customers can use standard server hardware and the principal and mirror servers do not share storage, which provides an additional level of redundancy. (For more information on clustering versus mirroring, see the sidebar "Clustering or Mirroring?".)

Advances for Free Edition

SQL Server 2005 introduced Express Edition, a replacement for the Microsoft SQL Desktop Engine (MSDE), a free, redistributable database engine based on SQL Server 2000 and commonly used by developers for simple Web applications and applications that require embedded databases. Like its predecessor, Express Edition uses the core SQL Server database engine, but it lacks most of SQL Server's advanced features, such as database mirroring. Nevertheless, Express Edition improves on its predecessor in some key ways. For example, Microsoft has removed throttling, which intentionally degraded MSDE performance if the database handled more than eight operations (such as processing a set of SQL commands) simultaneously, and has added support for the .NET Framework.

A new graphical management utility (which is also available as a stand-alone download) and a pair of new features round out Express Edition's feature set, and could compel customers to choose SQL Server Express over other free databases such as MySQL, PostgreSQL, and Oracle 10g Express.

New management utility. Previously, administration of Express Edition databases was limited to a set of command line utilities and Express Edition continues to support these utilities, but with the release of SP1, Express Edition gets a new, graphical management utility called Management Studio Express. The utility is a stripped-down version of the SQL Server Management Studio, a consolidated management suite for SQL Server 2005. The Management Studio subsumes the functions of SQL Server's previous stand-alone tools, such as Enterprise Manager (the main administrative tool in SQL Server 2000) and Query Analyzer, a graphical tool for building, testing, and running scripts authored in Transact-SQL (T-SQL), SQL Server's native scripting language. (Administrators can also use the full Management Studio to manage Express Edition databases if they are working in environments that include other SQL Server 2005 editions.)

(For a screen shot of the new management tool, see the illustration "Graphical Management Tool for Express Edition".)

Inclusion of a full-featured, graphical management utility will make Express Edition more useful for beginning developers, novice database administrators, testing purposes, and small, simple, production databases.

Reporting Services. A limited-feature version of Reporting Services, SQL Server's reporting engine and related development and management tools, will allow developers to use SQL Server Express to build lightweight, Web-based managed reporting applications or create custom applications with embedded reporting features. The Express Edition supports only a subset of Reporting Services' features—for example, the report server in Express Edition can build reports only from relational databases stored in a local Express Edition instance. Furthermore, Express Edition cannot be used for distributing reports (via e-mail, for instance) nor can it render reports in formats other than HTML, Adobe Portable Document Format, and Excel.

Full-text search. Introduced as part of SQL Server 7.0 in Nov 1998, full-text search provides efficient searching of text stored in SQL Server tables. Full-text search extends the capabilities of T-SQL, which is optimized for finding exact matches to search criteria. Full-text search can be used to search for phrases or groups of words or for related words, such as different tenses of the same verb. Inclusion of full-text search in Express will make the edition more useful for certain applications. For example, a developer could use full-text search to allow users to search product descriptions in an e-commerce Web site or to search within documents (journal articles or legal documents, for instance) that are managed and stored in a database.

Availability and Resources

Database mirroring was cut from the initial release of SQL Server 2005 but customers could access the feature for testing purposes. (Microsoft did not support its use in a production environment.) With the Apr. 2006 release of SP1, SQL Server 2005 customers get a fully supported version of database mirroring. The feature is available only in the Standard and Enterprise Editions of SQL Server. As is the case with clustering solutions, the standby server in a mirroring configuration does not require a separate SQL Server license if it is passive—that is, it serves no purpose other than its mirror role.

Customers have several options for downloading the free Express Edition. The most basic download contains only the bits for SQL Server Express Edition—it does not include Management Studio Express nor does it include Reporting Services and full-text search. A separate download includes Management Studio Express. A third, larger download, called Express Edition with Advanced Services, includes Reporting Services, full-text search, and Management Studio Express. However, to create reports using Reporting Services, users must download the Express Edition Toolkit, which contains Reporting Services' development tools.

To download SQL Server 2005 SP1 go to www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&displaylang=en.

The list of documented fixes in SP1 is at support.microsoft.com/kb/913090.

Other SP1 updates and improvements are described at support.microsoft.com/kb/916940.

SQL Server Express Edition is described at www.microsoft.com/sql/editions/express/default.mspx.

The various Express Edition downloads are available at msdn.microsoft.com/vstudio/express/sql/download.

Updated documentation for SQL Server 2005 can be downloaded from www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en.

For a discussion of SQL Server Management Studio and other manageability enhancements in SQL Server 2005, see "SQL Server 2005 Improves Management, Enterprise Features" on page 12 of the Dec. 2005 Update.