SET MIRROR OPTION statement

Changes the values of options that control the settings for database mirroring and read-only scale-out.

Syntax

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

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

Parameters

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, 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.
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_logfile_size	database mirroring and read-only scale-out	integer, with optional K, M, G suffix	no maximum size limit	Specifies a maximum size, in bytes, for mirror debug log files. Use K, M, or G to specify kilobytes, megabytes, or gigabytes. The minimum size is 10 KB. Once the maximum size is reached, the log file is renamed with the extension .old and a new file is created. Specify a mirror debug log file by setting the logfile `server-option` in the CREATE MIRROR SERVER or ALTER MIRROR SERVER statement.
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.
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.
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 as of version 16.
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.
lagtime	database mirroring	integer, in seconds	60	Specifies the approximate length of time the mirror server may run behind the primary server in applying the transaction log. The primary server reduces its rate of transactions when the lag time approaches the value of this setting. Set the value to 0 or any integer between 15 and 3600 seconds (one hour). Setting the option to 0 is equivalent to unlimited time.

Remarks

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

Read-only scale-out and database mirroring each require a separate license.

Privileges

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

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

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

SET MIRROR OPTION authentication_string = 'abc';