Fail over and recover mirrored databases in a single farm

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

Topic Last Modified: 2016-11-14

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:

• Automatic failover process for a mirrored database

• Manual failover process for a mirrored database

• Monitor and alert on mirroring failover

• Job: Fail over all remaining databases

• Job: Update connection aliases

• Failing back

• Summary

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 process for a mirrored database

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.

The following process occurs when automatic failover is triggered:

1. 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.

2. The witness and mirror servers register that the principal server is unavailable.

3. 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.

4. 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 (https://go.microsoft.com/fwlink/?LinkId=83690&clcid=0x409).

Manual failover process for a mirrored database

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:

1. 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.

2. 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.

Monitor and alert on mirroring failover

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.

Alert: Detect a single database switch from principal to mirror

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>' )

Job: Fail over all remaining databases

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

Job: Update connection aliases

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.

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

For an example of how to create this job, see Case Study of High Availability for SharePoint using Database Mirroring (white paper).

Failing back

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.

Summary

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.