Inventory of SQL Databases in a Typical Project Server Deployment

Christophe Fiessinger's Blog

The latest news you need to know about Microsoft Enterprise Social & Project Portfolio Management

Inventory of SQL Databases in a Typical Project Server Deployment

Rate This
  • Comments 2

I have recently seen this question on how many databases are created once the Project Server and Project Portfolio Server are installed?  My first answer was: “it depends!” Here is why. Assume we are installing both Project Portfolio Server and Project Server on a SharePoint Server farm and that they all share the same SQL server instance:

Product Database Name Description Requirement Related Documentation/Links
SharePoint 2007 SharePoint Farm Configuration SharePoint configuration database (only one per farm). Required http://technet.microsoft.com/en-us/library/cc262869.aspx
  SharePoint Central Admin Content The content database for Central Administration (only one per farm). Required  
  Shared Services Provider (SSP) Administration Database(n) A content database for the Shared Services Administration Web application (if the SSP is using its own Web application) Required  
  Shared Services Provider Content Database(n) SSP Content database Required  
  Shared Services Provider Search Database SSP Search database (one per SSP) Required  
  Portal site Web application content database (n) – PWA Site Collection You need a content database for each SharePoint site, we recommend having a dedicated site collection to host Project Web Access Required  
  Portal site Web application content database (n) – PWA Workspaces Have a separate site collection/content database to store all project workspaces, this helps with back/restore and general data isolation from the rest of the farm. Recommended  
  Portal site Web application content database (n) – My Sites Have a dedicated content database to store “My Site” content, refer to the link on right for other best practices. Recommended http://technet.microsoft.com/en-us/library/cc262706.aspx
Project Server 2007 Archive Database(n) The Archive database saves backed-up and older versions of projects. Required http://technet.microsoft.com/en-us/library/cc197534.aspx
  Draft Database(n) The Draft database contains tables for saving unpublished projects from Microsoft Office Project Professional 2007. Project data in the Draft database is not accessible by using Microsoft Office Project Web Access. Required http://technet.microsoft.com/en-us/library/cc197534.aspx
  Published Database(n) The Published database contains all of the published projects. Published projects are visible in Office Project Web Access. The Published database also contains tables that are specific to Office Project Web Access (timesheets, models, views, and so on), and global data tables (outline codes, security, and metadata). Required  
  Reporting Database(n) The Reporting database is the staging area for generating reports and online analytical processing (OLAP) cubes. Data in the Reporting database is updated nearly in real-time, is comprehensive, and is optimized for read-only report generation. Required  
Project Portfolio Server 2007 PPS Account Data This database contains the actual account data, such as the project, application, program and portfolio data, related cost and resource information, optimization scenarios, and other data. Even though this database can contain information from multiple accounts, we recommend having only one account per database to ease future upgrades or database migrations. For more information about setting up multiple accounts, see the Creating multiple accounts section of this document. Required http://www.microsoft.com/downloads/details.aspx?familyid=8977C122-A64F-4C4E-A78B-0EA8E150FE3A&displaylang=en
  PPS AccountIndex This database contains information about the defined accounts in Office Project Portfolio Server 2007. Each account acts like a single instance of Office Project Portfolio Server 2007. Information cannot be shared across multiple accounts. For more information about setting up multiple accounts, see the Creating multiple accounts section of this document. Required  
SQL Reporting Services 2005/2008 Report Server Database “ReportServer” This database is used for primary storage of all SSRS data (reports, security settings, …) Required (for Project Portfolio Server) http://technet.microsoft.com/en-us/library/ms157285.aspx
  Report Server Catalog “ReportServerTempDB” This second SSRS database is used to store all temporary data Required (for Project Portfolio Server)  
SQL Server Analysis Services 2005/2008 Analysis Services Repository The SQL Server Analysis Services Repository is used to store OLAP metadata ("OlapObjects”) (for example, cubes and dimensions). Recommended http://technet.microsoft.com/en-us/library/cc197552.aspx
.NET aspnet Membership provider database, it’s used to store all user account/password for users that are not part of Active Directory for instance Required (if you using Forms Authentication) http://technet.microsoft.com/en-us/library/cc197472.aspx

“(n)” simply means that you could have multiple databases depending on your configuration (number of PWA instances for instance).

So in the end for one Project Web Access instance setup on a SharePoint Server farm with Portfolio Server and SQL Reporting Services all sharing the same SQL Server instance you will typically have 18 relational databases. If you have another PWA instance, other SharePoint site, other SSP, PerformancePoint Server, etc… expect additional databases… I recommend you carefully plan your EPM deployment and architect your solution based on the above recommendations and the latest TechNet content for all these products; involve a trained Partner as well if you are not familiar with all the products. So in summary “it depends” but hopefully this gives you a good starting point :)

Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
Page 1 of 1 (2 items)