Microsoft has provided a prescribed process to do an in-place upgrade of MSDE 2000 to SQL Server 2008 R2 Express in their "SQL Server 2008 Upgrade Technical Reference Guide" (available for download at: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=15220). Part of that process requires that the installed instance of MSDE 2000 have Service Pack 4 applied. The MSDE installation that had been provided with certain builds of GlasPac LX is MSDE 2000 with Service Pack 3 or Service Pack 3a.
This document provides a detailed walkthrough of the upgrade process from MSDE 2000 with Service Pack 3 to MSDE 2000 with Service Pack 4, and then to SQL Server 2008 R2 Express with Service Pack 1.
Please note that there have been several significant changes to the limitations and feature sets that come with SQL Server 2008 R2 Express. All of these changes have been outlined in the aforementioned "SQL Server 2008 Upgrade Technical Reference Guide," but the key ones are listed below:
- MSDE incorporates a concurrent workload governor that is no longer used in SQL Server Express
- MSDE has a database size limitation of 2GB; while, SQL Server Express increases that limit to 4GB
- MSDE limits the maximum amount of RAM allocated to 2GB; while, SQL Server Express decreases that limit to 1GB
- MSDE can utilize 2 processors when running on a Windows Server operating system; while, SQL Server Express can only utilize 1 processor
- MSDE includes a SQL Server Agent that is no longer included with SQL Server Express
As always, make sure that a complete backup of the server has been made and verified before starting any of the instructions provided in this document.
System Requirements and Prerequisites
In order to successfully upgrade an instance of MSDE 2000 Service Pack 3 to SQL Server 2008 R2, the following minimum system requirements must be met for SQL Server 2008 R2 Express:
- Microsoft Windows Server 2003 or Windows Server 2003 R2 or newer
- Microsoft .NET Framework 3.5 SP1 (Download: http://www.microsoft.com/download/en/details.aspx?id=22)
- Microsoft Windows Installer 4.5 or later (Download: http://www.microsoft.com/download/en/details.aspx?id=8483)
- MSDE 2000 Release A Installer (Download: http://www.microsoft.com/download/en/details.aspx?id=22661)
- MSDE 2000 Service Pack 4 Installer (Download: http://www.microsoft.com/download/en/details.aspx?id=18290)
- Microsoft SQL Server 2008 R2 Express with Service Pack 1 Installer without Tools or Advanced Services (Download: http://www.microsoft.com/download/en/details.aspx?id=26729)
- Microsoft Internet Explorer 6.0 SP1 or later
- Processor speed of 1.0GHz or faster
- Minimum of 256MB of available memory for 32-bit, 512MB for 64-bit
- Minimum of 1GB of available hard drive space plus additional space for the databases to be upgraded
Repairing MSDE 2000 SP3 with MSDE 2000 Release A
In order to ensure that the installation of MSDE 2000 on the workstation or server is in a proper state for MSDE 2000 Service Pack 4 to be installed, the installation must be repaired. To repair the MSDE installation, use the following instructions:
- Extract the downloaded copy of the MSDE 2000 Release A to "C:\MSDERelA" by running the self-extracting executable
- Stop the GTS ComApp service if it is running
- The upgrade process requires that Windows Authentication be enabled and that the local Administrators group has rights to connect to the MSDE instance. From a command prompt window, run "osql -Usa" and provide the "sa" account password when prompted.
- Once connected to the MSDE instance, run the following commands:
- Run the following command from a command prompt to repair the MSDE 2000 installation on the computer:
msiexec REINSTALL=ALL REINSTALLMODE=VOMUS /I C:\MSDERelA\Setup\SqlRun01.msi
- After the repair of MSDE 2000 has been completed, delete the "C:\MSDERelA" directory
Applying MSDE 2000 Service Pack 4
After the installation of MSDE 2000 has been repaired with the MSDE 2000 Release A installation package, MSDE 2000 Service Pack 4 can be applied. To apply MSDE 2000 Service Pack 4, use the following instructions:
- Extract the downloaded copy of the MSDE 2000 Service Pack 4 to "C:\SQL2KSP4" by running the self-extracting executable
- Verify that the GTS ComApp service is still in a stopped state
- Run the following commands from a command prompt to start applying Service Pack 4:
exe /upgradesp sqlrun DISABLENETWORKPROTOCOLS=0 /L*v C:\MSDESP4.log
- The Service Pack 4 installation package may leave the MSDE services, "MSSQLSERVER" and "SQLSERVERAGENT", in a stopped state after the installation has been completed. Start both services and verify that a SQL connection can be made.
After applying MSDE 2000 Service Pack 4, delete the "C:\SQL2KSP4" directory
Upgrading to SQL Server 2008 R2 Express with Service Pack 1
Once the MSDE 2000 installation has had Service Pack 4 installed, that instance can now be upgraded, in-place, to SQL Server 2008 R2 Express with Service Pack 1. To perform the upgrade, follow the instructions below:
- Run the SQL Server 2008 R2 Express installer
- From the SQL Server 2008 R2 Installation Center, click on "Installation" and then choose "Upgrade from SQL Server 2000, SQL Server 2005 or SQL Server 2008"
- If it isn't already selected, select the "MSSQLSERVER" instance to upgrade
- During the upgrade process, the following error message may appear when the installer tries to connect to the SQL Server instance while the server is running in single-user mode:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The above error can safely be dismissed by clicking on Cancel.
- If the above error does come up, a Windows Error Reporting dialog may appear once the SQL Server upgrade process has completed. Dismiss the Error Reporting dialog and verify that the upgrade process has completed successfully.
Once the upgrade process has completed, verify that the LX application can connect to the GlasPacLX database and that the application functions normally. The upgrade process will remove the MSDE services and application from the computer.