SQL Server 2005 Express Edition - Part 23 - Manual Upgrade from Microsoft SQL Server Desktop Engine (MSDE)

May 22, 2008

In the previous installment of our series dedicated to topics related to SQL Server 2005 Express Edition, we have started discussion about migrating from MSDE-based installations. As we have explained, in many cases, this process can be performed by running an in-place upgrade of individual instances (either via GUI-based setup wizard or in unattended manner). Frequently, however, such an approach is not possible due to a mismatch of certain configuration settings (such as product code, language, or collation), forcing you to resort to one of several manual workarounds. In this article, we will review these scenarios in more detail, as well as point out additional factors that should be considered during migration to SQL Server 2005 Express Edition.

In general, if you encounter a situation in which the automatic process is not feasible, there are two approaches you can consider (in either case, you need to ensure that all basic prerequisites, such as .NET Framework 2.0, or Windows Installer 3.1, are satisfied). The first one involves a side-by-side upgrade and consists of the following steps:

• locating the source MSDE instance and its user databases - As mentioned earlier, the standard setup (based on .MSI files) appends names of installed instances to REG_MULTI_SZ value of InstalledInstances entry residing under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server registry key. To identify others, deployed via applications employing MSDE merge modules, you might have to refer to their documentation or search for them by searching through the registry (focusing in particular on the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server registry key) or examining a list of running services. To determine which user databases are hosted by the instance to be upgraded, execute SELECT NAME FROM sysdatabases WHERE dbid > 4 T-SQL statement using the osql.exe command line-based utility. If the databases are participating in replication, disable their respective configuration settings.
• detaching user databases from the MSDE instance - Once you have identified each of the user databases that you intend to migrate, take note of the name and location of their .mdf and .ldf files (this information can be retrieved by checking the output of the sp_helpdb stored procedure). Next, detach them from the MSDE instance by running (via osql.exe command line utility) the following sequence of T-SQL statements (with the assumption that we connect to the instance instance_name on the server server_name via Windows authentication and our database is called database_name):

osql -E -S server_name\instance_name

EXEC sp_detach_db 'database_name'

GO

• uninstalling MSDE instance - After you have detached all user databases from the source MSDE instance and completed its shutdown (this can be done by stopping all of its services - including database engine, SQL Server Agent, and Distributed Transaction Coordinator - leveraging Services MMC snap-in or net stop command), you are ready for the next step. Its implementation depends primarily on the installation method. If the initial setup was performed using Windows Installer merge modules embedded into a third-party application, you should be able to take advantage of its uninstaller program (available from the Add/Remove Programs applet in the Control Panel). If such functionality is not available, you might have to seek assistance from its vendor (whose development team would need to provide a custom procedure to remove their software along with any associated MSDE instances). In cases where the original setup was based on one of MSDE Windows Installer .MSI packages, it should be possible to remove any of the local instances directly via the Add/Remove Programs Control Panel applet. Alternatively, if you want to automate this process, you can invoke the Setup executable with a parameter, whose value matches the installation .MSI file or corresponding Product Code GUID, stored as the value of the ProductCode entry of REG_SZ data type under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup registry key for the default instance and under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_nameSetup registry key for each of the named instances. (The list of MSDE .MSI package names and associated product codes is included in the Microsoft Knowledge Base article 311762). Besides utilizing these registry entries, you can also determine names of relevant .MSI files by checking the content of their installation logs (assuming, of course, that their setup has been invoked with the verbose logging parameter /L*v Log_file_name and that the resulting output has been preserved), or by locating the ProductName entry matching the name of the source MSDE instance under HKEY_CLASSES_ROOT\Installer\Products\Product_GUID registry key and looking up the value of PackageName entry of REG_SZ data type under its SourceList subkey. For example, to remove the named instance called 'instance_name' installed on a local computer with sqlrun01.msi package, you could execute either one of the following commands (assuming the default directory structure of the MSDE source media, with .MSI files residing in the Setup subfolder):

Setup /x .\Setup\sqlrun01.msi INSTANCENAME='instance_name'

Setup /x {E09B48B5-E141-427A-AB0C-D3605127224A} INSTANCENAME='instance_name'

• installing SQL Server 2005 Express Edition instance and performing post-installation configuration tasks - For the details of this procedure, refer to one of the initial articles of this series. Note that you can reuse the same instance name, if desired. In addition, as we have pointed out earlier, ensure that the local system has Windows Installer 3.1 and .NET Framework 2.0 software present before you start.
• attaching previously detached user databases to the SQL Server 2005 Express Edition instance - If you have installed SQL Server 2005 Management Studio Express, you will be able to take advantage of its graphical interface and carry out this process by selecting the Attach option from the context sensitive menu of Databases folder in the Object Explorer window. (In the resulting Attach Database window, you can browse for the database and its log files). Otherwise, it is possible to accomplish the same goal by using the sqlcmd.exe command line utility (more powerful successor to MSDE osql executable), although this approach requires that you explicitly specify names and locations of .MDF and .LDF files. For example, assuming default location and naming conventions of MSDE installation of instance instance_name and its user database called database_name, you would need to execute the following:

EXEC sp_attach_db 'database_name', 

     'C:\Program Files\Microsoft SQL Server\instance_name\Data\database_name.mdf', 

     'C:\Program Files\Microsoft SQL Server\instance_name\Data\database_name_log.ldf'

Note that following the upgrade, you will not be able to connect to the SQL Server 2005 Express Edition instances using the legacy GUI management tools (e.g. SQL Server Enterprise Manager). This can be easily remedied by installing their latest version available at Visual Studio 2005 Express Developer Center.

The other method, which can be employed to implement a manual upgrade (in cases where the conditions required to make it automatic are not satisfied) leverages SAVESYSDB and USESYSDB setup command line switches (corresponding to Windows Installer .MSI properties) introduced in the MSDE Service Pack 4. This approach leaves system databases (master, model, msdb) in place, allowing you to preserve any custom changes they contain (and retaining the instance name). In this case, the migration process involves removing the existing MSDE installation by invoking its setup.exe program with SAVESYSDB parameter set to 1 and either the MSI package or product code corresponding to the source instance. For example, with instance_name created using the first of the MSI packages included with the MSDE source files, you would execute the following command:

Setup /x .\Setup\sqlrun01.msi SAVESYSDB=1 INSTANCENAME='instance_name'

Once the MSDE instance removal is finished, you should notice that all of its databases remain intact in its Data subfolder. At that point, you can invoke the SQL Server 2005 Express Edition setup (by calling its downloadable, self-extracting executable sqlexpr32.exe) with the USESYSDB parameter pointing to the directory structure created by the original installation. For example, assuming that the default location and naming conventions were used for the MSDE instance called instance_name, you would type the following at the Command Prompt. (For more information about command line switches that can be used to customize this procedure, refer to How to: Install SQL Server 2005 from the Command Prompt article in SQL Server 2005 Books Online).

sqlexpr32.exe USESYSDB="C:\Program Files\Microsoft SQL Server\instance_name" INSTANCENAME="instance_name"

If neither one of these methods satisfies your upgrade requirements, you can try leveraging one of standard data transfer mechanisms, such as the Copy Database Wizard or BCP to migrate from an MSDE platform. Alternatively, you might also consider using one of other editions of the SQL Server 2005 product line (such as Workgroup or Standard) as the migration target. We will continue coverage of different upgrade scenarios involving SQL Server 2005 Express Edition in the next article of our series.

» See All Articles by Columnist Marcin Policht