SharePoint 2010 is now mirroring-aware
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 can define a database name for every content database. This effectively allows SharePoint 2010 will automatically be aware of database mirroring. If the primary SQL server dies, SharePoint will automatically re-connect to the failover database.
For example; you have content database SQLDB1 and failover database SQLDB2. By default SharePoint will communicate with SQLDB1. But in case SQLDB1 dies or SharePoint unable to communicate with SQLDB1 it will automatically retry connection to failover database and start communication with SQLDB2. SQLDB2 is a mirror to the SQLDB1. The default timeout to connect to failover database is 15 seconds.
It will retry automatically, so you have no need to worry about any manual process to change the database.
However, 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. The values for the failover instances for the databases an be set through the Central Admin UI except for the Configuration database which needs to done through PowerShell.
To make SharePoint Server 2010 aware that failover mirrored databases exist, perform the following procedure for all configuration and content databases.
Note : It is recommended to use Windows PowerShell cmdlets to set failover database values. Although you can use the Central Administration Web site to set some failover database values, you cannot use
it for all databases like the Configuration Database.
To configure SharePoint 2010 Products to be aware of mirrored databases by using Windows PowerShell
- Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
- On the Start menu, click All Programs.
- Click Microsoft SharePoint 2010 Products.
- Click SharePoint 2010 Management Shell.
- At the Windows PowerShell command prompt, type the following commands, and then press ENTER
$db = get-spdatabase | where {$_.Name -eq "database name "}
$db.AddFailoverServiceInstance("mirror server")
$db.Update()
For more information, see Get-SPDatabase.
After running these cmdlets when you look at the connection strings for the databases, they will look something like following
DatabaseConnectionString : Data Source=<primary SQL instance>;Failover Partner=<mirror SQL instance>;
Initial Catalog=<SharePoint_Config>;Integrated Security=True;Enlist=False;Connect Timeout=15
LegacyDatabaseConnectionString : =<primary SQL instance>;Database=<SharePoint_Config>;Trusted_Connection=yes;
App=Microsoft SharePoint Foundation;Timeout=15;Failover Partner=<mirror SQL instance>
Test the fail over capability
Test the automatic failover capability of the mirror by shutting down the principal server. You will notice that the mirror server will become the principal server while continuing to host database operations. Power on the principal server. Any database changes made on the mirror server will be restored to the principal server.
Next, shut down the mirror server so the principal server can once again become the primary server. Power on the mirror server.
To configure a failover instance for all databases (except for the Logging database):
Get-SPDatabase | ? {$_.Type -ne “Microsoft.SharePoint.Administration.SPUsageDatabase”} | ? {!$_.FailoverServiceInstance} | ForEach ($_) {$_.AddFailoverServiceInstance(“SERVERNAME\INSTANCENAME”); $_.Update()}
The supported topologies include mirroring all content databases, the configuration database, the Central Administration content database, and the service application databases except for the Web Analytics Staging database and the User Profile Synchronization database.
Note:
We do not recommend that you mirror the Usage and Health Data Collection Logging database. A SharePoint environment can continue to run if this database fails, and this data can be quickly regenerated.
Avoid topologies that do not have matching principal server and database instances and mirror server and database instances. Also, keep the configuration database and the administration content database on the same server.
The information about which databases would be better to mirror is available at https://technet.microsoft.com/en-us/library/ff628961.aspx
Also, you can run the following command to get list of all databases and failover instances (if set)
Get-SPDatabase | SELECT Farm, Name, Type, FailoverServer | Sort-Object Name
Alternatively, you can export the output to a csv file using the following command:
Get-SPDatabase | SELECT Farm, Name, Type, FailoverServer | Sort-Object Name | Export-CSV Database_List_$env:computername.csv