If you created the Project Server database manually and you want to partition it prior to installing Project Server 2003, you can use the procedures in this topic to partition the Project Server databases and then connect to these databases during Project Server Setup.

These procedures require you run the partition scripts with a SQL Server login that is a member of the Systems Administrator role on each instance of SQL Server in your deployment. When you create this account, it will require the same password on each server. It is recommended that you delete these logins when you are finished partitioning the database. It is also possible to use the SQL Server sa account, so long as the password is the same on each server. The sa account cannot be deleted.

 Important   SQL Server uses the Microsoft Distributed Transaction Coordinator (MSDTC) automatically with Project Server database partitioning. Be sure that MSDTC is installed on each computer running SQL Server in your deployment. If you are also running SQL Server in a cluster, some database transactions will fail unless you use the COM+ Wizard (Comclust.exe) to configure the cluster. Using this tool routes all MSDTC requests to the computer in the cluster that is running MSDTC for the cluster. For information about configuring the cluster by using Comclust.exe, see the Microsoft Web site. If MSDTC is not installed on your computer, you can install it from the SQL Server 2000 CD.

 Important   By default, the network access settings of MSDTC are disabled on new installations of SQL Server 2000 on computers running Microsoft Windows Server 2003. If you are partitioning on a computer running Windows Server 2003, follow the procedure in Microsoft Knowledge Base Article 329332 to be sure that your MSDTC settings are correct. After you complete the procedure, restart your computer.

Partitioning the Database for New Deployments

Use the procedures in this topic to set up database partitioning as part of a new Project Server deployment. Complete these procedures assume before you install Project Server 2003 and its associated components. You can also partition your database after you install Project Server 2003. For more information, see Database Partitioningin the Microsoft Office Project Server 2003 Installation Guide.

You can partition your database in of the following three ways:

• By partitioning the View tables to move the View tables to a separate database server.
• By partitioning the Project tables to move the Project tables to a separate database server.
• By using three–way partitioning to move both the Project tables and the View tables to separate database servers.

Important   These procedures should only be used by someone who is familiar with SQL Server 2000 and experienced with the database creation process, security, and editing and executing scripts across a multi–server environment. All steps listed in these procedures must be performed in the order in which they are listed.

The files required for the procedures in this topic can be found on the Project Server 2003 CD in the Support/Database folder. It is recommended that you copy this folder to each of the database computers in your deployment.

Partitioning the View Tables

You can move the Project Server 2003 View tables to a separate database server. This configures the databases as follows:

• Main database server   Contains the Web tables, Cube tables, Project tables, and Security views.
• View Tables database server   Contains the View tables.

Project Server Setup can partition the View tables for you when you are installing Project Server 2003. If you prefer to do so manually before running Project Server Setup, complete the following procedures.

To configure the View Tables database server

1. In SQL Server 2000 Enterprise Manager, create a new Project Server database for the View tables (for example, ProjectServerVIEW).
2. Using SQL Server Query Analyzer and connecting with your login account, run PSRoles.sql from within the Project Server database context.

   This script creates the two database roles needed by the Project Server logins: MSProjectServerRole and MSProjectRole.

3. Using SQL Server Enterprise Manager, go to the Security folder, select Login, and create the following two logins: MSProjectServerUser and MSProjectUser. Be sure to use SQL Server authentication and to select the new database on the Database Access tab.
4. On the Database Access tab, assign the MSProjectServerRole role to the MSProjectServerUser login, and then assign the MSProjectRole role to the MSProjectUser login.
5. Using SQL Server Query Analyzer and connecting with your login account, run ViewTbls.sql from within the Project Server View database context.

To configure the Main database server

1. In SQL Server 2000 Enterprise Manager, create a new Project Server database (for example, ProjectServerMAIN).
2. Using SQL Server Query Analyzer and connecting with your login account, run PSRoles.sql from within the Project Server database context.

   This script creates the two database roles needed by the Project Server logins: MSProjectServerRole and MSProjectRole.

3. Using SQL Server Enterprise Manager, go to the Security folder, select Login, and create the following two logins: MSProjectServerUser and MSProjectUser. Be sure to use SQL Server authentication and to select the new database on the Database Access tab.

   Important   Be sure that the user names and passwords for these database logins are the same on both your Main database server and your View Tables database server.

