at my current customer, databases are stored in a SQL AlwaysOn group. An alias was made to connect to the listener and all was well. But one thing they did not take into account is the fact that mirroring / always on isn't supported for all databases. I have three illegal ones:

  • Usage & Health data
  • Profile Sync
  • Report Staging

So I was thinking of doing the following: - Removing those databases from the AlwaysOn group, and hosting them on one of the two instances only (since these don't impact high availability anyway) - Setting up a new alias point to the single static instance - Changing the server references in SharePoint

It's the third part which worries me. Moving database servers is always an issue and thus I'm not sure if I can adjust the database server names for these services. The options in Central Administration are greyed out, but perhaps Powershell can help me out here? I can also delete these service applications and just recreate them, but I'd rather not lose configuration and such.

So the question is: has anyone ever done this? If so: what method did you use?

Update: Using Set-SPUsageApplication and Set-SPWebAnalyticsServiceApplication it's possible to change the database servers for two of three. Now Profile Sync is the only one left.

1 Answer 1


Found it! This script does it all:

$alias = "sql-alias"

$uh_name = "Usage and Health Data Collection"
$uh_database = "SP2010_UsageAndHealthDB"
$wa_name = "Web Analytics Service Application"
$wa_database = "SP2010_WebAnalyticsStagingDB"
$up_name = "User Profile Service Application"
$up_database = "SP2010_UPSSyncDB"

Write-Host "Changing the database server for $uh_name"
$uh_app = Get-SPServiceApplication |? { $_.Name -eq $uh_name }
Set-SPUsageApplication -Identity $uh_app -DatabaseServer $alias -DatabaseName $uh_database

Write-Host "Changing the database server for $wa_name"
$wa_app = Get-SPServiceApplication |? { $_.Name -eq $wa_name }
$stagingDbs = "<StagingDatabases><StagingDatabase ServerName='$alias' DatabaseName='$wa_database'/></StagingDatabases>"
Set-SPWebAnalyticsServiceApplication -Identity $wa_app -ListOfStagingDatabases $stagingDbs

Write-Host "Changing the database server for $up_name"
$db = Get-SPDatabase |? { $_.Name -eq $up_database }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.