Thursday, March 16, 2006

SQL Server 2005 SP1 CTP Available for Download

Microsoft posted on 3/16/2006 download instructions for the first public Community Technical Preview (CTP) of Service Pack 1 (SP1) for SQL Server 2005 and SQL Server Express. The most important updates included in SP1 March CTP are promotion of database mirroring to production status for SQL Server 2005, and the addition of SQL Server Reporting Services (SSRS) plus full-text search features to SQL Express. These new capabilities will make SQL Server Express more attractive to potential users than its primary "free" and "open source" competitors—Oracle 10g Express Edition, IBM DB2 Express-C, MySQL 5.0, and PostgreSQL 8.1.

SP1 fixes the 19 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 - Community Technology Preview (CTP) March 2006." This article links to the individual KB articles that describe each bug. These fixes apply to all SQL Server 2005 editions, including SQL Express.

Brian Weckler's 2/16/2006 Direct Reports blog post notes that Report Builder gains the capability to add static images (for logos, backgrounds, et al.) and has a new Data Extension and Query Designer for SAP NetWeaver BI's InfoCubes and queries. (The .NET Framework 2.0's ReportViewer controls for Windows and Web forms have always supported static images.)

This post, updated 3/21/2006, summarizes the results of my initial SP1 March CTP test-drive with both SQL Server 2005 Standard Edition and SQL Server Express on separate partitions of a Dell PowerEdge 400SC server running Windows Server 2003 SP1 and Windows XP SP2, respectively (not virtual machines). The Microsoft SQL Server 2005 on Microsoft Windows Vista and 'Microsoft Windows Server Longhorn' page (posted March 6, 2006) states: "All editions of SQL Server 2005 Service Pack 1 (SP1) are supported on the February 2006 CTP release of Windows Vista and Windows Server 'Longhorn.' Note: The "Microsoft SQL Server 2005 Service Pack 1 Community Technology Preview" page is dated March 14, 2006, but wasn't accessible until 3/16/2006. Knowledge Base article 913090 was missing from the Microsoft site until 3/20/2006. Paul Flessner, Microsoft senior vice president of Server Applications, championed substitution of frequent, public CTPs to supplement periodic, restricted SQL Server beta 2005 releases. According to Microsoft Watch's Mary Jo Foley, "If Paul Flessner and his SQL Server team have their way, traditional beta releases may soon be a thing of the past." SP1 March CTP for SQL Server 2005 Individual self-extracting executable files are available from the Download Details page for SQL Server 2005's three platform versions: 32-bit X86 (251 MB), 64-bit X64 (302 MB), and 64-bit IA64 (351 MB). You can use each SQLServer2005SP1-KB913090-{x32/x64/ia64}-ENU.exe file to update Developer, Workgroup, Standard, and Enterprise editions. You can specify the components to which you apply the SP-1 March CTP in this installation dialog:

A few features must be updated, and Database, Analysis, and Reporting Services must be upgraded as a group. You can't roll back the SP1 installation.

Applying the full SP1 March CTP takes about 30 minutes on a moderately fast server. You'll probably need to reboot the server to complete the update. Another dialog displays SP1 installation progress:

You can read a semi-detailed installation log at the end of the update process.

SP1 March CTP for SQL Server Books Online

SP1 March CTP includes a prerelease version of an "April 2006 update" to SQL Server Books Online (BOL, 120 MB), which isn't guaranteed to include information about the modifications and additions made by SP1. Installation of the update takes about five minutes and "Removing backup files" consumes about 10 minutes.

Note: If you're using SQL Express and haven't installed the full version of SQL Server Books Online, download the December 2005 BOL update before updating BOL with the March CTP.

I wasn't able to find a topic that describes SP1 modifications and additions to BOL, but a search with "SQL Server 2005 Service Pack 1" returns 11 topics, most of which apply to SQL Server Reporting Services (SSRS).

Status of Database Mirroring

A Microsoft representative confirmed by e-mail that the SP1 release version will elevate database mirroring to production-grade, supported status. Thus, you can disregard the following note that each Books Online subtopic of the 'database mirroring [SQL Server]' index topic repeats and which the SP1 March CTP doesn't remove or update:

Important: Microsoft supportpolicies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

Ilya Bukshteyn, Microsoft’s director of product management for SQL Server, is quoted by several source as saying, "SP1 includes full database mirroring to offer real-time data backups." If that's the case, why wasn't BOL's 'database mirroring [SQL Server], about database mirroring' topic updated to reflect the change of status?

SP1 March CTP for SQL Server Express The Download Details: SQL Server 2005 SP1 Express Edition and Tools CTP page provides brief descriptions and links for downloading SP1 March CTP for SQL Server Express. Unlike SP1 for SQL Server 2005 Developer through Enterprise editions, to which you apply the service pack, you must replace your SQL Server Express instance with one of the following two new versions:

Microsoft SQL Server 2005 Express Edition Service Pack 1 CTP March 2006 (SQLEXPR.EXE, 54 MB). This version replaces your existing SQL Server Express instance (usually SQLEXPRESS).

Microsoft SQL Server 2005 Express Edition with Advanced Services CTP March 2006 (SQLEXPR_ADV.EXE, 235 MB). This version your existing SQL Server Express instance and adds support for SQL Server full-text catalogs (full-text search) and Reporting Services with Report Server. You can view reports created from data on a local SQL Server Express instance only. (You must install Business Intelligence Development Studio [BIDS] from the SQL Server Express Toolkit download to design reports.) SP1 March CTP also installs an updated version of SQL Server Management Studio Express (SSMSE).

Both preceding downloads install SQL Server Express March CTP with SP1 if you didn't install the RTM version.

Running the SQL Server Express with Advanced Services March CTP Setup Program

Executing SQLEXPR_ADV.EXE launches the Express version of the SQL Server 2005 setup program. In most cases, you'll want to install all Advanced Services, as shown here:

The SQL Express instance I attempted to update was installed on a Windows XP partition on which SQL Server 2005 Standard Edition and SQL Express were installed. Standard Edition was removed before starting the upgrade. I encountered an undocumented error during Database Engine installation that opened the following message box:

It was impossible to recover from this error, so I deleted the SQL Server Express instance and re-started the installation with mixed-mode authentication, which ran to completion, as shown here:

I then used SSMSE to reattach the original SQLEXPRESS instance's .mdf and .ldf files to the new SQLEXPRESS instance:

The SQL Server Configuration untility gains FullText Search and Reporting Services nodes:

The following downloads are available for SQL Server Express with Advanced Services and "vanilla" SQL Server Express:

Microsoft SQL Server 2005 Management Studio Express - CTP March 2006 (SQLServer2005_SSMSEE.msi, 39 MB). This download installs an updated version of SSMSE and is intended for users of SQL Server Express that's been updated to SP1 (without Advanced Services).

Microsoft SQL Server 2005 Express Edition Toolkit CTP March 2006 (SQLEXPR_TOOLKIT.EXE, 213 MB). The toolkit installs SSMSE, BIDS, client connectivity components, and SDKs. You need to install only BIDS from the Toolkit if you installed or upgraded SQL Server Express with Advanced Services.

Installing Business Intelligence Development Studio

Executing SQLEXPR_TOOLKIT.EXE launches the same setup app with a different list of installation options:

Features you select for installation that duplicate previously installed items, such as the Software Development Kit, are skipped during the installation process.

Expected SP1 RTM Date

According to Microsoft's Ilya Bukshteyn, RTM for SQL Server 2005 SP1 is scheduled for April 2006, which doesn't leave much time for the community to test the preview.