Naming Your PerformancePoint 2010 Database

In my blog post about SP1 features for PerformancePoint, I mentioned some new capabilities around the PerformancePoint (PPS) database side of things. For instance, you can now

• assign a failover server for the PPS database,
• assign a custom database name at provisioning time,
• allow administrators to use SQL authentication (rather than just Windows authentication) when connecting from the PPS service, and
• attach a new PPS service to an empty database, rather than having SharePoint create it during provisioning.

We weren’t able to fit either of these items into the product for RTM, but they were highly sought-after by many customers. Product features aren’t typically included with service packs in PerformancePoint, but we made an exception for this capability.

I know that it seems a little counter-intuitive to need a failover server for PPS, but consider the user experience if SharePoint’s server were to go down, while its content database went to a failover server, but PPS’s didn’t. The PPS database holds temporary objects, user-persisted filter values, and scorecard annotations and comments. If the PPS service can’t get to these things when it needs to, users will see a bunch of errors and essentially won’t be able to continue viewing or creating dashboards. Now, imagine that this happens in a meeting with executives – because that’s always where it happens, right? For setting up mirroring and failover for SQL, refer to this MSDN article. It does a great job discussing how to configure high-available mirroring within a SharePoint farm.

In addition to the failover feature itself, we also made some changes to how farm admins can set up the PPS database. The place to enable failover is also the place to assign a custom database name for the PPS database. This feature is important because many companies have database name policies, and the default PPS database name isn’t the friendliest one. The one on my test machine, for example, is called PerformancePoint Service Application_8969f6135b434215b8e6a7a9e9dbee09 (which is really just the name of my PPS service application followed by a unique identifier). Now, the farm admin can name it whatever company naming conventions dictate.

The other piece of this feature that helps manage the PPS database is this: PPS databases no longer need to be created when the service application is provisioned. Unless you work in an environment that clearly distinguishes DBAs from SharePoint farm admins, it’s easy to forget that many companies don’t allow their farm admins the required permissions to create databases. So, in SP1, a DBA can create an empty database (properly named, of course) and have the SharePoint farm admin use it when creating the PPS service application. In this case, the farm admin only needs the ability to create tables and not to create the database itself. Moreover, this can be done with either Windows or SQL authentication on the database.

Naturally, this feature would not be complete without the ability to accomplish these tasks (and more) using cmdlets. The actual cmdlet nouns aren’t new, but a few of the arguments are. The following sections show the syntax for a few common tasks around managing the database for a new or existing PPS service application (also demonstrating SQL authentication):

New DB with New Service Application

Creates a new service application and allows admins to create a named database.

New-SPPerformancePointServiceApplication [-Name <string>] [-ApplicationPool <spiiswebserviceapplicationpool>] [-DatabaseServer <string>] [–DatabaseName <string>] [-DatabaseSQLAuthenticationCredential <pscredential>] [-DatabaseFailoverServer <string>]

Existing DB with New Service Application

Creates a new service application and allows admins to attach to a supplied database.

New-SPPerformancePointServiceApplication [-Name <string>] [-ApplicationPool <SPIisWebServiceApplicationPool>] [-DatabaseServer <servername[\instancename]>] [–DatabaseName <string>] [-DatabaseSQLAuthenticationCredential <PSCredential>] [-DatabaseFailoverServer <servername[\instancename]>]

Attaching to an Empty Database (Set-)

Edits an existing service application and allows admins to attach to a supplied database.

The Set- cmdlet works largely the same as the New- cmedlets. But in this case, we would be editing an existing service application. The following cmdlet shows the case where a DBA provides the farm admin with an existing database and the admin wants to use that database for an existing PPS service application:

Set-SPPerformancePointServiceApplication –Identity 951424d0-b607-41be-87v7-7bf99488b01d [–DatabaseServer <servername[\instancename]>] [–DatabaseName <string>] [-DatabaseSQLAuthenticationCredential] [–DatabaseFailoverServer <string>]

Note: The GUID identity in the above example is just that: an example. You will need to use your service application’s actual GUID.

That’s essentially it. We are really pleased that we could provide both the UI and scriptable capabilities for this feature. For the admins who follow, and take advantage of, upgrades to PPS, this should be a welcome one indeed.

Kevin Donovan
Program Manager, PerformancePoint Services