Project Server Cache Synchronization

  • Article

Applies to: Office 2010 | Project 2010 | Project Server 2010 | SharePoint Server 2010

In this article
Understanding Project Server Caches and Synchronization
Tracking Project-Level Versions
Handling Deletes with Shadow Tables

Microsoft Office Project Server 2007 and Microsoft Project Server 2010 internally manage cache synchronization by tracking revisions to major entities such as projects and maintaining records of entity deletion. Although the Draft, Published, and Archive databases in Project Server are not documented, and direct access to those databases is not supported, a basic understanding of cache synchronization in Project Server can be useful to help with issues of performance optimization. (This article was written by Patrick Conlan, Microsoft Corporation.)

Warning

The information in this article applies only to the specified versions of Project Server. The database schemas and algorithms for calculating field values are subject to change without notice.

This article includes the following sections:

  • Understanding Project Server Caches and Synchronization

  • Tracking Project-Level Versions

  • Handling Deletes with Shadow Tables

Understanding Project Server Caches and Synchronization

In Office Project Server 2007, the Project team made a series of changes that were designed to improve the scalability and reliability of Project Server, especially when it is under usage stress. One of the most significant changes we made was the introduction of several caches, specifically:

  • The client-side Active Cache component, which creates the .mpc files that Project Professional users can see on their client computers, enables working on projects when offline.

  • Server-side SQL schemas include Published, Statusing, and Reporting. The SQL schemas can store a project plan of record, enable simultaneous status reporting, and help to make server reporting easier.

  • The server-side Archive SQL schema caches older data and enables fast administrative restore from the Archive database.

The location of the master copy of data varies by content. Projects use the Draft database; most other Project Server entities use the Published database to store data. The caches are synchronized during data movement operations such as the following:

  • Opening a project from Project Server, by using Project Professional.

  • Publishing a project. Data is copied from the Draft database to the Published database.

  • Publishing to the Reporting database, which Project Server automatically handles when publishing a project.

  • Many metadata operations move data within a database and between databases.

We use a fairly complicated versioning scheme to work out what data needs to move as part of the synchronization.

Depending on the number of project managers who use Project Professional within an organization, there can be hundreds or even thousands of caches. There is one cache on every Project Professional desktop, plus the caches in the Microsoft SQL Server databases. Depending on when they were last synchronized, the caches can contain different versions of project entities.

Each entity has a primary table that controls the entity version number. For example, the primary table for projects is MSP_Projects. When an entity is created, and each time the entity is saved, Project Server uses the entity version to aggregate the edit session changes.

Tracking Project-Level Versions

The following schema fragment from the MSP_Projects table shows the fields that are used to track changes at the project level.

[REV_NEXT_SEED], 
[REV_RANK], 
[PROJ_CURRENT_REV_COUNTER], 
[PROJ_CURRENT_REV_RANK], 
[CREATED_REV_COUNTER], 
[MOD_REV_COUNTER]

The REV_RANK field and the PROJ_CURRENT_REV_RANK field are used to signal an archive restore. If synchronization shows a disparity of rank between caches, then Project Server performs a full resynchronization (in effect, the REV_RANK field is reset with a revision = 0)

The REV_NEXT_SEED field contains the next version number to be used for any changes. The PROJ_CURRENT_REV_COUNTER field and the MOD_REV_COUNTER field contain the active version number. The two revision counter numbers enable you to determine when the document was last edited by Project Professional or by another application.

The CREATED_REV_COUNTER field is either 1 or 0, depending on how the project was created. When Project Professional creates a project, CREATED_REV_COUNTER = 1.

Handling Deletes with Shadow Tables

To make a consistent model for caches, we had two options for handling deletes:

  • Flag the existing data as deleted, by using an UPDATE query.

  • Delete the data and record the deletion, so that synchronization can propagate the deletion onward.

Flagging would give better short-term performance (less effect on SQL Server), but would quickly cause data bloat within the most important tables. We decided to use the second option of deleting the data and creating a record of the deletion.

We use shadow tables to record delete operations for tables that are in the cache synchronization scheme. Shadow tables are very narrow tables that contain the minimum data that is needed to track deletes. Project Server updates the shadow tables when major entities are deleted.

The following schema is for the project shadow table, MSP_PROJECTS_SHADOW.

[PROJ_UID] [dbo].[UID] NOT NULL, 
[DELETED_REV_COUNTER] [int] NOT NULL, 
[DELETED_DATE] [datetime] NOT NULL

When a project is deleted, it uses only 28 bytes of data per row in the MSP_PROJECTS_SHADOW table (16 bytes for the GUID + 4 bytes for the integer + 8 bytes for the DateTime value), plus normal SQL Server page overhead.

Administrators of Project Server installations that have a lot of data churn should maintain an active archive and delete policy to take advantage of the shadow tables and improve performance.