1

Our SP2010 farm have 1 SQL server (sql01). Recently we setup another sql server (sql02) with SQL mirroring. I follow this blog post to setup failover server for each database (including system db and content db) with PowerShell.

Later I found several service application also have "Failover Database Server" settings. For example, managed metadata and secure store: enter image description here

My question is, do I need to setup both?

1

Once you configured the mirroring on SQL server side and your services databases are part of it then you have to configure this settings. Once you have this setting then SharePoint will automatically try to connect to failed over server in case of primary failure.

Prior to configuring a database for database mirroring with SharePoint Server 2010 the database should first be configured for database mirroring on the SQL Server.

Read this Bill Bear post for complete understanding. Database Mirroring in SharePoint 2010

  • In Bill's post, he doesn't mention how to configure failover db server for config database. I think the only way is perform the setup by powershell. However, the failover is not automatically. In my test, I have to setup SQL alias (sql01 > sql02) in order to work around it. Not sure why. – Mark L Dec 27 '17 at 7:00
0

You can run Get-SPDatabase GUID | select name, failoverserver command to verify if the db have setting up the failover server . If not , you can configure via PowerShell or Central Admin . But powershell is a recommended approach .

From your screenshot , if you want to create a new service application (Managed Metadata Service application in this case) and want to use a new DB , you need to fill in the failover server name , but if you want to use the existing DB which have been set up failover database , you needn't fill this box in this page .

0

The blog reference in my question have a typo in the PowerShell. The blog used:

$db = Get-SPDatabase | Where-Object { $_.TypeName -eq "Configuration Database" }
$db.AddFailoverServiceInstance("sql02")
$db.update

Afterward, I run this command to verify:

Get-SPDatabase | select name, failoverserver, type | sort name | ft -auto | Out-String -Width 2000

The result shows all databases have been updated with failover server. However, in fact, the last line should be $db.update() (add the blanket). Without the blanket, the update is in temporary stage only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.