Updated: 8/22/2007

The most up-to-date version of this file is available on the World Wide Web at the Microsoft Download Center.

The SQL Server documentation team welcomes your documentation feedback. We view and investigate all documentation issues but do not answer technical support questions here. For help with technical issues not related to documentation, see Getting Assistance with SQL Server.

If it is possible, submit feedback to us in English.

Contents

1.0 Introduction

This file describes how to use Microsoft® SQL Server™ 2005 Express Edition (SQL Server Express) Service Pack 2 (SP2) to install new instances of SQL Server Express SP2 or to upgrade instances of SQL Server Express to SP2. SQL Server Express SP2 can also be used to upgrade instances of Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000) to SQL Server Express SP2. SQL Server Express service packs are cumulative, and this service pack upgrades all service levels of SQL Server Express to SP2.

Important:
This service pack can be used to upgrade only SQL Server Express (or MSDE 2000) to SQL Server Express SP2. You must use SQL Server Express with Advanced Services SP2 to upgrade instances of SQL Server Express with Advanced Services to SP2. SQL Server Express with Advanced Services to SP2 can be obtained from the MSDN page, SQL Server 2005 Express Edition. You must use Microsoft SQL Server 2005 Service Pack 2 (SP2) to upgrade all other editions of SQL Server 2005 to SP2. SQL Server 2005 SP2 can be obtained from the Microsoft Download Center page, SQL Server 2005 Service Pack 2. This service pack cannot be applied to any pre-release versions of SQL Server Express.

[Top]

1.1 Overview of SQL Server Express SP2 Installation

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

  1. Download and extract the service pack installation files. Section 2.0 describes how to obtain the SP2 installation files.

    Note:
    SQL Server Express SP2 requires the .NET Framework 2.0.

  2. Prepare for upgrade to SP2. Section 3.1 describes the preparatory steps to take before you install SQL Server Express SP2.

  3. Install SQL Server Express SP2. Section 3.2 details options for running SP2 Setup.

[Top]

1.2 Installation Requirements

Before you install SQL Server Express SP2, review the SQL Server Express installation requirements at the Microsoft Web page, SQL Server 2005 Express Edition System Requirements. Hardware and software requirements are also summarized in the Installation Requirements for SQL Server Express document.

SQL Server Express requires the .NET Framework 2.0. If you do not have the .NET Framework 2.0 installed, you must download and install it before you install SQL Server Express SP2. You can download the .NET Framework 2.0 from the Microsoft Download Center page, Microsoft .NET Framework Version 2.0 Redistributable Package.

Note:
The .NET Framework 2.0 is installed automatically by both Microsoft Visual Studio 2005 and all other editions of SQL Server 2005.

If you are upgrading an instance of MSDE 2000 to SQL Server Express SP2, you must make sure that the .NET Framework 2.0 is installed.

[Top]

1.3 Uninstalling SQL Server Express SP2

A new instance of SQL Server Express SP2 can be uninstalled from Add or Remove Programs. However, when you use SQL Server Express SP2 to upgrade a SQL Server Express instance to SP2, the upgrade cannot be uninstalled.

To revert to the previous version of SQL Server Express
  1. In Add or Remove Programs, select Microsoft SQL Server 2005 from Currently installed programs, and then click Remove.

  2. In the Components Selection dialog box, select the instance of SQL Server Express to remove.

  3. Click Next, and then click Finish to complete the uninstallation wizard.

  4. Reinstall an instance of the previous version of SQL Server Express.

  5. Apply any hotfixes that were previously installed on the instance.

    Note:
    Additional steps are required to make sure that you can revert to the previous version of SQL Server Express. For more information, see 3.1.1 Back Up Your SQL Server Express Databases.

[Top]

1.4 Additional Information About SQL Server Express SP2

For additional information about how to run SQL Server Express SP2 on Microsoft Windows Vista, see 5.3 Windows Vista Considerations.

A list of the fixes that are contained in this service pack is provided in Microsoft Knowledge Base article 921896. Each fix listed in 921896 has a link to a Knowledge Base article about the problem that the fix addresses. Follow the links to the individual Knowledge Base articles to see information about each fix.

Hotfixes

All publicly issued SQL Server 2005 security bulletins that were released before January 15, 2007 have been addressed in SP2. If you received a SQL Server 2005 or SQL Server Express hotfix after January 15, 2007, that hotfix might not be included in this release of SP2. Contact your primary support provider about obtaining the same hotfix for SQL Server Express SP2.

How to Determine the SQL Server Express Version Number

Every SQL Server product (including Service Packs) has its own version number. The version number of an instance of SQL Server Express can be viewed two ways:

  • In SQL Server Management Studio Express Object Explorer when connected to the instance.

  • By executing the query SELECT SERVERPROPERTY('ProductVersion') against the SQL Server Express instance.

