Tutorials

SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005

SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005

Windows SBS 2003 R2 Premium Edition includes SQL Server 2005 Workgroup Edition. You can install SQL Server 2005 Workgroup Edition as your database for a business application. Additionally, you can upgrade the instance of Microsoft SQL Server Desktop Engine (Windows) (MSDE) used by Microsoft Windows SharePoint® Services if you want to be able to search document libraries on your company's internal Web site.

Table of contents

TERMS This document and what comes with it are provided as-is with blunt warning: Use at your own risk, buyer beware.You break your system; you own the resolution as well. We have no liability for what you do, or can't do, or fail to do with this information. Your entire protection is to start over again with a protected backup, or from protected system. If you don't want to accept this idea, please don't use this document.

Based on the SQLINSTALLSTEPS readme from the R2 Premium CD I decided to create an article with all the steps and screenshots. The procedure to upgrade Sharepoint to SQL 2005 is a little different compared to upgrading Sharepoint to SQL 2000.


Prepare the companyweb site

  1. Stop the http://companyweb Web site. To do so, click Start, and then click Server Management. In the Server Management console pane, double-click Advanced Management, double-click Internet Information Services, double-click ServerName (local computer), and then double-click Websites, where ServerName is the name of your server computer. Right-click companyweb, and then click Stop.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  2. Stop the SharePoint Timer Service. To do so, click Start, point to Administrative Tools, and then click Services. Double-click SharePoint Timer Service to open the Properties dialog box. On the General tab, click Stop. Choose Manual for the Startup Type. Click OK to apply the changes and to close the SharePoint Timer Services Properties dialog. Leave Services open for the next step.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  3. Stop the MSSQL$SHAREPOINT service. While still in Services, right-click MSSQL$SHAREPOINT, and then click Stop.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  4. Back up the SharePoint database files. To do so, browse to %SystemDrive%\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\data, and then copy the following files to a backup location of your choosing.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
    Copy the Sharepoint databases

    STS_ServerName_1.mdf
    STS_Config.mdf
    STS_ServerName_1.mdf_log.LDF
    STS_Config_log.LDF
  5. Remove the SHAREPOINT WMSDE instance. Click Start, point to Control Panel, and then click Add or Remove Programs. Select Microsoft SQL Server Desktop Engine (Sharepoint), and then click Remove. Click Yes to confirm that you want to delete MSDE (SharePoint). You must restart the computer before you continue with the following procedures.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005

Create the SQL Server 2005 Workgroup Edition SHAREPOINT instance

  1. From the Autorun page of Premium Technologies Disc 1 (D:\Setup.exe, where D is the letter of your CD drive), click Install Microsoft SQL Server 2005.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  2. On the End User License Agreement page, review the licensing agreement. To continue, you must accept the agreement.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  3. On the Installing Prerequisites page click Install. SQL Server 2005 Setup installs the components that are required for SQL Server setup. When it finishes installing them, click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  4. On the Welcome to the Microsoft SQL Server Installation Wizard page, click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  5. On the System Configuration Check page, verify that you have no potential installation problems, then click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  6. If this is the first instance of SQL Server 2005 that you are installing or upgrading, the Registration Information page appears. By default, Setup automatically enters the product key for you. Enter your Name and Company information, and then click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  7. On the Feature Selection page, click SQL Server Database Services and then click Entire feature will be installed on local hard drive. Expand SQL Server Database Services and ensure that all sub-features, including Full-Text Search, are set to install.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  8. While still on the Feature Selection page, expand Client Components, click Management Tools, and then click Entire feature will be installed on local hard drive. By default, Setup also selects Connectivity Components and SQLXML Client Features for installation. Click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  9. On the Instance Name page, click Named instance. In the Named instance box, type SHAREPOINT.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  10. On the Service Account page, click Use the built-in System account, and then click Local system for the account. Click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  11. On the Authentication Mode page, choose Windows Authentication Mode. Click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  12. On the Collation Settings page, accept the default of Dictionary order, case-insensitive, for use with 1252 Character Set for SQL collations (used for compatibility with previous versions of SQL Server). Click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  13. When the Error and Usage Report Settings page appears, you can choose to automatically send error reports to Microsoft or to your corporate error-reporting server. You can also choose to send data about feature usage for SQL Server 2005 to Microsoft. Click Next.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  14. On the Ready to Install page, click Install.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  15. When SQL Server 2005 Setup prompts you to "Please insert Disc 2," insert Premium Technologies Disc 2, and then click OK.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  16. Click Next when that button becomes available on the Setup Progress page.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  17. On the Completing Microsoft SQL Server 2005 Setup page, click Finish.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005

