Migrating Project 2016 to Project 2019

I will be speaking on migration from SharePoint/Project Server 2016 to 2019 at the SharePoint Saturday in Charlotte August 11, 2018.

Migrating from SharePoint 2016 to 2019 is not going to be to difficult as previous SharePoint Migrations. The session goes over reviewing the requirements and looking at options for cleaning up SharePoint sites and web applications and then goes thru steps with migrations from 2016 to 2019 and using PowerShell to make it easier and more predicative

Click link for more information at SharePoint Saturday in Charlotte, NC

Click link below for my PowerPoint presentation that contains slides and scripts.

2018-08-11 SharePoint 2019 Upgrade V6

.

 

 

Limited PowerApps Features for Project Online

There is a lot of excitement about PowerApps and how it is designed for non-programs to build apps.   Yes, it can create basis phone apps based on SharePoint list, however I am a little disappointed in the features available with project online.  That is what this blog is about. I wanted to see what I can do for me when using Project Online.  In a word, I believe it is weak and doesn’t provide many useful features.  However, I am optimist that Microsoft will be adding to the Power App library for project online.

My initial reason for digging deeper inside the PowerApp tool was that I had a client who wanted the project online time sheet feature setup with Power Apps. Project online  was being evaluated and several other competitors and admit the competitors user interface was much easier to work with however they didn’t have all the powerful features found in project online.  I assume Power Apps could build an interface to to project online time sheets. As I explored Power Apps capability, I realized that it wasn’t there.  Paul Mather blog provides a work around for export PWA into SharePoint list and then building power apps from the list.  Very clever, however updates do not go back into PWA database

PowerApps usually start with a home page.   Figure 1 shows the basic functionality provided when using project online. The basic features are 1) List projects, 2) Create project, 3) Create Resources, 4) Listing tasks, 5) check in and publish projects and 6) checkout projects.  Displaying Power Bi reports is a supper feature but it is really just a standard Power App.

Figure 2 shows a simple list of projects.  Project list provides a pathway for listing tasks once a project is found and selected from list.  Project List page is create by using ProjectOnline.ListProject function which is a connection function for retrieving list of projects.

Figure 1. PowerApp home page     Figure 2. Project List

The next two functions that are available is CreateResource and CreateProject. As it can be seen in Figure 3 and 4, there are few parameters that are based to project online.  I am not seeing a whole lot of value at this point in time, however I believe the number of fields and functions will grow in the future.

Figure 3. Create Project            Figure 4. Create Resource

Figure 5 shows Task List feature listing all the tasks for a project. The feature doesn’t provide a method for updating task in the field.  Again, maybe in a future release but not much for now.  Figure 6 shows Power BI reporting page and I see a lot of potential with this aspect of a project PowerApp.

Figure 5 List Tasks                         Figure 6 Power BI reports

All-in-all it looks like a good first step and look forward to seeing Microsoft building many more function calls that will enable developers bring project online closer to the PMO fingers tips.

 

 

 

 

 

Rebooting the Project Management Office (PMO)

The PMO much like a computer becomes ineffective and inefficient over time and sometimes requires a reboot.  Rebooting a computer resets the system, clears out cache, releases memory and updates drivers.  Once the computer system is back up and running it runs faster and more efficient. The PMO often needs a reboot as well.   The PMO’s process become stall and outdated, executive management have come and gone, people are asking why are we doing it this way and newer software has been release over the years.  And over time the PMO becomes ineffective and needs a reboot as well.
The average lifetime of a PMO is about five and half years.  This sad fact actually makes sense as well.  Think about what has happen in five years after a PMO launches.  Executive management and sponsors have probably been promoted, left the company or perhaps retired, which means someone else has inherited it.  Processes and procedures have become stall and ineffective over time. The PMIS (Project Management Information System) lacks the latest features and hasn’t kept up with the latest enhancements. Project managers are incorporating agile into their processes creating chaos and confusion.  The original vision and purpose of the PMO has changed over time.  The PMO role and reach may need to expand and align with human resources or contract management.   It’s clearly evident that many events can occur in the past five years and instead of letting the PMO continue to become ineffective, perhaps it just needs a reboot.

