Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's SharePoint Admin Blog > Posts > Setting a SharePoint 2010 Config DB failover server with PowerShell
May 15
Setting a SharePoint 2010 Config DB failover server with PowerShell

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.

tk

Comments

Haha

Todd the most important thing to note here is that even SharePoint pro's make mistakes something thats not admitted as often as it should. Being self taught I find your honesty refreshing you could have just pretended it didnt happen but then who learns that lesson right?
Good job.
@mattmoo2
 on 5/15/2010 2:05 PM

great post

will it be a auto failover, or it depends or sql mirror setup?
 on 5/16/2010 6:37 PM

Re: great post

Thanks.

SharePoint will automatically fail over if a failover instance is defined. I believe it will do so if the primary instance is unavailable for 60 seconds, but I can't find anything to back that up.

I do not believe it queries the database directly to see if it is the principal or the mirror.

tk
Todd O. KlindtNo presence information on 5/16/2010 6:50 PM

Problems with script

Hi,
I don´t know if it´s related to my language configuration (spanish), but when I run the script it doesn´t make any change because the condition Where-Object $_.TypeName -eq "Configuration Database" returns false.
I´ve tried the following script:

Param([string]$FailoverInstance = $(Read-Host "Enter the Mirror (Partner) SQL Instance(like server\instance)"))
get-spserviceinstance -all | foreach-object {
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}
                                                }
And it works although it returns an error at the end like this:

No se puede llamar a un método en una expresión con valor NULL.
En C:\Users\mossadmin\Documents\mirror2.ps1: 19 Carácter: 40
+             $Database.AddFailoverServiceInstance <<<< ($FailoverInstance)
    + CategoryInfo          : InvalidOperation: (AddFailoverServiceInstance:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

My question is:
Is there any problem with omitting the condition? Is relevant the error?

Thank you!!!
 on 5/20/2010 4:14 AM

I have mirrored my first contentdb

Thanks for your helpful article. Is there a recommended way of testing the failover server?

 on 5/20/2010 8:35 AM

Re: I have mirrored my first contentdb

When I was doing my testing on this I had to use aliases to test the failover. I would have my database going to an alias instead of the actual SQL instance. Then I would remove the alias to simulate the SQL server going away. I wasn't testing it with the Config DB though, so it was very easy to test without rebuilding my SharePoint farm.

I'm not sure how else to test it without setting up another SQL instance and stopping your primary one, or at least taking the Config DB offline.

tk
Todd O. KlindtNo presence information on 5/20/2010 9:09 AM

Re: Problems with script

You only need the Where-Object part if you don't know the name of your Config DB. I really just threw that in as PowerShell theory, it's not necessary to mirror your Config DB. If you don't use the Where-Object loop and set the Config DB mirroring manually does it work?

I'd have to see the whole script to guess what's wrong with that one line.

tk
Todd O. KlindtNo presence information on 5/20/2010 9:12 AM

Re:Problems with script

Ok, I found my mistake... In spanish Configuration Database is "Base de datos de configuración" and that´s what I have to write if I want my configDB to be configured...
The script I tried takes all the databases and set the failover server for each one... but I don´t know if this is correct... I have read that Web Analytics Staging database and the User Profile Synchronization database are not recomended to be mirrored, and  Logging database is not necessary neither.
The script I used(but without the condition) is here:
http://www.sharepointmadscientist.com/Lists/Posts/Post.aspx?ID=57
and the information about which databases would be better to mirror is here:
http://technet.microsoft.com/en-us/library/ff628961.aspx
Thank you so much for the article and your quick help!
 on 5/20/2010 5:44 PM

Re:Problems with script

Thanks for the followup.

tk
Todd O. KlindtNo presence information on 5/21/2010 4:22 PM

Clustering vs Mirroring with SP2010

Howdy,

There seems to be some confusion about Sharepoint 2010 and whether the MS best practice is mirroring or clutsering.

However, the MS doco we have found doesnt really address our question of the pros and cons of the issue in the real world. It seems to be a sticky question.

What are your thoughts on this please?

To me mirroring seems less sensible for Sharepoint as I have found its rare to lose a database, you'd be more likely to lose a whole SQL instance ( I'm a DBA by the way ). Mirroring also requires all the witness server setup etc etc.

Also, while setting up a cluster is more messy, once its running ( as long as people dont mess with it ) its usually just fine.

I find the mirroring only makes sense if you must not have ANY downtime at all, but, that said, you could use say VMWare Fault Tolerance if things got really critical, and achieve roughly the same result.

We are looking at running SQL 2008 R2 on Windows 2008 R2 Enterprise on VMWare 4. We have some seriously grunty new boxes ( fully specced Dell R910s with 256 GB of RAM etc ) so perfomance wont be an issue.

All thoughts welcome. Hopefully you can cut through the "noise" on this topic for me.

cheers

Steve
 on 6/15/2010 7:38 PM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person

Twitter


Want a message when I reply to your comment? Put your Twitter handle here.

Attachments

 

 Please Support my Sponsors