Mirroring the Planning Server

Planning Server system mirroring refers to copying one Planning Server system installation to another. The data inside the Planning Server system is an identical copy.

However, the Planning Server system database server name does change, along with other necessary configuration-related data changes. These changes must be made for the newly mirrored Planning Server system to be up and running.

You can achieve the Planning Server system mirroring goal by backing up the source Planning Server databases (including SharePoint databases if Windows SharePoint Services is used) and restoring those source databases to a different database server as the target system. After backup and restore, modify the necessary configuration data to bring up the new Planning Server system.

Mirroring should be compared to the Migration of a Planning Server. Mirroring, described in detail in this document, will copy an existing Planning System from one environment to another. That means the Mirroring process will move all applications, model sites, models, dimensions, users, security, cycles, assignments, and reports. It will also fully replace the destination environment content, if any exists. Alternatively, Migration moves a single Planning Application from one Planning System to another. The destination Planning System must be configured and ready to create and accept new applications. Using Migration, the application being moved will be added to that destination system. Migration does not move reports, workflow, assignments, cycles, jobs, users, or security roles. The following scenario and instructions are intended to describe mirroring a Planning Server from one environment to a completely new one. A typical situation would be moving from a Development to a Test environment. Each environment should have its own physical hardware where no computers from either environment are shared.


The terms Windows SharePoint Services and SharePoint Services are used collectively in PerformancePoint Server documentation to refer to Office SharePoint Server 2007 and Windows SharePoint Services 3.0.

