02/09/2009 22:55:59

This document contains important information about Microsoft® SQL Server® 2005 Express Edition (SQL Server Express) Service Pack 3 (SP3) and Microsoft SQL Server 2005 Express Edition with Advanced Services SP3. Use SQL Server 2005 Express Edition (SQL Server Express) SP3 to install new instances of SQL Server Express SP3 or to upgrade instances of SQL Server Express to SP3. SQL Server Express SP3 can also be used to upgrade instances of Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000) to SQL Server Express SP3. SQL Server Express service packs are cumulative, and this service pack upgrades all service levels of SQL Server Express to SP3.

This document applies to both SQL Server 2005 Express Edition SP3 and SQL Server 2005 Express Edition with Advanced Services SP3.

The Release Notes document and the listed Knowledge Base articles supplement SQL Server Books Online. This version of the document replaces the version that ships with SP3.

Contents

1.0 Accessing SQL Server 2005 Express Edition SP3 Setup Documentation

2.0 Obtaining SQL Server 2005 SP3

2.1 Related Links

3.0 Overview of SQL Server Express SP3 Installation

3.1 Installation Requirements

3.2 Uninstalling SQL Server Express SP3

3.3 Additional Information About SQL Server Express SP3

3.3.1 How to Determine the SQL Server Express Version Number

3.4 Updates to SQL Server 2005 Books Online are Available

3.4.1 SQL Server 2005 Samples and Sample Databases

3.4.2 Updated SQL Server 2005 Samples Are Available

4.0 Installing SQL Server Express SP3

4.1 Prepare for a SQL Server Express SP3 Installation

4.1.1 Back Up Your SQL Server Express Databases

4.1.2 Verify That the System Databases Have Enough Free Space

4.1.3 Verify That SQL Server Express Service Accounts Are Not Disabled

4.1.4 Stop Services and Applications

4.2 Install SQL Server Express SP3

4.3 Restart Services and Applications

4.4 Setup Issues

4.4.1 Existing SQL Server Native Client Installation May Cause Setup to Fail

4.4.2 System Configuration Checker Fails with "Performance Monitor Counter Check Failed" Message

4.4.3 If Cryptographic Services Are Disabled on Windows Server 2003, Setup Fails with Windows Logo Requirement Message

4.4.4 MS DTC Is Not Fully Enabled on Windows

4.4.5 "Force Encryption" Configuration Might Cause SQL Server Express Setup to Fail

4.4.6 Installing a Default Instance of SQL Server 2000 May Disable SQL Server Express

4.4.7 Installing SQL Server Express on a Windows Domain Controller

4.4.8 Maintenance Mode Prompts for Path to Setup.exe

4.4.9 Troubleshooting Failure of Setup Command Shell Scripts

4.4.10 Database Compatibility Level Setting when You Upgrade from MSDE 2000

4.4.11 Setup Might Fail If There Is a Single Quotation Mark in the System Database Directory

4.4.12 Restrictions on ALTER DATABASE Might Cause Upgrade to Fail

4.4.13 Upgrade Might Fail When Using SQL Server Authentication

4.4.14 Upgrading an Instance of SQL Server to Another Edition

5.0 Additional Information

5.1 Using SQL Server Express

5.2 Service Account and Network Protocols

5.3 Getting SQL Server Express Assistance

5.4 Providing Feedback on SQL Server Express SP3

6.0 Documentation Notes

6.1 Database Engine

6.1.1 User Instance Functionality

6.1.2 Considerations for the Autorecovered Shadow Copy Feature of the Volume Shadow Copy Service

6.1.3 RC4 Encryption Should Not Be Used

6.1.4 DBCC Error Reporting

6.1.5 Change to the Behavior of Uncommittable Transactions in a Batch

6.2 Replication

6.2.1 Replication is Disabled by Default

6.3 Windows Vista Considerations

6.3.1 Administrator Rights Not Inherited from Windows

6.3.2 Setup fails if the machine name contains lowercase letters or case sensitive collation

[Top]

1.0 Accessing SQL Server 2005 Express Edition SP3 Setup Documentation

2.0 Obtaining SQL Server 2005 SP3

3.0 Overview of SQL Server Express SP3 Installation

During installation, SQL Server Express SP3 Setup will enumerate and let you select from an instance of SQL Server Express to upgrade to SP3. SQL Server Express SP3 can be reapplied to SQL Server Express instances that have already been upgraded to SP3. The general process for installing SP3 is as follows:

  1. Download and extract the service pack installation files. Section 2.0 Obtaining SQL Server 2005 SP3 describes how to obtain the SP3 installation files.
    Note:
    SQL Server Express SP3 requires the .NET Framework 2.0.

  2. Prepare for upgrade to SP3. Section 4.1 describes the preparatory steps to take before you install SQL Server Express SP3.
  3. Install SQL Server Express SP3. Section 4.2 details options for running SP3 Setup.

