Migrating from WMSDE to SQL Server

Published: July 30, 2004
 

When you install Microsoft Windows SharePoint Services on a single server using the defaults, you have an installation that uses Microsoft SQL Server 2000 Desktop Engine (Windows) (WMSDE) for your databases. This is fine in a small-scale environment, when you are hosting just a few Web sites, but if your server suddenly gets popular and you need to start hosting hundreds of sites, you may run into performance and storage problems. If you find yourself in this situation and need to move to a more scaled out solution, you can switch to using Microsoft SQL Server 2000 Service Pack 3 (SP3) as your database back end. There are two methods you can use to switch your databases to SQL Server 2000:

• Upgrade the databases to SQL Server on the same computer.

Use this option if you want to continue running Windows SharePoint Services on a single server.
• Migrate the content databases to a server farm running SQL Server and Windows SharePoint Services.

Use this option if you want to move your sites to a server farm, with at least one front-end Web server and at least one back-end database server.
On This Page
Upgrading the Databases to SQL Server on the Same Computer Upgrading the Databases to SQL Server on the Same Computer
Migrating the Content Databases to a Server Farm Migrating the Content Databases to a Server Farm

Upgrading the Databases to SQL Server on the Same Computer

If you want to continue using a single server for Windows SharePoint Services, you can simply upgrade your database instance from WMSDE to SQL Server. Because this process requires your sites to be offline while the databases are upgraded, it is recommended that you perform these steps at a time when usage of your sites is generally low, and also that you notify users that their sites will be offline for a time.

Note  Before you upgrade your databases, it is a good idea to back them up. If you have the SQL Server client tools installed on your server or on another server, you can use them to back up a WMSDE database. Otherwise, stop WMSDE and make a copy of the database files before installing SQL Server.

Install SQL Server and upgrade your databases

1. Run the SQL Server 2000 SP2 Setup program, and on the Autorun panel, click SQL Server 2000 Components.
2. Click Install Database Server, and then on the Welcome panel, click Next.
3. In the Computer Name box, select Local computer, and then click Next.
4. In the Installation Selection pane, select Upgrade, remove, or add components to an existing instance of SQL Server, and then click Next.
5. In the Instance Name pane, clear the Default check box, and then in the Instance name box, select SHAREPOINT, and click Next.
6. In the Existing Installation pane, verify that Upgrade is selected, and then click Next.
7. In the Upgrade pane, verify that the Yes, upgrade my programs check box is selected, and then click Next.
8. In the Licensing Options pane, select your licensing options, and then click Next.
9. In the Select Components pane, click Server Components, and then select the Full-text Search check box if you want to enable full-text searching.
10. In the left pane, select the Management Tools check box and in the right pane, select the Enterprise Manager and Query Analyzer check boxes.
11. Select any other components you want, and then click Next.
12. In the Start Copying Files pane, click Next, and then click Finish.

After the upgrade to SQL Server 2000 is complete, your SharePoint sites should work as usual.

Migrating the Content Databases to a Server Farm

If you are moving to a larger scale environment, with one or many front-end Web servers and one or many back-end database servers, the process is a more complicated. To switch from WMSDE to SQL Server and move to a server farm, you must perform steps using the Internet Information Services (IIS), Windows SharePoint Services, and SQL Server administration tools. You must also take your sites offline for a while. It is recommended that you perform these steps at a time when usage of your sites is generally low, and also that you notify users that their site will be offline for a time.

The process below assumes that you will continue to use the original Web server computer as either a stand-alone server or part of a server farm, and that you are moving the databases to a new back-end database server running SQL Server.

The steps you take to move from a single-server WMSDE installation to a server farm with SQL Server are:

1. Install the SQL Server client tools on the original server running WMSDE and SQL Server 2000 Service Pack 3 (SP3). The client tools are used to back up and restore the content and configuration databases. The version of WMSDE that is installed with Windows SharePoint Services does not enable remote connections from SQL Server client tools.
2. Prepare the destination database server by installing SQL Server 2000 Service Pack 3 (SP3).
3. In IIS, stop any virtual servers that are hosting SharePoint sites, so that users cannot access the sites.
4. Disconnect the content databases from the virtual server and remove Windows SharePoint Services from the virtual server.
5. Decide which domain accounts to use for the SharePoint Central Administration virtual server and the content virtual servers, and then update the SharePoint Central Administration virtual server to use the domain account.

