I had a little head scratching today changing the database used by SharePoint Foundation for logging of usage data. This is because, at the time of writing, the Microsoft Article (scroll to the bottom) doesn't mention you need to supply the identity of the Usage Application you are changing.
If you want to log to a non–default database then simply open the SharePoint 2010 Management Shell (Start > All Programs > Microsoft SharePoint 2010 Products > SharePoint 2010 Management Shell) and run:
$spUsageAppName = (Get-SPUsageApplication).DisplayName
Set-SPUsageApplication -Identity $spUsageAppName -DatabaseServer yourServer -DatabaseName newDBName -Verbose
All this does is retrieves and stores the DisplayName property for the Usage Application in a variable (first line) then configures the Usage Application with the new database name. Adding Verbose makes my output (shown below) a little more helpful and interesting!
Output of Set-SPUsageApplication
A few things to note:
- These instructions were tested on SharePoint Foundation RTM, but should also work on SharePoint Server 2010.
- This was on a new install of SharePoint Foundation so part of my post–installation configuration. If your environment is already up and running, it may not be so simple.
- The account you run this as must be a local admin on the SharePoint server and also be a member of the fixed server roles dbcreator and securityadmin on your SQL server (the install account is a good choice).
- I actually used a SQL Alias instead of server name (see screenshot) and it works fine. This isn't a production system so I am not giving anything confidential away
- The old database is not deleted; if you want rid of it then you will have to do it yourself!
