Upgrading MSDE 2000 to SQL Server 2005 Express

Important Note: Microsoft SQL Server Desktop Engine (MSDE) will not be supported on the Microsoft Vista operating system. If you plan to build an application and redistribute a database with it, you should consider using SQL Server 2005 Express Edition instead. For more information, see the Running SQL Server on Windows Vista page on microsoft.com.

Writer: Michael Otey

Applies To: SQL Server 2005 SP1

Summary: This white paper discusses the new SQL Server 2005 Express Edition features that are important to MSDE users. It covers how to upgrade your existing MSDE installation to SQL Server 2005 Express and presents criteria to help you decide when to upgrade your MSDE installation to other editions of SQL Server 2005.

On This Page

Introduction
System Requirements for SQL Server 2005 Express
Feature Comparison of MSDE and SQL Server 2005 Express
Upgrading MSDE to SQL Server 2005 Express
Performing Configuration Changes
Deploying SQL Server 2005 Express Applications
Conclusion

Introduction

SQL Server 2005 Express is the free version of the Microsoft SQL Server 2005 database. It replaces MSDE (Microsoft SQL Server 2000 Desktop Engine), which was based on Microsoft SQL Server 2000. Organizations that have developed MSDE applications will find it easy to move to SQL Server 2005 Express. SQL Server 2005 Express includes the new SQL Server 2005 database engine enhancements and a new graphical user interface (GUI) tool called SQL Server 2005 Management Studio Express Edition. SQL Server 2005 Express is integrated with Microsoft Visual Studio, which facilitates the design and development of database applications.

Like MSDE, SQL Server 2005 Express is designed to provide an easy-to-use database platform that can be freely distributed with your applications. It is easy to move your MSDE applications to SQL Server 2005 Express. SQL Server 2005 Express has a number of important advantages over MSDE, such as visual configuration tools, XML data support, and simplified deployment of applications. These changes have resulted in a number of important differences between SQL Server 2005 Express and MSDE.

This white paper discusses the new SQL Server 2005 Express features that are important to MSDE users. It shows you some workarounds to compensate for the handful of features that were removed from SQL Server 2005 Express. In addition, it shows you how to upgrade your existing MSDE installation to SQL Server 2005 Express and presents criteria to help you decide when to upgrade your MSDE installation to other editions of SQL Server 2005.

SQL Server 2005 Express is built on the same core database engine as the other editions of the SQL Server 2005 product line. It has the same essential data types and programmatic features as the other editions of SQL Server 2005, giving it some important advantages over MSDE. Some of these advantages are:

  • Increased maximum database size

  • No workload governor

  • SQL Server 2005 Management Studio Express Edition

  • CLR integration

  • New data types

  • User instance support

  • Reporting Services

  • SQL Service Broker

  • Full text search

  • Enhanced security

System Requirements for SQL Server 2005 Express

Following are the hardware and software requirements for SQL Server 2005 Express.

Hardware
SQL Server 2005 Express runs on the same hardware as MSDE. Recommended system requirements are as follows:

  • Processor — A minimum of a 600-MHz processor is required and a 1-GHz processor is recommended.

  • RAM — A minimum of 256 MB of RAM is required and 512 MB of RAM is recommended.

  • Disk Space — A minimum of 170 MB of free disk space is required, with the Microsoft .NET Framework V2.0 as a prerequisite.

    Note:  You can run SQL Server 2005 Express as a 32-bit application on the 64-bit Microsoft Windows XP Professional x64 Edition or the Microsoft Windows Server 2003 x64 editions. Windows on Windows 64 (WOW64) support enables running 32-bit SQL Server 2005 Express on 64-bit machines. SQL Server 2005 Express will not install on IA64 machines.

Software
SQL Server 2005 Express is supported on the following Microsoft operating systems:

  • All editions of Microsoft Windows 2000 with Service Pack 4

  • All editions of Microsoft Windows XP with Service Pack 2

  • All editions of Microsoft Windows Server 2003 Service Pack 1

  • All editions of Microsoft Windows Small Business Server 2003 Service Pack 1

    Note:  SQL Server 2005 Express also requires the installation of the .NET Framework 2.0 and the presence of Internet Explorer with Service Pack 1. The .NET Framework 2.0 redistributable file is named Dotnetfx.exe. This file is a 22-MB download available at https://go.microsoft.com/fwlink/?LinkId=47248.

Feature Comparison of MSDE and SQL Server 2005 Express

While the core database capabilities of MSDE and SQL Server 2005 Express are similar, the set of features and database capabilities of MSDE and SQL Server 2005 Express are different. The following table provides a feature comparison between MSDE and SQL Server 2005 Express.

Feature

MSDE

SQL Server 2005 Express Edition

Maximum number of instances

16

16

Maximum # of processors

2

1

Maximum RAM

2 GB

1 GB

Maximum database size

2 GB

4 GB

Workload governor

Yes

No

Graphical management tool

No

Yes

User instances

No

Yes

SQL Agent

Yes

No

DTS runtime

Yes

Yes (Web download)

Replication

Merge only

Merge subscription
Snapshot subscription
Transactional subscription

BI features (Analysis Services, Integration Services)

No

No

Report Server

No

Yes (Installed with SQL Server 2005 Express with Advanced Services)

Service Broker

No

Client only

Full-text search

No

Yes (Installed with SQL Server 2005 Express with Advanced Services)

Windows 9x support

Yes

No

MDAC Required

Yes

No

Business Intelligence Development Studio (BIDS)

No

Yes (Installed with SQL Server 2005 Express Toolkit)

Key advantages of SQL Server 2005 Express over MSDE