You can use the same account for both SharePoint Central Administration and the other virtual servers, or for more granular security, you can choose to use different accounts.
6. Register the instance of WMSDE in SQL Server Enterprise Manager, and then back up the content and configuration databases.
7. Copy the backup files to the destination server and restore the content and configuration databases.
8. In SQL Server, change the database ownership and permissions for the configuration and content databases.
9. Reconnect to the configuration database.
10. Extend the content virtual server and add the restored content databases to the virtual server.
11. Update the default content database server for future content database creation.

Installing the SQL Server Client Tools and Backing Up the Content Databases

To create a backup file for a WMSDE database, you must use the SQL Server client tools. You must install the SQL Server client tools to your original server, and then perform the backup. For more information about installing the client tools for SQL Server 2000, see the SQL Server 2000 documentation.

Preparing the Destination Server Farm

You can move your content to an existing or new server farm. Either way, you need to have a back-end database server running SQL Server 2000 SP3 and one or more front-end Web servers running Windows SharePoint Services. For more information about setting up separate servers with SQL Server and Windows SharePoint Services, see Remote SQL Server Deployment or Server Farm with Multiple Host Names Deployment. Your server farm must be up and running before you can upgrade and move the content databases.

Stopping the Virtual Servers Hosting SharePoint Sites

In order to completely back up and restore your SharePoint sites, you must be sure that no users are making changes to the sites. To block users from changing the sites, you can stop the sites in IIS.

Stop a virtual server in IIS

1. Click Start, point to All Programs, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
2. Click the plus sign (+) next to the server name that contains the virtual server you want to stop.
3. Click the plus sign (+) next to the Web Sites folder.
4. Right-click the virtual server you want to stop, and then click Stop.

Disconnecting the Content Databases and Removing Windows SharePoint Services from the Virtual Server

You must disconnect the content databases and remove Windows SharePoint Services from the virtual servers hosting SharePoint sites before you can back up the configuration and content databases.

Remove a content database

1. Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint Central Administration.
2. On the Central Administration page, under Virtual Server Configuration, click Configure virtual server settings.
3. On the Virtual Server List page, select the virtual server you want to configure.
4. On the Virtual Server Settings page, under Virtual Server Management, click Manage content databases.
5. On the Manage Content Databases page, under Content Databases, select the database you want to change.
6. On the Manage Content Database Settings page, in the Remove Content Database section, select the Remove content database check box.
7. Click OK.

Repeat these steps for any additional content databases. After the content databases have been removed, you can remove Windows SharePoint Services from the virtual servers.

Remove Windows SharePoint Services from a virtual server

1. Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint Central Administration.
2. On the Central Administration page, under Virtual Server Configuration, click Configure virtual server settings.
3. On the Virtual Server List page, select the virtual server you want to configure.
4. On the Virtual Server Settings page, under Virtual Server Management, click Remove Windows SharePoint Server from Virtual Server.
5. On the Remove Windows SharePoint Server from Virtual Server page, select Remove without deleting content databases.
6. Click OK.

Updating the Application Pool Account for SharePoint Central Administration

You must determine which accounts to use for the application pools for the SharePoint Central Administration virtual server and for any virtual servers hosting SharePoint sites. Then, you can update the application pool for the SharePoint Central Administration virtual server to run with the domain account you select. You can use the same account for both SharePoint Central Administration and the other virtual servers, or for more granular security, you can use separate accounts.

Update the application pool account for SharePoint Central Administration

1. Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint Central Administration.
2. On the SharePoint Central Administration page, under Server Configuration, click Configure virtual server for central administration.
3. Select Create a new application pool, and then select Configurable.
4. In the User name box, type the user name to use for the identity.
5. In the Password box, type the password for that user name.
6. In the Confirm password box, type the password again.
7. Click OK.

After you change the application pool for SharePoint Central Administration, you must restart IIS. To restart IIS, on the command line, type iisreset.

Registering the WMSDE Instance in Enterprise Manager and Backing Up the Databases

You must register the WMSDE instance with Enterprise Manager to be able to back up the configuration and content databases. You must have already installed the SQL Server client tools to use Enterprise Manager.

Register the WMSDE database in Enterprise Manager

