Configuring the migration tool for Project Server

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

In this article:

  • Migration configuration file overview

  • Migration configuration parameters

  • Migration configuration file example

  • Querying the Project Server 2003 database for projects

  • Configure an SQL linked server connection

This article provides information on how to configure the .ini file used by the migration tool to migrate Microsoft Office Project Server 2003 data to Microsoft Office Project Server 2007. This article also provides several helpful SQL queries used to search the Project Server 2003 database for projects in certain states to include in the migration configuration file.

Migration configuration file overview

Typically the migration tool is invoked using a command line that looks similar to this:

D:\Program Files\Microsoft Office\OFFICE12>P12MigrationTool.exe -c d:\migration\ProjectServer2007Migrate.ini

Before you run the migration tool, you must manually edit the migration configuration file in a text editor. This file configures the parameters that specify the location of the upgrade log file, which projects to migrate, the location of the computer running Microsoft SQL Server on which the Office Project Server 2007 databases will exist, and so forth.

A sample migration configuration file (named ProjectServer2007Migrate.ini.sample) is installed in the same directory as the migration tool. You can update this sample file with the correct configuration parameters and then point to it when you run the migration tool. The sample file includes comments that describe configuration options that are available for each parameter.

The following sections provide more details about the migration configuration file.

Migration configuration parameters

Following are the parameters that you can configure in the migration configuration file.

Parameter Description

[General]

General configuration section. The parameters for this section are listed next.

BatchName=

You can run the migration tool multiple times (for example, to migrate projects in batches). Therefore, specify a unique name for each run of the migration tool. The log file that is generated from each run of the migration tool is named according to the specified BatchName parameter. If a unique name is not specified, the log file that is generated will write over the previous log file.

LogFileNamePrefix=

Identifies the prefix of the migration log file. The log file name is a combination of LogFileNamePrefix and a timestamp specifying when the migration started. For example, if you have a log file name of batch1-20060314-1542.log:

  • LogFileNamePrefix=batch1

  • The migration run for this log was started on 3/14/2006 at 3:42 pm

Being able to apply a prefix to the log file name can be useful when you are migrating multiple times, such as in a gradual migration approach.

LogFilePath=

Identifies the path to the log files that are generated by the migration tool. The migration tool writes verbose information to the log file. The log file is your main source of troubleshooting information for the migration tool. If the migration tool cannot write to the log file, it stops running, so you can be sure that you can always refer to the log file for more information about the migration run.

MigrateAll=

Save: All projects in Project Server 2003 are migrated to Office Project Server 2007 and none of them are published.

Publish: All projects in Project Server 2003 are migrated to Office Project Server 2007 and the published projects in Project Server 2003 are automatically published in Office Project Server 2007.

If you want the MigrateAll parameter to be ignored, comment out the MigrateAll line with a semicolon. If this is done, no projects will be migrated when running the migration tool.

Note

Comment out the MigrateAll parameter if you choose to follow the best practice of running the migration tool to migrate non-project data (enterprise global template, enterprise resources, and Project Web Access data) prior to migrating any projects. You must also verify that no projects are specified in the [Project Names to Save and Publish] and [Project Names to Publish] sections.

Important

The MigrateAll= setting is ignored if you have specified any project names in the [Project Names to Save and Publish] or [Project Names to Publish] section.

FixUpSecurityCategories=

If FixUpSecurityCategories is set to Yes, projects are automatically added to security categories after they are migrated. If it is set to No, projects are not added to any security category after migration. Typically, you would set this policy to No if the categories in Office Project Server 2007 will be significantly different from the categories you have in Project Server 2003 and you want to add your projects to security categories manually.

StopProjectMigrationIfStatusUpdatesPending=

By default, the value of StopProjectMigrationIfStatusUpdatesPending is Yes. If it is set to Yes, any projects that have pending updates are not migrated. If this setting is set to No, any projects that have pending updates are migrated. The No option is useful if customers want to migrate projects even though they have pending updates. An example of this situation is when there are old projects that need to be migrated, but there are no active project managers for these projects to process pending updates.

NeverPublishMasterProjects=