Because it is built on the new SQL Server 2005 relational database engine, SQL Server 2005 Express provides some important advantages over MSDE, which is based on the older SQL Server 2000 technology. In addition to the core database engine enhancements that SQL Server 2005 Express inherits from the SQL Server 2005 family, SQL Server 2005 Express has been redesigned; some core database capabilities and characteristics have changed.

Increased maximum database size

One of the most important advantages that SQL Server 2005 Express has over MSDE is the increased database size. MSDE is limited to a maximum database size of 2 GB. SQL Server 2005 Express effectively doubles this by bumping up the maximum database size to 4 GB. It's important to note that the 4-GB limitation is per database not per instance. For example, you can have multiple 4-GB databases on one SQL Server 2005 Express instance, all of which can be online simultaneously.

No workload governor

Another important advantage that SQL Server 2005 Express has over MSDE is that it does not have the MSDE workload governor. The MSDE query governor is a widely misunderstood MSDE feature that was intended to limit the type of workloads that MSDE could be used for. The MSDE query governor throttles performance by stalling a user connection for a few milliseconds each time the connection requests a read or write operation on a database when there are more than eight concurrent connections.

For more information about the MSDE Query Governor, see The SQL Server 2000 Workload Governer on the Microsoft Developer Network (MSDN).

SQL Server 2005 Express does not include the workload governor. The only performance limitation that SQL Server 2005 Express possesses is that it can access only a single CPU and 1 GB of RAM. This is less than the 2-CPU and 2-GB limitation of MSDE. However, SQL Server 2005 Express provides a much more predictable environment than does the MSDE workload governor. The new SQL Server 2005 Express ceiling of 1 GB of RAM coupled with today's high performance processors provides more than adequate capacity for a large number of concurrent users.

Note:  If SQL Server 2005 Express is running on Windows XP Home, it is limited to five simultaneous connections. If it is running on Windows 2000 or Windows XP Professional, it is limited to 10 simultaneous connections. However, these are limitations of the operating system and not of SQL Server 2005 Express.

SQL Server 2005 Management Studio Express Edition

The graphical user interface tools for SQL Server 2005 Express makes it easy for even novices to use the database functionality in SQL Server 2005 Express. The new SQL Server 2005 Management Studio Express (SSMSE) is available as a separate Web download. It is also included in SQL Server 2005 Express Edition with Advanced Services and the SQL Server 2005 Express Edition Toolkit. SSMSE allows easy database management and query analysis capabilities, and is redistributable with SQL Server 2005 Express. See Figure 1 for an illustration of the SSMSE interface. You can download SSMSE from https://msdn2.microsoft.com/express/bb410792.aspx.

Cc966393.29366_figure1(en-us,TechNet.10).jpg

Figure 1: SQL Server 2005 Management Studio Express.

SQL Server Management Studio Express supports both local and remote connections to SQL Server 2005 Express, as well as to other SQL Server 2005 editions, SQL Server 2000, and MSDE 2000. All database management features, such as creating and modifying databases, tables, views, logins, and users are identical to the corresponding features in the full SQL Server Management Studio available in other editions of SQL Server. This includes the Query Editor tool, which allows users to manage their servers by using Transact-SQL.

CLR integration

One of the key features that SQL Server 2005 Express inherits from the SQL Server 2005 family is the integration of the .NET common language runtime (CLR). The integration of the .NET CLR with SQL Server 2005 Express enables you to create database objects by using any of the .NET Framework languages. Unlike Transact-SQL, which is a procedural language, .NET CLR database objects are built using fully object-oriented languages such as Microsoft Visual Basic and Microsoft Visual C#. SQL Server 2005 Express supports the creation of the following database objects.

  • Stored procedures

  • User-defined functions

  • Triggers

  • User-defined types

  • User-defined aggregates

The new .NET CLR objects can be used exactly like native Transact-SQL database objects. Because the object-oriented .NET Framework languages are compiled and provide modern structure programming operators, they are very good choices for database objects that require complex logic. In addition .NET CLR database objects are a great replacement for extended stored procedures. Unlike extended procedures that run in the same process address space as the SQL Server engine itself,.NET database objects are contained by the .NET CLR. In addition .NET CLR objects can utilize the hundreds of prebuilt methods that comprise the .NET Framework. However, that doesn't mean that you should replace all of your Transact-SQL stored procedures with new CLR-based objects. Transact-SQL is still the best choice for data-centric stored procedures and functions.

By default, CLR support in SQL Server 2005 Express is turned off because not all applications require CLR support. To enable CLR support in SQL Server 2005, you can use the SQL Server 2005 Surface Area Configuration tool.

To enable CLR support

  1. Open the Start menu. Select All Programs, then SQL Server 2005, then Configuration Tools, then SQL Server Surface Area Configuration.

  2. Select Surface Area Configuration for Features.

  3. From the Select a component, and then configure its features list, select CLR Integration.

  4. Select the Enable CLR integration check box and click OK.

New data types

Another important advantage over MSDE that SQL Server 2005 Express inherits from SQL Server 2005 is support for the new SQL Server 2005 data types. SQL Server 2005 has a new XML data type as well as a new varbinary(max) data type. The new XML data type provides support for typed and untyped XML documents. In both cases, the SQL Server relational database engine ensures that the data inserted into the XML data type is well-formed XML. In the case of typed XML documents, SQL Server will only store the XML data if it complies with the associated XSD (Extensible Schema Definition).

