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 SqlRun
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
REG_SZ data type
key for the default instance and under
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
Server registry key (in addition, each instance has a
corresponding set of subkeys under
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
DWORDtype located under the
HKLM\SOFTWARE\Microsoft\MSSQLServer\CurrentVersionregistry key for the default instance and under
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\CurrentVersionkey 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
osqlcommand line utility and confirm that the value of its
@@versionglobal 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.