To make your Sharepoint database reliable, you need to mirror it, meaning whatever 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 it's 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 you are 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.
