SAP SQL Anywhere

Question

Hi there,

our test-mirroring-system is allready and successfull running and now i have to test different scenarios for initiating a failover, before running in productive area. Is there something i have to consider for testing it? Can u give me some tips for that? I found this http://dcx.sybase.com/1101/en/dbadmin_en11/da-highavailability-s-4980336.html but i don't think this is the only possibility to test the mirror...

Another question: I found this in the documentation of HA (http://dcx.sap.com/index.html#1200/en/dbadmin/database-mirroring-overview.html): "... Any clients that were connected to the original primary server are disconnected, and any uncommitted transactions are lost. Clients must then reconnect to the database on the new primary server to continue accessing the database. When the original primary server becomes available again, it assumes the role of mirror server. "

What does it mean? Can the application use the same databasename at any time or must it be changed in the client application if the connection switched to the mirror server? Our target is, that the arbiter can unnoticed switch to the mirror server, so that the clients can continue working without disconnect and without login again with a different server name.

Thanks in advance and regards from germany Sabrina

Accepted Answer

3

Just a few hints:

To force failover, you can also issue an ALTER DATABASE SET PARTNER FAILOVER on the primary, or un-plug the network cable, or destroy the machine, or (the latter may miss an undo function:))
When a failover appears, clients are disconnected. There is no automatic re-connect to the new primary, just as your quote points out.
Clients should use a connection string that will fit to connect to any of both partner database engines so you will usually have to use the alternate server name for the SERVER connection and to specify two IPs in the HOST parameter. The database name itself is identical. So in case the connection is dropped, you should be able to re-establish that with the same connection string (that's basically one of the primary goals of HA...).

the arbiter can unnoticed switch

To clarify: The arbiter does not "switch", it is only required to allow the partner to take the quorum, i.e. to help to find out if the partner is "left alone" or not.

permanent link

answered 26 Mar '15, 05:50

Volker Barth
39.9k●360●547●817
accept rate: 34%

Hi Volker,

regarding your last hint:

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

create mirror server IMSTEST_primaryDB as partner
connection_string='SERVER=IMSTEST_primaryDB;HOST=IMS-02:46000' state_file='C:\IMSTEST_primaryDB.state'


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

create mirror server IMSTEST_mirrorDB as partner
connection_string='SERVER=IMSTEST_mirrorDB;HOST=IMS-backup:46035' state_file='C:\IMSTEST_mirrorDB.state';


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

You can see, i am using two servernames in the parameter. Do i understand you correctly: the clients doesn't switch automaticly to the new server and i have to change the program code in every application, that the connection reconnect to the new servername xyz, after failover? I thought this should happen automaticly? Is it possible to solve the problem if i change the odbc interface to :

Host--> IMS-02:46000,IMS-backup:46035 Port --> ?? Servername --> ??

thanks and regards, Sabrina

(26 Mar '15, 08:54) sabstar

Replies hidden

1

The client's connection string should contain the server name and host list you have specified in the CREATE MIRROR SERVER AS PRIMARY statement (unless you want a read-only connection to the mirror), i.e.

-c "SERVER=myPrimary;IMS-02:46000,IMS-backup:46035;UID=..."

Do i understand you correctly: the clients doesn't switch automaticly to the new server and i have to change the program code in every application, that the connection reconnect to the new servername xyz, after failover?

Yes and no:

Yes, the client does not automatically re-connect.
No, you should not need to change the connection string when a failover appears - but you will have to establish code to re-connect after a disconnect (unless your application already does that). - (FWIW, if it's alright for your users to simple re-start the application in case of a failover, you should not need to enter any program code.)

(26 Mar '15, 09:25) Volker Barth

flat view

Answer 2

Volker's comment on your question is excellent. In addition:

If you want to do more complete testing, you could optionally consider the following scenarios:

stopping (and then restarting) either the mirror or arbiter while the other two servers are running (the primary should remain available)
If the arbiter is down, stopping the primary or mirror will result in lost quorum - no primary will be available
stop server vs killing the server (terminating the process) vs unplugging the network cable

FYI an example of a connection string that will connect to which ever partner is the primary is:

dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_demo_primary;HOST=localhost:6871,localhost:6872"

in http://dcx.sap.com/index.html#1200/en/dbadmin/da-highavailability-s-4980336.html If you want your application to be able to connect to the primary after a failover, all of your client connections should be of this form (use primary alternate server name and host addresses for both partners).

Answer 3

Short answer: See High Availability Demo, Revised

Long answer: You test an HA setup by randomly killing the primary, secondary and/or arbiter servers and watching what happens with the databases and the client applications. If you stop one server, the client applications should continue (or be able to reconnect depending on which one server was killed). If you stop two servers then everything dies; there is no quorum so no primary can exist.

You can use dbstop or Windows taskkill to stop the servers.

Client applications that need to continue operating after a failover should (a) be able to gracefully handle a disconnection and do a reconnect, (b) use a connection string that will find the primary server on whatever computer it now resides on and (c) keep trying (b) if it fails so it reconnects as soon as a primary becomes available. The dbisql utility is an example of a client application that does just than, and you can see how that process works in this article: High Availability Demo, Revised

SAP SQL Anywhere

How to test mirroring and login clients to mirrored server while failover

Follow this question

Related questions