How to change Domain when Migration from PS2010 to PS2013

Migration project server 2010 and 2013 can be challenging, however when changing the domain is part of the migration, then additional unsupported steps must be done. Basically the enterprise resource pool contains the user profile, along with the active directory account (domain). There are several SQL tables that need to be updated using a SQL update command to switch the domain reference. Also, the each enterprise profile has a GUID that points back to the AD GUID and this needs to be update.
The following SQL command will help you get thru that process. As always, this should be tested in an test environment prior to migrating to production.
​– UpdateDomainUsers

— Updates the following tables for domain user from OLDdomain to NEWdomain
— 1) PUB.MSP_RESOURCES
— 2) PUB.MSP_EPMRESOURCE
— 3) REPORT.MSP_EPMRESOURCE

USE ProjectWebApp

— First look at the what the domain name replace looks like
— Make sure that domain name matches the account used to create PWA

SELECT r.WRES_ACCOUNT
, REPLACE (LTRIM(r.WRES_ACCOUNT), ‘OLDdomain ‘, ‘NEWdomain’) as WReS2
, R.WRES_CLAIMS_ACCOUNT
, REPLACE (LTRIM(R.WRES_CLAIMS_ACCOUNT), ‘OLDdomain ‘, ‘NEWdomain’) as W2
, WRES_AD_GUID
FROM pub.MSP_Resources r
Where not r.WRES_ACCOUNT is null

— After a visual inspection, the tables can be updated
— Also, there may be a few accounts to exclude, such as the farm admin account
— We are also setting the WRES_AD_GUID to null, because during the resource sync,
— the GUID will get update to the new DOMAIN

UPDATE pub.MSP_Resources
SET WRES_ACCOUNT = REPLACE (LTRIM(WRES_ACCOUNT), ‘OLDdomain’, ‘NEWdomain’)
, WRES_CLAIMS_ACCOUNT = REPLACE (LTRIM(WRES_CLAIMS_ACCOUNT), ‘OLDdomain’, ‘NEWdomain’)
, WRES_AD_GUID = null
WHERE not WRES_ACCOUNT is null
AND WRES_CLAIMS_ACCOUNT <> ‘i:0#.w|OLDdomain\username1’
AND WRES_CLAIMS_ACCOUNT <> ‘i:0#.w|OLDdomain\username2’

— There may be a few other accounts that need to have the RES_AD_GUID updated

UPDATE pub.MSP_Resources
SET WRES_ACCOUNT = REPLACE (LTRIM(WRES_ACCOUNT), ‘OLDdomain’, ‘NEWdomain’)
, WRES_CLAIMS_ACCOUNT = REPLACE (LTRIM(WRES_CLAIMS_ACCOUNT), ‘OLDdomain’, ‘NEWdomain’)
, WRES_AD_GUID = null
WHERE WRES_CLAIMS_ACCOUNT = ‘i:0#.w|OLDdomain\username’

— Check out accounts records and verify update done correctly

select r.WRES_ACCOUNT
, R.WRES_CLAIMS_ACCOUNT
, WRES_AD_GUID
FROM pub.MSP_Resources r
Where not r.WRES_ACCOUNT is null

— MSP_EpmResource table needs the same care for updating domains

SELECT
ResourceNTAccount
, UserClaimsAccount
FROM dbo.MSP_EpmResource R

— After visual inspection, then update the records

select
ResourceNTAccount
, REPLACE (LTRIM(r.ResourceNTAccount), ‘OLDdomain’, ‘NEWdomain’) as WReS2
, R.UserClaimsAccount
, REPLACE (LTRIM(R.UserClaimsAccount), ;OLDdomain’, ‘NEWdomain’) as W2
FROM dbo.MSP_EpmResource R



UPDATE MSP_EpmResource
SET ResourceNTAccount = REPLACE (LTRIM(ResourceNTAccount), ‘OLDdomain’, ‘NEWdomain’)
, UserClaimsAccount = REPLACE (LTRIM(UserClaimsAccount), ‘OLDdomain’, ‘NEWdomain’)
WHERE not ResourceNTAccount is null
–AND WRES_CLAIMS_ACCOUNT <> ‘i:0#.w|OLDdomain\username1’
–AND WRES_CLAIMS_ACCOUNT <> ‘i:0#.w|OLDdomain\username2’



SELECT
ResourceNTAccount
, UserClaimsAccount
FROM dbo.MSP_EpmResource R

——————————————-

— With Project 2010 the reporting database may need updates as well

SELECT [ResourceUID]
,[ResourceName]

,[ResourceType]
,[ResourceNTAccount]
,[ResourceIsActive]
FROM [ProjectServer_Reporting].[dbo].[MSP_EpmResource]
where ResourceNTAccount IS NOT NULL



SELECT
ResourceNTAccount
, REPLACE (LTRIM(r.ResourceNTAccount), ‘OLDdomain’, ‘NEWdomain’) as WReS2
FROM [ProjectServer_Reporting].[dbo].[MSP_EpmResource] R

UPDATE [ProjectServer_Reporting].[dbo].[MSP_EpmResource]
SET ResourceNTAccount = REPLACE (LTRIM(ResourceNTAccount), ‘OLDdomain’, ‘NEWdomain’)
WHERE not ResourceNTAccount is null

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s