To make your Sharepoint database reliable you need to mirror it, meaning what ever happens on SQL Server A will be mirrored or replicated on SQL Server B and it’s quite easy to achieve you just need two steps to do it.
First is to mirror your SQL Server databases, you can achieve that all on SSMS and I have a published simple instructions before on how its done. Now do this for all of your Sharepoint Databases and to get a complete list of that you will need to use Powershell to show all active databases your Sharepoint is using, the command for that is:
Get-SPDatabase|Select Name
You will then get a result something like this, listing all the databases you need.
Once you mirrored them all you’re ready for the next part, making your Sharepoint mirror aware.
Now go to your Central Administration and Manage Content Databases
Choose the WSS Content Database
Then set a Failover Database Server to the Mirror Server you set earlier.
Once done Set the Failover for all of your Databases by using the AddFailoverServiceInstance method from the SPContentDatabase Object. To do that easily I created a script that will loop to all the databases your Sharepoint uses and set the Failover Database on each.
$dbFailoverServer = "YOURSQLSERVER2" $dbNames = Get-SPDatabase|Select Name foreach ($dbName in $dbNames) { Write-Host "Setting Failover for " $dbName.Name $db = Get-SPDatabase | Where { $_.Name -eq $dbName.Name } $db.AddFailoverServiceInstance($dbFailoverServer) $db.Update() }
And your done, you can now try to failover your main SQL server and see if it works. Tomorrow we will be discussing High Availability on Web Front End servers to keep in touch.
Hi,
Many thanks for this article.
Prior testing and validating the failover works as expected, how can I check that all SP databases have been configured to $dbFailoverServer = “YOURSQLSERVER2” successfully ?
Team who setup the SharePoint/SQL failover and team who validate the solution are different organizations. In such context, I need to perform some validation checks prior testing the solution.
Which PowerShell commands should I execute to ensure that are SP Databases are mirrored and that all SP databases are associate to a database failover.
Once, I do have the confirmation that the failover is configured correctly, I may plan for further application failover testing.
I thank you in advance,
best regards,
P a s c a L