ALTER DATABASE Database Mirroring (Transact-SQL)

Controls database mirroring for a database. Values specified with the database mirroring options apply to both copies of the database and to the database mirroring session as a whole. Only one <database_mirroring_option> is permitted per ALTER DATABASE statement.

For ALTER DATABASE options, see ALTER DATABASE (Transact-SQL). For ALTER DATABASE SET options, see ALTER DATABASE SET Options (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

ALTER DATABASE database_name 
SET { <partner_option> | <witness_option> }
  <partner_option> ::=
    PARTNER { = 'partner_server' 
            | FAILOVER 
            | FORCE_SERVICE_ALLOW_DATA_LOSS
            | OFF
            | RESUME 
            | SAFETY { FULL | OFF }
            | SUSPEND 
            | TIMEOUT integer
            }
  <witness_option> ::=
    WITNESS { = 'witness_server' 
            | OFF 
            }

Arguments

• 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 Create a Database 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:

  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 (SQL Server).

    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 Specify a Server Network Address (Database Mirroring).

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

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

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

    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.

  • 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 (SQL Server).

  • 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 (Database Mirroring).

      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.

  • 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 (Database Mirroring).

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

  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 The Database Mirroring Endpoint (SQL Server).

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

  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.

Examples

A. Creating a database mirroring session with a witness

Setting up database mirroring with a witness requires configuring security and preparing the mirror database, and also using ALTER DATABASE to set the partners. For an example of the complete setup process, see Setting Up Database Mirroring (SQL Server).

B. Manually failing over a database mirroring session

Manual failover can be initiated from either database mirroring partner. Before failing over, you should verify that the server you believe to be the current principal server actually is the principal server. For example, for the AdventureWorks2012 database, on that server instance that you think is the current principal server, execute the following query:

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'AdventureWorks2012'; 
GO

If the server instance is in fact the principal, the value of mirroring_role_desc is Principal. If this server instance were the mirror server, the SELECT statement would return Mirror.

The following example assumes that the server is the current principal.

1. Manually fail over to the database mirroring partner:

   ALTER DATABASE AdventureWorks2012 SET PARTNER FAILOVER;
   GO
   

2. To verify the results of the failover on the new mirror, execute the following query:

   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'AdventureWorks2012'; 
   GO
   

   The current value of mirroring_role_desc is now Mirror.

See Also

Reference

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

sys.database_mirroring_witnesses (Transact-SQL)