Like the earlier image data type that is supported by SQL Server 2000 and MSDE, the new varbinary(max) data type can store up to 2 GB of data and is primarily intended to store large binary objects like bitmaps, jpegs, and other large binary objects. However, unlike the older image data type where the application needed to use an entirely different data access mechanism to read and write the binary data, the new varbinary(max) data type enables applications to access the data stored in the same way as the other SQL Server data types.

User instance support

Another important advantage that SQL Server 2005 Express has over MSDE is support for user instances. MSDE supports only server instances—it does not provide support for user instances. User instances simplify the ability to deploy database applications that are built on the .NET Framework. A user instance is similar to a normal server instance. However, user instances are created on demand unlike standard SQL Server instances, which are created only during setup. The user does not need to be an administrator to dynamically attach to the database. A user instance is created when the User Instance keyword is used as a part of the Native SQL Client connection string. In addition, the AttachDBFileName keyword is used to specify the name and path of the SQL Server .mdf database file that is enabling the application to connect to the target database. The following connection string illustrates using the user instance support in SQL Server 2005 Express.

"Data Source=.\SQLExpress;integrated security=true;
attachdbfilename=|DataDirectory|\mydb.mdf;
user instance=true;"

The application connects to a user instance of SQL Server 2005 Express that is running as the user who opened the connection. This is a feature only available in SQL Server 2005 Express.

Reporting Services

Like MSDE, SQL Server 2005 Express does not possess business intelligence (BI) features such as Analysis Services, Integration Services, or data mining. However, SQL Server 2005 Express does provide support for Reporting Services. While the initial version of SQL Server 2005 Express did not include support for Reporting Services, it is now available via separate downloads. SQL Server 2005 Express Edition with Advanced Services provides the Report Server. The SQL Server 2005 Express Toolkit provides the Business Intelligence Development Studio.

SQL Server 2005 Express can act as a data source for relational data that is on your local server for Reporting Services. Plus, Reporting Services in SQL Server 2005 Express can render reports in Excel, PDF, Image, Print, and DHTML formats. The Report Manager utility in SQL Server 2005 Express manages reports. Reporting Services reports used in SQL Server 2005 Express are completely compatible with the Reporting Services in the other SQL Server 2005 editions.

SQL Service Broker

A new SQL Server 2005 development feature that is supported by SQL Server 2005 Express and has no counterpart in MSDE is support for the new SQL Server Service Broker. The SQL Server 2005 Service Broker is an application development framework that supports the creation of asynchronous messaging applications. SQL Server 2005 Express can act as a client to SQL Server Service Broker applications running on the other editions of SQL Server 2005. This is useful in scale-out scenarios where multiple applications running on a distributed set of SQL Server 2005 Express systems all connect into a central SQL Server 2005 system running one of the other editions of SQL Server 2005. It should be noted that SQL Server 2005 Express also supports intra-instance SQL Server Service Broker messaging, which can be used to replace some of the job scheduling functionality that SQL Agent provides for MSDE and the other editions of SQL Server 2005. You can find more information on using SQL Server Service Broker to manage tasks at https://www.microsoft.com/technet/technetmag/issues/2005/05/ServiceBroker/default.aspx on Microsoft TechNet.

Full-text search

Support for full-text search is another feature that wasn't present in the initial release of SQL Server 2005 Express. However, it is available in the SQL Server 2005 Express Edition with Advanced Services download. As in the other editions of SQL Server 2005, the full-text search feature of SQL Server 2005 Express allows fast and flexible indexing for keyword-based queries of text data stored in a SQL Server database. Unlike the LIKE predicate, which only works on character patterns, full-text search operates on words and phrases based on the rules of a particular language. Full-text search indexes can be built on columns using the char, varchar, nvarchar, varbinary(max) and image data types. However, one limitation of full-text search with SQL Server 2005 Express is that it doesn't work in user instances.

Enhanced security

Another important advantage that SQL Server 2005 Express has over MSDE is the enhanced security that is built into the product. SQL Server 2005 Express is installed securely by default. Network connections are disabled. Likewise, support for the CLR is turned off. You need to explicitly enable these features in order to use them. In addition, like the other members of the SQL Server 2005 product line, SQL Server 2005 Express has the ability to encrypt database data, thereby ensuring that sensitive data can be accessed only by users who have the appropriate permissions. SQL Server 2005 Express supports both symmetric and asymmetric data encryption.

MSDE features not included in SQL Server 2005 Express

SQL Server 2005 Express has features that are not present in MSDE, such as Robust Setup UI, CLR support, GUI tools, and Visual Studio integration. However, some features that are present in MSDE are not in SQL Server 2005 Express.

Windows 9x support

MSDE is built on SQL 2000 technology and can be used with Windows 9x platforms. SQL Server 2005 Express is built on SQL Server 2005 technology and requires a Windows platform of at least Windows 2000 with SP4. However, MSDE applications running on older version of Windows cannot take advantage of the latest operating system enhancements and security patches. Likewise, they won't be able to utilize new security features such as the Windows Firewall.

DTS runtime

The Data Transformation Services (DTS) runtime, included with MSDE, is not included with SQL Server 2005 Express. The DTS runtime allows SQL Server 2005 Express to run existing DTS packages. Though not built-in to SQL Server 2005 Express, the DTS runtime components can be downloaded and installed along side of SQL Server 2005 Express. You will find the DTS runtime installer on the Microsoft Download Center at https://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc.

SQL Agent