1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
2. Click the plus sign next to Microsoft SQL Servers.
3. Right-click SQL Server Group, and then click New SQL Server Registration.
4. In the Register SQL Server Wizard, click Next.
5. In the Available Servers box, type the original server name and the instance name (for example, server_name\SHAREPOINT), and then click Add.
6. Click Next.
7. On the Select an Authentication Mode panel, select one of the following connection methods:
• The Windows account information I use to log on to my computer (Windows Authentication) 
• The SQL Server login information that was assigned to me by the system administrator (SQL Server Authentication)
8. Click Next.
9. On the Select SQL Server Group panel, click Next to add the server to the existing SQL Server Group.
10. Click Finish.
11. On the Server registration completed panel, click Close.

After you have registered the WMSDE database with Enterprise Manager, you are ready to back up your configuration and content databases.

Back up the configuration and content databases

1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
2. Click the plus sign next to Microsoft SQL Servers.
3. Click the plus sign next to SQL Server Group.
4. Click the plus sign next to the WMSDE instance name.
5. Click the plus sign next to Databases.
6. Right-click the configuration database name, point to All Tasks, and then click Backup Database.
7. In the SQL Server Backup dialog box, on the General tab, be sure the correct database is selected in the Database box, and then in the Name box, type the name for the backup.
8. Under Destination, click Add.
9. In the Select Backup Destination dialog box, in the File name box, type the path and file name for your backup file, and then click OK.

For example, c:\database_name.bak.
10. On the General tab, under Overwrite, select Overwrite existing media.
11. Click OK to begin the backup.
12. Repeat these steps to back up the content databases.

Copying the Backup Files and Restoring the Databases

Copy all of the backup files for your configuration and content databases to the following directory on your destination server: \Program Files\Microsoft SQL Server\MSSQL\Backup. After you have copied the files to the destination server, you can restore the databases. After the databases have been restored, they work as fully functional SQL Server databases.

Restore the configuration and content databases

1. On the new server, click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
2. Click the plus sign next to Microsoft SQL Servers.
3. Click the plus sign next to SQL Server Group.
4. Click the plus sign next to the WMSDE instance name, if you connected remotely to your original server, or next to (local) (Windows NT), if you performed the backup on another server and have just copied the backup file to the new server.
5. Right-click Databases, point to All Tasks, and then click Restore Database.
6. In the Restore database dialog box, on the General tab, in the Restore as database box, type the database name.
7. In the Restore section, select From device, and then click Select devices.
8. In the Choose Restore Devices dialog box, click Add.
9. In the File name box, type the path and file name for your backup file, and then click OK.

For example, c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\sts_config.bak.
10. Click OK to close the Choose Restore Devices dialog box.
11. On the Options tab, under Move to physical file name, verify that the paths listed for the database and log files are correct.

For example, the path in the WMSDE backup file might be similar to c:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\database_name, but on the new server, you need to use the path c:\Program Files\Microsoft SQL Server\MSSQL\Data\database_name instead.
12. Click OK to restore the database.
13. Repeat these steps to restore the content databases.

Changing the Database Ownership and Permissions for the Databases

You must change the database ownership and permissions for the databases to grant permissions to the application pool accounts you want to use. To change the ownership and permissions, you use SQL Query Analyzer.

Change the database ownership and permissions for the configuration database

1. On the new server, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
2. In the Connect to SQL Server dialog box, in the SQL Server box, type the server name, and then click OK.
3. On the Query menu, click Change Database.
4. In the Select Database of server_name box, click the configuration database (sts_config), and then click OK.
5. In the Query pane, type the following query. 
DECLARE @AdminVSAccount nvarchar(255)
DECLARE @ContentVSAccount nvarchar(255)
SET @ContentVSAccount = N'domain\contentaccount'; 
SET @AdminVSAccount = N'domain\adminaccount'; 
EXEC sp_grantlogin @ContentVSAccount;
EXEC sp_changedbowner @AdminVSAccount;
IF NOT EXISTS (SELECT * FROM sysusers WHERE name=@ContentVSAccount) 
      EXEC sp_grantdbaccess @ContentVSAccount; 
EXEC sp_addrolemember 'db_owner', @ContentVSAccount; 
EXEC sp_addsrvrolemember @AdminVSAccount, 'dbcreator'
EXEC sp_addsrvrolemember @AdminVSAccount, 'securityadmin'
EXEC sp_addsrvrolemember @AdminVSAccount, 'processadmin'

