SQL Server 2005 Express Edition – Part 22 – Upgrading from Microsoft SQL Server Desktop Engine (MSDE)

In the
initial articles
of our series dealing with topics related to SQL Server
2005 Express Edition, we have described its setup procedure and subsequent
configuration tasks, presenting them (for simplicities sake) in the context of
new installations. However, most of us operate in less fortunate circumstances,
forcing us to deal with legacy environments and the need to migrate existing
databases created using previous product versions. In this installment, we will
focus on one such scenario, involving upgrading from Microsoft Desktop Engine
(MSDE).

As we have explained earlier,
both MSDE and its direct successor (based on the SQL Server 2000 and 2005 code
base, respectively) are similar in many aspects, due to their common, primarily
single-user, desktop application purpose. As a result, they do not offer any enterprise-oriented
features, such as as high-availability (via clustering, database mirroring, log
shipping, or online indexing), or Business Intelligence (including Analysis
Services and Integration Services). However, due to general changes introduced
in the underlying database engine (affecting the entire product line) and
improvements specific to SQL Server 2005 Express Edition, there are several
important reasons to consider the upgrade. Collectively, these new features
address the following limitations of MSDE:

  • Concurrency Workload Governor affecting scalability by imposing
    incoming connection request wait time whenever the number of simultaneous
    active operations on the database engine instance level exceeds the
    preconfigured threshold (set to 8),
  • Operating System support restricted to pre-Windows Vista 32-bit
    releases – such as Windows 98 (including its Second Edition), Windows
    Millennium Edition, Windows NT 4.0 Server and Workstation, entire Windows 2000
    product line (Professional, Server, Advanced Server, and Datacenter Server),
    Windows Server 2003 (Standard, Enterprise, and Datacenter), as well as Windows
    XP Professional and Home Edition (Windows Vista and Windows Server 2008, as
    well as any 64-bit version of Microsoft operating system require SQL Server
    2005 Express Edition),
  • cumbersome and feature-limited management utilities,
  • no functionality equivalent to SQL Server 2005 User Instances and
    application Xcopy (for more information on these topics, refer to one of earlier
    articles
    of our series),
  • absence of features introduced in the SQL Server 2005 product
    line (such as, for example, new XML-based and User Defined data types or .NET
    Common Language Runtime integration),
  • inability to publish MSDE instance in Active Directory,
  • lack of Visual Studio integration,
  • maximum database size set at 2GB (increased to 4GB in SQL Server
    2005 Express Edition),
  • no Reporting Services, SQL Service Broker (allowing for
    asynchronous message delivery), or full-text search capabilities,
  • patching and service pack upgrades issues in cases where MSDE
    installation was performed using merge modules (more about this shortly),
  • subject to Microsoft Extended Support restrictions (its
    Mainstream Support stage ended on April 8, 2008).

On the other hand, you should keep in mind that, when compared with its
predecessor, SQL Server 2005 Express Edition falls short in certain areas. In
particular, this applies to replication (with the former capable of merge
replication publishing and the latter reduced to the role of the subscriber),
availability of SQL Server Agent-related features, or built-in DTS run-time
component (not present in the original release of the latter, although provided
later as a separate download). Interestingly, with the new release, Microsoft
also decided to decrease the amount of buffer memory (1 GB vs 2 GB) and the
number of processors (1 vs 2) supported by each database engine instance.

In order to facilitate the upgrade to SQL Server 2005 Express Edition, you
will need to satisfy a number of prerequisites. While the minimum processor and
memory specifications are equivalent to those associated with MSDE
installations, demand for free disk space has increased due to dependency on .NET Framework 2.0
(bringing the footprint to roughly 170MB). In addition, the host operating
system will have to be upgraded, if you are running a version older than
Windows 2000 Service Pack 4 or Windows XP Service Pack 2. The Windows Installer
component must be at least at the 3.1
level.

