Faire basculer et récupérer des bases de données en miroir dans une batterie de serveurs unique

Article
06/12/2012

Mise à jour : 2009-03-05

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 Configurer la disponibilité dans une seule batterie de serveurs à l’aide de la mise en miroir de bases de données SQL Server.

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.

Remarque :
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 (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:

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.

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.

Remarque :
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

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

For an example of how to create this job, see Étude de cas d’une disponibilité élevée pour SharePoint à l’aide de la mise en miroir de base de données (livre blanc).

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.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

Plan and configure availability within an Office SharePoint Server 2007 farm

See the full list of available books at Downloadable books for Office SharePoint Server 2007.

Partager via