Project Server Cache Synchronization

  • Article

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 for deletions of those entities. 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 in determining issues of performance optimization. (This article was written by Patrick Conlan, Microsoft Corporation.)

Warning:   The information in this article is applies only to the specified versions of Project Server, and is subject to change without notice.

In Project Server 2007, we 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 was the introduction of several caches, specifically:

  • Client side Active Cache, which creates the *.mpc files that Project Professional users can see on their client computers.
  • Server side Published, Statusing and Reporting SQL schema, which are used to store a project plan of record, enable simultaneous status reporting, and help to make server reporting easy.
  • Server side Archive SQL schema, which 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 data uses the Published database.

The caches are synchronized during data movement operations such as the following:

  • Project Professional opens a project from Project Server.
  • Publishing a project, where data is copied from the Draft database to the Published database.
  • Publishing to the reporting database, which is implicit in 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. Some of you may have already worked out some of the versioning processes!

Depending on the number of project managers who use Project Professional within an organization, there can be hundreds or even thousands of caches - one on every Project Professional desktop plus the caches in the 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, MSP_Projects is the primary table for 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.

Version Tracking Schema

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 and PROJ_CURRENT_REV_RANK fields 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 resetting the REV_RANK field 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 and MOD_REV_COUNTER contain the active version number. There are two numbers to enable us to see when the document was lasted 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

We had two options for handling deletes in the cache coherency model:

  • Flag the existing data as deleted (an update), or
  • Delete the data and record the deletion, so synchronization can propagate the deletion onward.

Flagging would give better short term performance (less impact 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 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 just 28 bytes of data in a row of the MSP_PROJECTS_SHADOW table (16 bytes for the GUID + 4 bytes for the INT + 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.