SQL Server 2005 Express Edition - Part 22 - Upgrading from Microsoft SQL Server Desktop Engine (MSDE)
May 9, 2008
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:
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 SqlRun
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:
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.