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 "http://webapp.com/PWA"

#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 http://webapp.com
Mount-SPProjectWebInstance -DatabaseName ProjectWebAppDB  -DatabaseServer ProdSQL2 -SiteCollection  http://webapp.com/PWA
Get-SPProjectWebInstance | Select Url, DatabaseName, Id

Browse and Test the Site. 

Note:

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

Advertisements

One thought on “Moving a project server Database and pwa site collection

  1. good day.
    even after
    Mount-SPProjectWebInstance -DatabaseName ProjectWebAppDB -DatabaseServer ProdSQL2 -SiteCollection http://webapp.com/PWA
    Get-SPProjectWebInstance | Select Url, DatabaseName, Id
    and see that database server was change the BICentral still try opening report from older database…

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s