What does it mean to reboot the PMO?   It really simple.  It’s just a matter of revisiting the vision and purpose of the PMO and ensure that it still aligns up to it business’s mission.  Realignment of the PMO may include some of the following steps below:

  1. Verify PMO executive support
  2. Write a PMO reboot plan and schedule
  3. Review the PMO vision and core values
  4. Review the PMO scope such as portfolios, program and projects
  5. Review the PMO maturity model.
  6. Review the staffing and resources required for PMO
  7. Review PMO training, mentoring and coaching
  8. Review PMO mythologies and processes
  9. Review PMO reporting meeting management requirements
  10. Review PMO standards for reporting
  11. Review PMO security and access permissions
  12. Review PMO tools, project and PMIS software

The last step is the main point of my blog; the review of PMO tools, project and PMIS software.  Companies often upgrade project server to a more current version without thinking about ​rebooting the PMO.  In many cases the project server upgrades are done without reflecting on what has changed over the years and how the new features can be incorporated into the PMO.

Personally for me, I think Microsoft Project server solution is the right solution for many companies, however, before spending money on upgrade, maybe it’s a time to bring in another PMIS solution.  Or perhaps it’s the right time to expand the maturity of the PMO and incorporate the other project server features such as timesheets, resource engagements, portfolio management or workflow to name a few.

In summary, every​ five years is a good time to reboot the PMO and make grow the maturity of the PMO.​

Project Server 2016 Provision Menus Are Gone


What happened to the options creating, editing and viewing the PWA in project server 2016?  Below is a screen shot of creating a project web app instance in SharePoint 2013.

ManageProjectWebApps2

Now look at the options in SharePoint 2016.  Notice they are missing except for manage.

ManageProjectWebApps