[Top]

3.1 Installation Requirements

3.2 Uninstalling SQL Server Express SP3

3.3 Additional Information About SQL Server Express SP3

3.4 Updates to SQL Server 2005 Books Online are Available

Starting with the April 2006 update of SQL Server 2005 Books Online, a separate Books Online for SQL Server Express is unavailable. SQL Server 2005 Books Online is the primary documentation for SQL Server Express. The November 2008 update of Books Online reflects all product upgrades that are included in this service pack, and also includes other improvements to the documentation. We strongly recommend that you install this update to keep the information in the local copy of the documentation current with SQL Server Express SP3. The November/December 2008 update is available at this Microsoft Web site. After installing Books Online, you can use the SQL Server Express filter in the Contents pane to hide the content that does not apply to your edition of SQL Server Express. For more information about SQL Server Express documentation, see "Using SQL Server Express Books Online" in SQL Server 2005 Books Online.

[Top]

3.4.1 SQL Server 2005 Samples and Sample Databases

The sample databases are not installed with SQL Server Express. The AdventureWorks sample database can be installed from the Microsoft Download Center Web site. The Northwind and pubs sample databases can be downloaded from the Microsoft Download Center Web site.

Note:
Although the Northwind and pubs sample databases will work with SQL Server Express, the AdventureWorks sample database is recommended for use with SQL Server Express.

3.4.2 Updated SQL Server 2005 Samples Are Available

New and updated samples for SQL Server 2005 are available. You can download these updated samples from this Microsoft Web site. For more information about installing the updated samples, see "Installing Samples" in the updated SQL Server 2005 Books Online.

[Top]

4.0 Installing SQL Server Express SP3

This section details how to install SQL Server Express SP3 and any late-breaking information or setup issues that can affect successful installation of the service pack. The steps to install SQL Server Express SP3 are as follows:

  1. Prepare for a SQL Server Express SP3 installation
  2. Install SQL Server Express SP3
  3. Restart services and applications

[Top]

4.1 Prepare for a SQL Server Express SP3 Installation

This section only applies when you upgrade an instance of SQL Server Express or MSDE 2000 to SP3. When you install a new instance of SQL Server Express SP3, you must also install the .NET Framework 2.0. This is the only preparation required. For more information, see 3.1 Installation Requirements. When you upgrade an instance of MSDE 2000, you must follow the steps in this section and install the .NET Framework 2.0.

Note:
We recommend that you run Microsoft SQL Server 2005 Upgrade Advisor against instances of MSDE 2000 before you upgrade them to SQL Server Express SP3. Upgrade Advisor identifies features and configuration changes that might affect an upgrade, and it provides links to documentation that describes each identified issue and how to resolve it. You can download Upgrade Advisor from the Microsoft Download Center Web site.

You must do the following before you upgrade an instance of SQL Server Express to SP3:

  1. Back up your SQL Server Express databases
  2. Verify that the system databases have enough free space
  3. Verify that SQL Server Express service accounts are not disabled
  4. Stop services and applications

[Top]

4.1.1 Back Up Your SQL Server Express Databases

4.1.2 Verify That the System Databases Have Enough Free Space

4.1.3 Verify That SQL Server Express Service Accounts Are Not Disabled

4.1.4 Stop Services and Applications

4.2 Install SQL Server Express SP3

4.3 Restart Services and Applications

This section only applies when you upgrade an existing instance of SQL Server Express to SP3.

When the Setup program is finished, it may prompt you to restart the computer. 4.1.4 Stop Services and Applications provides guidelines on when a restart is required. After the system restarts, or after the Setup program is finished without requesting a restart, use the Services application in Control Panel to make sure that any services you stopped before applying the service pack are now running. This includes services such as Microsoft Distributed Transaction Coordinator (MS DTC) and the Microsoft Search services, or instance-specific equivalents. Restart the applications that you closed before you run the service pack Setup program. You should also back up the upgraded master, msdb, and model databases now.

[Top]

4.4 Setup Issues

This section details setup issues for SP3.

Note:
Unless otherwise noted, these issues apply only when you install a new instance of SQL Server Express.

[Top]

4.4.1 Existing SQL Server Native Client Installation May Cause Setup to Fail

