The database schema of Project Server 2007 is very different from the database schema and use in Project Server 2003. The entire data of MS Project Server 2003 used to store in one database in SQL Server.
Project Server 2007 data is partitioned into following four databases in SQL Server.
- Draft database
- Published database
- Archive database
- Reporting database
The Draft database contains tables for saving unpublished projects from Office Project Professional 2007. Project Web Access does not show project data in the Draft database.
Example: If Project Manager updates the project plan and saved to server, those updates will be saved to Draft database.
The Published database contains all of the published projects as well as enterprise resources, the enterprise global template, and other project templates.
Published projects are visible in Project Web Access.
The Published database also contains tables that are specific to Office
Project Web Access (timesheets, resource information, models, views, and so on), and global data tables (outline codes, calendars, enterprise custom lookup tables, security, and metadata).
You can also find the association between projects and SharePoint workspace items (Issues and Risks) in Published database.
The Archive database saves backed-up and older versions of projects. It is possible to backup and archive project objects in Microsoft Office Project Web Access.
When an object is backed up, it is saved to the Project Server 2007 Archive database. A number of enterprise objects can be backed up in Microsoft Office Project Web Access.
Example: Objects in Microsoft Office Project Web Access:
Enterprise Projects; Project Resources, Assignments, Tasks, Custom field values, Baseline data; Enterprise Resource Pool and Enterprise Calendars; Enterprise Custom Field metadata, Enterprise Lookup Table Metadata, Enterprise Lookup Table values
Enterprise Global; View Definitions; Project Web Access view definitions; System Settings; Global system setting (such as backup/restore schedule, cube metadata, capacity window, system defaults – things you see in Admin dialogs); Security Category and Group Settings; Security settings (group definitions & category definitions)
Back ups are done on the Server Settings page under Database Administration. There are two methods available:
- Schedule Backup
- Administrative Backup
Administrative Backup allows you to back up enterprise objects at any time.
Schedule Backup allows you to back up enterprise objects daily at a scheduled time. The best time to schedule back up your enterprise objects is when server utilization is low.
The Reporting database is the staging area for generating reports and online analytical processing (OLAP) cubes. Data in the Reporting database is updated nearly real-time, is comprehensive, and is optimized for read-only report generation.
You can add data tables, fields, and objects that are not defined in the Project Server 2007 database schema to the Reporting database. Please do not modfy the column names and table names in Reporting database. If you needed, you can add the additional tables to Reporting database.