Upgrading the Microsoft Project Central Server Database

Article
02/20/2014

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Overview

The scope of migrating an existing database can range from upgrading the schema of a Microsoft® Project Central workgroup database, to rolling out Microsoft Project Server for enterprise use. Microsoft Project 2000 supported two database schemas (although they could be part of the same database): one for Microsoft Project data and one for Microsoft Project Central data. The upgrade path you choose depends on several factors:

If you are upgrading to Microsoft Project Standard 2002 and Microsoft Project Server, upgrading the Microsoft Project Central server database is appropriate.

If you are upgrading to Microsoft Project Professional 2002 and Microsoft Project Server, it may be more appropriate to import data into a new database rather than upgrading the Microsoft Project Central server database. This is especially true if:

You anticipate storing substantially more data in Microsoft Project Server and will install to a new server, perhaps even running Microsoft SQL™ Server and Microsoft Internet Information Services (IIS) on separate servers.

You are setting up standards in the enterprise global template to be applied to enterprise projects and resources, such as ensuring that all resources have values for required codes. Upgrading the Microsoft Project Central server database will not apply these standards.

Issues to Consider

It can be difficult to upgrade resources, and later convert them to enterprise resources, because Microsoft Project Server does not allow resources with duplicate names.

In cases where the Microsoft Project 2000 database tables coexist in the same database as the Microsoft Project Central server database tables, the project data must be moved before upgrading Microsoft Project Central to Microsoft Project Server.

If you are installing Microsoft Project Server on the same computer where Microsoft Project Central is installed, you must remove Microsoft Project Central before installing Microsoft Project Server.

Customizations to existing ASP pages in Microsoft Project Central are lost during the upgrade.

User permissions that allowed some users to view assignments for other users are also lost.

Any Microsoft Project Central views that you have added are preserved, although some customizations may need to be reapplied.

Preparing to Upgrade the Microsoft Project Central Server Database

Before you upgrade a Microsoft Project Central server database

Process all active update messages in Microsoft Project Central. In Microsoft Project Server, transactions replace project update messages, and project update messages cannot be upgraded.

Prevent further updates to the Microsoft Project Central server database by shutting down IIS on the computer where Microsoft Project Central is installed. The database should not be accessed during the conversion process, and shutting down IIS ensures this.

Back up the Microsoft Project Central server database.

Migrating the Database from Oracle to Microsoft SQL Server 2000

Microsoft Project Server requires Microsoft SQL Server 2000 or later. Microsoft Project Server was optimized to take advantage of unique performance, functionality, and programmability features available only in SQL Server 2000.

To upgrade a Microsoft Project Central server database on an Oracle server

Create the Microsoft Project Server tables in the target SQL Server database by running the Projtbls.sql script on the Microsoft Project Server installation CD.

Using Microsoft Data Transformation Services (DTS), copy the data only from the Microsoft Project Central server database in Oracle to the new Microsoft Project Server database in SQL Server.

Modify the Microsoft Project Server database registry key to point to the new database, and test the installation to ensure that the data was properly transferred.

Preparing the Upgrade Scripts

Some customers may have site database implementation standards to enhance performance and/or enable consistent maintenance. If so, as your next step, modify the database upgrade script to specify files and file groups for performance.

After the data, the database, and the upgrade script (if necessary) have been prepared, you can begin the upgrade process.

Upgrading the Database

Upgrading an existing Microsoft Project Central installation also requires that you convert the database for use by Microsoft Project Server.

To convert the Microsoft Project Central server database

Back up your existing Microsoft Project Central server database.

(Optional) Copy the Microsoft Project Central server database to a new computer.

Insert the Microsoft Project Server installation CD into the CD-ROM drive, or connect to a network installation point.

From a command prompt, access the CD-ROM drive (or network installation point), and then go to the folder \Support\Database.

Type the following command to convert the Microsoft Project Central server database for use by Microsoft Project Server.

Type the following commands to create the Microsoft Project Server tables and insert default data.

To create the Microsoft Project 2002 table schema (table names beginning with "MSP_"):

To create the stored procedures that Microsoft Project Server uses to ensure project data integrity:

To create the tables that store the data used when viewing projects in the Project Center (table names beginning with "MSP_VIEW"):

To create the Microsoft Project Server OLAP cube staging tables (table names beginning with "MSP_CUBE"):

To create the Microsoft Project Server stored procedures:

