Moving a project server Database and pwa site collection

Project Server 2013 database is a resource intensive database becuase of having Published, Draft, Reporting and Archive Tables.  Although Microsoft had distributed them to 4 databases in past but decided to stick with one in 2013.  For high project server usage Microsoft Recommended us to move Project Server Database and PWA Site collection database to a seperate SQL Server and Seperate Drive. 

PWA Site Collection can be moved to new Content Database using Move-SPSite cmdlet.  It guess it is simple process but The PWA Database move can be done using User interface if you are a Farm admin but process is somehow resulted in access denied for me.  I am putting it down.

  • Backup the PWA Database and Restore using SQL Server management studio on 2nd SQL Box.
  • Open Central Administration –> Manage Service Application –> Project Server Service Application
  • Click on down arrow next to PWA instance and Click Edit (Note you must have DBO on PWA DB to edit)
  • Change SQL Server name and click OK.
  • Browse the PWA Site.

You can also use PowerShell. 

  • Dismount the PWA Database that will automatically dismount the instance
  • Dismount the PWA Instance
$PSA = Get-SPServiceApplication | Where{$_.TypeName –Eq “Project Server Service Application”} #Change the name if required.

Dismount-SPProjectDatabase –Name ProjectWebAppDB –ServiceApplication $PSA

or Dismont the PWA instance

#If you have a single Instance
Get-SPProjectWebInstance | Dismount-SPProjectWebInstance

#if you have multiple instances
Dismount-SPProjectWebInstance -SiteCollection ""

#You can also use Get-SPProjectWebInstance and then get the ID to remove it.

then you can

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea 0 
Mount-SPProjectDatabase -Name ProjectWebAppDB -DatabaseServer ProdSQL2 -WebApplication
Mount-SPProjectWebInstance -DatabaseName ProjectWebAppDB  -DatabaseServer ProdSQL2 -SiteCollection
Get-SPProjectWebInstance | Select Url, DatabaseName, Id

Browse and Test the Site. 


Make sure you update any Reporting Services or Excel Reporting data connections on your BI Sites.  Better to publish them again.