By default, the value for NeverPublishMasterProjects is set to Yes. This setting ensures that master projects do not get published by mistake. This is useful for customers who have set the Never Publish Master Projects option in Project Server 2003.

If you want any master projects published after migration, set NeverPublishMasterProjects to No. Even if you specify this setting, you need to manually include the master project in the [Project Names to Save and Publish] section.

Note

Publishing a master project in Office Project Server 2007 does not double-count availability.

[Project Names to Save]

Type the names of the projects you want to save after migration. Projects specified in this section are not published.

Note

You must specify the complete name (as stored in the PROJ_NAME column in the MSP_PROJECTS table).

[Project Names to Save and Publish]

Type the names of the projects that you want to save and publish after migration.

Note

Only published projects (with .PUBLISHED extensions) in Project Server 2003 can be published to Office Project Server 2007. If you specify a versioned project, then it will only be saved and not published.

Note

You must specify the complete name of the project (as stored in the PROJ_NAME column in the MSP_PROJECTS table).

Note

If you specify a master project and set neverPublishMasterProjects=false, then the subprojects (if they are already migrated or are being migrated as part of this migration run) will also get published automatically. Also note that publishing a master project in Office Project Server 2007 does not double-count availability.

[Excluded Project Names]

Specify any project that you want explicitly excluded during migration. This option is typically used with the MigrateAll setting when you want to migrate most, but not all, of your projects.

Note

You must specify the complete name of the project (as stored in the PROJ_NAME column in the MSP_PROJECTS table).

[Project Server 2007]

The Office Project Server 2007 configuration section. The parameters for this section are listed next.

Project2007PWAServer=

Type the Microsoft Office Project Web Access URL. For example, Project2007PWAServer=http://Project2007PWAServer/pwa

Project2007SQLServer=

Type the name of the instance of SQL Server that hosts the Office Project Server 2007 databases.

Important

By default, the Project Server 2003 databases also should be part of the same instance of SQL Server as Office Project Server 2007. To do this, back up and restore the Project Server 2003 databases into the instance of SQL Server that hosts the Office Project Server 2007 databases. If this is a major restriction for you, refer to the Project2003LinkedSQLServer property in the [Project Server 2003] section.

DraftDB =

Type the name of the Office Project Server 2007 draft database.

PublishedDB =

Type the name of the Office Project Server 2007 published database.

[Project Server 2003]

The Project Server 2003 configuration section. The parameters for this section are listed next.

Project2003ProjectTablesDB =

If you have a single database configuration for the Project Server 2003 database (as opposed to a split database configuration), then type the name of the database in this configuration setting as well as the Project2003WebTablesDB= setting that follows.

Note

The migration tool does not migrate SQL Server Analysis Services cube tables. You will have to rebuild cubes after migration.

Project2003WebTablesDB=

If the Project Server 2003 database is in a split database configuration, type the name of the Web Tables database. As noted above, if Project Server 2003 is in a single database configuration, enter the database name here as well as in the Project2003ProjectTablesDB= setting.

Project2003LinkedSQLServer=SQLServerName

By default, this setting is not enabled (the Project2003LinkedSQLServer= setting is commented out). If this parameter is not specified, the migration tool assumes that the Project 2003 databases reside in the computer hosting the Office Project Server 2007 SQL Server. If the SQL Server computer in which the Project 2003 databases reside cannot reside on the computer that hosts the Office Project Server 2007 databases, you can use this setting to specify the computer on which the Project Server 2003 database resides. However, for this to work you must add the Project Server 2003 database as a linked server to the Office Project Server 2007 SQL Server. To configure an SQL linked server connection, see the section titled "Configure an SQL linked server connection" later in this article.

Migration configuration file example

The following is an example of a configured migration configuration file.

[General]
LogFilePath=C:\ProjectServer2007Migration
LogFileNamePrefix=Batch1 
MigrateAll=Publish
FixUpSecurityCategories=yes
StopProjectMigrationIfStatusUpdatesPending=yes
NeverPublishMasterProjects=yes
[Project Names to Save]
Project1.published
Project2.Target
[Project Names to Save and Publish]
Project3.Published
Project4.Published
[Excluded Project Names]
Project5.Published 
Project6.Target 
[Project Server 2007]
Project2007PWAServer=http://Project2007pwaserver/pwa
Project2007SQLServer=Project2007sqlserver
DraftDB=Project2007ProjectServerDraft
PublishedDB=Project2007ProjectServerPublished
[Project Server 2003]
Project2003ProjectTablesDB=Project2003ProjDB
Project2003WebTablesDB=Project2003WebDB
;Project2003LinkedSQLServer=LinkedSQLServerName

