Programmatic Administration of Database Mirroring and Failover
Published: May 2010
This topic describes the support provided by the Microsoft SharePoint Foundation object model for the database mirroring and failover feature of Microsoft SQL Server.
Any Microsoft SQL Server database can be mirrored on another SQL Server server. Every write and delete operation to the primary database is duplicated on the mirror database. Moreover, in the event of a hardware, network, or other failure on the primary server, the mirroring server can immediately assume the role of primary database with little or no interruption of service to consuming applications.
Database mirroring does not eliminate the need to backup data. In general, if the primary database is corrupted for any reason other than hardware failure, then the mirror database will be corrupted in the same way.
SharePoint Foundation support for database mirroring and failover servers is primarily a matter of telling SharePoint Foundation about the mirror. The detailed configuration is handled entirely in Microsoft SQL Server. The Central Administration application has a UI for identifying the failover servers for content databases and service application databases such as the search database. A Windows PowerShell command-line interface script can be used to do the same thing, and a Windows PowerShell command is available to specify the failover server for the configuration database. If your solution includes a database that you expect users to mirror, you can use the object model to tell SharePoint Foundation about the failover server and persist this information in the configuration database.
If a witness server has been set up, it automatically redirects database accesses to the mirror database if the primary database is inaccessible. Even if there is no witness server, SharePoint Foundation will support rapid switching to the mirror. If for any reason the primary database cannot be accessed, SharePoint Foundation will attempt to connect to the mirror. The mirror, of course, must be set to readable in SQL Server.
Mirroring and Failover Support in the Object Model
There are three members of the SPDatabase class that support database mirroring:
The FailoverServer property gets the SPServer object that represents the server that is hosting the mirror of the database. You can set this property only indirectly by calling AddFailoverServiceInstance(String).
The AddFailoverServiceInstance(String) method identifies to SharePoint Foundation a server and a database service instance that hosts the mirror of the database. If the server was not previously registered with the server farm, the method will register it. The parameter passed to AddFailoverServiceInstance(String) must be of the form “server_name/database_service_instance_name”; for example, BackupServer/Microsoft##SSEEMirror.
The following example shows how to register a mirror database with your SharePoint Foundation configuration database. The cdb is the SPDatabase object.
If your solution also provides a UI to farm administrators as an alternative to the Central Administration application, you can use the ContentDatabaseSection control. Certain of its properties enable farm administrators to set the relevant values, in particular the FailoverDatabaseServer property.