ALTER DATABASE Database Mirroring (Transact-SQL)

Important
A SET PARTNER or SET WITNESS command can complete successfully when entered, but fail later.

database_name

Is the name of the database to be modified.

PARTNER <partner_option>

Controls the database properties that define the failover partners of a database mirroring session and their behavior. Some SET PARTNER options can be set on either partner; others are restricted to the principal server or to the mirror server. For more information, see the individual PARTNER options that follow. A SET PARTNER clause affects both copies of the database, regardless of the partner on which it is specified.

To execute a SET PARTNER statement, the STATE of the endpoints of both partners must be set to STARTED. Note, also, that the ROLE of the database mirroring endpoint of each partner server instance must be set to either PARTNER or ALL. For information about how to specify an endpoint, see How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL). To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement:

Copy

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

<partner_option> ::=

Note
Only one <partner_option> is permitted per SET PARTNER clause.

'partner_server'

Specifies the server network address of an instance of SQL Server to act as a failover partner in a new database mirroring session. Each session requires two partners: one starts as the principal server, and the other starts as the mirror server. We recommend that these partners reside on different computers.

This option is specified one time per session on each partner. Initiating a database mirroring session requires two ALTER DATABASE database SET PARTNER ='partner_server' statements. Their order is significant. First, connect to the mirror server, and specify the principal server instance as partner_server (SET PARTNER ='principal_server'). Second, connect to the principal server, and specify the mirror server instance as partner_server (SET PARTNER ='mirror_server'); this starts a database mirroring session between these two partners. For more information, see Setting Up Database Mirroring.

The value of partner_server is a server network address. This has the following syntax:

TCP://<system-address>:<port>

where

• <system-address> is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.

• <port> is a port number that is associated with the mirroring endpoint of the partner server instance.

For more information, see Specifying a Server Network Address (Database Mirroring).

The following example illustrates the SET PARTNER ='partner_server' clause:

Copy

'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'

Important
If a session is set up by using the ALTER DATABASE statement instead of SQL Server Management Studio, the session is set to full transaction safety by default (SAFETY is set to FULL) and runs in high-safety mode without automatic failover. To allow automatic failover, configure a witness; to run in high-performance mode, turn off transaction safety (SAFETY OFF).

FAILOVER

Manually fails over the principal server to the mirror server. You can specify FAILOVER only on the principal server. This option is valid only when the SAFETY setting is FULL (the default).

The FAILOVER option requires master as the database context.

For more information, see Manual Failover.

FORCE_SERVICE_ALLOW_DATA_LOSS

Forces database service to the mirror database after the principal server fails with the database in an unsynchronized state or in a synchronized state when automatic failover does not occur.

We strongly recommend that you force service only if the principal server is no longer running. Otherwise, some clients might continue to access the original principal database instead of the new principal database.

FORCE_SERVICE_ALLOW_DATA_LOSS is available only on the mirror server and only under all the following conditions:

• The principal server is down.

• WITNESS is set to OFF or the witness is connected to the mirror server.

Force service only if you are willing to risk losing some data in order to restore service to the database immediately. For information about alternatives to forcing service, see Asynchronous Database Mirroring (High-Performance Mode).

Forcing service suspends the session, temporarily preserving all the data in the original principal database. Once the original principal is in service and able to communicate with the new principal server, the database administrator can resume service. When the session resumes, any unsent log records and the corresponding updates are lost.

For more information about the risks of forcing service, see Forced Service (with Possible Data Loss).

OFF

Removes a database mirroring session and removes mirroring from the database. You can specify OFF on either partner. For information, see about the impact of removing mirroring, see Removing Database Mirroring.

RESUME

Resumes a suspended database mirroring session. You can specify RESUME only on the principal server.

SAFETY { FULL | OFF }

Sets the level of transaction safety. You can specify SAFETY only on the principal server.