The following table shows the version numbers that correspond to versions of SQL Server Express:

Version Number SQL Server Express Version

9.00.1399

SQL Server Express (initial version)

9.00.2047

SQL Server Express SP1 or SQL Server Express with Advanced Services

9.00.3042

SQL Server Express SP2 or SQL Server Express with Advanced Services SP2

Note:
Your product version may be different from these values if you applied a hotfix.

[Top]

1.5 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 February 2007 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 SP2. The February 2007 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]

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.

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]

2.0 Obtaining SQL Server Express SP2

SQL Server Express SP2 is available at the MSDN page, SQL Server 2005 Express Edition SP2. Follow the instructions provided at the download site for downloading and extracting SQL Server Express SP2.

[Top]

3.0 Installing SQL Server Express SP2

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

  1. Prepare for a SQL Server Express SP2 installation

  2. Install SQL Server Express SP2

  3. Restart services and applications

[Top]

3.1 Prepare for a SQL Server Express SP2 Installation

This section only applies when you upgrade an instance of SQL Server Express or MSDE 2000 to SP2. When you install a new instance of SQL Server Express SP2, you must also install the .NET Framework 2.0. This is the only preparation required. For more information, see 1.2 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 SP2. 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 SP2:

  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]

3.1.1 Back Up Your SQL Server Express Databases

Before installing SQL Server Express SP2, back up the master, msdb, and model databases. Installing SQL Server Express SP2 modifies the master, msdb, and model databases. This makes them incompatible with pre-SP2 versions of SQL Server Express and MSDE. Backups of these databases are required if you decide to reinstall SQL Server Express or MSDE without SP2.

It is also prudent to back up your user databases.

Important:
When you apply SQL Server Express SP2 to instances that participate in a replication topology, you must back up your replicated databases together with the system databases before you apply SQL Server Express SP2.

[Top]

3.1.2 Verify That the System Databases Have Enough Free Space

If the autogrow option is not selected for the master and msdb system databases, these databases must have at least 500 KB of free space each. To verify that the databases have sufficient space, run the sp_spaceused system stored procedure on the master and msdb databases. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server 2005 Books Online.

[Top]

3.1.3 Verify That SQL Server Express Service Accounts Are Not Disabled

Setup might fail if the service startup type for the SQLExpress service is set to Disabled.

To verify and enable the service account
  1. In Control Panel, select Administrative Tools, and then select Services.

  2. Locate the SQLExpress service in the list and note the value for Startup Type.

  3. If the value for Startup Type is Disabled, double-click the service name and change Startup Type to either Manual or Automatic.

  4. Click OK.

[Top]

3.1.4 Stop Services and Applications

Before you install SP2, you should stop all applications and services that make connections to all instances of SQL Server Express being upgraded. This includes Control Panel and Add or Remove Programs

If you begin to apply SP2 without first shutting down applications and services, Setup will try to stop the SQL Server Express service. If Setup determines that other applications and services must be stopped, a warning displays the applications and services that must be stopped. After you stop these applications and services, click Try Again to resume Setup. If you cannot stop an application or service, click Continue. Setup will continue, but you might have to restart your computer after Setup finishes. If you click Cancel, the current component is not upgraded to SP2, but Setup continues.

Note:
Setup cannot always determine which applications and services must be stopped. Before you install SP2, stop all applications and services that make connections to SQL Server Express. This includes Control Panel.

[Top]

3.2 Install SQL Server Express SP2

To install SQL Server Express SP2, run the self-extracting download package. Complete installation instructions for running Setup in both attended and unattended mode are described in the "Installing SQL Server Express" section in SQL Server 2005 Books Online. You should review the Setup documentation before you install SQL Server Express SP2. You can access the Setup documentation during Setup by pressing the F1 key or by clicking the Help button in a dialog box that contains online Help. This documentation is also available on the World Wide Web at the MSDN Library.

Important:
SQL Server Express SP2 Setup must be run under an account that has administrative credentials on the computer where SQL Server Express SP2 will be installed.

[Top]

3.3 Restart Services and Applications

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

When the Setup program is finished, it may prompt you to restart the computer. 3.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]

3.4 Setup Issues

This section details setup issues for SP2.

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

[Top]

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

Setup might fail and roll back, and you receive the following error message: "An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'." To work around this problem, uninstall SQL Server Native Client by using Add or Remove Programs.

[Top]

3.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]

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

Windows Cryptographic Service Provider (CSP) is code that performs authentication, encoding, and encryption services that Windows-based applications access through CryptoAPI on Windows Server 2003. If the CSP service is stopped or disabled, SQL Server Setup fails and displays a Windows logo requirement message.

Note:
Before you run SQL Server Setup on a Windows Server 2003 failover cluster, the CSP service must be started on all cluster nodes.