4.4.2 System Configuration Checker Fails with "Performance Monitor Counter Check Failed" Message

System Configuration Checker (SCC) verifies the value of the Performance Monitor Counter registry key before SQL Server installation starts. If SCC cannot verify the existing registry key, or if SCC cannot run the Lodctr.exe system program, the SCC check fails, and Setup is blocked. To complete setup, you must manually increment the registry key.

Caution:
Incorrectly editing the registry can severely damage your system. Before making changes to the registry, we recommend that you back up any valued data on the computer. For information about how to back up, restore, and modify the registry, see Microsoft Knowledge Base article 256986.

To manually increment the counter registry key
  1. Click Start, click Run, type regedit.exe in the Run text box, and then click OK.

  2. Move to the following registry key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib]. Look up the following keys:

    • "Last Counter"=dword:00000ed4 (5276)
    • "Last Help"=dword:00000ed5 (5277)
  3. Verify the values of the Counter and Help keys in the following registry key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009].

    The maximum value of Counter must be equal to the Last Counter value from the previous step. The maximum value of Help must be equal to the Last Help value from the previous step.

    Note:
    "009" is the key that is used for the English (United States) language.

  4. If necessary, modify the value for the Last Counter and Last Help values in the \Perflib key. Right-click Last Counter or Last Help in the right pane, select Modify, click Base = Decimal, set the value in the Value data field, and then click OK. Repeat for the other key, if it is required, and then close the registry editor.

  5. Run SQL Server Express Setup again.

[Top]

4.4.3 If Cryptographic Services Are Disabled on Windows Server 2003, Setup Fails with Windows Logo Requirement Message

4.4.4 MS DTC Is Not Fully Enabled on Windows

4.4.5 "Force Encryption" Configuration Might Cause SQL Server Express Setup to Fail

4.4.6 Installing a Default Instance of SQL Server 2000 May Disable SQL Server Express

4.4.7 Installing SQL Server Express on a Windows Domain Controller

4.4.8 Maintenance Mode Prompts for Path to Setup.exe

4.4.9 Troubleshooting Failure of Setup Command Shell Scripts

4.4.10 Database Compatibility Level Setting when You Upgrade from MSDE 2000

4.4.11 Setup Might Fail If There Is a Single Quotation Mark in the System Database Directory

4.4.12 Restrictions on ALTER DATABASE Might Cause Upgrade to Fail

4.4.13 Upgrade Might Fail When Using SQL Server Authentication

4.4.14 Upgrading an Instance of SQL Server to Another Edition

5.0 Additional Information

This section describes additional considerations for using SQL Server Express SP3. This section is intended primarily for users who are new to SQL Server Express.

5.1 Using SQL Server Express

5.2 Service Account and Network Protocols

For SQL Server Express, the Local System Account is Network Service Account.

SQL Server Express listens on local named pipes and shared memory. By using a default installation, you cannot remotely connect to SQL Server Express. You will have to enable TCP/IP and check if the Windows firewall exception is enabled.

To enable TCP/IP
  1. Click Start, select All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

    Optionally, you can open Computer Manager by right-clicking My Computer and selecting Manage. In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager.

  2. Expand SQL Server 2005 Network Configuration, and then click Protocols for InstanceName.

  3. In the list of protocols, right-click the protocol you want to enable, and then click Enable.

    The icon for the protocol will change to show that the protocol is enabled.

To enable an exception in the Windows firewall
  1. Click Start, click Control Panel, and then click Network Connections.

  2. From the navigation bar on the left, click Change Windows Firewall settings.

  3. On the Exceptions tab, in the Programs and Services box, you will probably see that SQL Server is listed, but not selected as an exception. If you select the check box, Windows will open the 1433 port to let in TCP requests. Alternatively, if you do not see SQL Server listed, do the following:

    1. Click Add Program.
    2. Click Browse.
    3. Locate drive:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN
    4. Add the file sqlservr.exe to the list of exceptions.
Note:
You might also have to configure any third-party hardware or software firewalls to enable connections to SQL Server Express.

[Top]

5.3 Getting SQL Server Express Assistance

There are three principal sources of information about SQL Server Express:

Do not use other Microsoft newsgroups for posting questions regarding SQL Server Express. For more information, see "Getting SQL Server Express Assistance" in SQL Server Books Online. The latest information from the SQL Server Express team can be found at the SQL Server Express WebLog.

[Top]

5.4 Providing Feedback on SQL Server Express SP3

To provide suggestions and bug reports on SQL Server Express SP3:

For more information, see "Providing Feedback on SQL Server 2005" in SQL Server Books Online.