To create the Microsoft Project Server security views:

To insert localized data into the Microsoft Project 2002 tables:

To insert the enterprise global and enterprise resources into the Microsoft Project 2002 tables (Microsoft Project Professional only):

To convert localized data in the Microsoft Project Web Access tables:

To insert language pack data into the Microsoft Project Web Access tables:

The Insdeflp.sql script is found in the folder that corresponds to the locale ID (LCID) for your language. The English version, for example, can be found in the \SUPPORT\DATABASE\1033 folder.

If you are installing a localized version of Microsoft Project Server, you should also run the English version of this script after the version specific to your language.

To map Microsoft Project Central user roles to groups in Microsoft Project Server:

In order for the IIS server to have access to the Microsoft Project Server database, create a SQL Server user account for it, grant it access permission to the database, and assign it the MSProjectServerRole role.

In order for Microsoft Project Professional to have access to the Microsoft Project Server database, create a SQL Server user account for it, grant it access permission to the database, and assign it the MSProjectRole role.

Run the Microsoft Project Server Setup program, and click Custom.

Verifying the Upgrade

Once the Microsoft Project Central application has been upgraded, verify the database upgrade by exercising the application.

Migrating Data to Microsoft Project Professional 2002

Using Microsoft Project Server with Microsoft Project Professional 2002 is one of two possible usage combinations. The other combination is using Microsoft Project Server with Microsoft Project Standard 2002, described in the first part of this document.

Fully migrating existing data to Microsoft Project Professional 2002 can be a complex process and should be part of a broader implementation review process.

Deliverables from an enterprise implementation review process might include:

A business process model

View and report layouts

Likely sorts, filters, and groupings

Custom fields and outline codes needed

Preparing for Data Migration

Migrating from Microsoft Project Central to Microsoft Project Server is not a turnkey conversion. Like a classic data migration process, you first install the database tools that you are upgrading to, and then migrate the data. For Microsoft Project Central data, break the data migration process into two sequential phases: migrate resource information, and then migrate project plans.

Before you upgrade a Microsoft Project Central server database

Process all active update messages in Microsoft Project Central. In Microsoft Project Server, transactions replace project update messages, and update messages cannot be upgraded.

Prevent further updates to the Microsoft Project Central server database by shutting down IIS on the machine where Microsoft Project Central is installed. The database should not be accessed during the conversion process, and shutting down IIS ensures this.

Copy and save any status reports that should be kept. Existing status reports cannot be upgraded.

Back up the Microsoft Project Central server database.

Migrating Resource Information to Microsoft Project Server

You should migrate resource information before migrating project plans to Microsoft Project Server. Doing so helps ensure that you account for duplicate resource names that represent the same resource.

Resources and users are maintained separately in Microsoft Project Server—resources as team members or materials assigned to projects, and users as logons with permissions on Microsoft Project Server system commands and data. When an enterprise resource is added to Microsoft Project Server, a corresponding Microsoft Project Server user is also created with default permissions. If a user with the same name already exists, the enterprise resource can be merged with the existing user account. Microsoft Windows® account information in the user account is synchronized back to the newly added enterprise resource.

During the resource migration process, you choose which resources to promote to enterprise resources, and which to map as duplicates of enterprise resources. For example, the resource John Smith could be duplicated in separate project plans or (less likely) in a resource pool as JSmith, JohnS, and John Smith. During the resource data migration to Microsoft Project Server you might choose to promote John Smith as the enterprise resource, and map JSmith and JohnS as duplicates.

If your organization currently uses a centralized resource pool, use the Import Resources Wizard to migrate this resource information to Microsoft Project Server. This wizard can map local fields to enterprise custom fields, map enterprise base calendars, and check for common problems such as duplicate names or invalid Windows user account name formats.

If your resource information is not in a centralized resource pool, you can still use the import process to migrate the resource information to Microsoft Project Server, or you can create a central resource pool and share it with all project plans that include resources. Doing the latter may help you identify duplicate resource information between different project plans.

If your resource information resides in another data source, such as Active Directory or a human resources database, you can approach resource data migration with one of two goals in mind:

Migrate resource data with no further synchronization with external source.

Synchronize resource information between Microsoft Project Server and other resource databases after data migration.

As you begin the import process, keep the following in mind:

All of the resources in your existing Microsoft Project 2000 projects do not automatically become enterprise resources after being imported, and they don't get enterprise code values associated with them.