SQL Agent is a SQL Server service that is used to automatically schedule job execution for SQL Server. It is typically used to schedule the execution of DTS packages or to perform system maintenance tasks like database backups. While MSDE includes the SQL Agent service, SQL Server 2005 Express does not. However, you can use the Windows built-in Task Scheduler to schedule jobs for SQL Server 2005 Express. You can use Task Scheduler in combination with the downloadable DTS runtime to automatically schedule the execution of DTS packages. Likewise, you can use Task Scheduler in combination with the SQL Server 2005 Express command-line SQLCMD tool to regularly execute SQL Server 2005 Express system maintenance jobs like database backups and other database access jobs.

Replication publication

SQL Server replication allows users to maintain copies of data at multiple sites using a publisher-subscriber model. SQL Server 2005 Workgroup Edition and higher support three basic types of database replication; snapshot, transactional, and merge. Like its name suggests, snapshot replication works by sending a periodic copy of the data from the publisher to the subscriber(s). Transactional replication is designed to support near real-time update scenarios where individual transactions are sent from the publisher to the subscriber(s). Merge replication is intended to support disconnected scenarios where batches of database updates are merged together and a set of rules is used to resolve possible data update conflicts. MSDE can act as both a publisher and subscriber to merge replication. However, it doesn't support the other SQL Server replication scenarios. While SQL Server 2005 Express cannot act as a replication publisher, it does support subscriptions to merge, snapshot, and transactional publications, which is a more common requirement. For those rare implementations that require the ability to act a publisher, consider upgrading MSDE to SQL Server 2005 Workgroup Edition.

Upgrading MSDE to SQL Server 2005 Express

Upgrading from MSDE to SQL Server 2005 Express is a straightforward process. However, before you begin, there are some important considerations that you need to review as they determine the correct upgrade path for your MSDE installation.

Upgrade considerations

While the number of upgrade issues that the database administrator must contend with when upgrading MSDE is small, the following upgrade issues must be reviewed, understood, and accounted for in any MSDE upgrade plan:

  • Number of MSDE instances

  • MSDE install method

  • MSDE language

Number of MSDE instances

The computer on which you are intended to perform a database upgrade from MSDE to SQL Server 2005 Express may have multiple instances of MSDE, and may also have instances from other editions of SQL Server 2000 or SQL Server 7.0. Each instance must be upgraded separately and each instance might have a different set of upgrade requirements. For example, the different instances might use different languages or collation orders, or be installed by using a different installation method.

The first step in upgrading from MSDE to SQL Server 2005 Express is to determine the number of instances of MSDE that are installed. Up to 16 instances of MSDE can be installed on a single system. To determine the number of MSDE instances on your system, review the following registry key to determine the installed instances:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQLServer\InstalledInstances

Each entry under the InstalledInstances key represents a separate MSDE instance.

MSDE installation method

The methodology that was used to install an MSDE instance affects the available upgrade method for the MSDE instance being upgraded.

  • MSI Setup — If an MSDE instance was installed with an MSI setup, the SQL Server 2005 Express installation program will detect the instance and you can perform the upgrade by using the in-place upgrade method.

  • Merge Modules — If an MSDE instance was installed using merge modules, the SQL Server 2005 Express installation program will not detect the instance and you will not be able to perform the upgrade by using the in-place upgrade method. When MSDE is installed using merge modules, MSDE is actually installed as a part of another application that makes use of MSDE and MSDE is not installed using the MSDE standalone installer.

To determine if a MSDE instance has been installed with an MSI setup, go to Add or Remove Programs in Control Panel. If the instance appears in the list, it was installed with an MSI setup. If it does not appear in the list, it was installed as part of an application and should be removed by that application's installer program.

Important:  Although an instance of MSDE installed using merge modules cannot be upgraded in-place, its user databases can be upgraded individually by detaching and attaching the database as described later in this paper.

MSDE language

The language of the MSDE installation determines the language of the SQL Server 2005 Express you are upgrading to. If you are performing an in-place upgrade by installing SQL Server 2005 Express over the top of an existing MSDE installation, the language of SQL Server 2005 Express must be the same language of the MSDE installation or it must be set to English.

Note:  You can detach a user database from an MSDE instance and attach it to a SQL Server 2005 Express instance installed with any language.

Performing an in-place upgrade

For MSDE installations that were installed by using the MSDE setup program (which creates an MSDE entry in the Add/Remove Programs list), performing an in-place upgrade is the recommended upgrade procedure. The in-place upgrade automatically replaces all of the MSDE components and it also automatically upgrades all user databases.