[Top]

6.0 Documentation Notes

This section covers significant issues that can occur after you apply SQL Server Express SP3 and any late-breaking items that are not reflected in the updated SQL Server 2005 Books Online documentation. For more information, see 3.4 Updates to SQL Server 2005 Books Online Are Available.

This section does not describe all the fixes that are provided in SP3. For a complete list of these fixes, see Microsoft Knowledge Base article 955706.

[Top]

6.1 Database Engine

The notes in this section are issues for the Database Engine and Database Engine-specific command prompt utilities that also apply to SQL Server Express.

[Top]

6.1.1 User Instance Functionality

6.1.2 Considerations for the Autorecovered Shadow Copy Feature of the Volume Shadow Copy Service

The autorecovered shadow copy feature of the Volume Shadow Copy Service (VSS) has the following limitations.

Multiple Persisted Autorecovered Shadow Copies

On Windows Server 2003 SP1 and later versions, you can create only a single persisted autorecovered shadow copy. To create an additional shadow copy, you must first apply the update described in Knowledge Base article 891957.

Note:
If you have not applied this update, you can create a new persisted autorecovered shadow copy by deleting the existing one first, and then creating the new one.

Autorecovered Shadow Copies and Full-Text Catalogs

The autorecovered shadow copy feature does not support full-text catalogs. When an autorecovered shadow copy is created, any full-text catalogs in the database on the shadow copy are taken offline. When the database is attached from the shadow copy, the full-text catalog remains offline permanently. All other data remains available in the attached database.

When a database that contains a full-text catalog is attached directly from an autorecovered shadow copy, the attach operation returns the following error message:

Server: Msg 7608, Level 17, State 1, Line 1

An unknown full-text failure (0xc000000d) occurred during "Mounting a full-text catalog"

If you do not have to attach a database directly from the shadow copy, you can avoid this situation by copying the database files and full-text catalogs from the shadow copy to a regular drive letter-based volume. Then, attach the database from that location. If the attach command specifies the correct location of the copied full-text files, the full-text catalogs will work.

[Top]

6.1.3 RC4 Encryption Should Not Be Used

6.1.4 DBCC Error Reporting

6.1.5 Change to the Behavior of Uncommittable Transactions in a Batch

6.2 Replication

6.3 Windows Vista Considerations

Windows Vista includes a new feature, User Account Control (UAC) that helps administrators manage their use of elevated privileges. When running on Windows Vista, administrators do not use their administrative privileges by default. Instead they perform most actions as standard (non-administrative) users, temporarily assuming their administrative privileges only when necessary.

UAC causes some known issues. For more information, see the following Web pages on TechNet:

[Top]

6.3.1 Administrator Rights Not Inherited from Windows

Windows Vista users that are members of BUILTIN\Administrators are not automatically added to the sysadmin fixed server role when they connect to SQL Server Express. Only Windows Vista users that have been explicitly added to a server-level administrator role can administer SQL Server Express. Any member of the Built-In\Users group can connect to the SQL Server Express instance, but they will have limited permissions to perform database tasks. For this reason, users whose SQL Server Express privileges are inherited from BUILTIN\Administrators and Built-In\Users in previous releases of Windows must be explicitly granted administrative privileges in instances of SQL Server Express running on Windows Vista.

To add a new Login to the sysadmin fixed server role while logged in as machinename\Administrator
  1. Click Start, point to All Programs, point to SQL Server 2005, and then click SQL Server Management Studio.

  2. Connect to SQL Server.

  3. To add the Windows user to the sysadmin fixed server role, follow these steps:

    1. Click Security.
    2. Right-click Logins, and then Click New Login.
    3. Type the user name in the Login name text box.
    4. Click Server Roles.
    5. Select the sysadmin check box and then click OK.
To add a new Login to the sysadmin fixed server role while logged in as any user other than the administrator
  1. Click Start, Point to All Programs, Point to SQL Server 2005, right-click SQL Server Management Studio, and then click Run As Administrator.

    Note:
    Run as Administrator option elevates the user permissions.

  2. You will see a User Account Control dialog box. You might have to provide the administrator credentials. Click Continue.

  3. In SQL Server Management Studio, connect to SQL Server.

  4. To add the Windows user to the sysadmin fixed server role, follow these steps:

    1. Click Security.
    2. Right-click Logins, and then Click New Login.
    3. Type the user name in the Login name textbox.
    4. Click Server Roles.
    5. Select the sysadmin check box and then click OK.

[Top]

6.3.2 Setup fails if the machine name contains lowercase letters or case sensitive collation