Fail over and recover mirrored databases in a single farm
Updated: March 5, 2009
Applies To: Office SharePoint Server 2007
Topic Last Modified: 2009-06-22
This article describes how to fail over and recover a Microsoft Office SharePoint Server 2007 farm that has been configured to use high-availability database mirroring, following the procedures in the article Configure availability in a single farm by using SQL Server database mirroring.
This article includes the following sections:
When an Office SharePoint Server 2007 environment that is running mirrored databases fails over, the following process must occur:
One or more databases on the principal server fails over to the mirror, either automatically or manually.
All other databases on the principal server are forced to fail over.
We recommend that you use Microsoft SQL Server 2005 alerts and jobs to monitor mirroring and to force all databases to fail over.
Connection aliases on all front-end Web servers and computers that refer to databases are reset to point to the mirror server.
Automatic failover occurs when the principal server has lost communication with the rest of the database-mirroring configuration, and the mirror and witness servers retain communication.
|If all server instances lose communication, automatic failover does not occur, even if the witness and mirror servers later regain communication.|
The following process occurs when automatic failover is triggered:
If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.
The witness and mirror servers register that the principal server is unavailable.
If any logs are waiting in the redo queue, the mirror server finishes rolling forward the mirror database. The time that is required to apply the log depends on the speed of the system, the recent workload, and the size of the logs in the redo queue.
When the former principal server rejoins the session, it recognizes that its failover partner now owns the principal role. The former principal server takes on the role of mirror server, and the former principal database becomes the mirror database. The new mirror server synchronizes the new mirror database with the principal database as quickly as possible. As soon as the new mirror server has resynchronized the databases, failover is again possible, but in the reverse direction.
For a more detailed description of automatic failover, see Automatic Failover (http://go.microsoft.com/fwlink/?LinkId=83690&clcid=0x409).
Manual failover typically occurs when both the principal and mirror servers are still running. The administrator makes a conscious decision to switch the active database from the principal to the mirror.
The following process occurs during a manual failover:
The administrator connects to the principal server and issues the following Transact-SQL commands for each database:
USE master; ALTER DATABASE <database_name> SET PARTNER FAILOVER; -- where database_name is the mirrored database.
This statement initiates an immediate transition of the mirror server to the principal role.
On the former principal server, clients are disconnected from the database and uncommitted transactions are rolled back.
When you run manual failover with an automatic failover setup that includes a witness server, the principal and mirror servers automatically change roles.
Use SQL Server alerts to monitor mirroring, and run jobs that force failover. The jobs and alerts should be used on both the principal and mirror instances of SQL Server.
Within the alert, a Transact-SQL command is used to detect whether any databases have switched to the corresponding mirror database.
SELECT * FROM Database_MIRRORING_STATE_CHANGE WHERE State=8 AND (databasename='Central Administration' OR databasename='Configuration' ORdatabasename='SSP' OR databasename='SSP Content' OR databasename='SSP Search' OR databasename='WSS Search' OR databasename='Content_<port>' )
After the detection alert occurs, run a job to fail over all the databases to the corresponding mirror databases.
The following example provides a Transact-SQL script that you can use within a job to fail over all mirrored databases:
USE master; DECLARE i CURSOR READ_ONLY FOR SELECT name FROM sys.databases WHERE database_id IN (SELECT database_id FROM sys.database_mirroring WHERE mirroring_state=4) DECLARE @name varchar(255) DECLARE @cmd varchar(1000) OPEN i FETCH NEXT FROM i INTO @name WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN set @cmd = 'ALTER Database [' + @name + '] SET PARTNER FAILOVER;' exec (@cmd) DECLARE @message varchar(100) SELECT @message = 'Failover for : ' + @name PRINT @message END FETCH NEXT FROM i INTO @name END CLOSE i DEALLOCATE i GO
After failover, the database server name associated with the SQL Server connection alias must be changed from the principal server to the mirror server on all computers that are running Office SharePoint Server 2007.
|The reference within each Web application does not change. Therefore, no work is required within Office SharePoint Server 2007 after failover.|
Create a SQL Server job to run after failover is completed. Within the job, run a command to change the registry setting for the alias value to the mirror:
\Registry\Machine\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo <alias> = REG_SZ DBMSSOCN,DBMirror
|The account that you are using to run the SQL Server jobs and alerts must have appropriate permissions to change the registry values on the computers that are running Office SharePoint Server 2007. For more information, see Assign permissions to a registry key (http://go.microsoft.com/fwlink/?LinkId=116137&clcid=0x409).|
For an example of how to create this job, see Case Study of High Availability for SharePoint using Database Mirroring (white paper).
To fail back to the principal server, you must manually fail the databases back. You can use the same SQL Server jobs to automate failing back and resetting the SQL Server client connection aliases. You must change the values in the jobs before you run them.
After you configure mirroring in a single farm environment, we recommend that you test your process, alerts, jobs, and scripts for failing over and failing back. If a single database fails over, you may want to write scripts that first try failing back to the principal server before failing over all other databases to the mirror server.
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable books for Office SharePoint Server 2007 .