High-Availability and arbiter server

[Former Member]

Hello all, I wonder if, with SQLAnywhere and 12 it's possible start an mirroring environment without a arbiter server and manually manage the status of primary and mirror partner server.

Thanks for the info

[MarkCulp]

You can run an HA system with just the two partners but in such a system both partners would need to be up and connected to each other before either would respond to connection requests.... and thus make the system not that useful. The purpose of the arbiter is to allow one of the two partners to obtain quorum and thus continue to run without the need of the other partner to be running.

There is currently no method to manually manage the quorum status of the HA system.

---

[2012/04/16] Clarification of my first paragraph above: Although you could create and run a mirroring system without an arbiter, don't do it: (a) as I mentioned above there is no point since either both servers/database copies must be running or both will be down - one database copy cannot run without the other. (b) If you try to run a mirroring system without a defined arbiter then the system is not going to do what you expect it to do!

[MCMartin]

Just to add, if you have an arbiter you can manually force one of the servers to become the primary, see here

[Former Member]

Hello and thanks for your prompt response. My target is to have a mirror environment where server1 should always be primary when available and server2 must always be the mirror and become primary only when there is a failover of server1. To achieve this I started on server1 my DB with the -xp option, after which I executed on DB follow script:

CREATE SERVER MIRROR xxx AS PRIMARY connection_string = 'SERVER = xxx; HOST = ip-first-server: 2638, second-server-ip: 2638';

CREATE SERVER MIRROR sa1 AS PARTNER connection_string = 'SERVER = sa1; host = ip-first-server: 2638' state_file = '/ data/logs/server1.state' preferred = 'YES';

CREATE SERVER MIRROR yyy AS MIRROR connection_string = 'SERVER = yyy; HOST = HOST = ip-first-server: 2638, second-server-ip: 2638';

CREATE SERVER MIRROR sa2 AS PARTNER connection_string = 'SERVER = sa2; host = ip-second-server: 2638' state_file = '/ data/logs/server2.state';

SET MIRROR OPTION authentication_string = 'abc';

After that I made a backup of the DB and I started it on the second server.

Mirroring has started. The parameter preferred='YES' did that I wanted to reach.

One feature I have not yet very clear and is:

Why commands "CREATE SERVER MIRROR" are executed only on a DB instead of run for each entity the creation of the primary or the mirror?

Example:

Run on server1

CREATE SERVER MIRROR xxx AS PRIMARY connection_string = 'SERVER = xxx; HOST = ip-first-server: 2638, second-server-ip: 2638';

CREATE SERVER MIRROR sa1 AS PARTNER connection_string = 'SERVER = sa1; host = ip-first-server: 2638' state_file = '/ data/logs/server1.state' preferred = 'YES';

SET MIRROR OPTION authentication_string = 'abc';

and server2 run

CREATE SERVER MIRROR yyy AS MIRROR connection_string = 'SERVER = yyy; HOST = HOST = ip-first-server: 2638, second-server-ip: 2638';

CREATE SERVER MIRROR sa2 AS PARTNER connection_string = 'SERVER = sa2; host = ip-second-server: 2638' state_file = '/ data/logs/server2.state';

SET MIRROR OPTION authentication_string = 'abc';

Thank you again for the information.

[ian_mchardy]

The CREATE MIRROR SERVER/ALTER MIRROR SERVER/SET MIRROR OPTION commands update the system tables in the database that is being mirrored. So the statements need to be run against the primary since they update the database.

You don't need to run these commands against the mirror since first of all, they would fail saying the database is read-only, and secondly, all the partners and any copy nodes read the configuration information from the system tables (both when they start and when applying statements original made on the primary by applying the transaction log on the mirror and copy nodes).

[VolkerBarth]

FWIW, your particular request to have a HA system with one preferred partner as primary can be solved as you have listed above, cf. the docs:

Specifying a preferred database server

[Former Member]

Hi Ian and Volker and thanks for the clarifications.

@Ian: It may be possible, on the second server. only execute scripts without executing It may be possible, on the second server. only execute scripts CREATE...AS MIRROR without executing CREATE ... AS PRIMARY?

@Volker: Looking at the script you'll notice that I have already specified the parameter "PREFERRED = yes" but not very good because when I try to restore the failover, the primary server (which was originally the mirror) leaves no role in what should be the primary .

Thanks again