How to set Failover SQL Server for SharePoint services databases using PowerShell

Hi Mates,

You set the Failover server to Web Applications easily while creation and most of the time for few Service Applications too. But how you'll set the Failover database server for the service applications that do not give you the option to set while their creation or configuration?

This issue happened with me while establishing a SharePoint 2013 Farm. I think the same thing would happen with SharePoint 2010 so this article may be helpful for you.

You can set the Failover SQL server for SharePoint service application databases or any other configuration pr content database by using PowerShell commands.

Important: Before moving ahead, make sure the service accounts you are using for different services or SharePoint configuration must have the same permissions role in DB2 server as they have in DB1 server for a successful redirect of SharePoint in case of failure. They must have login permissions granted in DB2 server. Example below.

DB1 Server:


DB2 Server:


Here is the PowerShell code for adding Failover SQL server for a service application database. In this example i am setting Failover for ReportingService database.


Code
(Change the DB Name and Guid According to your requirement)
Get-SPDatabase  a3460e3b-a8f1-40a6-8850-082b147edf6c | select name, failoverserver
$db = get-spdatabase | where {$_.Name -eq "ReportingService"}  
$db.AddFailoverServiceInstance("PROD-SQL02");
$db.Update();
Or you can try the below code as well. In some cases this could may not work, so ideally you should be trying with the above code.

Get-SPDatabase  a3460e3b-a8f1-40a6-8850-082b147edf6c | select name, failoverserver
(Get-SPDatabase a3460e3b-a8f1-40a6-8850-082b147edf6c).AddFailoverServiceInstance("PROD-SQL02");
(Get-SPDatabase a3460e3b-a8f1-40a6-8850-082b147edf6c).Update();

Enjoy...!

Comments

Popular Posts

GREYCstoration Oil Paint plugin for Photoshop

Service Bus Gateway service stuck at Starting

PowerApps SubmitForm not clearing People Picker value

Apple iPhone sending SMS automatically 00447786205094

Download file failed signature verification and may have been tampered with - Workflow Manger 1.0 Refresh (CU2)