Mirror the Planning Server

  1. Back up all databases in the source Planning Server system. This includes PPSPlanningService, PPSPlanningSystem, and any application and staging databases. For more information, see Backing up and restoring Planning Server databases.

  2. Copy any forms and reports you want to move to the target system during the mirroring process. Default locations for Forms, and Reports storage are set up at the time an Application is created in the Planning Administration Console.

  3. Restore the source PPSPlanning Service and the PPSPlanningSystem databases to the target server running Planning Server and Microsoft SQL Server 2005.

  4. Paste the forms and reports to their default locations on the target system.

  5. Change the database owner (DBO) on the restored databases to the Service Identity account (SI) of the Planning Process Service.

    USE [PPSPlanningService]
    Exec sp_changedbowner 'SI Account'
    USE [PPSPlanningSystem]
    Exec sp_changedbowner 'SI Account'
  6. Prepare the new computers to be used as the target Planning Server system. Run Planning Server Setup and Configuration Manager to prepare the new computers. For information about preparing computers for the Planning Server system, see the Deployment Guide for Planning Server.


    When you prepare the Planning Server system, make sure that you select the Distributed Configuration option on the Installation Options page of the Planning Server Configuration Manager wizard, and clear the Planning System Database option. On the System Database page of the wizard, enter the name of the target computer, the one to which you restored your existing Planning System Database and Planning Service Database.

  7. Grant dbcreator permissions to the SI account.

    You only need to do this if you want the AutoCreate feature of the application database to work in Planning Administration Console and Planning Business Modeler. If your database administrator is going to manually create the application database for you, you do not need to perform this step.

     --Grant dbcreator permissions to SI account
          USE [master]
  8. In the Analysis Services portion of the server, right-click the Analysis Services server connection, click Properties, select Security, then add Service Identity Account to that role.

    For a multi-computer deployment scenario, you might need to configure the security settings among the computers that are running SQL Server, Analysis Services, and Windows SharePoint Services. For information about configuring security settings in multi-computer deployment scenarios, see Deployment Guide for Planning Server.

  9. After the target system is set up, continue restoring all application and staging databases from the source to the target. If you are using Windows SharePoint Services, also restore the Windows SharePoint Services databases.

  10. Change the database owner on the restored application and staging databases. USE [PPS_APP] Exec sp_changedbowner 'SI Account' GO USE [PPS_STG] Exec sp_changedbowner 'SI Account' GO

  11. Fix configuration-related data, such as database server names inside the databases and configuration files.

    The following instructions, which are based on a five-server setup, provide an example of fixing configuration-related data so that mirroring will work (after restore).

    Server 1: SQL Server database

    Server 2: OLAP server

    Server 3: Windows SharePoint Services server

    Server 4: Planning Web Service server, optional Planning Administration Console server

    Server 5: Planning Process Service server

    The following configuration is here for illustration purposes:

    • The Planning System Database and the Planning Service Databases are on the same database server.

    • Planning Server application databases are on different database servers (per application).

    • Planning Server OLAP databases are on different database servers (can be one per model site or one per application).


    The [ViewWorkFlowDBServer] column in the BizSystem table is no longer used. Please ignore this column’s value in BizSystem table.

    1. Manually update the BizSystem table in the PPSPlanningSystem database to reflect SQL instance and database name changes.

      UPDATE BizSystem
      SET ReferenceDBServerName = @NewAppDBServer,
      ReferenceDBName = @NewAppDBName, -- can remain the same
      StagingDBServerName = @NewStagingDBServer,
      StagingDBName = @NewStagingDBName, -- can remain the same
      WHERE BizApplicationLabel = @BizApplicationLabel
    2. In the same table, change the default storage locations for forms and reports.

      UPDATE BizSystem
      --AssignmentFormsUrl = @NewAssignmentFormsUrl,
      --AssignmentMasterFormsUrl = @NewAssignmentMasterFormsUrl,
      FormTemplatesUrl = @NewFormTemplatesUrl,
      ReportUrl = @NewReportUrl
      WHERE BizApplicationLabel = @BizApplicationLabel


      The AssignmentsFormsURL and the AssignmentMasterFormsURL are commented out (--) because these two fields are currently unused.

    3. Manually update the BizAppNodes table in application database for OLAP Server name.

      UPDATE BizAppNodes
      SET OLAPServerName = @NewOLAPServerName
      WHERE BizAppNodeLabel = @BizAppNodeLabel
    4. Manually update the DeployInfo table in the application database for SQL instance and database names.

      SET AppNodeSQLStoreDBServer = @NewAppNodeSQLStoreDBServer,
      AppNodeSQLStoreDBName = @NewAppNodeSQLStoreDBName, -- can remain the same
      AppNodeOLAPStoreDBServer = @NewAppNodeOLAPStoreDBServer,
      AppNodeOLAPStoreDBName = @NewAppNodeOLAPStoreDBName -- can remain the same
      dbo.DeployInfo d inner join
      dbo.BizAppNodes n on
      BizAppNodeID = @BizAppNodeID
      WHERE n.BizAppNodeLabel = @BizApplicationLabel
    5. If you want to move forms , the location of the *.xlsx file needs to be updated in the Workbooks table of the Application database. Manually update the StorageUrl column in the Workbooks table by using the replace function. Replace is needed because the StorageUrl stores the path to the form: \\computername\sharedfolder\ and the form name itself (form.xlsx). The form name must remain the same but the path needs to be updated.

      UPDATE dbo.Workbooks
      SET StorageUrl = REPLACE(StorageUrl, @OldStorageUrlPath, @NewStorageUrlPath
      WHERE [StoreageUrl] is Not NULL


      The Workbooks table contains all Storage URLs that are used by the AssignmentDefinitions table (joined by WorkbookID), and BizForms (joined by FormID, which is WorkbookID).

      The source system table might have many different StorageUrl values for various usages in the two tables mentioned in the previous note. But for the target system, you can simply place all Storage URLs into one single URL location to start with. The file copy task can be tough, because you need to copy these files from all URL locations into one location.

    6. Rename the Planning Process Service server.

      In the application database, update the AsyncWorkItems table for the MachineName column with the new Planning Process Service server name.

      UPDATE AsyncWorkItems
      SET MachineName = @NewAsyncServerName
      WHERE MachineName IS NOT NULL
    7. Update the OLAP database connection strings.

    8. Expand the OLAP database hierarchy in SQL Server Management Studio:

      • Expand each database name, and then expand the Data Sources folder.

      • Right-click each of the subnodes, select Properties, click Connection String, and then click the ellipsis for the Connection String box.

      • Enter the new SQL Server name in the Server Name field.

      • Repeat for all OLAP databases. This can also be accomplished if the OLAP databases are not moved and a Deploy Model Site is performed after the mirroring process is complete.

  12. On the Planning Web Service and Planning Process Service servers, make the following changes in the default PerformancePoint.Config file, located in the C:\Program Files\Microsoft Office PerformancePoint ServerTemp\3.0\Config folder.

    1. Change the database server entry to the new name for the computer that is running SQL Server.

    2. Change the OLAP server entry to the new name for the computer that is running OLAP server.


      You only need to do this in a single server setup, because the entry was set by Setup in this scenario. For a multi-server deployment, this field is empty; you do not need to modify it.

  13. On the computer that is running SQL Server, perform the following SQL Server query to manually enable SQL Server Service Broker on all Planning Server application databases:



    If Planning Process Service is started, stop the service so the Service Broker can be enabled. After it is enabled, restart the service. Occasionally, the previous enable broker SQL statement seems to be locked and the statement never finishes. To fix it, restart the SQL Server service and retry the SQL statement.

  14. Update the XML blob by running the following script:

    Use [PPSPlanningSystem]
    Select CAST(CAST(XML as VARBINARY(MAX)) as XML) AS Configuration_XML
    From dbo.Configuration
       Copy all text to notepad.
       Make changes to the following highlighted parameters as needed:
          ...audit.log" writerMachineName="machinename" />
          ...defaultOlapServer="machinename" ...
          ...Trace Log\server.log" writerMachineName="machinename" />
       …Copy ALL the updated xml text.
       Run the following update statement with your changed xml text.
    Use [PPSPlanningSystem]
    Update dbo.Configuration
  15. On both the Planning Web Service server and the Planning Process Service server, at the Windows command prompt, run IISRESET.

  16. On Server 4 (Planning Web Service server) and Server 5 (Planning Process Service server), restart the Planning Process Service. This ensures that all changes to the configuration files are picked up by the servers.

  17. Reconnect Windows SharePoint Services (optional step, needed only if you have a Windows SharePoint Services server).

    After restoration is complete, you must reconnect to the Planning Server system topology. This process involves pointing the computer that is running Windows SharePoint Services back to its configuration and content databases. To point the Windows SharePoint Services server back to its database, take the following steps:

    1. On Server 3 (Windows SharePoint Services server), open Internet Information Services.

    2. Expand the list and open the Web Sites folder.

    3. Right-click SharePoint Central Administration and select Browse.

    4. Click Set configuration database server.

    5. Change the server name to the new SQL Server name.

    6. Select Connect to existing configuration database.

    7. Click OK.

    8. Select Set default content database server and verify that the new name is correct. If it is not, update the name to the new database server name, and then click OK.

If reconnection fails

The following procedure is only required if the preceding procedure is unsuccessful in reconnecting to the Windows SharePoint Services server. If the Windows SharePoint Services server cannot be reconnected, the only remaining option is to remove and reinstall it from its installation point.

Remove and reinstall Windows SharePoint Service

  1. On Server 3 (Windows SharePoint Services server), uninstall Windows SharePoint Services by using Add or Remove Programs.

  2. On Server 4 (Planning Web Service server), open Internet Information Services Manager and delete the SharePoint Web site and the StsAdminAppPool.

  3. Install Windows SharePoint Services on the server. For more details see Recover Windows SharePoint Services.

  4. Rename the Windows SharePoint Services server (optional).


    This step is needed only if you have a server running Windows SharePoint Services.

  5. Manually copy all files from the source system URLs to the new URL locations.

  6. Rename the Planning Web Service server.

    All users who connect to the Planning Web Service server will need to be advised of the new name so they can connect to the server.

See Also