Upgrading SQL Server 2005 Express to MS SQL Server

Many organizations start investigating what WSS can do by installing WSS using a local SQL Server 2005 Express database. The idea is often to run a pilot project. More often than not, this pilot project then incidentally turns into a production environment, with lots of important data that cannot be discarded. I am sure you don't belong to such an organization, but you probably know somebody else who does, right? Well, you can tell your friend that it is possible to move up from the SQL Server 2005 Express database to the full MS SQL 2000 or 2005 Server, so there is no need to worry.

You have two types of possible upgrade scenarios:

• q An in-place upgrade of SQL Server 2005 Express to a local MS SQL Server.

• q Moving from a local SQL Server 2005 Express to a separate MS SQL Server.

The first scenario is straightforward: Simply run the setup program for MS SQL Server to upgrade SQL Server 2005 Express. The other scenario are more complicated, but not impossible. You might remember from earlier sections in this chapter that a remote SQL Server gives you better performance and also makes it possible to build a fault-tolerant MS SQL Server cluster. You will find the steps for each upgrade scenario in the following sections.

Preparing to Upgrade the SQL Server 2005 Express

The first and most important thing to remember is to back up your current WSS data. 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.

If it is a simple server configuration with just the WSS and the SQL Server 2005 Express database, you can of course perform a full Windows 2003 Server backup, including the system state (the local Registry, the IIS Metabase, and the boot files). The other option is to make a backup of the content database alone to make sure that you have all data intact in case the upgrade to MS SQL for some reason does not work as expected.

Doing a Full Server Backup

The first option, doing a full server backup, is always a good idea even if you will do a separate database backup later. This option ensures that you can do a complete restore of the Windows 2003 Server, including the WSS environment and all its data. You can use the backup utility that comes with Windows 2003 Server for this operation, if you want. See the following Try It Out.

Try It Out Do a Full Server Backup

1. Log on as an administrator to the WSS server. Make sure that no one will use this server during the backup procedure.

2. Choose Start All Programs Accessories System Tools Backup.

3. This starts the backup program in Wizard mode (unless you previously have unchecked that option), which is fine for your purpose this time. Click Next.

4. Make sure the following page has the following option selected: Backup files and settings. Click Next.

5. On the page What to back up, make sure you select the option All information on this computer. Note that this makes a complete backup of all data on the server. It also creates a system recovery disk that makes it possible to restore all data in case of a major failure. Click Next.

6. On the page Backup Type, Destination and Name, choose where to store this backup file and give it a proper name, such as Full WSS Server Backup. Click Next to go on.

   Important 

   You need a diskette to complete this backup operation. With this diskette you can later boot up the server and it will do a complete restore of the server!

7. On the next page you see a summary of your options. If it is okay, click Finish to close this page and start the backup procedure.

Of course, you can use whatever backup routine you normally run for your computers instead of this procedure. Just make sure that it is a complete backup, including the system state.

There is one drawback to this type of backup procedure: You will only be able to do a complete restore, if necessary. For example, you cannot just restore the WSS content database. That is why the second backup option is interesting.

Doing a File Backup of the WSS Databases

Once again, you have two options for this. You can simply stop the SQL Server 2005 Express database and make a file copy of all the database files, or you can use the STSADM tool that comes along with WSS for backing up parts or the entire content database.

Important 

There are lots of good and very cheap backup management tools for all types of MS SQL databases, including MS SQL Server 2005 Express. For example, check out http://www.simego.com and http://www.msde.biz. You can also use a local copy of the MS SQL Enterprise Manager.

Try It Out Copy All WSS Database Files Manually

To make a simple file copy of the database in SQL Server 2005 Express, do this:

1. Stop the service MSSQLSERVER (choose Start Administrative Tools Services).

2. Copy the files in C:\Program Files\Microsoft SQL Server\MSSQL \Data. (There may be more than one database in SQL Server 2005 Express. If you just want to copy the WSS config and content databases, look for all files starting with WSS or SharePoint.)

3. Start the MSSQLSERVER service again.

If you need to restore these database files after a failed MS SQL upgrade procedure, make sure to have a working WSS and SQL Server 2005 Express installation and then copy the WSS and SharePoint files back to the original file location.

Doing a Backup of the Content Database

This method is very fast and selective. You will only back up the content for the WSS environment. The result will be files that can be used to migrate into any WSS or even MOSS environment. The tool you must use here is TSADM.EXE. It is stored deep down in the file system, or to be exact, in this folder:

     C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN 

This is a command-based tool that you need to run in a command window. You will often need to access STSADM and other tools in this folder, and instead of entering the full path to these tools every time, it is easier to configure Windows to search in this folder directly. If you are old enough to remember when MS-DOS ruled the computer world, you might remember there is a system variable named PATH. When you enter a program name in a command window, Windows looks for that file in all file paths defined in this variable. The following Try It Out explains how to add the path to STSADM to this system variable.

