Fault tolerant SYBASE part deux 
Author Message
 Fault tolerant SYBASE part deux

INTRODUCTION -

A couple of weeks ago we sent out a general request for information
on SYBASE fault tolerant approaches and received some replys.  It seems,
though, that on-line, real-time, non-stop, fault tolerant, etc. etc.
designs are not being done, or are not needed most places, or more
probably, the people who know how aren't 'fessing up.  

We have made the simple log transfer approach work:
   o  dump the primary data base.
   o  ftp or whatever the dump file to the backup computer.
   o  load the secondary from the dump file.
   o  do not do anything that will cause a log record to be written
      on the secondary.  This would include inserts, updates, or
      deletes of rows AND/OR ANY ERROR MESSAGES.  Otherwise you must
      go back to step one.
   o  periodically (every 5 to 10 seconds):
      .  DUMP TRAN' the primary.
      .  ftp the dump file to the secondary computer.
      .  LOAD TRAN' from the flat file to the secondary database.

Whenever there is a primary system failure switch over to the secondary.
There are various different problems and procedures for switching the
communications and applications.  These approaches are not really SYBASE
specific and vary wildly with architecture.

The above approach has a serious defect.  You looses the transactions not
yet ftp'ed and/or are still in the primary machine.  Of coures Sybase's
Replication Server has the same defect but not nearly as seriously.

As mentioned in the first request-for-information the SYBASE two phase
commit fixes the above mentioned problem but decreses fault tolerance since
both systems stop if either has a problem.

THE QUESTION -

The question is has anybody thought of, succeeded at, or heard about
anything like the following?

It seems like what we really want to do is write all of our inserts, updates,
and deletes to both data bases at the same time.  It is very simple if
using dblib functions to issue an update command to one SQL server then
reissue the same update command to a second SQL server.  

If the primary system gets "nuked" switch to the secondary. They will
be in syncronization with non of the lag characterized by the log transfer
approach.

If the writes to the secondary start to fail the SQL should be send to
a queue.  This queue could be a table on the primary side.  When the
secondary "wakes up" the SQL in the queue could then be applied to the
secondary bringing it back up to synch'.

Taking the above as a rough outline, we coded (protyped) this idea.
It seems to work but we are not satisfied that we have an "industrial
grade" approach.  There are lots of details, exception conditions, etc.
that it does not handle. Its only a prototype.

One reason this may work for us and not for other SYBASE installations is
that we coded all our business functional C programs with no dblib and
no stored procedures. Everything goes through a single type of data
base server module that in turn interfaces with SYBASE.  The data base
server module translates an RPC to the appropriate SQL.  We would need
to change the database server module.

Has anybody tried this approach?
Please respond to:
    o   This article directly in the news group, or




Sat, 30 Dec 1995 04:24:31 GMT
 Fault tolerant SYBASE part deux

Quote:

>THE QUESTION -

>The question is has anybody thought of, succeeded at, or heard about
>anything like the following?

>It seems like what we really want to do is write all of our inserts, updates,
>and deletes to both data bases at the same time.  It is very simple if
>using dblib functions to issue an update command to one SQL server then
>reissue the same update command to a second SQL server.  

AFIC Computers Inc. has a product called MSO (Multi Server Option)
which provides many of these capabilities.  It is based on the use
of multiple replicated servers which provide local read/only access.
Any SQL which might cause a updates/inserts/deletes is broadcast
to all the replicated servers and applied to each in order.  This
is a very simple description of the system:  they have done a lot
of work on reducing broadcast overhead, increasing reliability, and
providing recovery and failover facilities.  It may not suit every
application.  I do not have personal experience using their product
but it seems to be well designed.  They can be reached in NY at
212-406-2503 (voice) / 2415 (fax).

The High Availability for Sun product is available from Open Vision.
They can be reached at:

Open Vision
7133 Koll Center Parkway
Pleasanton, CA   94556
Voice: 510.426.6400 or 800.223.OPEN(6736)
Fax:   510.426.6486

The High Availability product provides automatic restart or failover
of critical systems.  It uses two Sun systems which share disks
to provide "highly available" services.  Definitely worth considering.



Sun, 31 Dec 1995 19:19:03 GMT
 Fault tolerant SYBASE part deux

Quote:

> The question is has anybody thought of, succeeded at, or heard about
> anything like the following?

