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
IE 7
Firefox 2.0
Comment and Contribute