4. On the Database Access tab, assign the MSProjectServerRole role to the MSProjectServerUser login, and then assign the MSProjectRole role to the MSProjectUser login.
5. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts from within the Project Server database context:
   1. ProjTbls.sql
   2. WebTbls.sql
   3. CubeTbls.sql
   4. SecViews.sql
   5. ProjSPS.Sql
   6. WebSPS.Sql
6. Using the Osql tool (on the Project Server 2003 CD in the /Support/Database folder) and connecting to the main database with your login account, run PjcrtSPS.sql.

   Important   You must run this script with Osql. SQL Server Query Analyzer will not open this script file because of the line length of the binary data in the last UPDATE command. The Osql command must be in the following format and must be run from the command line on the server. You can run this command–line tool from either database server.

   osql –S <MainDatabaseServerName> –d <MainDatabaseName> –U <Login> –P <Password> –i <Input File>
   

   For example:

   osql –S PSMain –d ProjectServerMAIN –U MyAccount –P hello –i PjcrtSPS.sql
   

7. Using SQL Server Query Analyzer and connecting with your login account, create a linked server by using the following command:

   EXEC sp_addlinkedserver <ViewTablesDatabaseServerName>, N'SQL Server'
   

   For example:

   EXEC sp_addlinkedserver PSView, N'SQL Server'
   

8. Using SQL Server Query Analyzer and connecting with your login account, run LnkViews.sql from within the Project Server database context.
9. Using SQL Server Query Analyzer and connecting with your login account, execute the ExecLinkedViewsSP stored procedure as follows:

   EXEC ExecLinkedViewsSP <ViewTablesDatabaseServerName>, <ViewTablesDatabaseName>
   

   For example:

   EXEC ExecLinkedViewsSP PSView, ProjectServerVIEW
   

10. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts in order listed:

     Note   These files can be found in the Database\1033 folder.

    1. LocData.sql
    2. EGlobal.sql

       Important   You must run this script with Osql. For more information about using Osql, see the Microsoft Web site.

    3. InsDef.sql
    4. InsDefLP.sql

To connect to the new databases

1. Run Project Server Setup.
2. On the Enter database server information page, select the Connect to another existing database option.

   

3. In the Database server box, type the name of the SQL Server instance that you will use for the Main database server.

   

4. In the Database name box, type the name of the Project Server database that you created on the server, and then click Next .

   

5. On the Enter connection information for View tables page, clear the Connect to the main Project Server 2003 database check box.

   

6. In the Database server box, type the name of the View Tables database server.

   

7. In the Database name box, type the name of the Project Server View database that you created on that server.

   

8. Complete Project Server Setup.

Partitioning the Project Tables

You can move the Project Server database tables that are used by Project Professional on a separate database server. This configures the databases as follows:

• Main database server   Contains the Web tables, Cube tables, and View tables.
• Project Tables database server   Contains the Project tables and Security views.

Project Server Setup cannot create this database configuration when you are installing Project Server 2003. If you want to partition the Project tables, follow the procedures in this topic before you run Project Server Setup.

To configure the Project Tables database server

1. In SQL Server 2000 Enterprise Manager, create a new Project Server database for the Project Tables (for example, ProjectServerPROJ).
2. Using SQL Server Query Analyzer and connecting with your login account, run PSRoles.sql from within the Project Server database context.

   This script creates the two database roles needed by the Project Server logins: MSProjectServerRole and MSProjectRole.

3. Using SQL Server Enterprise Manager, go to the Security folder, select Login, and create the following two logins: MSProjectServerUser and MSProjectUser. Be sure to use SQL Server authentication and to select the new database on the Database Access tab.
4. On the Database Access tab, assign the MSProjectServerRole role to the MSProjectServerUser login, and then assign the MSProjectRole role to the MSProjectUser login.
5. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts in the listed order from within the Project Tables database context:
   1. ProjTbls.sql
   2. ProjSPS.sql
   3. SecViews.sql