To enable the Windows CSP service on Windows Server 2003
  1. In Control Panel, open Administrative Tools, and then double-click Services.

  2. In the Name column, right-click Cryptographic Services, and then click Start.

  3. Close Services.

  4. Run Setup.

[Top]

3.4.4 MS DTC Is Not Fully Enabled on Windows

Because the Microsoft Distributed Transaction Coordinator (MS DTC) is not completely configured in Windows, applications might not enlist SQL Server Express resources in a distributed transaction. This problem can affect linked servers, distributed queries, and remote stored procedures that use distributed transactions. To prevent such problems, you must fully enable MS DTC services on the server where SQL Server Express is installed.

To enable MS DTC fully
  1. In Control Panel, open Administrative Tools, and then double-click Component Services.

  2. In the left pane of Console Root, click Component Services, and then expand Computers.

  3. Right-click My Computer, and then click Properties.

  4. On the MSDTC tab, click Security Configuration.

  5. Under Security Settings, select all the check boxes.

  6. Verify that the DTC Logon Account name is set to NT AUTHORITY\NetworkService.

[Top]

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

Setup might fail if an existing SQL Server client installation is configured to have the "force encryption" option enabled. To work around this issue, disable the option on any SQL Server clients. For Microsoft Data Access Components (MDAC) clients in SQL Server 2000, use the SQL Server 2000 Client Network Utility. For SQL Server Native Client, uninstall SQL Server Native Client by using Add or Remove Programs.

[Top]

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

If your computer has SQL Server 2000 Management Tools and a default instance of SQL Server Express is installed, SQL Server Setup enables you to install a SQL Server 2000 default instance. However, doing this disables the installed instance of SQL Server Express. Therefore, do not install a default instance of SQL Server 2000 when SQL Server 2000 Management Tools and a default instance of SQL Server Express already exist on the computer.

[Top]

3.4.7 Installing SQL Server Express on a Windows Domain Controller

Security Note:
We do not recommend running SQL Server Express on a domain controller.

You can install SQL Server Express on a Windows domain controller. However, it cannot run on a Windows Server 2003 domain controller as Local Service or Network Service. SQL Server service accounts should run as Windows domain user accounts. You can also install SQL Server service accounts to run as Local System. This option is not recommended.

Do not change the role of the server after you install SQL Server Express. For example, if you install SQL Server Express on a member server, do not use the Active Directory Installation Wizard (Dcpromo.exe) tool to promote the server to a domain controller. Or, if you install SQL Server Express on a domain controller, do not use the Active Directory Installation Wizard to demote the server to a member server. Changing the role of a server after you install SQL Server Express can cause loss of functionality and is not supported.

[Top]

3.4.8 Maintenance Mode Prompts for Path to Setup.exe

If you install a new SQL Server Express component in maintenance mode, you are prompted for the location of Setup.exe on the SQL Server Express installation media. When you specify the location, make sure that the path includes "Setup.exe." For example, the path "D:\" fails, but "D:\Setup.exe" succeeds.

[Top]

3.4.9 Troubleshooting Failure of Setup Command Shell Scripts

Setup command shell scripts can generate Windows script errors when path variables contain parentheses. This occurs because command shell scripts do not support parentheses in path variables. This can occur when you install 32-bit components to the Windows on Windows (WOW64) 32-bit subsystem on a 64-bit computer. For example, the following script, with a path value of "C:\Program Files (x86)\", generates an error because the shell script interpreter misinterprets the parentheses in the expanded PATH variable as part of the IF/ELSE statement:

IF "%SOME_PATH%" == "" ( 
        SET PATH=%PATH%;%PATH_A% 
) ELSE ( 
        SET PATH=%PATH%;%PATH_B% 
) 

To work around this issue, change the script to remove the parentheses. For example:

IF "%SOME_PATH%" == "" set PATH=%PATH%;%PATH_A% 
IF NOT "%SOME_PATH%" == "" set PATH=%PATH%;%PATH_B% 

Or remove the SQL entry that contains parentheses from the path.

[Top]

3.4.10 Database Compatibility Level Setting when You Upgrade from MSDE 2000

This issue applies only when you upgrade an instance of MSDE 2000 to SQL Server Express SP2.

When an instance of MSDE 2000 is upgraded to SQL Server Express SP2, the database compatibility level remains at 80 to enable backward compatibility with existing MSDE 2000 applications. This backward compatibility level disables certain new functionalities of SQL Server Express. For more information, see the "sp_dbcmptlevel" reference topic in SQL Server 2005 Books Online.

[Top]

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

This issue applies only when you upgrade an instance of SQL Server Express to SP2.

When you upgrade an instance of SQL Server Express to SP2, Setup might fail if the system databases are installed in a directory that has a single quotation mark in the directory name. If failure occurs, you must move the system databases to a directory without a single quotation mark in the directory name. For more information about how to move system databases, see "Moving System Databases" in SQL Server 2005 Books Online.

