Inventory of SQL Databases in a Typical Project Server Deployment
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 | https://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 | https://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 | https://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 | https://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 | https://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) | https://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 | https://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) | https://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 :)