SP2010: Whoops! I mirrored all of my databases…

Technical / December 20, 2012 / Jasper Siegmund / 0

So you’re building a SharePoint farm which has to have a high percentage of availability. And to get that, you decide to make your databases highly available by the techniques offered by SQL Server. Think mirroring, or AlwaysOn if you’re using SQL2012. Which technique you’re using doesn’t really matter; you have to realise that this is not supported for all databases!

The good news; the databases which don’t support mirroring aren’t vital to keeping your sites up. It’s these three:

• Usage and Health Data Collection service application Logging database (typically called “WSS_Logging”)
• User Profile service Synchronization database (Typically known as “Sync DB”)
• Web Analytics Staging database (typically called “WebAnalyticsServiceApplication_StagingDB”)

Also note that search databases don’t have to be mirrored, because search has it’s own high availability mechanism. It’s doesn’t matter though, when you do have then mirrored. It’s just not nessessary.

Ok. But you didn’t consider this when you were building your farm. Eventually, you’ll run into problems; most likely when applying updates to your farm. These will fail with the message that mirroring is not supported for your databases. So then what?

Well, just use this Powershell script below and you’ll be able to change the alias for the databases which don’t support mirroring. Make sure that you first create the alias on all of your farm servers!

$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 = ""
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)

Source for non supported databases: http://mmman.itgroove.net/2012/03/some-databases-are-too-ugly-for-the-mirror/

Update 24-5-2013: it seems the above script leaves you with a database reference for the WebAnalyticsStagingDB. This causes exceptions with health monitoring rules which check these references for good database health. This can be easily solved by running Get-SPDatabase, selecting the correct one and issuing a Delete() command on it. This will remove the reference, making sure the health rules won’t check it again. This only happens for that single database, it seems the other ones are being cleaned up properly.

#powershell, #SharePoint, #SP2010

Jasper Siegmund