SQL Anywhere 12.0: Mirror not running

[Former Member]

Hi there,

i hope you can help me again 🙂

We want to install HA (mirroring) on 3 server: IMS-02 (192.168.0.109), IMS-backup (192.168.0.103) and Pro-2 (192.168.10.164) This mirroring system should be running as service.

I had connected to the primarydb on IMS-02 and hat created this with Interactive SQL:

create mirror server PrimaryDB_primary as primary connection_string='SERVER=PrimaryDB_primary;HOST=IMS-02:46000,IMS-backup:46035';

create mirror server PrimaryDB_partner as partner connection_string='SERVER=PrimaryDB_partner;HOST=IMS-02:46000' state_file='G:\\primary_db\\server1.state' preferred='YES';

create mirror server PrimaryDB_mirror as mirror connection_string='SERVER=PrimaryDB_mirror;HOST=IMS-02:46000,IMS-backup:46035';

create mirror server PrimaryDB_mirror_partner as partner connection_string='SERVER=PrimaryDB_mirror_partner;HOST=IMS-backup:46035' state_file='\\192.168.0.103\\IMSTTEST_db\\server2.state';

create mirror server PrimaryDB_Arbiter as arbiter connection_string='SERVER=PrimaryDB_Arbiter;HOST=PRO-2:46020';

set mirror option authentication_string='abc'; set mirror option synchronization_mode='synchronous';

Then i had defined a service on Pro2 for the Arbiter with the following config parameter: -n PrimaryDB_Arbiter -su sql23 -x "TCPIP(PORT=46020;DOBROAD=no)" -xf "c:\\arbiter\\arbiter.state" -xa "AUTH=abc;DBN=PrimaryDB"

2nd service on IMS-02 as primary server: -n PrimaryDB_Server -x tcpip(ServerPort=46000;doBroadcast=no) -xf "G:\\primary_db\\server1.state" G:\\primary_db\\primary.db -ek akira2468 -n PrimaryDB

3rd service on IMS-backup as mirror server: -n PrimaryDB_Mirror -x tcpip(PORT=46035;DOBROAD=no) -xf "F:\\IMSTTEST_db\\server2.state" -su sql23 F:\\IMSTTEST_db\\primary.db -oe "C:\\error_sql_mirror.txt" -ek akira2468 -n PrimaryDB

The services will be started, but in the overview on Sybase Central (Health & Statistics), there will not shown the grafic for the functions Arbiter and Mirror. If i have a look to the state file for primary server, i find this:

[PrimaryDB] Owner=<partner> State=synchronizing Mode=synchronous Sequence=1 Yielding=No

But the file for the mirror server is empty!

I had also started the services with the following parameters for each server but the mirror system doesn't work: -xp partner=(ENG=PrimaryDB_partner;LINKS=tcpip(PORT=46000;TIMEOUT=1));auth=abc;arbiter=(ENG=PrimaryDB_Arbiter;LINKS=tcpip(PORT=46020;TIMEOUT=1));mode=synchronous

Can somebody tell me what goes wrong and how to fix?

thanks and best regards from germany Sabrina

[Former Member]

I forgot something:

In the file error_sql_mirror.txt for the mirror server are these entries: Database could not be started - missing or invalid database mirroring options.

[ian_mchardy · Accepted solution]

I notice a few potential problems:

1. When you are using mirroring with mirror server definitions (eg CREATE MIRROR SERVER), you need "-xp on" on the command line after your database file on both partner servers.
2. You should not include -xf on the command line for the partner servers (but it is required for the arbiter server).
3. The PrimaryDB_mirror_partner has state_file='\\192.168.0.103\\IMSTTEST_db\\server2.state'. This should be a local file, not a network file. (Don't use a network file, but the path format is incorrect for a network file anyway).
4. If you have mirror server definitions (which you do), don't also use the deprecated -xp format (just use "-xp on"). Mixing these two options can result in unexpected results.
5. You don't have "-o engine_log_file.txt" command line options on either partner servers. You will want this output to see normal server messages that can help you understand what the server is doing. If after fixing all of the problems I suggest you still have errors that you don't understand, we would need to see this output to help you further.
6. After fixing the server command lines and restarting the server, and then fixing the mirror server definitions (the state_file), you will need to copy or backup the database from the partner where you modified the database to the other partner and then start both servers. If you are unable to connect to either server to modify the database (you might be since your state files are in weird state), connect to the utility DB on the server that was most recently the primary, and execute "ALTER DATABASE PrimaryDB FORCE START". Then that server should be the primary, you should be able to ALTER the mirror server definitions (to fix the state file), and then make a backup for the other partner, and restart the other partner.

[ian_mchardy · Accepted solution]

I had another look at the mirror server definitions. There a number of errors in the server names. The whole system has many errors. I suggest reading over the doc very carefully and paying attention to the details of the server names, mirror server definitions and command lines. See:

http://dcx.sap.com/index.html#1201/en/dbadmin/database-mirroring-tutorial-setup.html

http://dcx.sap.com/index.html#1201/en/dbadmin/backup-s-3894811.html

http://dcx.sap.com/index.html#1201/en/dbreference/create-mirror-server-statement.html*d5e39948

Take a really close look at the server names in the AS clause description of the CREATE MIRROR SERVER link.

[Former Member · Accepted solution]

Thanks for your answers!

Is it possible to run the Arbiter on a 3rd Server? Or is it ok if it runs on the same like the primary?

regards, Sabrina

[VolkerBarth · Accepted solution]

Is it possible to run the Arbiter on a 3rd Server?

It is not only possible but highly recommended to run the arbiter on a 3rd server.

If the arbiter runs on the same machine as the primary server (which is of course possible, too), once the network connection of this machine becomes unavailable the secondary server will not be able to take over as he cannot agree with the arbiter that he should become the primary now. Basically, you restrict the notion of "Two of the three (primary, secondary, arbiter) must be available" when putting two of them on the same hardware. Therefore you will have a restricted HA system with that configuration.

Note: That's meant for production uses. When you are just about to test the HA facility, you can also run all three on one hardware...

[reimer_pods · Accepted solution]

The arbiter should be running on a different machine (not on one of the mirrored servers), so the arbiter will keep running if one of the servers becomes unavailable.

[Former Member · Accepted solution]

Thanks, but where do i have to config the arbiter with "Create mirror server xyz as Arbiter...". On the same DB as the Primary DB (in Interactive SQL)? Because the 3rd Server don't knows the arbiter, if i create the arbiter on die PrimaryDB...

[VolkerBarth · Accepted solution]

The CREATE MIRROR SERVER statements are meant to be run on the original database before the HA setup is started, i.e. you let them run in the initial database and then copy that database to the second server and try to start your HA system, so both the primary and secondary server have the same database contents available when they start...

[Breck_Carter]

The SERVER=PrimaryDB_mirror_partner doesn't match anything else.