Upgrading from MSDE to SQL Server Express

Upgrading from MSDE

If you're currently running Microsoft SQL Server Desktop Engine (MSDE), then upgrading to SQL Server 2005 Express Edition is probably one of the high-priority items on your New Year's resolutions list. However, there are a few things you need to know about your MSDE installation before jumping headfirst into the upgrade. Generally, upgrading from MSDE to SQL Server Express is as simple as running the SQL Server Express setup program. However, that’s not always the case, and there are a couple of key considerations that you should be aware of before you begin the process. MSDE can be installed in one of two ways: by using Microsoft’s setup program or by using merge modules. The MSDE installation method you use determines how you should proceed along the upgrade path.

If you installed MSDE by using Microsoft's standalone setup program, the upgrade process is straightforward; you run the SQL Server Express setup program and perform what is called an in-place upgrade. (In-place means the existing copy of the MSDE database files is upgraded in its current location--or "in-place.") In this scenario, your primary consideration is the default instance name that SQL Server Express uses. MSDE and SQL Server use the name of the local computer as the default instance name, but SQL Server Express uses SQLEXPRESS as the default instance name to help differentiate between a SQL Server Express database instance and a standard SQL Server database instance. This scheme makes it easy for developers--they know that applications developed specifically for SQL Server Express will always connect to an instance named SQLEXPRESS. The naming scheme also makes it easier for applications to share the same database instance. The instance name you choose depends primarily on how you use the database and on your access to the application source code. If you use the database as a Microsoft Office back end or you have access to the application’s source code, then you'll probably want to use the SQLEXPRESS default instance name. On the other hand, if you’re using a database application that's locked into a previously defined instance name, you should select the existing named instance during the upgrade process.

If you installed MSDE by using the old merge module technology (which Microsoft removed from SQL Server Express), the upgrade process is very different. In this case, the SQL Server Express setup program can't do an in-place upgrade to the MSDE instance. Instead, you should do the upgrade using what is termed a side-by-side installation. This means that during the installation process, you should detach the databases from the old copy of MSDE, install a new copy of SQL Server Express, then reattach the databases to the new SQL Server Express instance. In this scenario, you typically don’t have access to the application’s source code. Make sure you use a named instance instead of the SQLEXPRESS default instance name when you install SQL Server Express; otherwise, an application might not work because the default instance name most likely won't match the instance name that the existing application expects.

Attaching and Detaching Databases

If you’re upgrading from Microsoft SQL Server Desktop Engine (MSDE) to SQL Server 2005 Express Edition by using the side-by-side upgrade process, you'll need to detach the databases from your old MSDE instance, install SQL Server Express, then reattach the databases to your new SQL Server Express instance. When you detach a database, you're essentially taking it offline so the database engine can't use it, but all of its data remains intact. When you reattach the database, it comes back online.

Detaching and reattaching a database is also a great way to move and copy databases between different SQL Server systems. For example, you can detach a database, copy the data (.mdf) and log (.ldf) files to another system, then reattach the database to make it available on the target system.

SQL Server 2005 provides two stored procedures that you can use to detach and reattach databases: sp_detach_db and sp_attach_db. You can run both procedures from the command-line osql or sqlcmd tools or by using Microsoft SQL Server Management Studio Express.

To detach a database, enter the following command, substituting the name of your database for ‘database_name’:

exec sp_detach_db 'database_name'

To reattach to the database, enter the following command, substituting the name of your database for ‘database_name’:

exec sp_attach_db 'database_name', 'C:Program FilesMicrosoft SQL ServerMSSQLDatadatabase_name.mdf', 'C:Program FilesMicrosoft SQL ServerMSSQLDatadatabase_name.LDF'

When you attach to a database, you need to tell SQL Server the name and location of all the data (.mdf) and log (.ldf) files.

Product Highlight: Microsoft’s E-Learning Center

Education is the key to learning how to take full advantage of SQL Server 2005 (or SQL Server 2005 Express Edition, for that matter), and Microsoft’s E-Learning center is a great place to start. Better yet, all of the SQL Server 2005 online courses are free until November 1, 2006.