Moving databases which don't support mirroring

Question

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.

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 }
$db.ChangeDatabaseInstance($alias)