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
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:
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:
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 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.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:
Execute the following two SQL queries:ALTER DATABASE
MODIFY FILE (NAME=" ", NEWNAME=" ") ALTER DATABASE
MODIFY FILE (NAME=" ", NEWNAME=" _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 = "
"
Set-SPPerformancePointServiceApplication -Identity "" -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.
