Forgotten SQL Servers -- Project Server

Project Server. Remember installing it? Well, your Project guys will remind you once in a while that it's something to keep abreast of.

• By Joshua Jones
• 08/16/2010

Yet another enterprise-level application that is often installed and then forgotten about (in terms of SQL Server administration) is Project Server. Ever since Project Server 2003, Microsoft's project management system has been more than just an application with Gantt charts; it sports a full range of team collaboration features, and a robust back end to enable hundreds of resources (users) and dozens of Project Managers to interact with the suite, while giving IT a single set of services to support. Of course, how does it get those features? SharePoint, naturally. And that means SQL Server.

While the majority of the tips in the previous article around SharePoint administration still apply, there's an added complexity with Project Server: Analysis Services. Since a large portion of the desired functionality of Project Server is rooted in the Cube Building Service (that allows for advanced project analysis), SQL Server Analysis Services (SSAS) must be up and running. And with that comes the need to make sure that any data in SSAS is properly backed up, and that the server is properly tuned.

SSAS Backups
In SSAS (2005 and 2008), backup and restore operations are done in XML queries. Additionally, unlike the SQL Server relational database engine, there is no "agent" that can be set up. However, you can set up a SQL Server Agent job to connect to the SSAS instance and run your backup command.

When you create a new job, simply select the "SQL Server Analysis Services Command" type from the New Job Step window (see Fig. 1).

Figure 1. Schedule your Project Server job from the SSAS New Job Setup window. [Click image for larger view.]

Then make sure your SQL Server Agent has rights to the SSAS server, by logging into the SSAS server via Management Studio and adding the SQL Agent account to the database.

As far as the code is concerned, it'll be something like this:

<Command>
  <Backup>
    <Object>MyDatabaseName</Object>
    <File>E:\MyBackupDirectory</File>
    <Security>CopyAll</Security>
    <ApplyCompression>True</ApplyCompression>
    <AllowOverwrite>False</AllowOverwrite>
    <Password>MyPassword</Password>
    <BackupRemotePartitions>True</BackupRemotePartitions>
  </Backup>
</Command>

What this does, simply, is backup the database to a specified location. If you want to see all the parameters, check out Books On-Line. For now, just remember that you'll need to make sure a backup is even happening on your Project Server's SQL Server.

Tuning
While the fine art of tuning a server to run SSAS, Project Server and the SQL Server relational engine is outside the scope of this article. Even so, let me be the first to remind you that unless your installation is distributed amongst multiple servers, you'll need to spend some time tweaking this machine to handle the load. SSAS uses quite a bit of RAM, and can easily choke a SQL Server relational instance if left unchecked.

Finally, since this is such a detailed setup, be sure to document your server, because it's highly likely that you'll only look at this server when there is a problem. So be sure to keep track of this forgotten system, and good luck!