6. Using the Osql tool (on the Project Server 2003 CD in the /Support/Database folder) and connecting to the ProjectServerPROJ database with your login account, run PjcrtSP1.sql.

   Important   You must run this script with Osql. SQL Server Query Analyzer will not open this script file because of the line length of the binary data in the last UPDATE command. The Osql command must be in the following format and must be run from the command line on the server. You can run this command–line tool from either database server.

   osql –S <DatabaseServerName> –d <DatabaseName> –U <Login> –P <Password> –i <Input File>
   

   For example:

   osql –S PSProj –d ProjectServerPROJ –U MyAccount –P hello –i PJCRTSP1.SQL
   

To configure the Main database server

1. In SQL Server 2000 Enterprise Manager, create a new Project Server database (for example, ProjectServerMAIN).
2. Using SQL Server Query Analyzer and connecting with your login account, run PSRoles.sql from within the Project Server database context.

   This script creates the two database roles needed by the Project Server logins: MSProjectServerRole and MSProjectRole.

3. Using SQL Server Enterprise Manager, go to the Security folder, select Login, and create the following two logins: MSProjectServerUser and MSProjectUser. Be sure to use SQL Server authentication and to select the new database on the Database Access tab.

   Important   Be sure that the user names and passwords for these database logins are the same on both your Main database server and your Project Tables database server.

4. On the Database Access tab, assign the MSProjectServerRole role to the MSProjectServerUser login, and then assign the MSProjectRole role to the MSProjectUser login.
5. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts from within the Project Server Main database context:
   1. WebTbls.sql
   2. CubeTbls.sql
   3. ViewTbls.sql
   4. ViewSPS.sql
   5. WebSPS.sql
6. Using SQL Server Query Analyzer and connecting with your login account, create a linked server by using the following command:

   EXEC sp_addlinkedserver <ProjectTablesDatabaseServerName>, N'SQL Server'
   

   For example:

   EXEC sp_addlinkedserver PSProj, N'SQL Server'
   

7. Using SQL Server Query Analyzer and connecting with your login account, run LNKPROJ.SQL from within the Project Server Main database context.
8. Using SQL Server Query Analyzer and connecting with your login account, execute the ExecLinkedViewsSP stored procedure as follows:

   EXEC ExecLinkedViewsSP <ProjectTablesDatabaseServerName>, <ProjectTablesDatabaseName>
   

   For example:

   EXEC ExecLinkedViewsSP PSProj, ProjectServerPROJ
   

9. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts in the order listed:

    Note   These files can be found in the Database\1033 folder.

   1. LocData.sql
   2. EGlobal.sql

      Important   You must run this script with Osql. For more information about using Osql, see the Microsoft Web site.

   3. InsDef.sql
   4. InsDefLP.sql
10. Modify the PjcrtSP2.sql script and change the <WinProjServerName> and <WinProjDatabaseName> parameters on the line where the MSP_PDS_SP_INSERT_PROJECT_BINARY stored procedure is called, to the name of the Project Tables SQL Server and the Project Server database respectively.
11. Using SQL Server Query Analyzer and connecting with your login account, run PjcrtSP2.sql from within the Project Server Main database context.

To connect to the new databases

1. Run Project Server Setup.
2. On the Enter database server information page, select the Connect to another existing database option.

   

3. In the Database server box, type the name of the instance of SQL Server that you will use for the Main database server.

   

4. In the Database name box, type the name of the Project Server database that you created on the server, and then click Next .

   

5. On the Enter Connection Information for View Tables page, select the Connect to the main Project Server 2003 database check box.

   

6. Complete Project Server Setup.

To update the registry for a two–way partition

1. Run the Connection Information tool (ConnInfo.exe) on the computer on which Project Server 2003 is installed.

    Note   For more information about ConnInfo.exe, see Connection Information Tool in the Microsoft Office Project Server 2003 Installation Guide.

2. Select the Partitioned database configuration option, and then select the Custom Deployment option.
3. From the list, select Project Tables .
4. In the SQL Server box, type the name of the Project Tables database server.

   

5. In the Database box , type the name of the Project Server database that you created on that server.

   

6. In the MSProjectServer Role Member section, type MSProjectServerUser for the user name and the password that you chose for that account.

   