To perform an in-place upgrade from MSDE to SQL Server 2005 Express

  1. Download and install Windows Installer 3.1.

    Windows Installer 3.1 is required by SQL Server 2005 Express. You can download Windows Installer 3.1 from the Microsoft Download Center. After you have downloaded Windows Installer 3.1, install it by running the WindowsInstaller-KB893803-v2-x86.exe program (if Windows Installer 3.1 is already present, the install will detect and abort). This install requires a system reboot.

  2. Download and install the .NET Framework 2.0.

    The .NET Framework 2.0 is a prerequisite to SQL Server 2005 Express. You can download it from: https://go.microsoft.com/fwlink/?LinkId=47248. After downloading the .NET Framework 2.0, install it by running the dotnetfx.exe program.

  3. Start the SQL Server 2005 setup program and install the prerequisite software.

    SQL Server 2005 Express is installed by running SQLEXPR.EXE. Or, to install SQL Server 2005 Express Edition with Advanced Services, run SQLEXPR_ADV.EXE. The prerequisite Microsoft SQL Native Client and Microsoft SQL Server 2005 Setup Support files are installed and the setup program copies and installs all supporting files on the target system.

  4. Perform the system configuration checks.

    The setup program runs the system configuration checks before it begins to verify that the system meets the minimum criteria for installation and detects any pending reboot requirements.

    If your system fails the configuration tests, click the failed link for more information and then take the corrective action required.

  5. Determine if advanced configuration options should remain hidden.

    By default, advanced dialog boxes, such as those for setting collations and service accounts, are hidden for user simplicity. Clear the Hide Advance Configuration check box (shown in Figure 2) if you want to change the advanced configuration dialog boxes.

    Cc966393.29366_figure2(en-us,TechNet.10).jpg

    Figure 2: Setup: Advanced Options.

    Important:  By default, Setup uses the instance name of SQLEXPRESS to install, rather than the MSDE default of the host computer name. If you wish to change the instance name to the name of the host computer, click the Advanced button, which causes the setup program to detect all instances that have been installed by using the MSI installation method. If you wish to upgrade a previously installed MSDE instance, click Installed Instance and select the MSDE instance that you wish to upgrade.

  6. On the Feature Selection page, specify the features to install.

    By default, several features on this page are turned off, so you must explicitly choose the components you want to install. For example, client components are not installed by default. Client components are not required for standalone databases but they are recommended for development systems or systems that will access other database systems.

  7. On the Instance Name page, select the appropriate instance to upgrade.

    Instance Name

    On the Existing Components page (Figure 3), select the SQL Server Database Services 8.00.761 check box and then click Next.

    Cc966393.29366_figure3(en-us,TechNet.10).jpg

    Figure 3: Upgrading Existing Components.

  8. Specify the logon information for Setup to use to connect to the instance that is being upgraded.

    Generally, you will select the default option of Windows Authentication.

  9. Specify the remaining configuration options (generally accept all defaults).

  10. In the Ready to Install dialog box, click Install.

    This upgrades the specified instance of MSDE to SQL Server 2005 Express.

Verify the SQL Server 2005 Express installation

Use Configuration Manager to verify that the upgraded instance is running. To start Configuration Manager, on the Start menu, open the Microsoft SQL Server 2005 program group, select Configuration Tools, and double-click SQL Server Configuration Manager. Within SQL Server Configuration Manager, open the SQL Server 2005 Services node and check for an entry upgraded instance to verify that it has a status of running. If the SQL Server service is not running, you can manually attempt to start it by right-clicking the entry and selecting Start from the context menu. If the service will not start, the installation was not successful and will need to be redone

Performing a side-by-side upgrade

While an in-place upgrade is the easiest method to use to upgrade from MSDE to SQL Server 2005 Express, there are situations where performing an in-place upgrade is not possible.

  • Cases where you cannot perform an in-place upgrade - You cannot perform an in-place upgrade when MSDE has been installed using merge modules or if you want to change languages or collation. In these cases, upgrades must be performed by using a side-by-side installation where SQL Server 2005 Express is installed at the same time as MSDE. To perform a side-by-side upgrade, the MSDE databases are detached, the MSDE instance is uninstalled, a new copy of SQL Server 2005 is installed, and then the databases are re-attached to the new instance.

    Note:  In this document, we refer to the detachment of databases, removal of the MSDE instance, and the re-attachment of its user databases to a new SQL Server 2005 Express instance as a side-by-side upgrade.

To upgrade from MSDE to SQL Server 2005 Express where you cannot or do not want to perform an in-place upgrade

  1. Log on to the MSDE system as an administrator and verify that the instance of MSDE that you wish to upgrade is running.

  2. Check for a green arrow on the SQL Server icon in the System tray.

  3. Open a command prompt and use osql to connect to the instance you wish to upgrade.

    To connect to the local, default instance of MSDE using Windows Authentication, use the following command:

    osql -E

    To connect to a named instance, use the -S switch and specify the instance name as shown below to connect to the desired named instance.

    osql -E -S servername\instancename
  4. List all of the databases on the MSDE instance by using the following commands at the osql prompt:

    1> SELECT name FROM master.dbo.sysdatabases WHERE DBID > 4
    2> GO

    This lists all of the user databases on the MSDE instance.

  5. Detach each of the user databases on the MSDE instance by entering the following command at the osql command prompt:

    1> EXEC sp_detach_db 'database_name'
    2> GO

    This takes each of the user databases offline. Replace the value of database_name with the name of the databases that you want to move from MSDE to SQL Server 2005 Express. The databases will later be attached to the new SQL Server 2005 Express instance.

  6. Exit the osql utility by entering the following command at the osql command prompt:

    1> exit
  7. Shut down MSDE by opening SQL Server Service Manager on the System Tray. In the Services drop-down list, select the SQL Server service, click Stop, and then click Yes.

    Note:  You can also use the Services application or the NET STOP command to stop the MSDE instance.

  8. Repeat for the Distributed Transaction Coordinator and the SQL Server Agent services (if they are running).

  9. Remove MSDE by opening Add/Remove Programs from the system's Control Panel, selecting the entry named Microsoft SQL Server Desktop Engine and clicking Remove.

    Note:  If MSDE was installed as a part of another application, it will not appear in the Add/Remove programs list. In this case, remove MSDE by using that application's installation program.

    Note:  This step can be skipped until a later time if you are installing SQL Server 2005 Express to a different instance name than the instance of MSDE that is being upgraded.

  10. Download and install Windows Installer 3.1.

    Windows Installer 3.1 is required by SQL Server 2005 Express. You can download Windows Installer 3.1 from the Microsoft Download Center. After you have downloaded Windows Installer 3.1, install it by running the WindowsInstaller-KB893803-v2-x86.exe program (if Windows Installer 3.1 is already present, the install detects this and aborts). This install requires a system reboot.

  11. Download and install the .NET Framework 2.0.

    The .NET Framework 2.0 is a prerequisite to SQL Server 2005 Express and it can be downloaded from the Microsoft Download Center. After downloading the .NET Framework 2.0, install it by running the dotnetfx.exe program.

  12. Install SQL Server 2005 Express by running the SQLEXPR.EXE or install SQL Server 2005 Express Edition with Advanced Services by running SQLEXPR_ADV.EXE.

    Select the appropriate installation options for the new instance you are installing, including the instance name if you wish to specify a name other than SQLEXPRESS.

  13. After SQL Server 2005 Express is installed, start sqlcmd by opening a command prompt and typing the following command and then pressing ENTER:

    sqlcmd -E

    This connects you to the local, default instance of SQL Server 2005 Express using Windows Authentication. If you wish to connect to a named instance, use the -S switch and specify the instance name as shown in the following.

    sqlcmd -E -S servername\instancename
  14. Attach each of the user databases that were detached from the MSDE instance by entering the following command at the sqlcmd command prompt:

    1> EXEC sp_attach_db 'database' , 'C:\Program Files\Microsoft SQL 
    Server\MSSQL\Data\database_data_filename.mdf', 'C:\Program 
    Files\Microsoft SQL Server\MSSQL\Data\database_log_filename.LDF'
    2> go

    Replace the values of database_data_filename and database_log_filename with the names of the database files from the user database that was detached from your previous MSDE installation. This example shows the default installation path that is used by MSDE. If your installation used a custom path, then you can substitute the correct path value. Repeat for each detached user database.

  15. Exit the sqlcmd utility by typing exit and pressing ENTER.

  16. Enable any needed protocols as described in the Configuration Changes section.