What this now means is that the easy to use menu driving procedure of creating and managing PWA instances is now history.   PowerShell is the way it is done now.   I really like PowerShell, but really wish Microsoft would keep some of the menu options. Rumor is that everything is going the way of PowerShell.
Here are a few PowerShell scripts that provides the same functions as before:
#######################################################
# Author: Michael Wharton
# Date: 08/30/2016
# Description: Project Server 2016 configuration
#    Create one or multiple PWA sites
#######################################################
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Confirm:$false -Verbose
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  -Verbose
#######################################################
#  Update the following metadata that is used consistantly thru the script
#######################################################
<$SqlServerName        = “LAB-2016SP.DOMAIN.LOCAL”
$WebAppURL            = “http://LAB-2016SP.DOMAIN.LOCAL&#8221;
$SitePwaURL           = “http://LAB-2016SP.DOMAIN.LOCAL/pwa&#8221;
$WebHostHeader        = “LAB-2016SP.DOMAIN.LOCAL”$WebAppURL            = “http://LAB-2016SP&#8221;
$SitePwaURL           = “http://LAB-2016SP/pwa&#8221;
$SitePwa2URL          = “http://LAB-2016SP/pwa2/PMO&#8221;
$SitePwaOOBURL        = “http://LAB-2016SP/pwaoob”$WebAppName           = “Project Server 2016”
$ProjectServiceApp    = “Project Service App”
$ProjectServicePool   = “Project Service Pool”
$WSS_ContentDB        = “LABK_PWA_WSS_Content”
# Create credentials
$ServiceSP            = “DOMAIN\ServiceSP”
$ServicePass          = “password”
$credServiceSP  = New-Object System.Management.Automation.PSCredential -ArgumentList @($ServiceSP,(ConvertTo-SecureString -String $ServicePass -AsPlainText -Force))
################################################################
# create Managed Account
New-SPManagedAccount -Credential $credServiceSP -Verbose
################################################################
# Create Project Service Pool
New-SPServiceApplicationPool -Name $ProjectServiceApp  -Account $ServiceSP -Verbose
################################################################
#  Create Project Server Service and Enable Project Key
$ProjectServiceID = New-SPProjectServiceApplication -Name $ProjectServiceApp -Proxy -ApplicationPool (Get-SPServiceApplicationPool $ProjectServicePool)
Enable-projectserverlicense -Key “23CB6-N4X8Q-WWD7M-6FHCW-9TPVP”
#################################################################
# Create Web Application on root that contains project server collection
#################################################################
$AP = New-SPAuthenticationProvider -Verbose
New-SPWebApplication -Name $WebAppName -port 80 -URL $WebAppURL -DatabaseName $WSS_ContentDB  -ApplicationPool $ProjectServicePool   -ApplicationPoolAccount (Get-SPManagedAccount $ServiceSP) -AuthenticationProvider $AP -Verbose -Confirm:$false
# Create PWA on Root
New-SPSite -Url $WebAppURL -Template “PWA#0” -Name “Project Web App” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $WebAppURL -Verbose
Set-SPProjectPermissionMode -Url $WebAppURL -Mode ProjectServer -Verbose
# Add a few resources and make basic configuration updates such as publish sites
Start $WebAppURL
###################################################
#  Create multiple PWA sites
$SitePwaURL           = “http://LAB-2016SP/pwa&#8221;
$SitePwa2URL          = “http://LAB-2016SP/pwa2&#8221;
$SitePwaOOBURL        = “http://LAB-2016SP/pwaoob&#8221;
###################################################
# Create Root
New-SPSite -Url $WebAppURL -Template “STS#0” -Name “Root” -OwnerAlias “DOMAIN\mawharton” -Verbose
# Create PWA Site as managed path
New-SPManagedPath “PWA” -WebApplication $WebAppURL -Explicit
# remove-spmanagedpath “PWA”  -WebApplication $WebAppURL
New-SPSite -Url $SitePwaURL -Template “PWA#0” -Name “Project Web App” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePwaURL -Verbose
Set-SPProjectPermissionMode -Url $SitePwaURL -Mode ProjectServer -Verbose
Start $SitePwaURL
# Create PWA2 as second site
New-SPManagedPath “PWA2” -WebApplication $WebAppURL -Explicit
New-SPSite -Url $SitePWA2URL -Template “PWA#0” -Name “Project Web App2” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePWA2URL -Verbose
Set-SPProjectPermissionMode -Url $SitePWA2URL -Mode ProjectServer -Verbose
Start $SitePWA2URL
# Create PWAOOB as second site (Out-Of-Box)
New-SPManagedPath “PWAOOB” -WebApplication $WebAppURL -Explicit
New-SPSite -Url $SitePwaOOBURL -Template “PWA#0” -Name “Project Web App OOB” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePwaOOBURL -Verbose
Set-SPProjectPermissionMode -Url $SitePwaOOBURL -Mode ProjectServer -Verbose
Start $SitePwaOOBURL
# Create PWA2 as second site
New-SPManagedPath “PWA2” -WebApplication $WebAppURL
New-SPSite -Url $SitePWA2URL -Template “PWA#0” -Name “Project Web App2” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePWA2URL -Verbose
Set-SPProjectPermissionMode -Url $SitePWA2URL -Mode ProjectServer -Verbose
Start $SitePWA2URL
#################################################
#  List Web Application
#################################################
Get-SPProjectWebInstance | Select URL , adminAccount, ProvisioningResult, Online | Sort-Object URL
#################################################
#  Delete PWA Site
#################################################
Remove-SPSite -Identity $SitePwaURL  -Confirm:$false -verbose     # Removes PWA site
Remove-SPSite -Identity $SitePwa2URL -Confirm:$false -verbose     # Removes PWA2 site
Remove-SPSite -Identity $SitePwaOOB  -Confirm:$false -verbose     # Removes PWAOOB site

Installing Project Server using AutoSPInstaller

Wow!  For years I have been building PowerShell scripts to install and configure SharePoint and Project server.  It has worked well and have learned a lot about PowerShell in doing so.   However, I recently discovered a tool in CodePlex called AutoSPInstaller.   http://autospinstaller.codeplex.com/

