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)

CREATE DATABASE “WSS_Logging” COLLATE LATIN1_General_CI_AS_KS_WS

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)

DROP DATABASE “WSS_Logging_TMP”

5. Start Proxy Service (PowerShell)

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

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.
About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s