Resources with assignments don't automatically become team members of those projects that they are assigned to.

Creating a new enterprise resource that is a non-generic work resource also creates a corresponding user with the following characteristics:

Belongs to the Team Member group

Has a blank password

Can log on

Counts towards Microsoft Project Web Access CAL licenses

Migrating Specific Project Plans to Microsoft Project Server

After you have successfully migrated resource information to Microsoft Project Server, you are ready to migrate data from specific project plans. Microsoft Project Central maintains pointers to projects that are stored outside the database. Importing these projects into a Microsoft Project Server database will not map the destination enterprise project with the workgroup project. The only significant loss is of the security settings for the projects. There is cleanup work to be done after importing a project, such as regenerating actuals and deleting the non-enterprise project to avoid duplicate assignments.

There are two methods of migrating projects to Microsoft Project Server, and both methods provide the same data validation so that no projects or tasks are migrated with invalid values or missing required fields. The two methods of project migration are the Import Project Wizard and using the Save As command on the File menu in Microsoft Project 2002.

With the Import Project Wizard, you can:

Map local to enterprise custom fields.

Map project resources to enterprise resources, leave project resources as local, or promote project resources to enterprise resources.

Import more projects in the same wizard session with the same field mapping.

Using the Save As command does not allow you to perform these actions.

Before you begin the project plan data migration, you may need to address some complicating factors, including the following:

If you have master and subprojects, you may see rolled up costs duplicated when the project plans have been migrated to Microsoft Project Server. To correct this duplication, represent master and subproject relationships with enterprise codes. In Microsoft Project Server, think in terms of programs and projects rather than master and subprojects. You can still use traditional master projects locally if you want, just don't save them to Microsoft Project Server.

If you have dependencies between tasks in different MPP files, open all linked files in Microsoft Project. Save each file to Microsoft Project Server, while keeping all of the linked files open. Microsoft Project resolves the links while the files are still in memory.

Running the SharePoint Configuration Wizard

To use the documents library and issues tracking features of Microsoft Project Server, you must install SharePoint™ Team Services from Microsoft and configure it for use with Microsoft Project Server. Although the SharePoint Configuration Wizard also installs SharePoint Team Services (if it is not already installed), you must use the SharePoint Configuration Wizard to configure SharePoint Team Services, even if SharePoint Team Services is already installed from another source.

To run the SharePoint Configuration Wizard

Insert the Microsoft Project Server installation CD into the CD-ROM drive, or connect to a network installation point.

If installing from the Microsoft Project Server CD with AutoPlay enabled, click SharePoint Configuration Wizard from Microsoft Project Server Setup, or browse the Microsoft Project Server installation CD (or network installation point), and run the file named Stswiz.exe.

Read and accept the license agreement, and then click Next.

From the list of available Web sites on your Microsoft Internet Information Server (IIS) computer, select the Web site where SharePoint Team Services should be extended.

Enter information about the SharePoint Team Services database in the Database server, Logon name, and Password boxes.

At the end of Setup, write down the information provided for Server name, Web server port, SharePoint Administration Port, Database server name, and Database name. This information is needed to connect SharePoint Team Services and Microsoft Project Server.

If you have not installed Microsoft Project Server, you can enter this information during the setup process.

If you have already installed Microsoft Project Server, you can use Microsoft Project Web Access to connect to Microsoft Project Server, and then click Admin in the top link bar to go to the Administration overview page. In the side pane, click Manage SharePoint Team Services. In the side pane, under Options, click Connect to servers, and then click Add Server.

Notes

If new Windows user accounts were created for use by Microsoft Project Server (when connecting to the SharePoint Team Services computer for administrative tasks or to the SharePoint Team Services database) and the User must change password at next logon check box is selected for the accounts, those users must log on and change their passwords before Microsoft Project Server can be configured to connect with SharePoint Team Services.

You should run PSCOMPlus.exe (found in the \Program Files\Microsoft Project Server\Bin\1033 on the Microsoft Project Server computer) before connecting with Microsoft Project Web Access. (The folder 1033 contains the files for the English version. Files for other languages can be found in the folder that corresponds to the locale ID [LCID] for that language.) PSCOMPlus provides the names of user accounts (in the format domain\account) that should be impersonated by a COM+ application, which allows Microsoft Project Server to connect to the SharePoint Team Services and SharePoint Team Services database computers.