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 HKLM\SOFTWARE\Microsoft\MSSQLServer\Setup key for the default instance and under HKLM\SOFTWARE\Microsoft\Microsoft 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 HKLM\SOFTWARE\Microsoft\Microsoft SQL Server registry key (in addition, each instance has a corresponding set of subkeys under HKLM\SOFTWARE\Microsoft\MSSQLServer 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 HKLM\SOFTWARE\Microsoft\MSSQLServer\CurrentVersion registry key for the default instance and under HKLM\SOFTWARE\Microsoft\Microsoft 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.