Share via

  • Article

Configure availability by using SQL Server database mirroring (Project Server 2010)

 

Applies to: Project Server 2010

Topic Last Modified: 2011-09-11

Microsoft SQL Server database mirroring provides availability support by sending transactions directly from a principal database and server to a mirror database and server. This can provide redundancy for the four Microsoft Project Server 2010 databases and for the associated content database. This is useful in the event of a system failure, and also for regular system maintenance.

This article describes requirements for using database mirroring with a Microsoft Project Web App (PWA) site. This article applies specifically to the four Project Server 2010 databases (Draft, Published, Archive, and Reporting), and also to the Microsoft SharePoint Server 2010 content database associated with PWA. For information about database mirroring for the rest of your SharePoint Server 2010 farm, see Configure availability by using SQL Server clustering (SharePoint Server 2010)

Database mirroring for Project Server 2010 can only be configured by using Windows PowerShell. When you create a new PWA site, use the New-SPProjectWebInstance cmdlet; when you update an existing PWA site for use with database mirroring, use the Set-SPProjectWebInstance cmdlet. By using these cmdlets, you can specify the primary and secondary mirroring servers for the four Project Server 2010 databases.

For detailed information about how to configure database mirroring in SQL Server, see Database Mirroring.

Important

Project Server 2010 requires synchronous mirroring.

Failover considerations

Project Server 2010 requires manual failover for the Draft, Published, and Archive databases. These databases all interact with one another. These databases must all reside on the same instance of SQL Server. Using automatic failover with a witness server could cause one of these databases to fail over to the mirror server while the other databases remain on the primary server. This could result in failures in Project Server Interface (PSI) calls and other Project Server functionality. When you have to fail over the Draft, Published, or Archive database, manually fail over all three of them to the secondary server at the same time.

The Reporting database operates independently of the other three Project Server databases. Use of a witness server and automatic failover for the Reporting database is supported.

When failing over the Project Server databases for the purposes of system maintenance or other non-urgent tasks, we recommend failing over during a time of low usage for the system. This minimizes the time that is required for log-redo tasks on the secondary server and lets you bring Project Server online on the secondary server faster.

Video demonstration

This video shows the steps involved in configuring database mirroring.

Screenshot of video

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

Configuration

We recommend that you configure mirroring at a time of minimal system use. This speeds initial database synchronization.

Before you configure SQL Server mirroring, ensure that any database access accounts that are used by SharePoint Server or Project Server are duplicated on the instance of SQL Server where you plan to deploy your mirrored databases. This includes the Farm Administrator account and any other accounts that you might use for database access related to Project Server.

