One of the great new improvements in SharePoint 2010 is native support for SQL mirroring and automatic failover. In SharePoint 2007 you had to use some complicated SQL aliases to provide failover support. SharePoint 2010 has built in support for database mirrors, and allows you to define a failover SQL server for any databases you have mirrored. You can mirror one database, you can mirror several, it's all up to you. You can do this with content databases or service application databases. To take advantage of this simply add the instance name of the SQL server where the mirror of your database is in the settings for the database, like below:
You will have to configure the database mirroring independent of SharePoint. SharePoint does not configure the mirroring in SQL for you. SharePoint will however verify the instance you specify is valid. With your new found knowledge you'll probably start running through your SharePoint farm mirroring all your databases. You'll find out pretty quickly that approach has two problems. First, all that clicking is tedious. Clicking is for losers. Second, no matter how of that cruddy clicking you do there's no place to mirror the granddaddy of all SharePoint databases, the Config DB. Fortunately the solution to both of those issues is our old friend PowerShell. We can use PowerShell to loop through our databases and configure the failover server for them all at once. The Config DB is included in the collection of databases, so it gets a failover instance as well. We can use the PowerShell cmd Get-SPDatabase to retrieve the databases. Its output looks like this:
The red highlighted database is our configuration database. We can use the FailoverServer property of a database to see if it's mirrored or not. To set a mirrored instance for a database use the AddFailoverServiceInstance method. You'll also need to use the Update method for the setting to take effect. To verify our Config DB is not already mirrored, and then set a mirror for it on the SQL server SQL02 use the following commands:
Here's the code in text form:
PS C:\> (Get-SPDatabase 74e036c2-13d3-4f6e-ac20-f5f189c22967).AddFailoverServiceInstance("SQL02")
PS C:\> (Get-SPDatabase 74e036c2-13d3-4f6e-ac20-f5f189c22967).Update()
PS C:\> Get-SPDatabase 74e036c2-13d3-4f6e-ac20-f5f189c22967 | select name, failoverserver
That gets the database object using the GUID of my Config DB from the preceding image, sets its FailoverServiceInstance value to SQL02, and then updates it. If you do this with a Content DB or Service App DB you can double-check the setting worked by looking in Central Admin.
This approach works, but it's clunky. Fortunately we can use PowerShell to walk through our databases, look for the Config DB and then set its FailoverServiceInstance to SQL02. Since we have to do two operations on the database, I had to break it up into a couple of lines. The following lines should work on any SharePoint Farm and set the Config DB's Failover Server to SQL02.
PS C:\> $db = Get-SPDatabase | Where-Object { $_.TypeName -eq "Configuration Database" }
PS C:\> $db.AddFailoverServiceInstance("sql02")
PS C:\> $db.update
Here's what it looks like when it runs:
Be very, very careful with your Where-Object statement. When you do programming or scripting you'll find that the equal sign (=) has two different meanings. In some cases it is used for evaluation, where "a = b" means "Does A have the same value as B?" In other cases it's used for assignment so "a = b" means "assign a the value of b." PowerShell uses the equal sign for assignment, like the latter example. In PowerShell if you want to evaluate to values, use –eq instead like I did above. If you accidentally use the equal sign instead of –eq you're not asking "Is the current object's TypeName property the same as "Configuration Database" like you want to. Instead you're telling PowerShell to assign the TypeName propery to "Configuration Database", which won't work since the TypeName property is read-only, and you'll get an error. However, if you're testing your PowerShell logic and you're using a different property, maybe the Name property, you'll have a different experience since that Name property is not read-only. If you use the equal sign accidentally when evaluating you'll overwrite the Name property instead of evaluating it. Compare these two statements:
Get-SPDatabase | Where-Object {$_.Name -eq "SharePoint_Config"}
Get-SPDatabase | Where-Object {$_.Name = "SharePoint_Config"}
The first one evaluates each database's name to see if it is equal to "SharePoint_Config." The second walks through each database and set its name to "SharePoint_Config." Yes, it renames every database in your farm to "SharePoint_Config." Go ahead, ask me how I know. Another word of advice, take lots of snapshots when you're doing this. J Renaming all of your databases doesn't seem to break SharePoint, or at least it didn't break my VM. It did, however, make it very difficult to tell them apart:
Enough of my cautionary tale. Our TypeName loop will work with any type of database in your farm. Here are all of the database types that exist on my VM:
You could loop through all of your databases, or just specific types like "Configuration Database" or "Content Database."
Hopefully this blog accomplished three things. It taught you about Database mirroring. It taught you how to mirror databases, especially the Config DB with PowerShell. And it taught you that I'm a bonehead.