7. In the MSProject Role Member section, type MSProjectUser for the user name and the password that you chose for that account.

   

8. Click Save , and then click Exit.

Using Three–Way Partitioning

You can install the Web tables, Project tables, and View tables onto three different database servers. This configures the databases as follows:

• Main database server   Contains the Web tables and Cube tables.
• Project Tables database server   Contains the Project tables and Security views.
• View Tables database server   Contains the View tables.

The Project Server Setup program cannot create this database configuration when you run Setup. If you want to create a three–way partition, follow the procedures in this topic before you run Project Server Setup.

To configure the Project Tables database server

1. Create a new Project Server database (for example, ProjectServerPROJ).
2. Using SQL Server Query Analyzer and connecting with your login account, run PSRoles.sql from within the Project Server database context.

   This script creates the two database roles needed by the Project Server logins: MSProjectServerRole and MSProjectRole.

3. Using SQL Server Enterprise Manager, go to the Security folder, select Login, and create the following two logins: MSProjectServerUser and MSProjectUser. Be sure to use SQL Server authentication and to select the new database on the Database Access tab.
4. On the Database Access tab, assign the MSProjectServerRole role to the MSProjectServerUser login, and then assign the MSProjectRole role to the MSProjectUser login.
5. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts in the listed order from within the Project Tables database context.
   1. ProjTbls.sql
   2. ProjSPS.sql
   3. SecViews.sql
6. Using the Osql tool (on the Project Server 2003 CD in the /Support/Database folder) and connecting to the Project Tables database with your login account, run PjcrtSP1.sql.

   Important   You must run this script with Osql. SQL Server Query Analyzer will not open this script file because of the line length of the binary data in the last UPDATE command. The Osql command must be in the following format and must be run from the command line on the server. You can run this command–line tool from either database server.

   osql –S <MainDatabaseServerName> –d <MainDatabaseName> –U <Login> –P <Password> –i <Input File>
   

   For example:

   osql –S PSMain –d ProjectServerMAIN –U MyAccount –P hello –i PJCRTSP1.SQL
   

To configure the View Tables database server

1. Create a new Project Server database (for example, ProjectServerVIEW).
2. Using SQL Server Query Analyzer and connecting with your login account, run PSRoles.sql from within the Project Server database context.

   This script creates the two database roles needed by the Project Server logins: MSProjectServerRole and MSProjectRole.

3. Using SQL Server Enterprise Manager, go to the Security folder, select Login, and create the following two logins: MSProjectServerUser and MSProjectUser. Be sure to use SQL Server authentication and to select the View Tables database on the Database Access tab.

   Important   Be sure that the user names and passwords for these database logins are the same on both your Main database server and your Project Tables database server.

4. On the Database Access tab, assign the MSProjectServerRole role to the MSProjectServerUser login, and then assign the MSProjectRole role to the MSProjectUser login.
5. Using SQL Server Query Analyzer and connecting with your login account, run ViewTbls.sql from within the Project Server database context.
6. Using SQL Server Query Analyzer and connecting with your login account, run ViewSPS.sql from within the View Tables database context.

To configure the Main database server

1. Create a new Project Server database (for example, ProjectServerMAIN).
2. Using SQL Server Query Analyzer and connecting with your login account, run PSRoles.sql from within the Project Server database context.

   This script creates the two database roles needed by the Project Server logins: MSProjectServerRole and MSProjectRole.

3. Using SQL Server Enterprise Manager, go to the Security folder, select Login, and create the following two logins: MSProjectServerUser and MSProjectUser. Be sure to use SQL Server authentication and to select the Project Server database on the Database Access tab.

   Important   Be sure that the user names and passwords for these database logins are the same on both your Main database server and your Project Tables database server.

4. On the Database Access tab, assign the MSProjectServerRole role to the MSProjectServerUser login, and then assign the MSProjectRole role to the MSProjectUser login.
5. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts from within the Project Server Main database context:
   1. WebTbls.sql
   2. CubeTbls.sql
   3. WebSPS.sql
6. Using SQL Server Query Analyzer and connecting with your login account, create a linked server by using the following command:

   EXEC sp_addlinkedserver <ProjectTablesDatabaseServerName>, N'SQL Server'
   

   For example:

   EXEC sp_addlinkedserver PSProj, N'SQL Server'
   

