[ErikSorensen]

Searched the forum, but couldn't find an answer to this one.

We currently have 1 Sybase 12.0 ASE server (soon to be 12.5) as the db server for our web sites. We are in the middle of implementing redundancy throughout the site including firewalls, web servers, app servers, and now db server. Other than the "high availability" sybase option, what methods of failover can be used?

We are thinking of using the same method for sybase that we are currently using for our http servers, i.e. the load balancer (an Alteon AD3) sends a tcp health check to port 80 (or 4100 in sybase's case) to see if it is up. If no response is detected - it redirects traffic to the backup sybase server. The backup server would be using sybase replication services to keep the data current.

Any problems with this? Any other ideas?

Thanks,
Erik ( <-- not a dba.... )

[ErikSorensen]

^^^

[ErikSorensen]

anyone? Bueller?

[db_mate]

Erik,

I think it is not very good idea since there could be some short term network problem which may cause lag in response from Sybase; so just to switch without trying to resolve possible network problem does not sound good...

Secondly how does client access the Sybase server through central application server or from individual machines?

- You will need to have interface entry for failover server

It sounds like you have warm standby in place
If you failover to your backup site for some short term network problem it will not be easy to fallback if network response gets slower on other site... moreover you may have to give time to queues to drain...
You can not play ping-pong between database sites just based on response time…

Does it make sense ?

[RayD]

In any database standby/failover scenario you have two issues to deal with; keeping data in sync and switching clients from one server to the other. The HA solution deals with the data piece by keeping only one copy of it. The companion server "knows" about the primary server's databases and devices and brings them online in the event of a failure on the primary. The failover piece is also built in, I believe. In the config you specify the failover server and it routes traffic to it if the primary is not available. But as db_mate mentioned, I'd want to be real sure about the reason for switching.

Without this, you have two seperate data servers and two copies of the data. If one is the primary and one is the standby, you must replicate (in one way or another) the data from the primary to the standby so it's up to date in the event you need the standby server. Then to switch the clients over you can use Sybase's OpenSwitch product. It's an open server application that you can configure to route all traffic to the primary until something bad happens. You can specify the criteria to force it to cut over to the standby. Before Open Switch I think the options were 1) shutdown the primary and change the standby's IP to that of the primary 2) change the clients sql.ini files 3) write your own open server.

Neither of these solutions is load-balancing. The primary does all the work until the standby is needed, then it does all the work.

RD

[RayD]

In your scenario with the LB determining which to direct traffic to, sounds like once it switches to the backup server, it should stay there and assume it is the new primary. You'd then fix the 1st server that went down, bring it up, switch the replication direction and now your primary and standby's are reversed until the next failure.

Oh, did I mention test all of this 'till you're blue in the face? Sounds like it would work. Whether it's better to have the LB determine which server to use or use Open Switch, I can't say. Just in the early stages of working with OpenSwitch myself. We do use a LB for our web site across two Sybase servers, but they're read only so it's easy - no replication.

[ErikSorensen]

Thanks for the insight guys - I gotta look more closely at this OpenSwitch thing. The main reason I was averse to any client-side changes is that we have many web apps, using a combination of ODBC, OLEDB, and JDBC connections - and I would want to make it seamless (i.e. not change app code...)

Also, I wasn't hoping for a load balanced solution - I guess that wasn't clear. I figured that keeping all the data in sync would be a nightmare. A hot-standby setup would be great - we would really be using the "load balancer" as a failover device. Good idea of keeping it switched if it *does* failover...

I'm thinking of having a more sophisticated health check running against the sybase server - instead of a simple TCP port check maybe configure a web page to do a simple SQL query on a table, then have the page return a "server's up" or "server's down" message that the LB can read to determine when to failover. Of course if the web server goes down.....

[RayD]

I'm thinking of having a more sophisticated health check running against the sybase server - instead of a simple TCP port check maybe configure a web page to do a simple SQL query on a table, then have the page return a "server's up" or "server's down" message that the LB can read to determine when to failover. Of course if the web server goes down.....


Good idea. I do the same thing. I mean IP can be responding and the Sybase server could be up, technically, but if a simple query does not return in a reasonable amount of time, you can assume something's wrong. I do a simple 1 row select from a look-up table that there's never any transaction activity on.

[Orc]

err,
I was reading this post..got me interested..so I read the Sybase manual..
It talks about configuring Sybase Companion with Proxy databases...this way, even the secondary can be used for active transactions - so your clients can be distributed across the two servers. OF course, you need a OS level cluster system like Sun Cluster...And in OpenClient 12.0, the client will automatically switch to the companion. Any clients with transactions get a deadlock error..and have to resubmit...
I'm wondering how it takes care of both servers working on the same database -there would have to be an arbitrator somewhere..?

[willy_and_the_ci]

Hiya Orc,

With proxy databases for most commands, it does not matter whether you issue them from within the primary database or the proxy database; only the primary database is updated. This is done through Component Integration Services. Hence there is no real need of an arbitrator like in distributed transactions.

Cheers
Willy

[Orc]

thanks!
Is anyone here using this kind of config?