Getting rid of pesky GUID based SharePoint service databases
In some cases where SharePoint deployments that have been carried out by resorting to the “Wizard” you’ll most likely end up with service and content databases that are GUID based. Also no matter what method you use the PerformancePoint service application when initially provisioned will contain a GUID as there is no option to specify the databasename paramter via PowerShell. However you can get rid of these GUIDs and set the database names to be human readable. The correct ay to do this is by first talking with your DBA’s or if you have full access to your SQL deployment you’d be able to run the following SQL script(s) to rename the database correctly.
So the high level process as follows. In this example I am going to highlight the provisioning of the PerformancePoint service application and how to rename the DB and then reset the service application instance to use the renamed DB.
All PowerShell commands relating to PerformancePoint is outlined on TechNet here. http://technet.microsoft.com/en-us/library/ee906547.aspx
The command I have used in this example as part of my initial Service Application provisioning and starting the PerformancePoint service on the server.
Initially setup some variables
1: #PerformancePoint service application
2: $performancePointSAName = “KC PerformancePoint Service Application”
Provision the Service application
1: New-SPPerformancePointServiceApplication -Name $performancePointSAName -ApplicationPool $saAppPoolName > $null
2: New-SPPerformancePointServiceApplicationProxy -Default -Name “$performancePointSAName Proxy” -ServiceApplication $performancePointSAName > $null
This will create the PerformancePoint service application and the connection proxy and the infamous GUID database name. You can go to SQL server management studio and see the resulting database which of course has a GUID.
Also you’ll notice that the name of the DB takes the specified $performancePointSAName = “KC PerformancePoint Service Application” and appends the GUID as KC PerformancePoint Service Application_XXXXXXXXXXXXXXXXXXXXXX. So now how to rename the DB. Now you may say hey that’s easy I’ll just right click and rename in the SQL management studio right? well actually that only changes the visible name and if you look in the file system where the database (mdf and ldf) files are located they will still have the GUID.
Choose Rename and renamed to “SP_SERVICE_PERFORMANCEPOINT_KC”
Renamed DB.
As you can see the MDF and LDF files haven’t been renamed. So what’s the correct way to do it? Basically before you rename any database you should first detach the DB. Also you might have noticed although I provisioned my PerformancePoint Service Application I didn’t start the PerformancePoint service on the server. Go to Services on Server and you’ll see that it’s not Started yet.
Now let’s look at how to rename the DB the correct way by using a SQL script. Open a SQL query window in SQL management studio. Note down the names of the .mdf and .ldf file names of the PPS database and the location.
5: ALTER DATABASE [KC PerformancePoint Service Application_b7b635cd34374fd6984ceb5a2a83fe55] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
6: GO
7: ALTER DATABASE [KC PerformancePoint Service Application_b7b635cd34374fd6984ceb5a2a83fe55] MODIFY NAME = [SP_SERVICE_PERFORMANCEPOINT_KC]
8: GO
9: ALTER DATABASE [SP_SERVICE_PERFORMANCEPOINT_KC] MODIFY FILE (NAME=N’KC PerformancePoint Service Application_b7b635cd34374fd6984ceb5a2a83fe55’, NEWNAME=N’SP_SERVICE_PERFORMANCEPOINT_KC’, FILENAME=N’E:\SQLData\DATA\SP_SERVICE_PERFORMANCEPOINT_KC.mdf’)
10: GO
11: ALTER DATABASE [SP_SERVICE_PERFORMANCEPOINT_KC] MODIFY FILE (NAME=N’KC PerformancePoint Service Application_b7b635cd34374fd6984ceb5a2a83fe55_log’, NEWNAME=N’SP_SERVICE_PERFORMANCEPOINT_KC_log’, FILENAME=N’E:\SQLData\DATA\SP_SERVICE_PERFORMANCEPOINT_KC_log.ldf’)
12: GO
13: ALTER DATABASE [SP_SERVICE_PERFORMANCEPOINT_KC] SET OFFLINE
14: GO
15: EXEC xp_cmdshell ‘RENAME “E:\SQLData\DATA\KC PerformancePoint Service Application_b7b635cd34374fd6984ceb5a2a83fe55.mdf”, “SP_SERVICE_PERFORMANCEPOINT_KC.mdf”’
16: GO
17: EXEC xp_cmdshell ‘RENAME “E:\SQLData\DATA\KC PerformancePoint Service Application_b7b635cd34374fd6984ceb5a2a83fe55_log.ldf”, “SP_SERVICE_PERFORMANCEPOINT_KC_log.ldf”’
18: GO
19: ALTER DATABASE [SP_SERVICE_PERFORMANCEPOINT_KC] SET ONLINE
20: GO
21: ALTER DATABASE [SP_SERVICE_PERFORMANCEPOINT_KC] SET MULTI_USER WITH ROLLBACK IMMEDIATE
22: GO
Depending on the configuration of your SQL server you may not be able to run this command which uses the xp_cmdshell stored procedure and you may come across this error message. This is why you will first need to talk to your DBA and get them to help you here.
Server: Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.
1: — To allow advanced options to be changed.
2: EXEC sp_configure ‘show advanced options’, 1
3: GO
4: — To update the currently configured value for advanced options.
5: RECONFIGURE
6: GO
7: — To enable the feature.
8: EXEC sp_configure ‘xp_cmdshell’, 1
9: GO
10: — To update the currently configured value for this feature.
11: RECONFIGURE
12: GO
**
Basically you can enable this setting which is turned off by default. In any case make sure your DBA is involved as this is a highly privileged command and you may opt to disable it after renaming the DB.
Refer this MSDN article and guide for xp_cmdshell > http://msdn.microsoft.com/en-us/library/ms190693.aspx DISCLAIMER! You will need to test this before applying and it is entirely up to you to use this method.
After this you’ll see that the DB has been renamed including the files on the file system.
Now you’ll need to run the following command to set the service application to use this renamed database. Open a SharePoint PowerShell session and run:
1: $performancePointSAName = “KC PerformancePoint Service Application”
2: $performancePointDBName=“SP_SERVICE_PERFORMANCEPOINT_KC”
3:
4: Set-SPPerformancePointServiceApplication -Identity $performancePointSAName -SettingsDatabase $performancePointDBName
5: Get-SPServiceInstance | where-object {$_.TypeName -eq “PerformancePoint Service”} | Start-SPServiceInstance > $null
You should see the following message in PowerShell.
To check that all settings are working navigate to Manage Service Applications and access the PerformancePoint service application and click “Manage” on the Ribbon.
You should be able to navigate to the PPS management page and set various settings. To verify the Service Application configuration you can also run : Get-SPPerformancePointServiceApplication > http://technet.microsoft.com/en-us/library/ff608054.aspx
For extensive planning information for deployment and using the capabilities here are some important resources.
A good reference to the SP2010 PPS2010 capabilities > http://blogs.msdn.com/b/performancepoint/archive/2009/10/20/what-is-performancepoint-services-for-sharepoint-2010.aspx
Building a BI solution : http://technet.microsoft.com/en-us/magazine/2009.08.introtobi.aspx
PowerPivot : http://blogs.msdn.com/b/excel/archive/2009/10/29/powerpivot-for-sharepoint-2010-the-business-user-s-perspective.aspx
Architectural Overview of PPS2010 > http://technet.microsoft.com/en-us/library/ee890835.aspx
Planning Overview for PPS2010 > http://technet.microsoft.com/en-us/library/ee681486.aspx
PerformancePoint dashboard designer > http://office.microsoft.com/en-us/dashboard-designer-help/dashboard-designer-help-and-how-to-FX101834978.aspx