My first reaction to this tool was the same as using SharePoint Products Configuration Wizard.  It couldn’t be that good and all being executed behind code.   When I finally looked at AutoSPInstaller and discovered that it was all based on PowerShell.  This perk my interested.

My first few attempts using AutoSPInstaller failed for me and my reaction was not very good.  However, I kept hearing about how others were using it and so I decided to give it another chance only this time, I took a little time in reading the documentation.  After examining the PowerShell code and using the “Build Farm” tool found at https://autospinstaller.com/ and with a successful installation of SharePoint and a configuration of SharePoint Farm; I WAS SOLD.

This is one of the best tools I have seen for building a SharePoint farm and documenting the farm.  And it also configures other useful items to make your farm more robust such as setting up SQL alias and disabling the loop check.  And it works for SharePoint and Project server 2010, 2013 and 2016.  I am consistently build different SharePoint scenarios in my lab and AutoSPInstaller automates the process of setting it up.

One other item, I should mention about AutoSPInstaller. If you want to learn more about PowerShell and see good examples of modules and well written code, then dig deep into the code.  I really like PowerShell and learning PowerShell techniques.  AutoSPInstaller is full of many PowerShell tricks.

Building the SharePoint Farm XML is done by jumping to the https://autospinstaller.com/ site and then clicking “Build a Farm”.   The following screen shot are for building a SharePoint farm that configures Project Server on a single server.  My configuration is for the very basics services that project server typically uses such as Excel Services, Secure Store, Performance Point Service and of course project server service.  I should point out project server service doesn’t always work after the installation and at times I have needed to de-provision and re-provision the project service to get it working.

SPAutoInstaller-Servers

Servers Page.  Its purpose is to define the servers in the SharePoint farm. If only one server is being setup for the farm, then leave servers list blank.  It requires either no servers or a minimum of two servers for the Share Point farm.

SPAutoInstaller-2-Options

Installation Options page.  The highlighted options are important to me.

SPAutoInstaller-3-Main

Farm – Configure the Main Farm Options

General settings for the SharePoint configuration.  I don’t like putting real password in the build farm tool, so I put in bogus passwords and replace them once I create the XML file.

I really like the database preview option.   It allows you to have all the SharePoint farm databases prefixed and makes it much easier for the DBA to organize the databases.

The create alias option is a nice feature for setting this up as well.  Makes it much easier to move SharePoint to another SQL server in the future.

I specify only a few managed accounts for project server deployment.  If setting up for SharePoint deployment, I would add all the account in this page.  It validates the username and passwords.

SPAutoInstaller-4-FarmServices

Farm Services is really depends on your requirements.  I will leave it at that.

SPAutoInstaller-5-FarmLogging

Farm Logging screen.  Take the default and change it later.  I had some issues, so now keep it as the default.

SPAutoInstaller-6-RootWeb

Root Web Application.  I change this up a little by just setting up a root.

SPAutoInstaller-7-stateService

For project server, I typically setup State Service, Secure Store, Excel Services and PerformancePoint Services.  Under the Other option are special settings for project server.

 

SPAutoInstaller-8-SecureStore

 

Miscellaneous Components.   Setting up the PDF content type has never been so easy.

SPAutoInstaller-9-ExcelServiceSPAutoInstaller-10-PerformancePointSPAutoInstaller-11-Miscelanous

SPAutoInstaller-12-ProjectService

Here is the final screen.  Word of caution is that the configuring project server doesn’t work for me.  I am going to look into the PowerShell and see what the problem is.  In the meanwhile, I would just letting it configure project server. Once it is complete, test to see if it works.  If project server doesn’t work properly then do the following

  1. De-provision PWA
  2. Delete the web application
  3. Reboot the server
  4. Create a new Project Server 2013 web application
  5. Provision PWA

Finally, I would like to thank Ivan Josipovic and Brian Lalancette for building and maintaining AutoSPInstaller on CodePlex. You can find out more about these guys at https://autospinstaller.com/Contact

 

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