Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements


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

SET MIRROR OPTION option-name={ option-value | NULL }
option-name :
  • 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.


database mirroring

integer, in seconds


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.


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.


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

Side effects

Automatic commit.

  • ANSI/ISO SQL Standard

    Not in the standard.


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

SET MIRROR OPTION authentication_string = 'abc';