Deploying Usage and Health Data Collection Service Issue

I ran into this issue this week that stumped me for a little while until I finally got the closest I could to a final resolution. It wasn’t the resolution I preferred, but does the trick and taught me a little lesson in some SharePoint 2010 oddities.

Scenario: I needed to deploy SharePoint 2010 Foundation to a shared SQL 2008 R2 instance in which SysAdmin (SA) and DBCreator would not be given even temporarily.

Issue: The issue is you cannot provision the Usage and Health Data Collection Service directly to a pre-made database. You will get an error message similar to the opening screenshot above stating, “The specified database already exists.” It seems, SharePoint 2010 Foundation wants to have the service deployed and in the configuration database before you can then move the service to a different database or SQL server.

This to say the least, was quite annoying, but luckily our SQL team has a solution.

Solution: We have an Installation SQL instance in which DBCreator can be given to my setup user, so here is the flow I used.

NOTE: You can do all of this via Central Administration as well as PowerShell, but it’s more fun to show the PowerShell method

1. Install Service to Installation SQL Instance (PowerShell)

New-SPUsageApplication -Name “Usage and Health Data Collection Service” -DatabaseServer “<ServerName\InstanceName>” -DatabaseName “WSS_Logging_TMP”

2. DBA Creates Empty Database on DEV SQL Instance (SQL)


3. Point Service to Newly Created Empty Database (PowerShell)

Set-SPUsageApplication -Identity “Usage and Health Data Collection Service” -DatabaseName “WSS_Logging” -DatabaseServer “<ServerName\InstanceName>”

4. DBA Drops Temporary Database on Installer SQL Instance (SQL)


5. Start Proxy Service (PowerShell)

Get-SPServiceApplicationProxy (Copy Usage and Health GUID (see above)
$up = Get-SPServiceApplicationProxy | where {$_.ID –eq “GUID”}

Verify the Proxy Service is started by going to:

Central Administration > Application Management > Manage service applications

This should get you going with the Usage and Health Data Collection if your environment has more strict rules on Database deployment.

  • Click Here for more information regarding renaming SharePoint 2010 databases.
  • Click Here for deploying to pre-made databases.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s