[Top]

3.4.12 Restrictions on ALTER DATABASE Might Cause Upgrade to Fail

This issue applies only when you upgrade an instance of SQL Server Express to SP2.

When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, upgrade might fail. Restrictions to ALTER DATABASE can include the following:

  • Explicitly denying the ALTER DATABASE statement.

  • A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction that contains the ALTER DATABASE statement.

If you have restrictions on ALTER DATABASE, and Setup does not upgrade system databases to SP2, you must disable these restrictions and then re-run Setup.

[Top]

3.4.13 Upgrade Might Fail When Using SQL Server Authentication

This issue applies only when you upgrade an instance of SQL Server Express to SP2 or apply a later hotfix.

When you apply SP2, Setup upgrades the system databases. When you apply SP2 to upgrade an instance of SQL Server Express, you must use Windows authentication. If you specify SQL Server authentication, even if you use the parameter sapwd, the upgrade will fail.

[Top]

3.4.14 Upgrading an Instance of SQL Server to Another Edition

After you apply SP2 to an instance of SQL Server 2005, you will not be able to upgrade to another edition of SQL Server 2005. To upgrade an instance of SQL Server to another edition, you must perform the edition upgrade before you apply SP2. However, you can upgrade SQL Server 2005 Express Edition SP2 to SQL Server 2005 Express Edition with Advanced Services SP2.

[Top]

4.0 Additional Information

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

4.1 Using SQL Server Express

After you install SQL Server Express, use the following command to connect to an instance of SQL Server Express by using the command prompt:

sqlcmd -S Server\Instance

Where Server is the name of the computer and Instance is the name of the instance you want to connect to. If you have used the default named instance during setup, specify the instance as "SQLExpress".

For information about how to connect to and manage a SQL Server Express database, see Microsoft Knowledge Base article 907716 or the updated SQL Server 2005 Books Online documentation. For more information about this update to Books Online, see 1.5 Updates to SQL Server 2005 Books Online Are Available.

[Top]

4.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]

4.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]

4.4 Providing Feedback on SQL Server Express SP2

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

  • Send suggestions and bug reports about the features and user interface of SQL Server Express SP2 at the page Microsoft Connect for SQL Server 2005.

  • Choose to send error reports and feature usage data automatically to Microsoft for analysis. For more information, see "Error and Usage Report Settings (SQL Server Express)" in SQL Server Books Online.

  • Send suggestions and report inaccuracies about the documentation by using the feedback functionality in SQL Server 2005 Books Online.

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

[Top]

5.0 Documentation Notes

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

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

[Top]

5.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]

5.1.1 User Instance Functionality

SQL Server Express lets non-administrator users to copy or move databases without requiring DBCreator permissions. For more information, see "User Instances for Non-Administrators" in SQL Server 2005 Books Online.

[Top]

5.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]

5.1.3 RC4 Encryption Should Not Be Used

Do not use RC4 encryption to protect your data in SQL Server Express. Use a block cipher such as AES 256 or Triple DES instead.

[Top]

5.1.4 DBCC Error Reporting

Introduced in SQL Server Express SP1.

A mini-dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory whenever a DBCC CHECKDB, DBCC CHECKALLOC, DBCC CHECKFILEGROUP, or DBCC CHECKTABLE command detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL Server, the file is automatically forwarded to Microsoft. The collected data is used to improve SQL Server functionality. For more information, see "Error and Usage Report Settings" in SQL Server 2005 Books Online.

The dump file contains the results of the DBCC command and additional diagnostic output. The file has restricted discretionary access- control lists (DACLs). Access is limited to the SQL Server service account and members of the sysadmin role. By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. The DBCC command does not fail if the data collection process fails.

[Top]

5.1.5 Change to the Behavior of Uncommittable Transactions in a Batch

Introduced in SQL Server Express SP1.

When a batch is completed, the Database Engine automatically rolls back any active uncommittable transactions. Before the release of SQL Server Express SP1, uncommittable transactions had to be rolled back manually.

[Top]

5.2 Replication

The notes in this section are late-breaking items for replication.

[Top]

5.2.1 Replication is Disabled by Default

SQL Server Express can serve as a Subscriber for all kinds of replication, but replication is not installed by default for this edition.

To install replication components
  1. On the Feature Selection page, expand Database Services.

  2. Click Replication, and then click Entire feature will be installed on local hard drive.

To install connectivity components and Replication Management Objects (RMO)
  1. On the Feature Selection page, click Client Components, and then click Entire feature will be installed on local hard drive.

[Top]

5.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:

5.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]

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

SQL Server 2005 installation fails if the collation is case sensitive and the computer name contains lowercase letters or special characters.  To fix, rename the computer name to use only uppercase letters.