Upgrading MSDE to different editions of SQL Server 2005

While the core database capabilities of MSDE and SQL Server 2005 Express are similar, the features sets and limitations are different. It's possible that either these differences or the projected requirements for features outside of the SQL Server 2005 Express feature set could cause you to select a different edition of SQL Server 2005. The following table compares the major features in each SQL Server 2005 edition.

Scalability and Performance

Feature

Express

Workgroup

Standard

Enterprise

Number of CPUs

1

2

4

No Limit

*Includes support for multicore processors

 

 

 

 

RAM

1 GB

3 GB

O/S maximum

O/S maximum

64-bit support

WOW

WOW

Yes

Yes

Database size

4 GB

No limit

No limit

No limit

High Availability

Database mirroring

No

No

Yes

Yes

*Includes fast failover and automatic client redirection

 

 

 

 

Failover clustering

No

No

Yes

Yes

Backup log shipping

No

Yes

Yes

Yes

Integration and Interoperability

Import/export

No

Yes

Yes

Yes

Integration Services - basic transformations

No

No

Yes

Yes

*Includes graphical extract, transform, and load(ETL)

 

 

 

 

Integration Services - advanced transformations

No

No

No

Yes

*Includes data mining, text mining, and data cleansing.

 

 

 

 

Merge replication

Subscriber only

Yes

Yes

Yes

Transactional replication

Subscriber only

Yes

Yes

Yes

Business Intelligence

Report Server

Available in Advanced Services Edition and as a Web download

Yes

Yes

Yes

Report Builder

No

Yes

Yes

Yes

Reporting Data Sources

Relational only

Yes

Yes

Yes

BI Development Studio

Report Designer only

Report Designer only

Yes

Yes

Analysis Services

No

No

Yes

Yes

The following discussion will help you to determine if you need to upgrade to a different edition of SQL Server 2005.

Upgrading to SQL Server 2005 Workgroup Edition

SQL Server 2005 Workgroup Edition is the next step up in the SQL Server 2005 product line from SQL Server 2005 Express. SQL Server 2005 Workgroup Edition is designed to act as an entry-level database server for small businesses. It supports more memory and processors than does SQL Server 2005 Express and has no limits on database size. SQL Server 2005 Workgroup includes the full SQL Server Management Studio in addition to the other SQL Server database management tools including SQL Agent, SQL Profiler, and the Import/Export Wizard. SQL Server 2005 Workgroup Edition can also perform backup log shipping for greater availability and can act as a replication publisher and subscriber for snapshot, transactional, and merge replication.

Upgrading to SQL Server 2005 Workgroup Edition is a compelling option for the following scenarios.

RAM requirements beyond the level supported by SQL Server 2005 Express
MSDE supports up 2 GB of RAM and two processors while SQL Server 2005 Express only supports 1 GB of RAM and a single processor. While rare, some MSDE applications need more that 1 GB of RAM. If this is your case, you should consider upgrading to SQL Server 2005 Workgroup edition.

To quickly check MSDE RAM usage

  1. Press CTRL+ALT+DEL.

  2. Open Task Manager.

  3. Check the Mem Usage column for the sqlservr.exe process.

Processor requirements beyond the level supported by SQL Server 2005 Express
While MSDE supports two processors compared to the single processor supported in SQL Server 2005 Express, it is unlikely that this would necessitate a move to SQL Server 2005 Workgroup edition. In most cases, it may be more cost effective to upgrade to a higher performance processor. SQL Express also supports multi-threading and multi-core processors.

Database size requirements beyond the level supported by SQL Server 2005 Express
Another reason that you might consider moving to SQL Server Workgroup Edition rather than to SQL Server 2005 Express is to support databases that are larger than 4 GB. For the vast majority of upgrade scenarios, this will not be the case as the SQL Server 2005 Express 4 GB database capacity doubles the 2 GB maximum database capacity provided by MSDE. However, in unusual cases where projected requirements exceed 4 GB, SQL Server Workgroup Edition with its unlimited database capability would be the preferred upgrade path.

