Mirroring the Planning Server

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.

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.

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

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

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'
GO
USE [PPSPlanningSystem]
Exec sp_changedbowner 'SI Account'
GO

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.

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]
      GO
      GRANT ALTER TRACE TO [DOMAIN\User1]
      GO

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.

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.

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

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).

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
   SET 
   --AssignmentFormsUrl = @NewAssignmentFormsUrl,
   --AssignmentMasterFormsUrl = @NewAssignmentMasterFormsUrl,
   FormTemplatesUrl = @NewFormTemplatesUrl,
   ReportUrl = @NewReportUrl
   WHERE BizApplicationLabel = @BizApplicationLabel
   

   Tip

   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.

   
   UPDATE
   SET AppNodeSQLStoreDBServer = @NewAppNodeSQLStoreDBServer,
   AppNodeSQLStoreDBName = @NewAppNodeSQLStoreDBName, -- can remain the same
   AppNodeOLAPStoreDBServer = @NewAppNodeOLAPStoreDBServer,
   AppNodeOLAPStoreDBName = @NewAppNodeOLAPStoreDBName -- can remain the same
   FROM 
   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
   

   Note

   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.

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.

   Note

   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.

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:

ALTER DATABASE [PPSApplicationDatabaseName] SET ENABLE_BROKER

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
Set XML = convert(XML, 'PASTE ENTIRE CHANGED XML TEXT HERE')

On both the Planning Web Service server and the Planning Process Service server, at the Windows command prompt, run IISRESET.

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.

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.