SET MIRROR OPTION statement

SET MIRROR OPTION option-name={ option-value | NULL }

option-name :
authentication_string
auto_add_fan_out
auto_add_server
auto_failover
child_creation
page_timeout
max_disconnected_time
max_retry_connect_time
synchronization_mode

NULL Specifies the default value for the option. When the option-name is set to NULL, the option value is set to its default value.

option-name	Applies to	Values	Default	Description
authentication_string	database mirroring	string	null	Specifies the authentication string used by all the servers in the database mirroring system. The authentication string is required for database mirroring.
auto_add_fan_out	read-only scale-out	integer	10	Specifies the maximum number of children for each branch. The minimum value that can be specified is 2.
auto_add_server	read-only scale-out	string	null	Specifies the name of the database server that acts as the parent of the automatic assignment tree.
auto_failover	database mirroring	on, off	null	Specifies whether the mirror server automatically takes over as the primary server when the current primary server goes down. This option does not apply to synchronous mode. This option accepts Boolean values (automatic failover is turned on with YES, ON, TRUE, or 1, and is turned off with any of NO, OFF, FALSE, and 0). The parameters are case insensitive. If you are using asynchronous or asyncfullpage mode, it is recommended that you set the auto_failover option to on. Then, if the primary server goes down, the mirror server automatically takes over as the primary server.
child_creation	read-only scale-out	automatic, off, manual	automatic	Controls whether copy nodes are created automatically.
page_timeout	database mirroring	integer, in seconds	5	Specifies how often, in seconds, transaction log pages are sent to the mirror server, whether or not they are full. This option applies only when using asyncfullpage mode.
max_disconnected_time	read-only scale-out	integer, in seconds, greater than or equal to max_retry_connect_time	no time limit	Specifies the amount of time since the last time the copy node was connected to the parent, alternate parent, or root database before the copy node shuts down.
max_retry_connect_time	read-only scale-out	integer, in seconds	120	Specifies the length of time that a copy node attempts to reconnect to its parent once the parent becomes unavailable.
promotion_time	read-only scale-out	integer, in seconds, greater than or equal to max_retry_connect_time	3600	Specifies the length of time that a copy node stays connected to the root database server after a parent connection is lost before promoting itself (adjusting the scale-out tree to not have a disconnected parent). This option helps to avoid adjusting the scale-out tree if a copy node is down for a short time (which can result in a shallow scale-out tree), while attempting to avoid increased load on the primary database for long periods of time. To never promote, set this option to 315,360,000 (10 years) or higher. This option is only supported in version 16 databases.
synchronization_mode	database mirroring	synchronous, asynchronous, asyncfullpage	synchronous	Specifies the synchronization mode used for database mirroring: synchronous (sync), asynchronous (async), or asyncfullpage (page). The synchronization mode controls when and how transactions are recorded on the mirror server.

Once you create a database server for a database mirroring system or a read-only scale-out system using the CREATE MIRROR SERVER statement, you can use the SET MIRROR OPTION statement to configure the settings for the system.

You must have started the server, or have the MANAGE ANY MIRROR SERVER system privilege.

Automatic commit.

SQL/2008 Vendor extension.

The following statement sets the authentication string for a database mirroring system to abc:

SET MIRROR OPTION authentication_string = 'abc';

SET MIRROR OPTION statement

Note

Syntax

Parameters

Remarks

Privileges

Side effects

See also

Standards and compatibility

Example