Update: This script is now posted on the TechNet Script Repository. This repository should prove an invaluable resource in the near future.

http://gallery.technet.microsoft.com/ScriptCenter/en-us/4494e092-8de1-4c68-ae7b-362964b88d00

There’s a little article on TechNet that walks you through many of the configuration considerations for database mirroring on SharePoint 2010. It shows a diagram where not only is each content DB mirrored but also the configuration and Central Administration DB’s as well.

In fact, I think that is a near exact replica of a diagram I created for MSFT. Anyways, there’s a little note at the bottom of the article that particularly troubling for me:

The article then goes on to provide sample powershell to set the failover database, however, it assumes you know the name of each database and want to type that name plus the corresponding powershell for every database in your farm. Considering that a fully configured SharePoint 2010 Server Farm will have a minimum of 18 databases before content, the prospect of manually typing each command is ridiculous and defies the very point of powershell. Instead, I built a script for my demos that will set the failover partner on every database in the farm and all you have to do is run the script and enter the name of the mirror SQL instance. It goes something like this:

##################################################################
#
# Set the failover partner instance on every database in the farm
#
####### Instructions
#  Run this script in the SharePoint Management Shell
# When Prompted, enter the name of the SQL instance that will act
# as the mirror partner for each database
#
####### Known Issues
#  Script will fail if mirror SQL instance is offline or otherwise
# unavailable. 
#
##################################################################
Param([string]$FailoverInstance = $(Read-Host "Enter the Mirror (Partner) SQL Instance(like server\instance)"))
get-spserviceinstance -all | foreach-object {
	if ($_.typeName -eq "Microsoft SharePoint Foundation Database") {
		foreach ($Database in $_.Databases){
			
			write-host "Updating FailoverPartner on" $Database.Name "to" $FailoverInstance
			$Database.AddFailoverServiceInstance($FailoverInstance)
			$Database.Update()
			write-host "Successfully Updated Failover Partner on" $Database.Name "to" $FailoverInstance
		}
		
	}
}

Now this script makes a few assumptions that you should be aware of:

1. It assumes a one to one mirroring relationship. That is, it expects that all your databases reside on a single principal SQL instance and that you will be mirroring those databases to just one other SQL instance.

2. It assumes that you have rights to make these changes

3. It assumes that the mirror SQL instance is available. The script will fail if not.

4. It assumes that you have or will configure mirroring on the backend before a failover is required.