Configure SQL Server and Analysis Services (Project Server 2010)
Applies to: Project Server 2010
Topic Last Modified: 2011-07-21
Before installing Microsoft SharePoint Server 2010 and Microsoft Project Server 2010, you must first configure Microsoft SQL Server and Analysis Services.
Note
If you are installing Project Server 2010 to an existing SharePoint Server farm, some of these steps may already be completed.
Complete the procedures in each section below:
Configure SQL Server network settings
Add a login for the Farm Administrator account
Enable the common language runtime
Configure Analysis Services
Additionally, depending on the needs of your organization, you may want to do the following:
Create the Project Server databases
Create additional TempDB files
We also recommend that you start the SQLSERVERAGENT service on the instance of SQL Server where your SharePoint Server databases are located. SharePoint Server and Project Server 2010 use the SQL Server Agent service to perform various database cleanup activities.
When you have finished configuring SQL Server and Analysis Services, go to the next article, Install SharePoint Server 2010 (Project Server 2010).
Configure SQL Server network settings
For Microsoft Project Server 2010 to work correctly, the associated instance of SQL Server must be configured to enable remote connections using TCP/IP. This is the default configuration for SQL Server, but we recommend confirming that the configuration is correct before you install Project Server 2010.
Use one of the next two procedures, depending on your version of SQL Server.
To configure SQL Server 2005 network settings
Click Start, click All Programs, click Microsoft SQL Server 2005, click Configuration Tools, and then click SQL Server Surface Area Configuration.
In the SQL Server 2005 Surface Area Configuration dialog box, click Surface Area Configuration for Services and Connections.
In the tree view, expand the node for your instance of SQL Server, expand the Database Engine node, and then click Remote Connections.
Select Local and Remote Connections, select Using TCP/IP only.
Note
The Using both TCP/IP and names pipes option will also work. If your instance of SQL Server is already configured to use both TCP/IP and named pipes, you can keep that setting.
Click OK.
To configure SQL Server 2008 network settings
Click Start, click All Programs, click Microsoft SQL Server 2008, click Configuration Tools, and then click SQL Server Configuration Manager.
In the left pane, expand SQL Server Network Configuration, and then select the instance of SQL Server where you will be installing Project Server 2010 databases.
In the right pane, ensure the Status for TCP/IP is Enabled.
Add a login for the Farm Administrator account
In order for Project Server 2010 setup and configuration to function, you must create a SQL Server login for the Farm Administrator domain account and give it the required server roles.
To create a SQL Server login
Open SQL Server Management Studio.
Connect to the database engine of the instance of SQL Server that you will be using with Project Server 2010.
Expand the Security node.
Right-click Logins and then click New Login.
On the New page, in the Login name text box, type the domain account that you created for the Farm Administrator.
In the Select a page list, click Server Roles.
In the Server roles list, select the dbcreator, public, securityadmin, and sysadmin check boxes.
Click OK.
Enable the common language runtime
The common language runtime will improve the performance of your Project Server 2010 deployment. To enable the common language runtime, execute the following query:
sp_configure 'clr enabled', 1
go
reconfigure
go
Enabling the common language runtime provides a significant improvement in performance for custom field operations.
Configure Analysis Services
Project Server 2010 can build OLAP cubes in Microsoft SQL Server Analysis Services (SSAS) for use in reporting. If you plan to use the Project Server 2010 cube building feature, you must configure the Farm Administrator account to have administrative permissions in SQL Server Analysis Services for the instance of Analysis Services that you will be using with Project Server 2010.
To add the Farm Administrator as an Analysis Services server administrator
Open SQL Server Management Studio. In the Connect to Server window, connect to the instance of SQL Server 2005 Analysis Services that you are using with Project Server 2010.
In Microsoft SQL Server Management Studio, in Object Explorer, right-click your SQL Server 2005 Analysis Services instance name, and then click Properties.
On the Analysis Services Properties page, in the Select a page pane, click Security.
Click Add.
On the Select Users, Computers, or Groups page, type the name of the Farm Administrator account.
Click OK. The Farm Administrator account appears in the Members list.
Click OK.
Note
For information about using OLAP databases in Project Server 2010, see OLAP database management (Project Server 2010).
Creating the Project Server databases
When you create a Microsoft Project Web App (PWA) site, Project Server databases are created automatically. You can also create these databases manually before creating the PWA site. Doing so may be desirable if you want to place the databases in a particular location (for example, on a specific LUN) or if the Administrator creating the PWA site has insufficient permissions to create databases in SQL Server.
When creating Project Server databases in SQL Server, create an empty database for the Draft, Published, Archive, and Reporting databases using the SQL_Latin1_General_CP1_CI_AS collation. Create a full set of databases for each instance of PWA that will be created.
Create additional TempDB files
Both Project Server 2010 and Microsoft SharePoint Server 2010 make extensive use of TempDB during SQL transactions. To optimize performance, create additional TempDB files.
As a rule, create an additional TempDB file for each processor (core) in the computer that is running SQL Server. Create the files on a separate partition from other database files.