How do SET MIRROR OPTION and DB_PROPERTY ( 'MirrorMode' ) work?

[Breck_Carter]

Does SET MIRROR OPTION synchronization_mode actually affect 12.0.1.3298 HA operations after the servers have started?

Does SET MIRROR OPTION synchronization_mode have any effect on an old-school HA setup that uses -xp instead of CREATE MIRROR SERVER?

Is DB_PROPERTY ( 'MirrorMode' ) supposed to show the effect of SET MIRROR OPTION synchronization_mode or not?

The following version 12.0.1.3298 behavior has been noticed in BOTH old-school and declarative HA setups; note that the option_value and MirrorMode values are different:

SET MIRROR OPTION synchronization_mode = 'asynchronous';
SELECT * FROM SYSMIRROROPTION;
SELECT DB_PROPERTY ( 'MirrorMode' );

option_name,option_value
'synchronization_mode',asynchronous

DB_PROPERTY('MirrorMode')
'synchronous'

[former_SQLA_member1694878]

Does upgrading to a newer EBF resolve the issue? On 12.0.1.3967 changing the synchronization_mode using SET MIRROR OPTION seems to update the MirrorMode property immediately. This appears to have been addressed by CR#666011 in builds 3327 and higher.

[ian_mchardy]

The current behaviour (in 12.0.1.3994 and I believe in the current 16.0.0.1761) is for:

• when a database starts (including restarting after for example, loss of quorum) the MirrorMode comes from the first of: -xp mode=value OR the synchronization_mode mirror option OR the default of synchronous
• if SET MIRROR OPTION synchronization_mode = 'asynchronous' is executed, that is effective immediately. However, if the -xp mode=value is specified on the command line, the next time the database starts or restarts, if the command line value (-xp mode=value) is specified, it will override the mirror option.

Note that you should either be using the SQL Anywhere 11 -xp options OR using the SQL Anywhere 12 SET MIRROR OPTIONS and CREATE/ALTER MIRROR SERVER ... syntaxes. Mixing the old and new syntaxes is not recommended and at your own risk. (Note that the SQL Anywhere 11 -xp options are deprecated in 12 and 16 and no longer documented. Support for these old -xp options will be removed in a future version since they can create confusion.)

From a quick look at CR#666011 , I believe that before that fix, SET MIRROR OPTION synchronization_mode = 'asynchronous'; incorrectly set the mode to synchronous.

Given the many mirroring issues that have been fixed since 12.0.1.3298, I recommend upgrading to a recent build.

[VolkerBarth]

if SET MIRROR OPTION synchronization_mode = 'asynchronous' is executed, that is effective immediately.

Is that behaviour the same for the other two modes, too, i.e. is a change of the synchronization mode via SET MIRROR OPTION generally effective immediately?

[ian_mchardy]

Yes, from a quick look at the code I believe that is the behaviour.