locked
Move reporting database to a new SQL instance RRS feed

  • Question

  • Hi,

    During PS installation we set up all 4 databases on the same SQL instance. Now I would like to move just the reporting database to a new sql instance for security purposes. Is there a supported way to do this?

    Tried to edit the PS Service App and just change the instance of the reporting but got an error saying that it was an unsupported operation:

    Provisioning 'PWA': One or more of the databases already contains schema. When editing or creating a Project Server instance, you may specify:
    * Four databases that do not exist
    * Four existing, blank databases
    * Four existing Project Server databases of the same version from the same installation.
    Combinations of blank, new, and existing databases are not allowed.

    Thanks

    Pedro


    Pedro Ferreira
    Monday, May 10, 2010 8:44 AM

Answers

  • You can relocate the RDB for an existing PWA site if you do the following:

    • Make sure there are no active jobs in the queue.
    • Stop the Windows Project Queue service for the PWA instance
    • Back up the RDB
    • Restore the RDB to the new SQL Instance
    • From Manage Service Applications in Central Admin, click on the Project Service, then Edit PWA for that instance, update the server name for the RDB and click OK
    • Once provisioning has finished, restart the Windows Project Queue Service for that PWA instance

    Treb Gatte, Program Manager

     

     


    Treb Gatte Program Manager Microsoft Project Server
    Sunday, May 16, 2010 11:34 PM
  • Pedro:

    Try creating a new instance of PWA attaching to the existing databases after reloacting the Reporting database.


    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG
    Tuesday, May 11, 2010 3:19 PM

All replies

  • Pedro:

    Try creating a new instance of PWA attaching to the existing databases after reloacting the Reporting database.


    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG
    Tuesday, May 11, 2010 3:19 PM
  • Hi Gary,

    I thought that by changing the reporting database settings, PS would create it if it didn't exist. I was wrong :)

    After reading your reply, I did a backup/restore of the reporting database to the new instance, changed the settings and it worked just fine.

    Thanks for your help.


    Pedro Ferreira
    Tuesday, May 11, 2010 6:18 PM
  • You can relocate the RDB for an existing PWA site if you do the following:

    • Make sure there are no active jobs in the queue.
    • Stop the Windows Project Queue service for the PWA instance
    • Back up the RDB
    • Restore the RDB to the new SQL Instance
    • From Manage Service Applications in Central Admin, click on the Project Service, then Edit PWA for that instance, update the server name for the RDB and click OK
    • Once provisioning has finished, restart the Windows Project Queue Service for that PWA instance

    Treb Gatte, Program Manager

     

     


    Treb Gatte Program Manager Microsoft Project Server
    Sunday, May 16, 2010 11:34 PM