What is the JDBC connection string for a High Availability server group?

[former_SQLA_member1694877]

Hello,

I am running a High Availability group of servers on AWS. Let's say they are: Server1 @ <ipaddress1> <port1> Server2 @ <ipaddress2> <port2> Server3 @ <ipaddress3> <port3>

I start the database <dbname> on: Server1 as "PrimDBName" Server2 as "MirrDBName" Server3 is the arbiter.

I need to know how to connect to <dbname> with JDBC. In a "normal" (single server) environment I can use a connection string that looks like: "jdbc:sybase:Tds:<ipaddress1>:?Port1>?ServiceName=<dbname>"

I cannot find any documentation on what to be used here and nothing I have guessed at works. Would someone please tell me what this string should be?

Thank you.

[chris_keating]

The SAJDBC is preferred for HA JDBC connections.

The UDP discovery mechanism used by jConnect ALTERNATE_SERVER_NAME only works when the client and server are on the same subnet and never when they are separated by a WAN. Depending on the network configuration and the server OS being used, there are also cases where the UDP discovery mechanism isn't reliable even when the client and servers are on the same subnet on the same LAN. (Thanks to a colleague for highlighting some of the limits using jConnect in a SQL Anywhere HA environment).

[former_SQLA_member1694877]

Hi Chris,

Thanks for your initial reply but as you indicated in your revised comment (here) when the client must connect across a WAN ALTERNATIVE_SERVER_NAME won't work. Please see my reply to Volker.

[chris_keating]

For some reason, I cannot see your response to Volker.

[former_SQLA_member1694877]

Sorry - it took me longer to compose than I expected - I should have waited until I posted it before pointing you towards it! can you help? Thanks!

[chris_keating]

The URL would be

jdbc:sybase:Tds:SomeRandomHost:12345?ALTERNATE_SERVER_NAME=mirror_demo_primary

where SomeRandomHost:12345 is ignored for HA connection and the ALTERNATIVE_SERVER_NAME is the server name in the CREATE MIRROR SERVER… AS PRIMARY statement. In the tutorial, the ALTERNATIVE_SERVER_NAME is mirror_demo_primary given this statement:

CREATE MIRROR SERVER mirror_demo_primary
AS PRIMARY
connection_string='SERVER=mirror_demo_primary;HOST=localhost:6871,localhost:6872';

[VolkerBarth]

Do you need to use jConnect or would the SQL Anywhere JDBC driver do, as well? I guess the latter is easier to configure for HA setups as it can use the common SQL Anywhere connection parameters, i.e. those used in the HA samples like here.

For jConnect, see that other current FAQ issues with HA setups as jConnect seem to need a broadcast to locate the according servers... - see the jConnect ALTERNATE_SERVER_NAME connection property here.

[former_SQLA_member1694877]

Hi Volker.

Thanks for the link; somehow I missed seeing that. However, I have a strange result from this. If I use: jdbc:sybase:Tds:<ipaddress>:<port1>?HOST=<ipaddress>:<port1>,<ipaddress>:<port2>;ServerName=Prim<dbname>

I am able to connect to the database. Then, my Jave log shows that I am able to create two more connections to this database using the same connection string. But when I try to SELECT from a table I get SQLError -141 "Table Not Found". The table is definitely there. If I connect to the same database using SQL Central (using ServerName = Prim<dbname>) I see the table and if I activate iSQL I can even execute the SELECT statement (from the Java log) that triggers the -141 error. Finally, if I change back to using the (original) non-HA connection string for this same db that I've been using for months: jdbc:sybase:Tds:<ipaddress>:<port1>?SERVICEName=<dbname> (that's SERVICE not SERVER)

we have no trouble finding the table.

So, it would seem that connecting in this "HA mode" has caused a rather strange behavior. Do you have any idea what in the world is causing this?

Thank you.

[chris_keating]

That URL is not valid for a SQL Anywhere connection - HA or otherwise. I believe that the values after the ? are effectively ignored. It certainly will not failover in HA.

That URL will connect to <ipaddress>:<port1>. If that server is down, the connection will fail. If that server is up, it will connect regardless of whether it is acting as a primary or not. If the SQL Anywhere server running on <ipaddress>:<port1> is acting as a mirror, the connection would be made to it but it would be read-only.

I would encourage you to use the SA JDBC driver and if that is not feasible, jConnect requires ALTERNATE_SERVER_NAME to connect to a SQL Anywhere HA environment if you want to have failover.

[former_SQLA_member1694877]

Hi Chris, Thanks for letting me know that jConnect/ALTERNATE_SERVER_NAME won't work for a WAN. I am now working to change over to SA JDBC and will report what happens. (At the moment I have the sajdbc4.jar in the WEB-INF\\lib folder but I am getting "No suitable driver found" so I have a problem.)

I am a bit confused. In your earlier response you said ALTERNATE_SERVER_NAME won't work in my WAN case but at the end of this post you say that if SA JDBC won't work I'll need to use ALTERNATE_SERVER_NAME. PLease calrify.

Thanks!

[VolkerBarth]

In my understanding Chris does strongly recommend the SAJDBC driver. However, it requires native libraries, so it might not fit your requirements, or you might not be able to change the driver type and must use jConnect. Apparently, the latter reason does not apply to you.

Therefore Chris has also mentioned what needs to be done when you cannot switch to SAJDBC and must use jConnect and what limits hold.

[former_SQLA_member1694877]

Hi Chris,

This thread is getting a bit confusing. I am closing it and posting a new question (jConnect "High Availability Connection String" Failing) that I hope you will help me with.

Thanks.

[former_SQLA_member1694877]

Hi Volker,

This thread is getting a bit confusing. I am closing it and posting a new question (jConnect "High Availability Connection String" Failing) that I hope you will help me with.

Thanks.

[VolkerBarth]

I'd still agree with Chris that you should better use the SAJDBC driver here...

[former_SQLA_member1694877]

Hi Volker,

I would like to move to that but I am having trouble getting that driver running with Wildfly so I've postpones that for the moment. In the meantime I'm trying to solve a problem using jConnect (where everything is on the same subnet). Can you help with that (please see new post: "High Availability Connection String" Failing".

Thanks