|
Installing Project Server using AutoSPInstaller
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
My Project Expert 