MSDE, by default, supports up to 16 instances of the database engine on a
single operating system and each of them can be a target of independent upgrade
to SQL Server 2005 Express Edition. To understand the details of this process,
it is important to realize that MSDE supports two types of installation. The
first one involves executing one or more of 16 Windows Installer .MSI files
(corresponding to individual MSDE instances), named SqlRunxx (where xx is a number between 01 and 16) residing in the Setup subfolder of a directory
containing the source files. As the result, each instance gets associated with
a unique Product Code identifier (this is a general property of every
.MSI-based installation) expressed as GUID (a string of 32 hexadecimal digits)
that can be retrieved by checking the ProductCode
entry of REG_SZ data type
located under HKLMSOFTWAREMicrosoftMSSQLServerSetup
key for the default instance and under HKLMSOFTWAREMicrosoftMicrosoft
SQL Server<instance_name>Setup
key for named
instances. The most typical example of such behavior is a standard
installation, invoked by launching Microsoft-provided setup.exe, which is part
of the downloadable source files. To identify all instances installed in this
manner, examine the value of the InstalledInstances entry of REG_MULTI_SZ data type residing under
the HKLMSOFTWAREMicrosoftMicrosoft SQL
Server
registry key (in addition, each instance has a
corresponding set of subkeys under HKLMSOFTWAREMicrosoftMSSQLServer
registry key). The other approach is commonly employed by developers, who
distribute their applications (with their own, unique Product Codes) bundled
with MSDE-based data and the database engine installation program by employing
Microsoft-provided Windows Installer merge modules. While this prevents
accidental clashes with any existing instances, it introduces manageability
issues, including patching, as well as service packs and version upgrades (due
to the Product Code mismatches). You can easily determine which of these two
methods was used by checking the list of installed software via the Add/Remove
Programs applet in the Control Panel. In case of the former, you should see
there individual entries for each installed MSDE instance, while their absence
indicates use of merge modules.

The simplest way to transition from an existing MSDE installation is to
perform an in-place upgrade. This process is automatic and takes place when
running setup of SQL Server 2005 Express Edition on a system hosting one or
more MSDE instances (keep in mind that the upgrade is performed on a per-instance
basis), as long as the following conditions are true:

  • matching ProductCode – the setup program expects that the ProductCode
    assigned to the instance to be upgraded is the same as the GUID associated with
    the corresponding MSI file included in the original product. As described
    above, this should be the case if you installed MSDE using its standard setup
    process, but it is unlikely if the database engine was added via a merge module
    as part of a third party application deployment. (You should be able to
    establish the installation method by checking the list of entries appearing in
    the Add/Remove Programs applet in the Control Panel).
  • matching language – SQL Server 2005 Express instance inherits the
    MSDE language settings. Both MSDE and SQL Server 2005 support the set 12 of
    languages (Brazilian Portuguese, Simplified Chinese, Traditional Chinese,
    Dutch, English, French, German, Italian, Japanese, Korean, Spanish, and
    Swedish). To determine the language associated with a specific MSDE instance,
    check the value of the Language
    entry of DWORD type located
    under the HKLMSOFTWAREMicrosoftMSSQLServerCurrentVersion
    registry key for the default instance and under HKLMSOFTWAREMicrosoftMicrosoft SQL Server<instance_name>CurrentVersion
    key for named instances.
  • minimum product version – it is recommended that MSDE
    operates at the Service
    Pack 4
    level. To verify its presence, connect to a target MSDE instance via
    osql command line utility
    and confirm that the value of its @@version
    global variable is 8.00.2939 or higher. Alternatively, you can check output of
    the SELECT SERVERPROPERTY('ProductLevel')
    T-SQL statement. For example, in the case of the named instance myMSDE01, you would need to type the
    following in the Command Prompt window (refer to the Microsoft Knowledge Base
    article 321185 for the
    mapping between the version number and the Service Pack level):

  • osql -E -S (local)myMSDE01
    1> select @@version
    2> go

The automatic upgrade can be implemented by taking advantage of the
graphical interface of the Microsoft SQL Server 2005 Express Edition Setup
wizard or via unattended installation. In the case of the former, once you install
the prerequisites (including Microsoft SQL Native Client and SQL Server 2005
Setup support files) and complete configuration system checks, clear the
"Hide advanced configuration options" checkbox on the Registration
Information page. Confirm that Database Services (with its Data Files and
Shared Tools) feature is selected (and add any others you intend to use) and on
the Instance Name page, instead of selecting Default or Named instance (with
its default "SQLExpress" entry), click on the "Installed instances"
command button. Pick the target instance from the list (note that you can
verify here its version and edition information) and click on OK to return to
the main page. This should automatically populate the text box next to the
Named instance option with the appropriate value. The Existing Components page
provides a confirmation of your selection by listing software that will be
upgraded. The remaining pages of the wizard (determining configuration of
authentication, service accounts, user instances, SQL Server Administrator
role, or error and reporting) are identical to the ones we described in our original
discussion
about the SQL Server 2005 Express Edition setup. For details
regarding unattended installation process, refer to SQL Server 2005
Books Online
.

In the next article of our series, we will be dealing with situations in
which in-place upgrade is not an option, as well as discuss other factors that
need to be taken into consideration during an upgrade from MSDE to SQL Server
2005 Express Edition.

»


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