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

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_MACHINESOFTWAREMicrosoftMicrosoft
    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_MACHINESOFTWAREMicrosoftMicrosoft
    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_nameinstance_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_MACHINESOFTWAREMicrosoftMSSQLServerSetup
    registry key for the default instance and under HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Serverinstance_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_ROOTInstallerProductsProduct_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 .Setupsqlrun01.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 FilesMicrosoft SQL Serverinstance_nameDatadatabase_name.mdf’, 
         ‘C:Program FilesMicrosoft SQL Serverinstance_nameDatadatabase_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 .Setupsqlrun01.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 FilesMicrosoft SQL Serverinstance_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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles