Best practices for managing a large number of resources in Project Server 2010

Overview of Project Server 2010 site permissions

Project Server 2010 uses the normal SharePoint permissions infrastructure to set access control for the Microsoft Project Web App (PWA) site. It also uses that same infrastructure to set access control for any project sites (previously known as project workspace sites) that are created for the individual project plans held in Project Server.

At the PWA site level, users are added to specific SharePoint groups depending on their permission level within Project Server. The site settings page for a PWA site contains "Microsoft Project Server" SharePoint groups for Project Managers, Readers, Team members, Web Administrators, and Workflow and Project Detail Pages Administrators. When users are added in Project Server 2010, they are also added to the appropriate group for the PWA site that they are allowed to access. For example, let's say a user is added to Project Server 2010 and is a member of the Project Managers security group. The user is added to the Project Managers Group (Microsoft Project Server) in Site Permissions for the PWA home page, the Project Center page, the Approval Center page, and all PWA site pages that the user is allowed to access.

The PWA site's Site Permissions settings not only contain the "Microsoft Project Server" SharePoint groups, but also show some individual SharePoint user accounts for site collection administrators and other farm administrator accounts. The following table shows the SharePoint permission levels for the Microsoft Project Server SharePoint Group at the PWA site level.

For project sites, users are added directly as individuals, and they are not added to specific "Microsoft Project Server" SharePoint groups. The permission level granted to the user is determined by his or her role.

Performance issues when the recommended user limit to project sites is exceeded

In Office Project Server 2007, a performance issue can occur in user synchronization to the PWA sites because individual users are added to PWA and project workspace sites with specific permission levels. When changes are made to the user permission for the site, all users who have permissions to the site are removed, and then they are added back to the site. When there are many users on a site, this process can take a very long time. Users who are in process of being added back to the site get an Access Denied error until they are added back to the site.

In Project Server 2010, two changes were made to user access to PWA sites to correct the issue that exists in Office Project Server 2007:

• Users who access PWA sites were added to SharePoint user groups instead of added individually.

• When user synchronization occurs, users are removed individually and added back to the site one at a time (instead of removing all users and then adding them back one at a time).

These changes were enacted only for PWA sites, which typically have lots of users who access them. This change was not made for project sites. The reason is that project sites typically have a lot fewer users who access them (usually the resources assigned to the project) and are less likely to be affected by the synchronization issue. Where this can be an issue is when you want to have many or all of the users accessing many or all the project sites. This could be achieved either by adding many users to a project, or by assigning the View Project Sites permission at the team-member level in a category that includes many or all projects. However, when this scenario occurs, it is possible for the recommended software boundaries and limits of SharePoint Server 2010 to be exceeded if the number of users is large. Exceeding these recommended boundaries can lead to performance issues. Each user who is added individually to a site is considered a security scope. The recommended maximum number of unique security scopes per list is 1,000. Each list and library in the site would be inheriting from the parent site permissions — and would exceed this limit if more than 1,000 individual users have access to the site. For more information about security scope limitations for lists, see SharePoint Server 2010 capacity management: Software boundaries and limits.

When the recommended unique security scope boundaries are exceeded, performance issues can occur. The problems occur when a change is made in site membership caused by changes in the categories or group, or because of actions such as adding a user or deactivating a user. One reason the recommended limitation for individual users was imposed was that if it was exceeded, the process for removing users from the site can become very slow. It is especially slow if the same user is removed from multiple sites, all of which have exceeded the recommended limit. In cases in which multiple users are deactivated, it is possible that the server will become unresponsive and unable to authenticate users. This creates a unique problem when you start to exceed the recommended individual user limits for a site. Your server could become unresponsive when you have to manage users because you have too many users who have permission on a site. When you attempt to correct the problem by removing users from the site, you can also make the server unresponsive.

Best practices for avoiding this issue depend on what your intended purpose is:

• If you really do require that most of the users can access most of the project sites: Instead of adding users individually to sites, use SharePoint Server 2010 groups and use inheritance from PWA to add the users to these groups. For example, generally the parent site for project sites is the PWA home site. In this scenario, project sites will inherit the permissions of the PWA home site, which has all PWA users in Microsoft Project Server SharePoint groups.

• If having many users who are accessing many sites is unintended and you have to resolve the problem: Remove the View Project Sites category permission from the category that gives users access to the sites. You can also reduce the number of resources assigned to the project. Prior to doing either action, you must stop the synchronization of users to the site. Otherwise, either action may make your server unresponsive.

To disable project site synchronization, you can write a utility that uses the Project Server Interface (PSI), the Admin Web Service, and sets the UserSyncSetting Enumerationto DisablePWS. Call the enumeration with the Admin.UpdateUserSyncSetting Method.

You can also disable the setting by making changes directly to the MSP_WEB_ADMIN table in the Published database in the WADMIN_USER_SYNC_SETTING column. You can run the following SQL query to disable project site synchronization:

Update [ProjectServer_Published].[dbo].[msp_web_admin] set [WADMIN_USER_SYNC_SETTING]=2

You may find the option of making changes directly to the MSP_WEB_ADMIN table in the Published database to be much easier than creating and testing a tool to do the same thing through the PSI.

Inheriting permissions from the PWA parent site

Once project site synchronization is disabled, it is still possible to trigger the same issues if you try to remove users directly from the project site through the functionality that is provided in the SharePoint site permission page. One way to remove users quickly without triggering the individual deletion that causes the problem is to inherit permissions from the parent site. This can be done through the user interface ribbon on the Site Permissions page of the individual project site (under the Site Actions menu).

To inherit permissions from the parent site to the project site

1. On the project site, click Site Actions, and then click Site Permissions.

2. On the Permissions page, click the Edit ribbon.

3. Click Inherit Permissions.

4. On the message box that warns you that changing to inherited permissions may prevent users from accessing the site, click OK.

The ribbon will indicate that the site now inherits permissions from its parent site, which should be a PWA site. The site permission structure from the parent site will now be the site permission structure for the project site, which means that individual PWA users are now contained within Microsoft Project Server site groups.

If your end goal is to give most users access to most sites, you may want to have your project sites inherit parent permissions from PWA as described above. Before taking this action, you should make sure that the PWA site has the right permissions for all users who need access. This is because the permission that the users have in PWA will be the permission the users will have in the project site when you execute the procedure to inherit permissions.

If you have many project sites, you can use Windows PowerShell to automate the change for you. The following Windows PowerShell command causes all project sites that are children of the specified parent PWA site to inherit its permissions. You can run the Windows PowerShell command in the SharePoint 2010 Management Shell:

$site = Get-SPSite "<url of PWA>"
     Foreach ($web in $site.AllWebs) {
        $web.Update()
        $web.ResetRoleInheritance()
        $web.Update()
        }
     $site.Dispose()

You would have to run this command (or the previous procedure for inheriting site permissions) for any new project sites to avoid a reoccurrence of the issue.

In an environment in which all project sites are inheriting PWA permissions, it is possible that you may want to have certain projects that have sensitive information and you would want to manage permissions and users at an individual level. It would be possible to use Windows PowerShell to set a property for these sites, and then use the property to filter it out in a modified version of the Windows PowerShell command used to set inheritance (above).

Disabling the Project Site Permissions setting

You can also avoid the automatic addition of users to your project sites by disabling the Project Site Permissions setting located in the Operational Policies section of your Project Web App Server Settings. When this setting is enabled, Project Web App users are automatically synchronized with project sites whenever user permissions change in Project Server 2010, the project manager publishes the project, or when the project site is created. If the setting is enabled and any of the above things occurs, the following actions will occur automatically:

• Project managers who have published a project or who have Save Project permission on a project are added to the site with Project Manager (Microsoft Project Server) permissions.

• Team members with assignments in a project are added to the site with Team Members (Microsoft Project Server) permissions.

• Other Project Server users who have View Project Site permissions on a project are added to the site with Readers (Microsoft Project Server) permissions.

Disabling this option stops the automatic addition of Project Server users to the site, but it will not remove users that have already been added.

To disable the Project Site Permissions setting

1. On the Server Settings page, in the Operational Policies section, click Project Site Provisioning Settings.

2. On the Project Site Provisioning Settings page, in Project Site Permissions section, clear the check box labeled Check to automatically synchronize Project Web App users with Project Sites when they are created, when project managers publish projects, and when user permissions change in Project Server.

   When the check box is cleared, Project Server users are never synchronized with project sites.

3. Click Save.

For more information about the Project Site Permissions setting, see Project Site Provisioning Settings (Project Server 2010 settings).

Errors associated with exceeded recommended user limits to project sites

The following are errors that might appear in the ULS logs when your Project Server deployment has performance problems because more than the recommended maximum users are accessing project sites. Typically the user who initiates the problem (perhaps by deactivating a resource), sees the Save button on the Edit User page hang on the "clicked" position, eventually displaying a message that "An unexpected error has occurred." The correlation ID can be found in the ULS logs, and it will provide data that relates to an SQL deadlock. The “Critical” level entry will resemble the following:

• 08/10/2011 12:17:02.85 w3wp.exe (0x2178) 0x314C SharePoint Foundation Database 5586 Critical Unknown SQL Exception 1205 occurred. Additional error information from SQL Server is included below. Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 886d9cdd-5c0c-4f3a-8f89-f4e8c92acde3

A "High" level error that provides more information about the query causing the issue will resemble the following text. The entry provided has been shortened significantly, but do note the key “proc_SecRemoveUserFromSite” text:

• 
  08/10/2011 12:17:06.97 w3wp.exe (0x2178) 0x314C SharePoint Foundation Database tzkv High SqlCommand: 'SET NOCOUNT ON; DECLARE @DN nvarchar(256),@LN nvarchar(128),@@DocUIVersion int,@@S uniqueidentifier,@@Level tinyint; DECLARE @ItemId int; DECLARE @@iRet int; DECLARE @ExtraItemSize int; SET @@Level = 1; SET @@S=@wssp0; EXEC @@iRet = proc_SecRemoveUserFromSite @@S, @wssp1, @wssp2 SELECT @ItemId = @wssp3 IF @@iRet <> 0 BEGIN GOTO DONE; END ;BEGIN TRAN IF NOT EXISTS( SELECT tp_ID FROM UserData WHERE tp_ListId = '06C8C9BB-B10B-4042-8859-9F9985E73E76' AND tp_ID = @ItemId AND tp_Level = 1 AND tp_RowOrdinal =0) BEGIN SELECT @ExtraItemSize = 0 EXEC @@iRet = proc_AddListItem @SiteId….

An “Unexpected” level entry that also may be generated, similar to the following:

• 
  08/10/2011 12:17:06.97 w3wp.exe (0x2178) 0x314C SharePoint Foundation Runtime tkau Unexpected System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x80131904 at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateMembers(String bstrUrl, UInt32 dwObjectType, String bstrObjId, Guid& pguidScopeId, Int32 lGroupID, Int32 lGroupOwnerId, Object& pvarArrayAdd, Object& pvarArrayAddIds, Object& pvarArrayLoginsRemove, Object& pvarArrayIdsRemove, Boolean bRemoveFromCurrentScopeOnly, Boolean bSendEmail) at Microsoft.SharePoint.Library.SPRequest.UpdateMembers(String bstrUrl, UInt32 dwObjectType, String bstrObjId, Guid& pguidScopeId, Int32 lGroupID, Int32 lGroupOwnerId, Object& pvarArrayAdd, Object& pvarArrayAddIds, Object& pvarArrayLoginsRemove, Object& pvarArrayIdsRemove, Boolean bRemoveFromCurrentScopeOnly, Boolean bSendEmail) 886d9cdd-5c0c-4f3a-8f89-f4e8c92acde3

The server could be unresponsive for 15-30 minutes. During this time, other users will get timeout errors on their pages and the ULS logs may show the following entries:

• 
  08/10/2011 12:20:22.30 w3wp.exe (0x1228) 0x1454 SharePoint Foundation Monitoring b4ly High Leaving Monitored Scope (ExecuteStoredProcedureDataReader -- MSP_AUTH_GETUSERBYNAME). Execution Time=120002.728838442 2be0491a-a64b-4237-8cfc-40342a374d49
• 
  08/10/2011 12:20:22.30 w3wp.exe (0x1228) 0x1454 Project Server General 8ym5 Monitorable PWA:http://<server>/PWA, ServiceApp:Project Web App Service Application, User:, PSI: SqlException occurred in DAL: <Error><Class>0</Class><LineNumber>0</LineNumber><Number>-2</Number><Procedure></Procedure> <Message> System.Data.SqlClient.SqlError: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. </Message> <CallStack> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at …