Note

The migration configuration file that is installed with the migration tool contains comments that describe the configuration options that are available. The comments can help you correctly configure the file. You can leave the comments in the file after you configure it.

Querying the Project Server 2003 database for projects

The SQL Server scripts shown in this section can be very useful for searching your Project Server 2003 database for projects that need to be included in any of the following sections of the migration configuration file:

  • [Project Names to Save]

  • [Project Names to Save and Publish]

  • [Excluded Project Names]

  • The project names resulting from the queries can then be copied into the appropriate sections of the migration configuration file as needed.

Projects not yet started

SELECT p.PROJ_NAME FROM dbo.MSP_PROJECTS p

INNER JOIN dbo.MSP_TASKS t ON (t.PROJ_ID = p.PROJ_ID AND t.TASK_UID = 0)

WHERE p.PROJ_TYPE = 0 AND t.TASK_PCT_COMP = 0 AND t.TASK_PCT_WORK_COMP = 0

Projects that are finished

SELECT p.PROJ_NAME FROM dbo.MSP_PROJECTS p

INNER JOIN dbo.MSP_TASKS t ON (t.PROJ_ID = p.PROJ_ID AND t.TASK_UID = 0)

WHERE p.PROJ_TYPE = 0 AND t.TASK_PCT_COMP = 100 AND t.TASK_PCT_WORK_COMP = 100

Projects not yet finished

SELECT p.PROJ_NAME FROM dbo.MSP_PROJECTS p

INNER JOIN dbo.MSP_TASKS t ON (t.PROJ_ID = p.PROJ_ID AND t.TASK_UID = 0)

WHERE p.PROJ_TYPE = 0 AND (t.TASK_PCT_COMP != 100 OR t.TASK_PCT_WORK_COMP != 100)

Project that are in progress

SELECT p.PROJ_NAME FROM dbo.MSP_PROJECTS p

INNER JOIN dbo.MSP_TASKS t ON (t.PROJ_ID = p.PROJ_ID AND t.TASK_UID = 0)

WHERE p.PROJ_TYPE = 0 AND (t.TASK_PCT_COMP > 0 OR (t.TASK_STOP_DATE > t.TASK_START_DATE AND t.TASK_DUR > 0))

Projects with the project outline code 'Project Status' equal to 'Opportunity'

For the following query, substitute the outline code and value you want to query with the "Project Status" and "Opportunity" values used for this example.

SELECT p1.PROJ_NAME

FROM dbo.MSP_FIELD_ATTRIBUTES fa

INNER JOIN dbo.MSP_ATTRIBUTE_STRINGS ats ON (fa.PROJ_ID = ats.PROJ_ID AND fa.AS_ID = ats.AS_ID AND fa.ATTRIB_ID = 206)

INNER JOIN dbo.MSP_PROJECTS p ON (p.PROJ_ID = fa.PROJ_ID AND p.PROJ_TYPE = 2)

INNER JOIN dbo.MSP_OUTLINE_CODES oc ON (p.PROJ_ID = oc.PROJ_ID AND oc.OC_FIELD_ID = fa.ATTRIB_FIELD_ID)

INNER JOIN dbo.MSP_CODE_FIELDS cf ON (cf.CODE_FIELD_ID = oc.OC_FIELD_ID AND cf.CODE_UID = oc.CODE_UID)

INNER JOIN dbo.MSP_PROJECTS p1 ON (p1.PROJ_ID = cf.PROJ_ID)

WHERE ats.AS_VALUE like '%Project Status%' AND oc.OC_CACHED_FULL_NAME like 'Opportunity'

Project templates

SELECT p.PROJ_NAME FROM dbo.MSP_PROJECTS p WHERE PROJ_TYPE = 1

Configure an SQL linked server connection