We recommend that you transfer your logins and permissions from the principal server to the mirror server by running a script. An example script is available in Knowledge Base article 918992 How to transfer the logins and the passwords between instances of SQL Server 2005 (https://go.microsoft.com/fwlink/p/?LinkId=122053\&clcid=0x409). For more information about how to transfer SQL Server metadata between instances, see the SQL Server Books Online article Managing Metadata When Making a Database Available on Another Server Instance (https://go.microsoft.com/fwlink/p/?LinkId=122055\&clcid=0x409).

SQL Server mirroring requires that the recovery model be set to Full on each database that you plan to mirror. Full recovery is the default for Project Server databases, but we recommend that you confirm this setting before configuring mirroring.

Perform the following procedure for each Project Server database that you plan to mirror.

To set the recovery model

  1. In SQL Server Management Studio, expand Databases.

  2. Right-click the database, and then click Properties.

  3. In the left pane, select Options.

  4. From the Recovery model list, choose Full.

  5. Click OK.

Once you have verified the recovery model, you must do the following:

  1. Configure SQL Server mirroring in SQL Server for each database that you want to mirror.

  2. Configure the PWA settings to point to the mirror server.

  3. If you are mirroring the content database, configure SharePoint Server to mirror that content database.

Configure mirroring on SQL Server

You must back up each database that you plan to mirror and restore it to the instance of SQL Server where you want the mirror to reside. A transaction log backup is also required.

Important

The Draft, Archive, and Published databases must all be running on the same instance of SQL Server. If you mirror one of them, you must mirror all three of them.

Perform the following procedures on each Project Server database that you plan to mirror.

To back up a database

  1. In SQL Server Management Studio, expand Databases.

  2. Right-click the database, click Tasks, and then click Back up.

  3. Confirm that the backup type is Full.

  4. Set the backup destination to Disk and choose a location on the local disk.

  5. Click OK.

Once the database backup has finished, you must back up the transaction log.

To back up the transaction log

  1. In SQL Server Management Studio, expand Databases.

  2. Right-click the database, click Tasks, and then click Back up.

  3. In the Backup type list, select Transaction Log.

  4. Set the backup destination to the same file and location as the Full backup.

  5. Click OK.

Once you have backed up each database, copy the database backup files to the instance of SQL Server where you want to create the mirror.

On the instance of SQL Server where you want to create the mirror, restore each database by using the following procedure.

To restore a database for mirroring

  1. In SQL Server Management Studio, right-click Databases, and then click Restore Database.

  2. In the To database box, type the name of the database that you are restoring.

  3. Select the From device option, and then click Browse.

  4. In the Specify Backup dialog box, confirm that the Backup media type is File and then click Add.

  5. Navigate to the location of your backup files and select the database that you want to restore.

  6. Click OK.

  7. Click OK.

  8. In the Select backup sets to restore table, select the check boxes for both the Full and Transaction Log backups.

  9. In the left pane, click Options.

  10. In the Recovery state section, select the RESTORE WITH NORECOVERY option.

    Important

    You must restore the database by using NORECOVERY for mirroring to function.

  11. Click OK.

Once the backups have been restored, you can configure mirroring. For each database that you want to mirror, perform the following procedure on the instance of SQL Server where your active databases reside.

To configure mirroring

  1. In SQL Server Management Studio, expand Databases.

  2. Right-click the database that you want to mirror, click Tasks, and then click Mirror.

  3. On the Database Properties dialog box, click Configure Security.

  4. On the wizard, click Next.

  5. On the Include Witness Server page, select the No option, and then click Next.

  6. On the Principal Server Instance page, click Next.

  7. On the Mirror Server Instance page, click Connect.

  8. On the Connect to Server dialog box, type the name of the mirror server, and then click Connect.

  9. Click Next.

  10. On the Service Accounts page, type the names of the service accounts for the Principal and Mirror servers, and then click Next.

  11. Click Finish.

  12. When the Configuring Endpoints dialog box shows Success, click Close.

  13. On the Database Properties dialog box, click Start Mirroring.

  14. Click OK.

Once SQL Server is configured for mirroring, you must configure Project Server. You must also configure SharePoint Server if you are mirroring the content database.

Configure Project Server for mirroring

Configuring Project Server for mirroring involves using the Windows PowerShell Set-SPProjectWebInstance cmdlet to set the mirror server for the Project Server database that you are mirroring. The -PrimaryDBMirrorServer parameter configures the mirror server for the Draft, Archive, and Published databases. The -ReportingDBMirrorServer parameter configures the mirror server for the Reporting database.

Perform the following procedure on the application server where Project Server is running.

To configure mirroring in Project Server

  1. Click Start, All Programs, Microsoft SharePoint 2010 Products.

  2. Right-click SharePoint 2010 Management Shell, and then click Run as administrator.

  3. Run the Windows PowerShell Set-SPProjectWebInstance cmdlet with the appropriate values for your mirror servers. For example:

    Set-SPProjectWebInstance -AdminAccount Litware\FarmAdmin -ArchiveDbname Proj_Archive -DraftDbname Proj_Draft -PrimaryDbserver SQL1 -PublishedDbname Proj_Pub -ReportingDbname Proj_Rep -ReportingDbserver SQL1 -PrimaryDBMirrorServer SQL2 -ReportingDBMirrorServer SQL2 -Url http://SharePointFarm/pwa

Database mirroring configuration is now complete for the Project Server databases.

Configure SharePoint Server for mirroring

If you are mirroring the content database, you must configure the Failover Database Server settings in the SharePoint Central Administration Web site after database mirroring has been configured in SQL Server. Perform the following procedure for the content database if you are mirroring the content database.

To configure SharePoint Server to mirror the content database

  1. In Central Administration, under Application Management, click Manage content databases.

  2. In the Database Name section, click the Project Server content database that you want to mirror.

  3. On the Database settings page, in the Failover Database Server box, type the name of your mirror server.

  4. Click OK.

The content database is now configured to enable failover to the mirror server.

Failing over databases

You can decide to fail databases over to the mirror server for several reasons, including the need to perform maintenance on the primary server, or in response to a hardware failure on the primary server.

When failing over Project Server databases, be aware of the following:

  • The Draft, Archive, and Published databases must all be running on the same instance of SQL Server. If you fail one of them over, you must fail over the other two also. The Reporting database and the content database can each be failed over independently.

  • Failing over databases is not a method of moving databases to a new server permanently. Project Server is still configured to use the original database server; it merely redirects database calls to the mirror server until you fail the databases back to the original server.

Failing over a mirrored database is done through SQL Server. Use the following procedure to fail over a database to the mirror server. Perform this procedure on the instance of SQL Server where the mirroring Principal is located (not the mirror).

To fail a database over to the mirror server

  1. In SQL Server Management Studio, expand Databases.

  2. Right-click the database, click Tasks, and then click Mirror.

  3. Click Failover.

  4. On the confirmation dialog box, click Yes.

When you fail over a database, SQL Server will swap roles between the principal and mirror servers — the mirror will become the principal and the principal will become the mirror. To fail back, perform the same procedure on the instance of SQL Server where the now-principal database is located.

Note that if you fail over databases in response to a hardware failure or other problem on the primary server, the farm can run on the mirror server as long as necessary. However, you will not have database redundancy until you fix the failure and reconfigure mirroring.

When you have completed the necessary maintenance or repairs on the primary server, make sure that you fail your databases back to the original server.