Developer Product Briefs
Install SP-1 for SQL Server 2005 and Express
Microsoft posted on April 18, 2006 the RTM version of Service Pack 1 for SQL Server 2005 Developer, Workgroup, Standard, Enterprise, and Express editions. Get the lowdown on SP-1's added features, including fully supported database mirroring, bug fixes, and add-ons for SQL Express.
Install SP-1 for SQL Server 2005 and Express
Get the lowdown on SP-1's added features, including fully supported database mirroring, bug fixes, and add-ons for SQL Express.
by Roger Jennings
April 26, 2006
Paul Flessner, Microsoft's Senior Vice President of Server Applications, promised in early April 2006 to deliver SQL Server 2005 Service Pack 1 (SP1) before the end of the month (see Resources). Microsoft's "end of the {month | quarter | half | year}" RTM commitments tend to lag by a period or two at least. To prove the naysayers wrong, at least in this instance, Microsoft posted SP1 for all SQL Server 2005 versions almost two weeks before the end of April and about a month after posting the only SP1 Community Technical Preview (CTP) on March 16.
What's more, SP1 not only contains bug fixes, but also delivers significant feature upgrades for all SQL Server versions. However, the updated SQL Server Books Online (April 2006) missed the April 18, 2006 SP1 release and was posted on April 22, 2006 (see Resources). This file (SqlServer2K5_BOL_Apr2006.msi or later) installs online help for the full and Express versions and replaces the earlier, truncated BOL version for SQL Express.
To show you what to expect when you update SQL Server 2005 editions with SP1, I'll summarize the bug fixes and describe major feature upgrades. Developer, Standard, and Enterprise editions gain fully supported database mirroring and Database Mirroring Monitor. Workgroup editions don't support mirroring, but you can use SQL Express as a mirroring witness server instance.
SQL Server 2005 Express Edition (SSX) SP1 adds Reporting Services, full-text search, SQL Server Management Studio Express (SSMSE), and Business Intelligence Development Studio (BIDS). see Resources for links to MSDN download points for the six SP1-related installers. The "Installing SP1 for SQL Server 2005" and "Installing SQL Express SP1 with Advanced Services" sidebars give you a preview of the SP1 installation process for the full and Express versions.
SP1 fixes the 45 bugs listed in Microsoft Knowledge Base article KB 913090, "A list of the bugs that have been fixed in SQL Server 2005 Service Pack 1." (The March CTP article version listed only 19 bugs.) The current article links to individual KB articles that describe each bug, many of which fall in the arcane category. In my two years or so of working with SQL Server 2005 betas and RTM editions, the only listed bug I encountered was 915047: "When you try to run a query in a query editor in SQL Server Management Studio, a query in a different query editor runs instead." Most of these fixes apply to all SQL Server 2005 editions, including SSX, but a few apply to Analysis Services, SSX, or SQL Server Management Studio (SSMS) only.
SP1 Database Mirroring and Monitoring
Database mirroring for enhancing data availability, disaster recovery, or both finally gains supported status for Developer, Standard, and Enterprise editions. Mirroring features were present—but not supported—in the initial (RTM) release. Mirroring, which is simpler than fail-over clustering or log shipping to implement and manage, maintains an exact or close-to-exact copy of a production database on a remote (mirror) server instance. A database mirroring system consists of two or three SQL Server 2005 instances: principal server, mirror server, and an optional witness server. As mentioned earlier, Express and Workgroup editions don't support mirroring, but you can use SQL Express SP1 as a witness instance. Participants in a mirrored database configuration are called partners.
The principal's transaction log synchronizes the corresponding mirror database by redoing on the mirror each INSERT, UPDATE, DELETE, or DDL operation performed by the principal. The witness is required for synchronous high-availability mode (synchronous, two-phase commit with automatic failover) and determines which instance is the principal (see Figure 1). Employing a SQL Express SP1 instance as the witness server avoids a third license charge. Systems without a witness run in high-protection (synchronous, SAFETY=FULL) or high-performance (asynchronous, SAFETY=OFF) mode and require DBA intervention for failover.
High-performance mode doesn't include log updates in transactions and processes updates as fast as possible. SQL Server 2005 Standard Edition doesn't support high-performance mode, parallel REDO (more than one thread for REDO operations in high-availability or high-protection mode), or database snapshots (see the SQL Server 2005 Features Comparison page's High-Availability table for details). Developer Edition supports the same mirroring feature set as Enterprise Edition. See the "Set Up a Database Mirror Testbed" sidebar for illustrated, step-by-step mirroring configuration instructions. To learn more about mirroring modes, read TechNet's "Database Mirroring in SQL Server 2005" technical paper.
Synchronous mirror updates exact a significant performance penalty and both synchronous and asynchronous updates consume substantial server resources. SQL Server Management Studio's Database Mirroring Monitor (DMM) lets you analyze the effect of database mirroring on transaction performance (see Figure 2). You also can receive alerts in the event that unsent or unrestored transaction log entries exceed a specified size (in KB) or lifetime (in minutes). The "Measure the Effect of Mirroring on Update Performance" sidebar shows you how to set up DMM for a sample mirrored database. Alternatively, you can use the sp_dbmmonitorresults and related sp_dbmmonitor* system stored procedures to manage a mirrored system programmatically. Industry analysts appear to agree that database mirroring puts SQL Server 2005 on a par with Oracle's high-availability and disaster-recovery features.
In addition to supporting database mirroring, SP1 enables reporting on SAP NetWeaver Business Intelligence with the new .NET Data provider for SAP NetWeaver Business Intelligence and a new MDX Query Designer, which lets SAP users create and manage reports based on data from the SAP Business Information Warehouse (SAP BW). The "Using SQL Server 2005 Reporting Services with SAP NetWeaver Business Intelligence" white paper provides more details on this new feature (see Resources).
New SQL Server 2005 Express Features
SQL Server Management Studio Express replaces the less-than-stellar SQL Express Manager that Microsoft removed from the SSX's RTM version. Installing SQL Server 2005 Express Edition SP1 with Advanced Resources or the SQL Server 2005 Express Edition Toolkit runs SSMSE the setup program. SSMSE has been available in CTP versions since November 2005, so most SSX users are familiar with its use (see Figure 3). For detailed information on using SSMSE to manage SSX instances, download the "Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition" white paper.
Installing Reporting services for SSX requires running the SQL Server 2005 Express Edition SP1 with Advanced Resources setup program (see Resources). SSX Reporting Services are identical to those provided by full SQL Server 2005 versions, except that SSX supports Reporting Services on the local server only. So you must install Reporting Services and the SSX Database Engine on the same machine. When installing Reporting Services, verify that Internet Information Services (IIS) is running on your machine and accept the "Install the Default Configuration" option in the Services Installation Options dialog. If you don't, running the Reporting Services Configuration tool later is a time-consuming and error-prone operation. You must install BIDS with the SQL Server 2005 Express Edition Toolkit to design new or modify existing reports.
SQL Server 2005 Report Packs provide SSX users with a painless introduction to reporting systems (see Resources). The most useful of the eight Report Packs for Report Services neophytes are SQL Server 2005 Report Pack for Microsoft Internet Information Services (IIS) and SQL Server 2005 Report Pack for Financial Reporting (see Figure 3). Use SSMSE to attach the Pack's sample database to your SSX instance and open the solution file (Financial Report .sln for this example) in BIDS; double-click one of Solution Explorer's ReportName.rdl report nodes to open it. SP1 enables reports to include static images for backgrounds and logos; client-side ReportControls for Windows and Web forms always have supported static images.
Report Packs connect to the local SQL Server 2005 instance by default. If your ReportServer database's name is ReportServer$SQLExpress, you must open the Financial Report Pack Property Pages by choosing Report | Financial Report Pack Properties. Change the TargetServerURL property value from http://localhost/ReportServer to http://localhost/ReportServer$SQLExpress (see Figure 4).
Regardless of the ReportServer name, expand Solution Explorer's Shared Data Sources node and double-click the DatabaseName.rds (FinSampleDB.rds for this example) node to open the Shared Data Source dialog. Change the Connection String from Data Source=(local);Initial Catalog=FinSampleDB to Data Source=.\SQLEXPRESS;Initial Catalog=FinSampleDB (see Figure 5). Then click the Preview tab to display the report (see Figure 6). Press F5 to deploy the reports and open the Microsoft SQL Server Report Pack for Financial Reports folder in Internet Explorer.
Microsoft's claim that SSX SP1 supports full-text search is a stretch; a better description is permits or no longer prohibits full-text search. You ordinarily manage SQL Server 2005's full-text search feature with GUIs from SSMS's (the full version's) \Databases\DatabaseName\Storage\Full Text Catalogs node to add full text catalogs. The Full-Text Indexing Wizard, which opens from the Full-Text Index context menu for each database table, manages full text indexes. However, neither SSMS nor SSMSE expose a Storage node or Full Text Index menus for SSX databases.
The SSMSE Readme file states: "You cannot update the full-text catalog by using SSMSE because it is not supported by the tool. You can use sqlcmd utility or Windows Scheduler to update the full-text catalog. For more information, see 'Creating Full-Text Indexes,' 'CREATE FULLTEXT CATALOG (Transact-SQL),' and 'ALTER FULLTEXT CATALOG (Transact-SQL)' in SQL Server 2005 Books Online at this Microsoft Web site." My tests show the preceding warning to be true for both SSMS and SSMSE, which indicates that SSX—not SSMSE—is the culprit. This means you must use T-SQL DDL commands to create or modify full-text catalogs for SSX databases and full-text indexes on tables. (Note that there is no 'Creating Full-Text Indexes' index item in the April 2006 Books Online, but the topic exists; search for the term or use 'full-text indexes' as the index item.) As you'd expect, full-text search isn't permitted on SQL Server Express run as a normal user (RANU) or User Instance.
These new capabilities will make SSX more attractive to potential users than its primary "free" and "open source" competitors—Oracle 10g Express Edition, IBM DB2 Express-C, MySQL 5.0, Ingres 2006, and PostgreSQL 8.1. But I'm certain that SSX users will miss GUI management for full-text catalogs and indexing. On the whole, however, I think you'll agree that SP1 is a worthwhile update for all SQL Server 2005 editions.
About the Author
Roger Jennings is an independent XML Web service and database developer and author. Roger's latest book is Expert One-on-One Visual Basic 2005 Database Programming (ISBN 0-7645-7678-X) for WROX/Wiley. He's also a Visual Studio Magazine contributing editor and FTPOnline columnist, and writes the OakLeaf Systems Weblog (oakleafblog.blogspot.com). His Code of Federal Regulations Web services won Microsoft's 2002 .NET Best Horizontal Solution Award. Reach Roger at [email protected].