Upgrading SQL Express to MS SQL Server | Software Testing Fundamentals: Methods and Metrics

If for any reason you choose to install MOSS 2007 with a local SQL Express database and later find that you need to upgrade it to MS SQL 2000/2005 Server, you have two choices:

q Perform an in-place upgrade of SQL Express to a local MS SQL server. This is easy.
q Move from SQL Express to a separate MS SQL server. This is a complicated process.

Important

There is a quick guide for an in-place upgrade to SQL Server 2005. Be sure to follow Microsoft's detailed instructions for upgrading more complex SQL Server scenarios. The guide can be found at : http://msdn2.microsoft.com/en-us/library/ms143516.aspx.

This section describes the first option only: an in-place upgrade. If you want to move from a local database to a remote database server, you must check for a migration guide in Microsoft's Knowledge Base.

Preparing to Upgrade to MS SQL Server

The most important thing to do before starting the upgrade process is to back up your current MOSS database. You are about to perform a very sensitive operation, and if something goes wrong, you must be sure you can go back to the previous version. The previous section described how to back up your database.

During this upgrade process, no one can use the MOSS server. Make sure to pick a time when no one is using the server. Since the SQL Express version that comes with MOSS only allows up to 4 GB of data, this upgrade process should be fairly quick, depending on your server hardware, but plan for at least two hours to be on the safe side.

Upgrading MOSS to a Local MS SQL Server

As mentioned before, this is a very straightforward process. Make sure that no one is using the system before you start to upgrade, because they may lose information during this process. The best practice is to schedule this upgrade process at a specific time and date and inform all affected users when this will happen. Note that you can only upgrade SQL Express to SQL Server 2005 (any edition) but not to SQL Server 2000. If you upgrade SQL Express to SQL Server 2005, you cannot use the graphical installation mode. You must run the Setup.exe program with the command switch /SKUUPGRADE=1.

Try It Out Upgrade SQL Express to MS SQL Server 2005

Log on to the MOSS server as an administrator.
Make sure that no one is using MOSS.
Click Start Run Browse, and locate the Setup.exe program for SQL Server 2005. At the end of this line, add SKUUPGRADE=1 (see Figure 4-8) and click OK.

Figure 4-8
On the End User License Agreement page, read the license text, and if you agree, check I accept the licensing term and conditions and click Next.
The setup program will now load a number of components required for the SQL Server setup; wait until it is finished. When it is done, click Next.
On the Welcome page, click Next. A system configuration check is now performed; it should complete without any warnings or errors. If not, read the message and correct the problem. Click Next to continue.
On the Registration Information page, check that the server name is correct, and click Next.
On the Components to install page, check the option SQL Server Database Services, and click Next.
On the Instance Name page, make sure to use the option Default instance to upgrade the current SQL Express. If you have more instances installed on the SQL Express, then click the button Installed Instances to get a list of these instances. Then click OK and make sure that the option Named Instance = OFFICESERVERS (which is the default name that SharePoint uses for Basic installations of MOSS) is selected. When you're ready, click Next.
On the Existing components page, check the option SQL Server Database Services (which is your SQL Express installation), then click Next.
On the Upgrade Logon Information page, make sure that Windows Authentication Mode is selected, then click Next.
On the Ready to Install page, click Install to start the actual installation. This will take 5–60 minutes, depending on the size of the SQL Express database and the hardware configuration of the server.
When the setup is complete, make sure that there are no errors or warnings. Click Next, and then Finish.

Open a web browser, and check the MOSS environment. Make sure that it works like before. You should see no differences, except possibly better performance. Open the MS SQL Server Enterprise Manager and take a look at the databases for the local server. You should see the same databases as before, using the same instance name.

Important

Do not remove the SQL Express database from the MOSS Server! It is used by the upgraded MS SQL Server and contains the actual databases.