Welcome Guest!
Create Account | Login
Locator+ Code:

FTPOnline Channels Conferences Resources Hot Topics Partner Sites Magazines About FTP RSS 2.0 Feed

FTPOnline Special Report: SQL Server

email article
printer friendly
more resources

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.

Back to top

Java Pro | Visual Studio Magazine | Windows Server System Magazine
.NET Magazine | Enterprise Architect | XML & Web Services Magazine
VSLive! | Thunder Lizard Events | Discussions | Newsletters | FTPOnline Home