> It seems like what we really want to do is write all of our inserts, updates,
> and deletes to both data bases at the same time.  It is very simple if
> using dblib functions to issue an update command to one SQL server then
> reissue the same update command to a second SQL server.  

> If the primary system gets "nuked" switch to the secondary. They will
> be in syncronization with non of the lag characterized by the log transfer
> approach.

Sounds like a lot of work maintaining two databases...  I assume you will need
to use the expensive 2-phase commit protocol to insure integrity of both databases.  
Couldn't you write an open server application to act as a SQL Server?  The only
difference would be that the Open Server would handle data coordination between
the primary and secondary SQL Servers.  This sort of sounds like a transaction
monitor.  In fact, if Sybase supported the XA interface, couldn't we use a product
such as Tuxedo?

At the Sybase User's Show in San Jose, DG demo'd a high-availabilty (HA)
solution.

The best part of the DG solution is that you don't have to buy a third party
product.  Bundled with the UNIX 5.4 operation system is a set of Operator
Initiated Failover (OIF) software and Machine Initiated Failover (MIF) software
which allows you to set up your environment such that SQL Server is automatically
restarted on a secondary AViiON server should the primary AViiON server fail.  
The Sybase database resides on a CLARiiON disk-array subystem, which is
dual-ported to both AViiON servers.

One CLARiiON supports up to 20 disk modules (500MB, 1GB, or 2GB drives), that
can be configured in various RAID combinations.  HA for the database is usually
achieved by placing the database devices on a RAID-5 stripe and the log devices
on a RAID-1 (mirrored pair) stripe.  

The CLARiiON is a dual-initiator configuration disk subsystem that provides
SCSI-2 interfaces.   The OIF/MIF software uses a simple give-and-take scheme.
The system that currently owns the physical disk, and has set up the system
failover databases can give the physical disk away to the other host.  The other
host in the dual-initiated configuration can take the physical disks it does not
own.  Management of the system failover database (flat files) is done via
the sysadm utility (GUI or character-based interface).

The Sybase software and interface file reside on the CLARiiON.  If the primary
server fails, the secondary server detects the failure and immediately takes
ownership of the disks.  The secondary server also re-starts the SQL Server.
Once the primary server is back up, you can have the secondary server give
control back to the primary server.  Or, you can keep the primary server
in standby mode so that it automatically takes control if the secondary server
now running the production stuff fails.

Typically, customers actively use both servers.  The primary server runs
production stuff while the secondary is for development and misc stuff such
as printer services.  If the primary fails, the production stuff is
migrated to the secondary server.

_________________________________
Dale Benedict
Data General Corp
(919)248-5994      

UUCP: mcnc!rti!dg-rtp!benedict

I speak for myself, not my employer...



Wed, 03 Jan 1996 02:14:46 GMT
 Fault tolerant SYBASE part deux

...

Quote:
>The Sybase software and interface file reside on the CLARiiON.  If the primary
>server fails, the secondary server detects the failure and immediately takes
>ownership of the disks.  The secondary server also re-starts the SQL Server.
>Once the primary server is back up, you can have the secondary server give
>control back to the primary server.  Or, you can keep the primary server
>in standby mode so that it automatically takes control if the secondary server
>now running the production stuff fails.

Is this done automatically? Transparently?

Seems to me that a *lot* of work would need to be done in order to
switch from one server to the other while preserving context. If I
cannot do that, then it is not really fault tolerant. IMHO, fault
tolerance in software is not such a good idea. It makes software
mind-bogglingly complex. Stratus has what I consider an excellent
implementation of fault tolerance in hardware by redundancy. In
mission-critical apps where such levels of fault tolerance is
required, the extra money that you have to fork out might be
justifiable.

Just my $0.02.

Regards
Rajappa Iyer
--

#include <std_disclaimer.h>
        Good day for water sports; take a bath with a friend.



Sat, 06 Jan 1996 04:42:02 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Fault Tolerant Sybase

2. datetime woes (part deux)

3. I don't grok DTS, part deux

4. Trimming the Fat, Part Deux ...

5. creating an updatable cursor - Part Deux

6. SQL in BNF - Part Deux

7. SQL in BNF - Part Deux

8. Fault-tolerant DTS processes

9. Fault-tolerant SQL Server 2000 Transactional Replication

10. Fault tolerant SQL Servers

11. Fault tolerant solution

12. Help: Fault tolerant SQL servers


 
Powered by phpBB® Forum Software