The default is FULL. With full safety, the database mirroring session runs synchronously (in high-safety mode). If SAFETY is set to OFF, the database mirroring session runs asynchronously (in high-performance mode).

The behavior of high-safety mode depends partly on the witness, as follows:

• When safety is set to FULL and a witness is set for the session, the session runs in high-safety mode with automatic failover. When the principal server is lost, the session automatically fails over if the database is synchronized and the mirror server instance and witness are still connected to each other (that is, they have quorum). For more information, see Quorum: How a Witness Affects Database Availability.

  If a witness is set for the session but is currently disconnected, the loss of the mirror server causes the principal server to go down.

• When safety is set to FULL and the witness is set to OFF, the session runs in high-safety mode without automatic failover. If the mirror server instance goes down, the principal server instance is unaffected. If the principal server instance goes down, you can force service (with possible data loss) to the mirror server instance.

If SAFETY is set to OFF, the session runs in high-performance mode, and automatic failover and manual failover are not supported. However, problems on the mirror do not affect the principal, and if the principal server instance goes down, you can, if necessary, force service (with possible data loss) to the mirror server instance—if WITNESS is set to OFF or the witness is currently connected to the mirror. For more information on forcing service, see "FORCE_SERVICE_ALLOW_DATA_LOSS" earlier in this section.

Important
High-performance mode is not intended to use a witness. However, whenever you set SAFETY to OFF, we strongly recommend that you ensure that WITNESS is set to OFF.

For more information, see Transact-SQL Settings and Database Mirroring Operating Modes.

SUSPEND

Pauses a database mirroring session.

You can specify SUSPEND on either partner.

TIMEOUT integer

Specifies the time-out period in seconds. The time-out period is the maximum time that a server instance waits to receive a PING message from another instance in the mirroring session before considering that other instance to be disconnected.

You can specify the TIMEOUT option only on the principal server. If you do not specify this option, by default, the time period is 10 seconds. If you specify 5 or greater, the time-out period is set to the specified number of seconds. If you specify a time-out value of 0 to 4 seconds, the time-out period is automatically set to 5 seconds.

Important
We recommend that you keep the time-out period at 10 seconds or greater. Setting the value to less than 10 seconds creates the possibility of a heavily loaded system missing PINGs and declaring a false failure.

For more information, see Possible Failures During Database Mirroring.

WITNESS <witness_option>

Controls the database properties that define a database mirroring witness. A SET WITNESS clause affects both copies of the database, but you can specify SET WITNESS only on the principal server. If a witness is set for a session, quorum is required to serve the database, regardless of the SAFETY setting; for more information, see Quorum: How a Witness Affects Database Availability.

We recommend that the witness and failover partners reside on separate computers. For information about the witness, see Database Mirroring Witness. For information about automatic failover, see Automatic Failover.

To execute a SET WITNESS statement, the STATE of the endpoints of both the principal and witness server instances must be set to STARTED. Note, also, that the ROLE of the database mirroring endpoint of a witness server instance must be set to either WITNESS or ALL. For information about specifying an endpoint, see Database Mirroring Endpoint.

To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement:

Copy

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

Note
Database properties cannot be set on the witness.

<witness_option> ::=

Note
Only one <witness_option> is permitted per SET WITNESS clause.

'witness_server'

Specifies an instance of the Database Engine to act as the witness server for a database mirroring session. You can specify SET WITNESS statements only on the principal server.

In a SET WITNESS ='witness_server' statement, the syntax of witness_server is the same as the syntax of partner_server.

OFF

Removes the witness from a database mirroring session. Setting the witness to OFF disables automatic failover. If the database is set to FULL SAFETY and the witness is set to OFF, a failure on the mirror server causes the principal server to make the database unavailable.

A. Creating a database mirroring session with a witness

B. Manually failing over a database mirroring session

SELECT db.name, m.mirroring_role_desc 
FROM sys.database_mirroring m 
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks'; 
GO

Reference