Requirements for advanced database functionality
SQL Server Workgroup would be the best upgrade choice for cases where the following advanced database functionality is required:

  • SQL Agent. Like MSDE, SQL Server Workgroup Edition includes the SQL Agent component that enables the database to automatically execute jobs.

  • Import/Export Wizard. Like SQL Server 2005 Express, SQL Server Workgroup Edition does not include the full SQL Server Integration Services functionality. However, it does include the basic Import/Export Wizard.

  • Replication publishing. SQL Server 2005 Express can only act as a replication subscriber. SQL Server Workgroup Edition can act as publisher for both transactional and merge replication.

Upgrading to SQL Server 2005 Standard Edition

One reason you might consider upgrading from MSDE to SQL Server 2005 Standard Edition is if you predict that your future database requirements will exceed the capabilities or feature set that is available in SQL Server 2005 Express or SQL Server 2005 Workgroup Edition. Consider upgrading to SQL Server 2005 Standard Edition if your future database requirements could exceed 3 GB of RAM, you need a database size larger than 4 GB, or you need up to four processors for your applications.

You might also consider upgrading to SQL Server 2005 Standard Edition from MSDE if you need business intelligence features. Unlike SQL Server 2005 Express or SQL Server 2005 Workgroup Edition, SQL Server 2005 Standard Edition offers the complete BI feature set, including Analysis Services, Integration Services, Notification Services, and Reporting Services.

Upgrading to SQL Server 2005 Enterprise Edition

While most MSDE users will not need to migrate their applications to SQL Server 2005 Enterprise Edition, since MSDE databases are fully compatible with SQL Server 2005 Enterprise Edition, it is possible to do so. SQL Server 2005 Enterprise Edition offers scalability, availability, and business intelligence features that go beyond the other editions of SQL Server 2005. For instance, SQL Server 2005 Enterprise Edition has no limit to the number of CPUs that it supports, it supports partitioning tables for very large databases, and offers online page and file restore for increase enterprise availability as well as advanced data mining algorithms. Migrating from MSDE to SQL Server 2005 Enterprise Edition would only be required for applications that need the highest levels of scalability or advanced functionality.

Performing Configuration Changes

With MSDE, in order to set up or change configuration options such as enabling network access, you must either run the setup program along with a series of command-line parameters or else manually change the registry. To facilitate configuration changes, SQL Server 2005 Express includes the graphical SQL Server Configuration Manager. SQL Server Configuration Manager enables you to view and manage the different configuration options of SQL Server 2005 Express.

Enabling network protocols

The default installation for SQL Server 2005 Express enables shared memory, which permits local access only; the named pipes and TCP/IP protocols are disabled. If your database installation requires network access, use the following procedure.

To enable network access

  1. Start SQL Server Configuration Manager.

  2. Expand the SQL Server 2005 Network Configuration node.

  3. Select Protocols for SQLEXPRESS.

  4. Enable the required protocols by right-clicking the protocol and selecting Enable from the context menu, as shown in Figure 4.

    Cc966393.29366_figure4(en-us,TechNet.10).jpg

    Figure 4: Network Configuration.

Enabling the TCP/IP or named pipes protocols opens up SQL Server 2005 Express for network access. However, depending on the environment there are some additional considerations. First, the SQL Server Browser service needs to be enabled. The SQL Server Browser service identifies which network ports the SQL Server 2005 Express instance listens on.

To change the SQL Server Browser service startup type to automatic

  1. You can start the SQL Server Browser service by using the SQL Server 2005 Surface Area Configuration utility. Open the Start menu and then select All Programs, SQL Server 2005, Configuration Tools, SQL Server Surface Area Configuration.

  2. From the SQL Server 2005 Surface Area Configuration utility, select Surface Area Configuration for Services and Connections.

  3. From the Select a component, and then configure its services and connections list, select SQL Server Browser.

  4. Change the Startup Type to Automatic, then click Start.

In addition, if the system running SQL Server 2005 Express is using a firewall you need to configure the firewall to allow connections to SQL Server 2005 Express. The default TCP port for SQL Server 2005 Express is 1433. The SQL Server Browser listens on UDP port 1434. For more information about using SQL Server 2005 Express with a firewall, see Knowledge Base article KB914277, How to configure SQL Server 2005 to allow remote connections.

Service accounts

When you install SQL Server 2005 Express, you can specify how the SQL Server 2005 Express service will be started—either by using the Network Service account or by selecting a Windows domain user account. This is applies to all operating systems except Windows 2000 where the Local Service account is used. The Local Service account is also the default choice for MSDE.

The Network Service account is more secure than the Local Service account. The Local Service account possesses administrator-like authority. However, as a safeguard to limit exposure to this account from unauthorized access, the Local Service account has restricted network connectivity. In contrast, the Network Service account used by SQL Server 2005 Express has a much lower level of privilege, making it more secure while still providing network access privileges. If you are installing a SQL Server 2005 Express instance that will be accessed by multiple networked users, then SQL Server 2005 Express should be run using a Windows domain account. This enables you to have more explicit control of the security settings used by the SQL Server 2005 Express database service.

Deploying SQL Server 2005 Express Applications

Deploying applications has been made easier with SQL Server 2005 Express. With MSDE embedded applications, deploying the applications entailed a three step process:

  • Creating an MSI package.

  • Merging the MSDE merge modules

  • Running Setup to install the custom application and MSDE

The use of merge modules for deployment has been a problem in MSDE. SQL Server 2005 Express does not use this functionality. Instead, SQL Server 2005 Express contains a setup executable that you can bundle with the custom application. The executable accepts command-line parameters, thereby allowing you to customize the installation options.