Configuring an SQL linked server connection is done in conjunction with using the Project2003LinkedSQLServer= setting in the migration configuration file. You only need to use this setting and configure the SQL linked server connection when you are migrating a Project Server 2003 database that cannot be copied and restored on the Office Project Server 2007 database server and it must be connected to remotely.

Configure a SQL linked server connection using SQL Server 2000

If you are migrating a Project Server 2003 database in SQL Server 2000 to Office Project Server 2007 on another computer, and the database tier for Office Project Server 2007 is SQL Server 2000 as well, use the following procedure to configure an SQL linked server connection.

  1. Connect to the Office Project Server 2007 SQL Server by using Enterprise Manager.

  2. In the tree structure, click to expand the Security folder.

  3. Right-click Linked Servers, and then click New Linked Server.

  4. Refer to SQL Server documentation for more information on how to create a linked server (search for the term "linked servers"). Make sure you click the Security tab and select the correct security option. (For example, select be made using the login's current security context option button. If this option does not work in your environment, select be made using this security context and give a valid user name and password that works on the computer running SQL Server that is hosting the Project Server 2003 database.)

  5. Verify that the linked server you created functions properly before proceeding with the migration. You should see the linked server you just created under the Linked Servers node. Once you expand the linked server, you should see the Tables and Views nodes. If you click Tables or Views, you should see some entries.

  6. Use the name of the SQL linked server as the value for the Project2003LinkedSQLServer= parameter in the migration configuration file.

    Note

    In some IT environments, specific ports related to Microsoft Distributed Transaction Coordinator (MSDTC) might need to be opened for the linked server configuration to work. For more information, see the SQL Server documentation about linked servers or contact your system administrator.

Configure a SQL linked server connection from SQL Server 2000 to SQL Server 2005

If you are migrating a Project Server 2003 database in SQL Server 2000 to Office Project Server 2007 on another computer, but the database server being used by Office Project Server 2007 is SQL Server 2005, use the following procedures to configure an SQL linked server connection.

First, in both SQL Server 2000 and SQL Server 2005, set the authentication mode to mixed, so that SQL Server logins are accepted along with Windows logins.

Configure SQL Server 2005 authentication to mixed mode

  1. Open SQL Server 2005 Management Studio.

  2. In the Object Explorer pane, right-clicking the SQL 2005 server name, then click Properties.

  3. In the Select a Page list, click Security.

  4. In the Server Authentication section, select SQL Server and Windows Authentication Mode.

  5. Click OK.

Configure SQL Server 2000 authentication to mixed mode

  1. Open SQL Server Enterprise Manager.

  2. Expand the server group that contains the database server that you will use with Project Server 2003.

  3. Right-click the server, and then click Properties.

  4. Click the Security tab.

  5. Under Authentication, click SQL Server and Windows.

  6. Click OK.

Configure the connection

  1. On the computers running SQL Server 2000 and SQL Server 2005, create an identical SQL authenticated user account to run the linked server connection. Make sure that the password is the same for the accounts on both servers. Ensure that the accounts have read/write access to the databases you intend to access using the linked server system.

  2. In SQL Server 2000, open Query Analyzer and on your master database run a file called instcat.sql. It should be located under your SQL folders in Program Files.

  3. In SQL Server 2005 Management Studio, select your computer running SQL Server 2005, expand Server Objects, right-click Linked Servers, and click New Linked Server.

    1. Enter the server name of your computer running SQL Server 2000.

    2. Select the SQL Server option.

    3. Click the Security tab in the side pane.

    4. Select the Be made using this security context option.

    5. Enter the SQL account and password needed to access the Project Server 2003 databases and Office Project Server 2007 databases (created in step 1).

    6. Click OK.

  4. In SQL Server 2005 Management Studio, launch a query against the linked server to verify that it worked:

    1. SELECT * from <P200Server>.<P11DBName>.dbo.msp_web_admin

    2. Replace the server and database name with appropriate values.

    3. If this query returns successfully, your connection is successful.

      Note

      When you run the migration, make sure the user account with which you are running the migration tool has read access to both your Project Server 2003 and Office Project Server 2007 databases.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for Project Server 2007.

See Also

Concepts

Upgrading project workspaces from Project Server 2003