Configure permissions for the new SHAREPOINT instance of SQL Server 2005

  1. Open SQL Server Management Studio. 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, ensure that Server type: is set to Database Engine and that Server name: is set to ServerName\SHAREPOINT, where ServerName is the name of your server. Click Connect.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  3. In the Object Explorer pane, expand ServerName\SHAREPOINT (SQL Server 9.0.1399 - DomainName\administrator), where DomainName is the name of your Windows SBS domain.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  4. Double-click Security, right-click Logins, and then click New Login.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  5. In Login name, type NT Authority\Network Service, and then click OK.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  6. Repeat steps 4 and 5 to add the DomainName\SBS SP Admins group as a new login.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  7. In the Object Explorer pane, expand Logins, and then double-click the NT Authority\Network Service account.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  8. On the Login Properties page for NT Authority\Network Service, under Select a page, click Server Roles.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  9. Under Server roles, select the dbcreator and securityadmin roles. Click OK.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  10. Keep Microsoft SQL Server Management Studio open for the next procedure.

Attach the database files to the new SHAREPOINT instance of SQL Server 2005

  1. While still in Microsoft SQL Server Management Studio, in Object Explorer , expand ServerName\SHAREPOINT (SQL Server 9.0.1399 - DomainName\administrator), where DomainName is the name of your Windows SBS domain.
  2. Right-click Databases, and then click Attach.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  3. On the Attach Databases page, click Add.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  4. Browse to %SystemDrive%\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\data, select STS_ServerName_1.mdf and then click OK.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
    Note: If you installed the Sharepoint MSDE instance on another drive as described in 'Planning and installing your SBS 2003 Server', you will need to attach the databases from the partition the Sharepoint MSDE instance was originally installed. As you can see my databases are on partition D:.
     
  5. On the Attach Databases page, click OK. The action finishes and Object Explorer in Microsoft SQL Server Management Studio appears.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  6. Repeat step 2 through 5 to attach the STS_Config.mdf database file. After you have repeated steps 2 through 5 for the Sharepoint Services Configuration database the end result you look like.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  7. Restart the Windows SharePoint Timer Service by clicking Start, pointing to Administrative Tools, and then clicking Services. Right-click SharePoint Timer Service, and then click Properties. On the General tab, select Automatic for the Startup Type. Click Start to start the service, and then click OK to close the SharePoint Timer Service Properties dialog box.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  8. Close Services.
  9. Restart http://companyweb by clicking Start and then clicking Server Management. In the Server Management console, double-click Advanced Management, double-click Internet Information Services, double-click ServerName (local computer), and then double-click Websites. Right-click companyweb, and then click Start.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005

Turn on full-text search in Windows SharePoint Services

  1. Click Start, point to Administrative Tools, and then click SharePoint Central Administration.
  2. In Component Configuration, click Configure full-text search.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
  3. Select the Enable full-text search and index component check box, and then click OK.
    SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005SBS 2003 R2 - Upgrading the Sharepoint MSDE instance to SQL 2005
    Check the companyweb for Full-Text search. The search box must appear at the top right site of your companyweb.

Subscribe and receive ‘how to’ and ‘best practice’ articles on server and cloud maintenance, design and troubleshooting.

Join our community.

Excellent content, great people!

Like what you see? Join us for free*

  • Monthly newsletter with a summary of all new tutorials
  • Get an email as soon as a new tutorial has been published

What others say about the tutorials