• Article

Move the Reporting database to a new server (Project Server 2010)

 

Applies to: Project Server 2010

Topic Last Modified: 2011-08-05

Microsoft Project Server 2010 performance can be improved by deploying the Reporting database to a separate instance of Microsoft SQL Server from the other three Project Server databases. If you want to move the Reporting database to a different instance of SQL Server after you have deployed your Microsoft Project Web App (PWA) site, you can do so by using the procedures in this article.

Moving the Reporting database is a major maintenance procedure and should be done at a time of minimal system activity. Users cannot access the PWA site while you are performing these procedures.

The basic steps involved in moving the Reporting database are as follows:

  1. Unprovision the PWA site

  2. Detach the Reporting database

  3. Copy the Reporting database to the new instance of SQL Server

  4. Attach the Reporting database to the new instance of SQL Server

  5. Reprovision the PWA site

Video demonstration

This video shows the steps involved in moving the Reporting database to a new instance of Microsoft SQL Server.

Screenshot of video

Watch the video (https://go.microsoft.com/fwlink/p/?LinkId=202555). To download the video file, right-click the link, and then click Save Target As.

Unprovision the PWA site

When moving the Reporting database, you must unprovision the PWA site as a first step. This removes references to the site from Microsoft SharePoint Server 2010 without making any changes to the site itself. Once you have done this, you can reconfigure PWA by moving the Reporting database and then reprovision the PWA site.

To unprovision the PWA site

  1. In the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.

  2. On the Manage Service Applications page, click the Project Server service application.

  3. On the Manage Project Web App Sites page, click the drop-down menu for the PWA site where you want to move the reporting database, and then click Delete.

  4. On the Delete Project Web App Site page:

    1. Clear the Delete site collection from SharePoint check box.

      Warning

      The Delete site collection from SharePoint check box must be cleared or else the PWA site will be deleted from the Content database.

    2. Click Delete.

Unprovisioning the site may take several minutes. Once the PWA site is no longer listed on the Manage Project Web App Sites page, it is unprovisioned and you can move the reporting database.

Move the reporting database

The Reporting database can be moved by detaching it from the current instance of SQL Server and attaching it to the new instance of SQL Server. We recommend that you have your database administrator perform these steps if you are unfamiliar with moving databases in SQL Server.

To move the Reporting database

  1. Start SQL Server Management Studio.

  2. Connect to the instance of SQL Server where the Reporting database is located.

  3. In Object Explorer, expand Databases.

  4. Right-click the Reporting database, click Tasks, and then click Detach.

  5. Copy the database files (.mdf and .ldf files) to the new instance of SQL Server.

  6. In SQL Server Management Studio, in Object Explorer, click Connect, and then click Database Engine.

  7. Connect to the instance of SQL Server where you copied the Reporting database.

  8. Right-click Databases and then click Attach.

  9. Click Add.

  10. Select the Reporting database (.mdf) file and then click OK.

  11. Click OK.

Once the Reporting database is attached to the new instance of SQL Server, you can reprovision the PWA site.

Reprovision the PWA site

Reprovisioning the PWA site is achieved by adding a new PWA site and connecting it to your existing databases and site collection. This is performed from SharePoint Central Administration.

To update PWA properties

  1. In SharePoint Central Administration, in the Application Management section, click Manage service applications.

  2. Click the Project Server service application.

  3. On the Manage Project Web App Sites page, click Create Project Web App site.

  4. On the Create Project Web App Site page:

    1. Check all settings to make sure that they match the PWA site that you unprovisioned. Database names must exactly match the databases in SQL Server for this PWA site.

    2. In the Reporting Database section, make sure that the values for Reporting database server and Reporting database name correspond to the Reporting database that you just moved.

  5. Click OK.

Once the PWA site has been reprovisioned (when the status is Provisioned), the users can return to using PWA as usual.