Migration performance data for Project Server 2007

  • Article

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

Important

This article contains rough data based on the migration testing Microsoft has done. This data is approximate and should only be used to get a general idea of migration performance. Although we include specific configuration information used in our testing, it is important to note that a number of variables can affect migration performance times.

Configuration Specifications

The configuration used to derive the migration performance data used the following specifications:

Configuration   Project Server 2007 application server, Project Web Access, and database server on a single computer; Project 2003 database on a separate computer running Microsoft SQL Server

Hardware for Project Server 2007   2.8 GHz CPU, 2 GB RAM

Hardware for Project Server 2003 SQL Server   4 processors (2.5 GHz each), 4 GB RAM

Profile specifications

Four different customer migration profiles were used to derive the performance data. The table below gives specifications for each profile that was used during testing for migration performance data. It represents data from Project Server 2003 to be migrated.

Note

If you want to verify any of these values in your existing database to see which profile your data most closely resembles, the "SQL Script to obtain Profile Information" section of this article contains an SQL script to help you determine the quantities for your system.

Profile 1: Small number of projects Profile 2: Medium number of projects Profile 3: Large number of projects Profile 4: Very large number of projects

Number of projects

40

450

1200

8250

Number of inserted projects

0

0

0

250

Number of cross-project links

0

0

0

4000

Number of tasks

5400

28630

11000

420000

Number of assignments

6400

11000

65000

550000

Number of enterprise resources

200

2500

2000

2200

Number of custom fields in use

40

80

70

200

Number of project-authenticated users

20

300

300

120

Number of Windows authenticated users

150

2100

1500

2000

Number of security groups

12

13

10

150

Number of security categories

11

12

10

120

Number of custom Project Web Access views

20

27

30

200

Performance profile times

The following table contains performance times required to migrate data for each of the four profiles described in the previous section, based on the described configuration specifications.

Profile 1: Small number of projects Profile 2: Medium number of projects Profile 3: Large number of projects Profile 4: Very large number of projects

Time taken to migrate global data

8 minutes

17 minutes

20 minutes

25 minutes

Time taken to migrate (save and publish)

1 hour, 15 minutes

2 hours, 30 minutes

12 hours

1 day, 12 hours

SQL script to obtain profile information

The following SQL script can be run on your Project Server 2003 database to derive migration profile information (for example, number of projects, number of custom fields in use, and so on). You can then compare the results with the table in the "Profile specifications" section to help you determine which profile your Project Server 2003 data that you want to migrate most closely resembles. When you determine this, then you can anticipate the migration performance times you are most likely to have.

To run the script:

  1. Open Microsoft SQL Server Query Analyzer.

  2. Connect to the computer running SQL Server on which the Project Server databases reside.

  3. Open the "profile assessment" script in SQL Query Analyzer.

  4. Insert your Project Server database names where noted in the script.

    1. Find <Enter "Project Tables" database name> in the script. Replace it with your "project tables" database name.

    2. Find <Enter "Web tables" database name> in the script. Replace it with your "Web tables" database name.

      If you do not use the split database configuration, simply enter the name of your Project Server database for both database names.

  5. On the Query menu, choose Results to Text.

  6. On the Query menu, click Execute to run the script.

Profile assessment script

Make sure to edit the script with your Project Server 2003 database information before executing it.

select '#### Project Tables Information ####'

use <Enter "Project Tables" database name>

--Total size of DB 
exec sp_spaceused 

--Number of projects in the database
select count(*) as 'Number of Projects' from msp_projects

--Number of inserted projects
select count(*) as 'Number of Inserted Projects' 
  from MSP_TEXT_FIELDS where TEXT_FIELD_ID = 188743706

--Number of cross-project links
select count(*) as 'Number of cross-project links' 
  from MSP_TEXT_FIELDS 
  where TEXT_FIELD_ID = 239075346 or TEXT_FIELD_ID = 239075347

--Number of total tasks, assignments in the system
select count(*) as 'Number of tasks' from msp_tasks
select count(*) as 'Number of assignments' from msp_assignments

--Number of Enterprise resources (in ResGlobal)
select count(*) as 'Number of Enterprise Resources' 
  from msp_resources where proj_id = 1 and res_name is not null

--Number of custom fields in use
select count(*) as 'Number of Custom Fields in use' 
  from msp_field_attributes 
  where attrib_id = 206 and proj_id in (select proj_id 
  from msp_projects where proj_type =2 )

select '#### Web Tables Information ####'

use <Enter "Web Tables" database name>

--Total size of DB 
exec sp_spaceused 

--Number of Project Authenticated users, Windows authenticated users
select count(*) as 'Number of Project Authenticated users'  
  from MSP_WEB_RESOURCES 
  where WRES_USE_NT_LOGON = 0 
  and WRES_CAN_LOGIN <>0 and WRES_COUNT_LICENSE <> 0
select count(*) as 'Number of Windows Authenticated users' 
  from MSP_WEB_RESOURCES 
  where WRES_USE_NT_LOGON <> 0 
  and WRES_CAN_LOGIN <>0 and WRES_COUNT_LICENSE <> 0

--Number of security groups,security categories
select count(*) as 'Number of Security Groups' 
  from MSP_WEB_SECURITY_GROUPS
select count(*) as 'Number of Security Categories' 
  from MSP_WEB_SECURITY_CATEGORIES

--Is there any Windows SharePoint Services integration ?
select count(*)  as 'Is there any Windows SharePoint Services Integration ?' 
  from msp_web_admin where WADMIN_CURRENT_STS_SERVER_ID <> -1

--Number of linked issues, risks, documents 
-- (indicates how much Windows SharePoint Services integration is used)
select count(*) as 
  'Number of linked Windows SharePoint Services issues/risks/documents' 
  from MSP_WEB_OBJECT_LINKS

--Number of custom Project Web Access views
select count(*) as 'Number of customer Projet Web Access views' 
  from MSP_WEB_VIEW_REPORTS where WVIEW_ID > 100

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.