Note  In lines 3 and 4, replace domain\contentaccount and domain\adminaccount with the domain account for the content virtual server and the domain account for the SharePoint Central Administration virtual server. If the accounts are the same, SQL Query Analyzer will display an error, but the process will still succeed.
6. Click the Execute Query button to update the database.

Change the database ownership and permissions for the content databases

1. On the new server, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
2. In the Connect to SQL Server dialog box, in the SQL Server box, type the server name, and then click OK.
3. On the Query menu, click Change Database.
4. In the Select Database of server_name box, click the content database you want to update, and then click OK.
5. In the Query pane, type the following query.
DECLARE @AdminVSAccount nvarchar(255)
DECLARE @ContentVSAccount nvarchar(255)
SET @ContentVSAccount = N'domain\contentaccount'; 
SET @AdminVSAccount = N'domain\adminaccount'; 
EXEC sp_grantlogin @ContentVSAccount;
EXEC sp_grantlogin @AdminVSAccount;
EXEC sp_changedbowner @AdminVSAccount;
IF NOT EXISTS (SELECT * FROM sysusers WHERE name=@ContentVSAccount) 
      EXEC sp_grantdbaccess @ContentVSAccount; 
EXEC sp_addrolemember 'db_owner', @ContentVSAccount;

Note  In lines 3 and 4, replace domain\contentaccount and domain\adminaccount with the domain account for the content virtual server and the domain account for the SharePoint Central Administration virtual server. If the accounts are the same, SQL Query Analyzer will display an error, but the process will still succeed.
6. Click the Execute Query button to update the database.

Reconnecting to the Configuration Database

After the permissions have been set, you are ready to reconnect to the configuration database.

Connect to the restored configuration database

1. On the server running Windows SharePoint Services, click Start, point to All Programs, point to Administrative Tools, and then click SharePoint Central Administration.
2. Under Server Configuration, click Set configuration database server.
3. On the Set Configuration Database Server page, in the Database server box, type the name of the new server.
4. In the SQL Server database name box, type the name of the configuration database (the default is sts_config).
5. Select the Connect to existing configuration database check box.
6. Click OK.

Extending the Virtual Servers and Adding the Content Databases

You can extend either the default virtual server (if it was not in use already) or a newly created virtual server. For more information about creating a virtual server, see Extending Virtual Servers.

Important  When you extend the virtual server, you must specify the application pool identity to use for the virtual server processes. Be sure to specify an account that is a member of the database owners role in SQL Server for the restored content database, or else add the account to the database owners role before you extend the virtual server. For more information about adding an account to a role in SQL Server, see the SQL Server 2000 documentation.

Extend a virtual server

1. On the SharePoint Central Administration page, click Extend or upgrade virtual server.
2. On the Virtual Server List page, click the name of the virtual server to extend.
3. On the Extend Virtual Server page, in the Provisioning Options section, select Extend and map to another virtual server.
4. In the Server Mapping section, in the Host name or IIS virtual server name box, click the name of the virtual server that contained the original sites.
5. In the Application Pool section, select Create a new application pool.

Note  It is recommended that you create a new application pool for each virtual server, so that they run in separate processes. Be sure that the application pool account you specify is a member of the database owners role for the content database you restored.
6. In the Application pool name box, type the new application pool name.
7. Under Select a security account for this application pool, select Configurable.
8. In the User name box, type the account name.
9. In the Password box, type the password for the account.
10. In the Confirm password box, type the password again.
11. Click OK.

Now that the virtual server is extended, you can add any additional restored content databases.

Add the restored content databases

1. On the Central Administration page, under Virtual Server Configuration, click Configure virtual server settings.
2. On the Virtual Server List page, select the virtual server you want to configure.
3. On the Virtual Server Settings page, under Virtual Server Management, click Manage Content Databases.
4. On the Manage Content Databases page, click Add a content database.
5. In the Database Information section, click Specify database server settings.
6. In the Database name box, type the name of the restored database.
7. In the Database Capacity Settings section, fill in the capacity settings you want to use.
8. Click OK.

Repeat these steps to add any additional content databases.

Updating the Default Content Database Server

Now that all of the databases have been moved, you can update the default content database server, so that any new content databases are created on the correct server.

Update the default content database server

1. Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint Central Administration.
2. Under Server Configuration, click Set default content database server.
3. In the Content Database section, enter the new database server name.
4. Click OK.