7. Using SQL Server Query Analyzer and connected with your login account, create a linked server by using the following command:

   EXEC sp_addlinkedserver <ViewTablesDatabaseServerName>, N'SQL Server'
   

   For example:

   EXEC sp_addlinkedserver PSView, N'SQL Server'
   

8. Using SQL Server Query Analyzer and connecting with your login account, run LnkProj.sql from within the Project Server Main database context.
9. Using SQL Server Query Analyzer and connecting with your login account, execute the ExecLinkedViewsSP stored procedure as follows:

   EXEC ExecLinkedViewsSP <ProjectTablesDatabaseServerName>, <ProjectTablesDatabaseName>
   

   For example:

   EXEC ExecLinkedViewsSP PSProj, ProjectServerPROJ
   

10. Using SQL Server Query Analyzer and connecting with your login account, run LnkViews.sql from within the Project Server database context.
11. Using SQL Server Query Analyzer and connecting with your login account, execute the following stored procedure as follows:

    EXEC ExecLinkedViewsSP <ViewTablesDatabaseServerName>, <ViewTablesDatabaseName>
    

    For example:

    EXEC ExecLinkedViewsSP PSView, ProjectServerVIEW
    

12. Using SQL Server Query Analyzer and connecting with your login account, run the following scripts from in the listed order:

     Note   These files can be found in the Database\1033 folder.

    1. LocData.sql
    2. EGlobal.sql

       Important   You must run this script with Osql. For more information about using Osql, see the Microsoft Web site.

    3. InsDef.sql
    4. InsDefLP.sql
13. Modify the PjcrtSP2.sql script and change the WinProjServerName and WinProjDatabaseName parameters on the line where the MSP_PDS_SP_INSERT_PROJECT_BINARY stored procedure is called, to the name of the Project Tables SQL Server and the Project Tables database respectively.
14. Using SQL Server Query Analyzer and connecting with your login account, run PjcrtSP2.sql from within the Project Server database context.

To connect to the new databases

1. Run Project Server Setup.
2. On the Enter database server information page, select the Connect to another existing database option.

   

3. In the Database server box, type the name of the instance of SQL Server that you used for the Main database server.

   

4. In the Database name box, type the name of the Project Server database that you created on that server, and then click Next .

   

5. On the Enter connection information for Views tables page, clear the Connect to the main Project Server 2003 database check box.

   

6. In the Database server box, type the name of the View Tables database server.

   

7. In the Database name box, type the name of the View Tables database that you created on that server.

   

8. Complete Project Server Setup.

To update the registry for a three–way partition

1. Run the Connection Information tool (ConnInfo.exe) on the computer on which Project Server 2003 is installed.

    Note   For more information about ConnInfo.exe, see Connection Information Tool in the Microsoft Office Project Server 2003 Installation Guide.

2. Select the Partitioned database configuration option, and then select the Custom Deployment option.
3. From the list, select Project Tables .
4. In the SQL Server box, type the name of the Project Tables database server.

   

5. In the Database box , type the name of the Project Server database that you created on that server.

   

6. In the MSProjectServer Role Member section, type MSProjectServerUser for the user name and the password that you chose for that account.

   

7. In the MSProject Role Member section, type MSProjectUser for the user name and the password that you chose for that account.

   

8. From the list, select View Tables .
9. In the SQL Server box, type the name of the View Tables database server.

   

10. In the Database box , type the name of the View Tables database that you created on that server.

    

11. In the MSProjectServer Role Member section, type MSProjectServerUser for the user name and the password that you chose for that account.
12. In the MSProject Role Member section, type MSProjectUser for the user name and the password that you chose for that account.
13. From the list, select Web Tables .
14. In the SQL Server box, type the name of the Project Server Main database server.
15. In the Database box , type the name of the Project Server Main database.
16. In the MSProjectServer Role Member section, type MSProjectServerUser for the user name and the password that you chose for that account.
17. In the MSProject Role Member section, type MSProjectUser for the user name and the password you that chose for that account.
18. Click Save , and then click Exit.