Command-line installation

In addition to installing SQL Server 2005 Express by using the graphical setup program, you can also install it from the command line. Installing SQL Server 2005 Express from the command line enables you to explicitly control the various setup options. You can call the executable and pass in a series of parameters on the command line. Or, you can configure the parameters in a single file named template.ini, and then pass the name of the template.ini file as a command-line parameter to the setup.exe executable.

A few of the important command-line parameters that can be specified for the SQL Server 2005 Express installation include the following parameters.

ADDLOCAL — This is a required parameter and specifies which SQL Server 2005 Express components to install. Specifying ADDLOCAL=ALL installs all of the components of SQL Server 2005 Express.

The SQL Server 2005 Express-related components that you can specify are listed in the table.

ADDLOCAL parameter

Description

SQL_Engine

Installs the SQL Server 2005 Express database and SQL Browser services.

SQL_Data_Files

Installs the SQL Server 2005 Express databases, including master, the resource database, and tempdb.

SQL_Replication

Installs the components used for replication support in SQL Server 2005 Express.

SQL_FullText

Installs the components used by full-text search.
(SQL Server 2005 Express with Advanced Services only)

RS_Server

Installs the components used by Reporting Services.
(SQL Server 2005 Express with Advanced Services only)

RS_Web_Interface

Installs the components for Report Manager.
(SQL Server 2005 Express with Advanced Services only)

Client_Components

Installs the components for communication between clients and servers, and network libraries for ODBC and OLE DB. Includes the sqlcmd utility, SQL Server Configuration Manager, and the Surface Area Configuration tool.

Connectivity

Installs SDKs containing resources for model designers and programmers. Includes SQL Server Management Objects (SMO) and Replication Management Objects (RMO).

SDK

Installs the Business Intelligence Development Studio component.

SQL_SSMSEE

Installs the SQL Server Management Studio Express Edition component.
(SQL Server 2005 Express with Advanced Services only)

REMOVE — This parameter specifies which SQL Server 2005 Express components to remove. Similar to ADDLOCAL, specifying REMOVE=ALL completely uninstalls SQL Server 2005 Express.

UPGRADE — This parameter specifies that you are upgrading from MSDE to SQL Server 2005 Express. Because it is possible to have up to 16 instances of MSDE on a single computer, when using the UPGRADE parameter you must specify the name of the MSDE instance you want to upgrade. For example, UPGRADE=SQL_Engine INSTANCENAME=MyMSDE.

To learn more about using the SQL Server 2005 Express command-line installation options see the SQL Server Books Online help topic "How to: Install SQL Server 2005 from the Command Prompt" at https://msdn2.microsoft.com/en-us/library/ms144259.aspx.

ClickOnce deployment

Alternately, you can deploy SQL Server 2005 Express with your custom applications by using the ClickOnce technology found in Visual Studio 2005. ClickOnce is a new feature that is part of the .NET Framework version 2.0. ClickOnce lets you deploy custom applications to a computer by bundling the application files and SQL Server 2005 Express and the .NET Framework 2.0 into a single deployment package.

To deploy an application using ClickOnce, you create your application in Visual Studio 2005. Then select the Publish option from the Build menu. The Publish Wizard steps you through deployment specifications, such as where to deploy the application, how it will be installed (Web site, file share, CD/DVD), and if the application should check for updates. When the Publish Wizard is finished, files, including compressed data files and a setup installer, are placed into your deployment directory, which you can then use to distribute the custom application.

Licensing

Both MSDE and SQL Server 2005 Express are available free to download. SQL Server 2005 Express is available for anyone to download free of charge and you do not have to have a license to another Microsoft product. To redistribute SQL Server 2005 Express with your applications, you need to register for redistribution rights. This is also free of charge, and can be found at the Register for SQL Server Express Edition Redistribution Rights Web page.

To register for SQL Server 2005 Express redistribution rights, you need to:

  • Review the SQL Server 2005 Express Edition Redistribution End-User License Agreement (EULA).

  • Print and retain a copy of the SQL Server 2005 Express Redistribution EULA.

  • Register for SQL Server 2005 Express redistribution rights.

Conclusion

SQL Server 2005 Express goes a step beyond MSDE. The core SQL Server 2005 Express relational database engine goes far beyond the capabilities of MSDE. It supports the same set of feature set as the other members of the SQL Server 2005 family including support for building CLR database objects, the new XML and varbinary(max) data types, support for Reporting Services, a graphical management console, and more. Even more importantly for application developers who want to build applications on top of SQL Server 2005 Express, the database is now easier to understand, install, and deploy than MSDE. The MSDE workload governor is gone, being replaced by much more straightforward system limitations of 1 GB of RAM and a single CPU, making system tuning and capacity planning much easier. Support for user instances and the ability to attach databases via the connection string makes deploying your SQL Express applications a more seamless process than with MSDE. While there are a few features that MSDE possesses that are not in SQL Server 2005 Express, these features were not used by the vast majority of installations and there are workarounds. For example, the DTS runtime, which is not built into SQL Server 2005 Express, can be downloaded from the Microsoft Download Center. With it, SQL Server 2005 Express can run DTS packages.

In addition, when you consider the fact that SQL Server 2005 Express is freely distributable, it is clear that SQL Server 2005 Express is the best choice for upgrading your existing MSDE-based applications.

You can download SQL Server 2005 Express Edition from the Microsoft SQL Server 2005 Express Edition Web site.

Download

DownloadUpgrading MSDE 2000 to SQL Server 2005 Express
371 KB
Microsoft Word file