Renaming the PerformancePoint Service Application database in SharePoint 2010

When creating the PerformancePoint Service Application, there is no way to control the name of the database that is created, not even when using PowerShell to create the Service Application. The database that gets created is in the form

<Service Application Name>_GUID

which for some reason a good many DBAs are not too keen on!

The database can however be renamed by following these steps:

  • Stop the PerformancePoint service on all SharePoint servers in the farm that are running the service using the ‘services on servers’ area of Central Administration:
    Stopping the PerformancePoint Service
  • Rename the database and log file – there are two ways of completing this; I prefer the second option of the two outlined below as it completely renames all of the references to the database, but it is a more involved process:
    1. Open SQL Server Management Studio on the SQL Server for the farm.
      Select the PerformancePoint Service Application database and then click again to allow renaming:
      Rename PerformancePoint DB in GUI
      Rename the database to match the naming convention you wish to use for farm databases. Note that this only renames the database friendly name as shown in SQL Server Management Studio and not the file names or the logical database and log file names.
    2. Alternatively:
      Open SQL Server Management Studio on the SQL Server for the farm.
      If you wish to, you can change the recovery mode of the PerformancePoint database to ‘simple’; this saves having to backup and restore a log file as well as the database file.
      Backup the PerformancePoint database created during the Service Application creation process.
      Restore the PerformancePoint database from the backup completed to a new database name which matches the naming convention you wish to use for farm databases. Note that the default naming convention for the log files on restore appends ‘_1’ to the database name to form the log file name; you may wish to change this to ‘_log’ to match the other log files that the database server hosts. The backup and restore will change the filenames used for the databases and the display name shown in SQL Server Management Studio, but not the logical database names. To change the logical database names, first find the logical names of the database and log for the database you wish to change; you can find this information either by taking note of the original database name when it was created, or from the ‘files’ section of the database properties screen within SQL Server Management Studio:
      Database Logical Names
      Execute the following two SQL queries:

      ALTER DATABASE <new PerformancePoint database name> MODIFY FILE (NAME="<original logical database name>", NEWNAME="<new PerformancePoint database name>")

      ALTER DATABASE <new PerformancePoint database name> MODIFY FILE (NAME="<original logical log file name>", NEWNAME="<new PerformancePoint database name>_log")

      If you changed the database recovery mode to ‘simple’, change it back to ‘full’.

  • On one of the SharePoint servers in the farm, open an instance of the SharePoint 2010 Management Shell, ensuring that it is run as administrator and issue the following PowerShell Commands:

    $newdatabasename = "<new PerformancePoint database name>"
    Set-SPPerformancePointServiceApplication -Identity "<name of the PerformancePoint Service Application>" -SettingsDatabase $newdatabasename

  • Restart the PerformancePoint service on the servers in the farm it was running on originally.
  • Delete the original PerformancePoint database that was created during the Service Application creation from SQL Server Management Studio.

2 Replies to “Renaming the PerformancePoint Service Application database in SharePoint 2010”

  1. Following the release of SP1 for SharePoint 2010, this procedure is no longer required as the PowerShell cmdlet used to create the PerformancePoint service application now has additional paramters to allow you to name the database during creation.

Leave a Reply to Andy Cancel reply

Your email address will not be published. Required fields are marked *