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