Try It Out Update the PATH System Variable

1. Start Windows Explorer and navigate to the path for the STSADM as given previously. Right- click the file path in the Address field and select Copy.

2. Click Start to see the Windows start menu.

3. Right-click My Computer, and select Properties.

4. Switch to the Advanced tab, and click its Environment Variables button.

5. In the lower pane named System Variables, locate PATH and click Edit.

6. Go to the end of the current list under Variable value (use the End key or the right arrow on the keyboard). Type in a semicolon (;) as a separator, and paste in the path you copied in step 1. Then click OK three times to save this modification and close all dialog boxes.

7. Test it by opening a command window (Start Run and type Cmd), then type STSADM in this command window. If you get a long list of options, you did it right. If not, redo the steps above, and be careful to do everything exactly as described.

By now, you have access to STSADM and the other tools in the same folder, regardless of where you are in the folder tree. But before you can perform your backup, you must know how it works. Every web site you create in WSS is either a top site or a subsite. Top sites are the start of a tree with any number of nested subsites, much like a top folder and subfolders in a file system. SharePoint calls this a site collection. All site collections are stored in a given content database in SQL Server. STSADM will help you back up a given site collection (a top site and all its subsites).

If you installed WSS using the Basic installation option, a site collection was automatically created containing one top site. In Chapter 3, you will learn how to create new top sites and subsites for each of these top sites. To make it simple, say you only have a site collection consisting of one top site with five subsites. The URL address for this top site is http://srv1, and you want to back up this complete site structure to a file named WSS-back.bak in the folder C:\Bkup. To do this, follow these steps:

Try It Out Use STSADM to Back Up a Site Structure

1. Log on to the WSS server as an administrator.

2. Open a command window, type in the following text, and press Enter key:

        Stsadm --o Backup --url http://srv1 --filename c:\bkup\wss-back.bak 

3. When the backup is done, you will see the file wss-back.bak in the folder C:\bkup.

Upgrading WSS to a Local MS SQL 2005 Server

By now, you have your WSS environment backed up. It is time to upgrade your SQL Server 2005 Express database to the full MS SQL 2005 Server. As mentioned before, this is a very straightforward process. Just make sure that no one is using the system before you start to upgrade. Follow the steps in the Try It Out to upgrade the database.

Important 

The following is a quick guide for upgrading to SQL Server 2005. Be sure to follow Microsoft's detailed instructions for upgrading more complex SQL Server scenarios: http://msdn2.microsoft.com/en-us/library/ms143516.aspx.

Try It Out Upgrade SQL Server 2005 Express to a Local MS SQL 2005 Server

1. Log on to the WSS server as an administrator.

2. Make sure that no one is using the WSS system.

   Important 

   Important: Make sure that Windows 2003 Server is updated with Service Pack 1 or later if you want to upgrade to MS SQL Server 2005!

3. Mount the MS SQL 2005 Server CD. You automatically see a dialog box where you can start the installation. Stop this installation page, and instead click Start Run: D:\Setup\Setup SKU- UPDATE=1 directly from the CD (assuming that D: is mapped to the CD). This step is required to upgrade an existing SQL Server Express!

4. On the End User License Agreement page, read the terms, and if you agree, check I accept the licensing terms and conditions, and click Next.

5. On the next page, you will be informed about what will be installed. Click Install to continue. After a short period, during which the setup program checks the installation prerequisites, click Next.

6. After a system configuration check, you will see the page Welcome to the Microsoft SQL Server Installation Wizard. It will show all checks that have been done. They all should be marked with a green check mark. If not, read the message and take the recommended action. Click Next to continue the installation of SQL Server 2005.

7. You will see a page with the server name, company name, and the production key. If necessary, update these values, and click Next.

8. On the page Components to install, check SQL Server Database Services. You may also want to check the option Workstation components, Books Online, and development tools, then click Next.

9. On the page Instance Name, select the option Named instance and enter the name of the WSS SQL instance, usually OFFICESERVERS. (You can also click on Installed instances to see all available SQL server instances.) Click Next.

10. On the page Service Account, enter the user account to be responsible for the SQL Server services, then click Next.

11. On the page Authentication Mode, accept Windows Authentication Mode, and click Next.

12. On the page Collation Settings, accept the default, and click Next.

13. On the page Ready to Install, click Install. The actual installation and upgrade process now starts; wait for it to complete.

14. When the installation is completed, click Next and then Finish.

15. Finally, check for the latest service pack for MS SQL Server 2005 on the MS SQL home page: http://www.microsoft.com/sql/download.

16. Check your WSS environment. Make sure it works like before. You should see no differences, except possibly better performance.