Migrating from WMSDE to SQL Server 2005 (Windows SharePoint Services 2.0)

  • Article

When you install Microsoft Windows SharePoint Services on a single server by using the Typical option, 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 might have performance and storage problems.

Using Microsoft SQL Server 2005 allows you to host all of your databases together and manage them with SQL Server Enterprise management tools. For example, SQL Server provides backup and restore, database management, and full text searching, which are not available in WMSDE.

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 2005 for your databases. The following methods can be used to migrate your databases from WMSDE to SQL Server 2005:

  • Migrate the databases to SQL Server 2005 on the same computer.

    Use this option if you want to continue running Windows SharePoint Services on a single server.

  • Migrate the databases to a separate database server computer.

    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.

    Note

    If you are running Windows SharePoint Services on Microsoft Windows Small Business Server 2003, you must perform some steps differently than those provided below. For more information, see the Windows Small Business Server 2003 documentation (https://go.microsoft.com/fwlink/?LinkId=104730&clcid=0x409).

Migrating Databases to SQL Server 2005 on the Same Computer

This section describes how to migrate configuration and content databases from WMSDE to SQL Server 2005 in a single server deployment scenario. 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. You should also notify users that their sites will be offline for a time during the migration period.

The following list describes the high level steps in the order they must be performed. Each of these steps are described in detail in following sections.

  1. Backup the configuration and content databases.

  2. Install SQL Server 2005.

  3. Stop all virtual servers that are hosting SharePoint sites, so that users cannot make changes to the sites.

  4. Remove the content databases and Windows SharePoint Services from the virtual server.

  5. Copy the database files to the SQL 2005 folder.

  6. Attach the configuration and content databases to SQL Server 2005.

  7. Reconnect to the configuration database and restart virtual servers.

  8. Extend the virtual servers and add the content databases.

  9. Update the default content database server for future content database creation.

Backing Up the Configuration and Content Databases

Before you migrate your configuration and content databases, it is a good idea to back them up. If you have the SQL Server client tools installed on your server, you can use them to back up a WMSDE database. Otherwise, you can use the following procedures to stop the WMSDE service and make a copy your LDF and MDF database files before installing SQL Server.

Stop the WMSDE SQL service

  1. Click Start, point to Administration Tools, and then click Services.

  2. Scroll down the list, right-click MSSQL$Sharepoint, and then click Stop.

Backup LDF and MDF database files

Locate the following files and copy them to a safe backup location:

  • STS_config.mdf

  • STS_<servername>_1.mdf

  • STS_config_log.LDF

  • STS_<servername>_1_log.LDF

    Note

    These database files are in the %drive%\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data folder, by default.

    Note

    If you have extended multiple virtual servers with Windows SharePoint Services, there will be multiple database files in this folder. Ensure that you backup all your SharePoint databases.

Restart the WMSDE SQL service

After you back up your database files to a safe location, you must restart the WMSDE SQL service. This is because you will need to connect to the WMSDE databases in a later procedure.

  1. Click Start, point to Administration Tools, and then click Services.

  2. Scroll down the list, right-click MSSQL$Sharepoint, and then click Start.

Installing SQL Server 2005

Now that you have backed up your configuration and content databases, the next step is to install SQL Server 2005.

Install SQL Server 2005

  1. On the End User License Agreement page, review the license agreement, select the I accept the licensing terms and conditions check box if appropriate, and then click Next.

  2. On the Installing Prerequisites page, click Install.

    Note

    The status bar at the bottom of the Installing Prerequisites page indicates the progress of installing the prerequisites, which can take several minutes to install.

  3. When the prerequisites are finished installing, click Next.

  4. On the Welcome to the Microsoft SQL Server Installation Wizard page, click Next.

  5. On the System Configuration Check page, verify that the status of all actions is successful, and then click Next.

  6. On the Registration Information page, enter your name, company name, and product key, and then click Next.

  7. On the Components to Install page, select the SQL Server Database Services check-box and then click Next.

  8. On the Instance Name page, select Default instance and then click Next.

  9. On the Service Account page, select Use the built-in System account, select Local system from the drop-down list, and then click Next.

  10. On the Authentication Mode page, select Windows Authentication Mode.

    Note

    Windows SharePoint Services supports both Windows Authentication mode and Mixed mode. However, Windows Authentication Mode is recommended.

  11. Click Next.

  12. On the Collation Settings page, click Next.

  13. On the Error and Usage Report Settings page, select the usage reports in which you want to participate, and then click Next.

  14. On the Ready to Install page, click Install.

    The Setup Progress page indicates the progress of the installation.

  15. When the setup progress is complete, click Next.

  16. On the Completing Microsoft SQL Server 2005 Setup page, click Finish.

Stopping the Virtual Servers Hosting SharePoint Sites

In order to completely back up and restore your SharePoint sites, you must ensure that users cannot make changes to the SharePoint sites, during the migration. To block users from changing the sites, you must use Internet Information Services (IIS) Manager to stop all virtual servers hosting SharePoint sites.

Stop a virtual server in IIS

  1. Click Start, 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.

  5. Perform step 4 for each virtual server that is extended with Windows SharePoint Services.

    Note

    Do not stop the SharePoint Central Administration site, because you will need to access it during the next procedure.

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

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

Remove a content database

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Virtual Server Configuration section, click Configure virtual server settings.

  3. On the Virtual Server List page, select the virtual server that contains the content database.

  4. On the Virtual Server Settings page, in the Virtual Server Management section, click Manage content databases.

  5. On the Manage Content Databases page, in the Content Databases section, select the content database you want to remove.

    By default the content database is named STS_<Servername>_1.

  6. On the Manage Content Database Settings page, in the Remove Content Database section, select the Remove content database check-box.

  7. Click OK to close the warning dialog box.

  8. Click OK to return to the Manage Content Databases page.

  9. Repeat these steps for any additional content databases.

After all content databases have been removed, the next step is to remove Windows SharePoint Services from the virtual servers.

Remove Windows SharePoint Services from a virtual server

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Virtual Server Configuration section, click Configure virtual server settings.

  3. On the Virtual Server List page, select the virtual server from which you want to remove Windows SharePoint Services.

  4. On the Virtual Server Settings page, in the Virtual Server Management section, click Remove Windows SharePoint Services from virtual server.

  5. On the Remove Windows SharePoint Services from Virtual Server page, select Remove without deleting content databases, and then click OK.

  6. Repeat steps 2 through 5 for each additional virtual server extended with Windows SharePoint Services.

Copying the Database Files to the SQL 2005 Folder

The next step is to stop the WMSDE SQL service and copy the MDF and LDF database files to the SQL Server 2005 Data directory.

Stop the WMSDE SQL service

  1. Click Start, point to Administration Tools, and then click Services.

  2. Scroll down the list, right-click MSSQL$Sharepoint, and then click Stop.

Back up MDF and LDF database files

  1. Locate the following files:

    • STS_config.mdf

    • STS_<servername>_1.mdf

    • STS_config_log.LDF

    • STS_<servername>_1_log.LDF

      Note

      These files are in the %drive%\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data folder, by default.

      Note

      If you have extended multiple virtual servers with Windows SharePoint Services, there will be multiple database files. Ensure that you restore all your SharePoint databases.

  2. Copy the files (listed above) to the %drive%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder.

    Note

    It is critical that you copy the files from this directory and not your backup copy because the administrative changes made up to this point are contained in the configuration database that is in this directory.

Attaching the Configuration and Content Databases to SQL Server 2005

After you have copied your database files to the appropriate folder, the next step is to attach your configuration and content databases to SQL Server 2005.

Attach the configuration and content databases

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. On the Connect to Server page, select the name of the local server from the Server name drop-down list.

  3. Select Windows Authentication from the Authentication drop-down list and then click Connect.

  4. In Object Explorer, click the plus sign (+) next to the server name.

  5. Right-click Databases, and then click Attach.

  6. On the Attach Databases page, click Add.

  7. On the Locate Database Files - <Servername> page, navigate to the location in which you copied the MDF and LDF database files (%drive%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data), select the STS_config.mdf file, and then click OK.

    The MDF and associated LDF files appear in the details section of the Attach Databases page.

  8. On the Attach Databases page, click Add.

  9. Select the MDF file for your content database and click OK.

    By default, the content database is named STS_<Servername>_1.mdf.

  10. Repeat steps 8 through 9 for each additional content database.

  11. Click OK to close the Attach Databases page.

  12. In Object Explorer, click the plus sign (+) next to the Databases node and ensure the Databases you added are listed.

Configure Permissions for the SharePoint Application Pool account

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. On the Connect to Server page, select the name of the local server from the Server name drop-down list.

  3. Select Windows Authentication from the Authentication drop-down list and then click Connect.

  4. In Object Explorer, click the plus sign (+) next to the server name.

  5. Right-click the Security node, point to New and then click Login.

  6. In the Login – New page, in the Login name box, type NT Authority\Network Service, and then click OK.

  7. In Object Explorer, click the plus sign (+) next to Logins, right-click NT Authority\Network Service, and then click Properties.

  8. On the NT Authority\Network Service page, in the Select a page section, click Server Roles.

  9. In the Server Roles column, select the dbcreator and securityadmin check-boxes, and then click OK.

  10. In Object Explorer, click the plus sign (+) next to Security, right-click NT Authority\Network Service, and then click Properties.

  11. On the Login Properties – NT Authority\Network Service page, in the Select a page section, click User Mapping.

  12. In the Users mapped to this login section, ensure that the check-boxes for all SharePoint databases are selected.

  13. Ensure that the db_owner and public check-boxes are selected for all SharePoint databases, and then click OK.

Reconnecting to the Configuration Database and restarting the Virtual Servers

After your databases have been attached to SQL Server 2005, the next steps are reconnecting Windows SharePoint Services to the configuration database and then restarting the virtual servers.

Connect to the restored configuration database

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Server Configuration section, click Set configuration database server.

  3. On the Set Configuration Database Server page, in the Database server box, type the NetBIOS name of the server.

    Tip

    The NetBIOS name of your server may already be displayed and appended with the text “\SharePoint”. In this case, simply delete the text “\SharePoint” that is appended to the NetBIOS name.

  4. In the SQL Server database name box, type the name of the configuration database.

    By default, the configuration database is named STS_Config.

  5. Select the Connect to existing configuration database check-box and click OK.

Use the following steps to restart the virtual servers that you stopped earlier.

Restart Virtual Servers

  1. Click Start, 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 restart.

  3. Click the plus sign (+) next to the Web Sites folder.

  4. Right-click the virtual server you want to restart, and then click Start.

  5. Perform step 4 for each virtual server that you want to restart.

Extending the Virtual Servers and Adding the Content Databases

After you have reconnected Windows SharePoint Services to the configuration database and restarted the virtual servers, the next steps are to extend the virtual servers and then add the content databases to each virtual server that will run Windows SharePoint Services.

Perform the following steps to extend the default virtual server.

Extend a virtual server

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Virtual Server Configuration section, click Extend or upgrade virtual server.

  3. On the Virtual Server List page, click the name of the virtual server to extend.

  4. On the Extend Virtual Server page, in the Provisioning Options section, click Extend and map to another virtual server.

  5. On the Extend and Map to Another Virtual Server page, in the Server Mapping section, select the name of the virtual server that contained the original sites from the Host name or IIS virtual server name drop-down list.

  6. In the Application Pool section, select Use an existing application pool and then select StsAppPool1 (NT AUTHORITY\NETWORK SERVICE) from the drop-down list.

  7. In the Security Configuration section, select NTLM, and then click OK.

    Note

    Do not close SharePoint Central Administration, because you will need it in the next procedure.

After the virtual server is extended, the next step is to add the restored content databases.

Add the restored content databases

  1. In SharePoint Central Administration, on the Virtual Server Settings page, in the Virtual Server Management section, click Manage Content Databases.

  2. On the Manage Content Databases page, in the Content Databases section, click Add a content database.

  3. On the Add Content Database page, in the Database Information section, click Specify database server settings.

  4. In the Database name box, type the name of the restored content database.

    Note

    By default, the configuration database is named STS_<Servername>_1.

  5. In the Database Capacity Settings section, fill in the capacity settings you want to use, and then click OK.

  6. Repeat steps 3 through 5 to add any additional content databases.

Updating the Default Content Database Server

After all of the databases have been migrated, 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 Administrative Tools, and then click SharePoint Central Administration.

  2. In the Server Configuration section, click Set default content database server.

  3. In the Content Database Server section, type the NetBIOS name of your server.

    Tip

    The NetBIOS name of your server may already be displayed and appended with the text \SharePoint. In this case, simply delete the text "\SharePoint" that is appended to the NetBIOS name.

  4. Click OK.

You have completed your migration from WMSDE to SQL Server 2005 in a single-server scenario.

Migrating Databases to a Separate Database Server

This section describes how to migrate configuration and content databases from WMSDE to SQL Server 2005 running on a back-end server in a server farm deployment. To accomplish this migration, you must perform the steps described in this section using Internet Information Services (IIS) Manager, SharePoint Central Administration HTML pages, and SQL Server administration tools.

Because this process requires your sites to be offline while the databases are migrated, it is recommended that you perform these steps at a time when usage of your sites is generally low. You should also notify users that their sites will be offline for a time during the migration period.

The migration instructions provided in this section assume that you will continue to use your original server as a front-end Web server running Windows SharePoint Services and that you are moving the configuration and content databases from WMSDE to a new back-end database server running SQL Server 2005.

The following list describes the high level steps in the order they must be performed. Each of these steps are described in detail in following sections.

  1. Install SQL Server 2005 on the back-end server.

  2. Backup the configuration and content databases.

  3. Stop all virtual servers that are hosting SharePoint sites, so that users cannot make changes to the sites.

  4. Remove the content databases and Windows SharePoint Services from the virtual server.

  5. Update the application pool identity for SharePoint Central Administrations with a domain account.

  6. Copy the database files to the back-end server.

  7. Attach the configuration and content databases to SQL Server 2005.

  8. Change the database ownership and permissions for the configuration and content databases

  9. Reconnect to the configuration database and restart virtual servers.

  10. Extend the virtual servers and add the content databases.

  11. Update the default content database server for future content database creation.

Installing SQL Server 2005

The first step is to install SQL Server 2005 on the back-end server. By performing this step first, in a server farm deployment, you minimize the amount of downtime required for migrating your databases.

Note

This procedure assumes that the back-end database server is not being upgraded from SQL Server 2000. Rather, you are performing a new installation of SQL Server.

Install SQL Server 2005

  1. On the End User License Agreement page, review the license agreement, select the I accept the licensing terms and conditions check box if appropriate, and then click Next.

  2. On the Installing Prerequisites page, click Install.

    Note

    The status bar at the bottom of the Installing Prerequisites page indicates the progress of installing the prerequisites, which can take several minutes to install.

  3. When the prerequisites are finished installing, click Next.

  4. On the Welcome to the Microsoft SQL Server Installation Wizard page, click Next.

  5. On the System Configuration Check page, verify that the status of all actions are successful, and then click Next.

  6. On the Registration Information page, enter your name, company name, Product Key, and then click Next.

  7. On the Components to Install page, select the SQL Server Database Services check-box and then click Next.

  8. On the Instance Name page, select Default instance and then click Next.

  9. On the Service Account page, select Use the built-in System account, select Local system from the drop-down list, and then click Next.

  10. On the Authentication Mode page, select Windows Authentication Mode.

    Note

    Windows SharePoint Services supports both Windows Authentication mode and Mixed mode. However, Windows Authentication Mode is recommended.

  11. Click Next.

  12. On the Collation Settings page, click Next.

  13. On the Error and Usage Report Settings page, select the usage reports in which you want to participate, and then click Next.

  14. On the Ready to Install page, click Install.

    The Setup Progress page indicates the progress of the installation.

  15. When the setup progress is complete, click Next.

  16. On the Completing Microsoft SQL Server 2005 Setup page, click Finish.

Backing Up the Configuration and Content Databases

Before you migrate your configuration and content databases, it is a good idea to back them up. If you have the SQL Server client tools installed on your server, you can use them to back up a WMSDE database. Otherwise, you can use the following procedures to stop the WMSDE SQL service and make a copy your LDF and MDF database files.

Stop the WMSDE SQL service

  1. Click Start, point to Administration Tools, and then click Services.

  2. Scroll down the list, right-click MSSQL$Sharepoint, and then click Stop.

Backup LDF and MDF database files

Locate the following files and copy them to a safe backup location:

  • STS_config.mdf

  • STS_<servername>_1.mdf

  • STS_config_log.LDF

  • STS_<servername>_1_log.LDF

    Note

    These database files are in the %drive%\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data folder, by default.

    Note

    If you have extended multiple virtual servers with Windows SharePoint Services, there will be multiple database files in this folder. Ensure that you backup all your SharePoint databases.

Restart the WMSDE SQL service

After you back up your database files to a safe location, you must restart the WMSDE SQL service. This is because you will need to connect to the WMSDE databases in a later procedure.

  1. Click Start, point to Administration Tools, and then click Services.

  2. Scroll down the list, right-click MSSQL$Sharepoint, and then click Start.

Stopping the Virtual Servers Hosting SharePoint Sites

In order to completely back up and restore your SharePoint sites, you must ensure that users cannot make changes to the SharePoint sites, during the migration. To block users from changing the sites, you must use Internet Information Services (IIS) Manager to stop all virtual servers hosting SharePoint sites.

Stop a virtual server in IIS

  1. Click Start, 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.

  5. Perform step 4 for each virtual server that is extended with Windows SharePoint Services.

    Note

    Do not stop the SharePoint Central Administration site, because you will need to access it during the next procedure.

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

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

Remove a content database

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Virtual Server Configuration section, click Configure virtual server settings.

  3. On the Virtual Server List page, select the virtual server that contains the content database.

  4. On the Virtual Server Settings page, in the Virtual Server Management section, click Manage content databases.

  5. On the Manage Content Databases page, in the Content Databases section, select the content database you want to remove.

    By default the content database is named STS_<Servername>_1.

  6. On the Manage Content Database Settings page, in the Remove Content Database section, select the Remove content database check-box.

  7. Click OK to close the warning dialog box.

  8. Click OK to return to the Manage Content Databases page.

  9. Repeat these steps for any additional content databases.

After all content databases have been removed, the next step is to remove Windows SharePoint Services from the virtual servers.

Remove Windows SharePoint Services from a virtual server

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Virtual Server Configuration section, click Configure virtual server settings.

  3. On the Virtual Server List page, select the virtual server from which you want to remove Windows SharePoint Services.

  4. On the Virtual Server Settings page, in the Virtual Server Management section, click Remove Windows SharePoint Services from virtual server.

  5. On the Remove Windows SharePoint Services from Virtual Server page, select Remove without deleting content databases, and then click OK.

  6. Repeat steps 2 through 5 for each additional virtual server extended with Windows SharePoint Services.

Updating the Application Pool Identity 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 identity 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 identity for SharePoint Central Administration

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the SharePoint Central Administration page, in the Server Configuration section, 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 application pool identity in the form of Domain\Username.

  5. In the Password box, type the password for the user name you specified in step 4.

  6. In the Confirm password box, type the password again.

  7. In the Security Configuration section, choose NTLM.

  8. Click OK.

After you update the application pool identity for SharePoint Central Administration, you must restart IIS. To restart IIS, on the command line, type iisreset and press ENTER.

Copying the Database Files to the back-end Server

The next step is to stop the WMSDE SQL service on the front-end Web server and copy the MDF and LDF database files to the back-end server.

Stop the WMSDE SQL service

  1. On the front-end Web server, click Start, point to Administration Tools, and then click Services.

  2. Scroll down the list, right-click MSSQL$Sharepoint, and then click Stop.

Back up MDF and LDF database files

  1. Locate the following files:

    • STS_config.mdf

    • STS_<servername>_1.mdf

    • STS_config_log.LDF

    • STS_<servername>_1_log.LDF

  2. Copy the files (listed above) to the %drive%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder on the back-end server running SQL Server 2005.

    Note

    It is critical that you copy the files from this directory and not your backup copy because the administrative changes made up to this point are contained in the configuration database that is in this directory.

Attaching the Configuration and Content Databases to SQL Server 2005

After you have copied your database files to the appropriate folder, the next step is to attach your configuration and content databases to SQL Server 2005.

Attach the Configuration and Content Databases to SQL Server 2005

  1. On the back-end server running SQL Server 2005, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. On the Connect to Server page, select the name of the local server from the Server name drop-down list.

  3. Select Windows Authentication from the Authentication drop-down list and then click Connect.

  4. In Object Explorer, click the plus sign (+) next to the server name.

  5. Right-click Databases, and then click Attach.

  6. On the Attach Databases page, click Add.

  7. On the Locate Database Files - <Servername> page, navigate to the location in which you copied the MDF and LDF database files (%drive%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data), select the STS_config.mdf file, and then click OK.

    The MDF and associated LDF files appear in the details section of the Attach Databases page.

  8. On the Attach Databases page, click Add.

  9. Select the MDF file for your content database and click OK.

    By default, the content database is named STS_<Servername>_1.mdf.

  10. Repeat steps 8 through 9 for each additional content database.

  11. Click OK to close the Attach Databases page.

  12. In Object Explorer, click the plus sign (+) next to the Databases node and ensure the Databases you added are listed.

Changing the Database Ownership and Permissions for the Configuration and Content Databases

After your databases have been attached to SQL Server 2005, you must change the database ownership and permissions for the configuration and content databases to grant permissions to the application pool identities 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 server running SQL Server 2005, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. On the Connect to Server page, select the name of the local server from the Server name drop-down list.

  3. Select Windows Authentication from the Authentication drop-down list and then click Connect.

  4. In Object Explorer, click the plus sign (+) next to the Databases node.

  5. Right-click the configuration database (STS_Config) and then click New Query.

  6. 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'

    Note

    In the query, 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.

  7. On the toolbar, click the Execute button to run the query and update the database.

Change the database ownership and permissions for the content databases

  1. On the server running SQL Server 2005, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. On the Connect to Server page, select the name of the local server from the Server name drop-down list.

  3. Select Windows Authentication from the Authentication drop-down list and then click Connect.

  4. In Object Explorer, click the plus sign (+) next to the Databases node.

  5. Right-click the content database (STS_servername_1) and then click New Query.

  6. 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 the query, 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.

  7. On the toolbar, click the Execute button to run the query and update the database.

Reconnecting to the Configuration Database and Restarting the Virtual Servers

After the permissions have been set, the next steps are reconnecting Windows SharePoint Services to the configuration database and then restarting the virtual servers.

Connect to the restored configuration database

  1. On the server running Windows SharePoint Services, click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Server Configuration section, click Set configuration database server.

  3. On the Set Configuration Database Server page, in the Database server box, type the NetBIOS name of the server that is running SQL Server 2005.

    Tip

    This box may display the NetBIOS name of your server running Windows SharePoint Services appended with “\SharePoint”. You must delete this text and replace it with the NetBIOS name of the back-end server running SQL Server 2005.

  4. In the SQL Server database name box, type the name of the configuration database.

    By default, the configuration database is named STS_Config.

  5. Select the Connect to existing configuration database check-box and click OK.

Use the following steps to restart the virtual servers that you stopped earlier.

Restart Virtual Servers

  1. On the front-end Web server, click Start, 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 restart.

  3. Click the plus sign (+) next to the Web Sites folder.

  4. Right-click the virtual server you want to restart, and then click Start.

  5. Perform step 4 for each virtual server that you want to restart.

Extending the Virtual Servers and Adding the Content Databases

After you have reconnected Windows SharePoint Services to the configuration database and restarted the virtual servers, the next steps are to extend the virtual servers with Windows SharePoint Services and then add the content databases to each virtual server that will run Windows SharePoint Services.

Perform the following steps to extend the default virtual server.

Extend a virtual server

  1. On the front-end Web server, click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. On the Central Administration page, in the Virtual Server Configuration section, click Extend or upgrade virtual server.

  3. On the Virtual Server List page, click the name of the virtual server to extend.

  4. On the Extend Virtual Server page, in the Provisioning Options section, click Extend and map to another virtual server.

  5. On the Extend and Map to Another Virtual Server page, in the Server Mapping section, select the name of the virtual server that contained the original sites from the Host name or IIS virtual server name drop-down list.

  6. In the Application Pool section, select Create a new application pool.

  7. In the Application poolname box, type the new application pool name.

  8. Under Select a security account for this application pool, select Configurable.

  9. In the User name box, type the account name.

    Note

    You must specify the same account that you configured for the ContentVSAccount with SQL Query Analyzer, earlier.

  10. In the Password box, type the password for the account.

  11. In the Confirm password box, type the password again.

  12. Click NTLM.

  13. Click OK.

    Note

    Do not close SharePoint Central Administration, because you will need it in the next procedure.

After the virtual server is extended, the next step is to add the restored content databases.

Add the restored content databases

  1. In SharePoint Central Administration, on the Virtual Server Settings page, in the Virtual Server Management section, click Manage Content Databases.

  2. On the Manage Content Databases page, in the Content Databases section, click Add a content database.

  3. On the Add Content Database page, in the Database Information section, click Specify database server settings.

  4. In the Database name box, type the name of the restored content database.

    Note

    By default, the configuration database is named STS_<Servername>_1.

  5. In the Database Capacity Settings section, fill in the capacity settings you want to use, and then click OK.

  6. Repeat steps 3 through 5 to add any additional content databases.

Updating the Default Content Database Server

After all of the databases have been migrated, 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. On the front-end Web server, click Start, point to Administrative Tools, and then click SharePoint Central Administration.

  2. In the Server Configuration section, click Set default content database server.

  3. In the Content Database Server section, type the NetBIOS name of the back-end server that is running SQL Server 2005.

    Tip

    This box will display the NetBIOS name of your server running Windows SharePoint Services appended with “\SharePoint”. You must delete this text and replace it with the NetBIOS name of the back-end server running SQL Server 2005.

  4. Click OK.

You have completed your migration from WMSDE to SQL Server 2005 in a server farm scenario.