Controls when database changes are assured to have been sent to a mirror server when running in asynchronous or asyncfullpage mode.
|PUBLIC role||For current user||For other users|
|Allowed to set permanently?||Yes, with SET ANY PUBLIC OPTION||Yes||Yes, with SET ANY PUBLIC OPTION|
|Allowed to set temporarily?||Yes, with SET ANY PUBLIC OPTION||Yes (current connection only)||No|
The synchronize_mirror_on_commit option allows fine-grained control over when database changes are assured to have been sent to a mirror server when running in asynchronous or asyncfullpage mode. The option is Off by default. When set to On, each COMMIT causes any changes recorded in the transaction log to be sent to the mirror server, and an acknowledgement to be sent by the mirror server to the primary server once the changes are received by the mirror server.
The option can be set for specific transactions using SET TEMPORARY OPTION.
It may also be useful to set the option for specific applications by examining the APPINFO string in a login procedure. This allows mirroring behavior to be tailored to meet the needs of different applications.