Synchronize Sybase databases per transaction

I need some help with synchronizing Sybase Adaptive Server Anywhere databases.  I need the databases to be synchronized on a per-transaction basis - whenever one row is modified on any database, it should initiate a synchronization of all of the other databases.  

The way I see it, each sync should be of only one row (the most recently changed) and so a synchronization should not take very long.  

Can someone explain how to set this whole thing up?  I'm fairly new to Sybase.  Are there any major problems you see arising from such a setup?  How do I set it so that the sync's are automatically initiated?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Hmmm. Replication (whether SQL Remote or Replication Server) are fine with your point #1, but #2 presents some difficulties.

Perhaps I'm being too literal in interpreting when you say "data needs to be up-to-the-instant", but if you mean that committing a transaction in onw server should mean it's now committed in the others, this is no longer a "replication" scenario but one of "distributed transactions".

Replication is *not* up to the instant. Replication Server is designed for a constant connection and transaction delivery (with tolerance for servers and networks failing), but even so it will have a certain degree of latency. SQL Remote (the out of the box replication in ASA) is *not* designed to be constantly connected, and has been architected for occasional batch connection.

The best place is to start is with the ASA 9 SQL Remote User's Guide at the link already posted above by alpmoon:

Chapter 2 discusses the basic concepts, and the section on server-to-server makes the points that SQL Remote is not designed for up-to-the-minute availability of data from one site to another. You can probably get the latency down to a low number of minutes.

SQL Remote uses messages to deliver the replication. By default this is just in terms of files which are copied (FTP or some similar tool) from one server to another, but you can set it up to work with email instead.

If I've understood you correctly in that you want a literally up-to-the-instant distributed transaction system where transactions are shared in seconds, SQL Remote is not the answer. (Nor is any other replication system.)

If the only requirement for "up-to-the-instant" was to avoid clashes, you *can* configure you own "collision logic" to deal with the case where two users at different offices have made conflicting bookings. Perhaps that's enough for your needs.
Jan FranekCommented:

if you are talking about synchronizing databases on one server, than it can be done with triggers - you have to create triggers on all the tables, that you want to keep in sync. For more info about triggers see - Chapter 17.

But more probably, you are talking about synchronizing between servers. Then you probalby need replication server. To learn more about replication server see
andyesperAuthor Commented:
I need to do both of the things you mentioned above.  I have three offices, each with a server in it.  I have many users in each office, who should sync to the local server on every transaction.  The servers in each office should then sync to each other on every transaction as well.  

Can you show me an example of how to write a script to sync a table to the consolidated database whenever there is a change to it?  I understand that I need to use a trigger, but what command should be called?  The above links do not answer this question.  Do I then have to write a similiar script for every single table?  Is it necessary to write seperate scripts for upload, delete, and modify, or can one script cover all three actions?  It seems that there should be not only a series of scripts in the remote database to sync to the consolidated, but also a series of scripts in the consolidated database to initiate synchronization with each of the remote db's.  Are these scripts different?
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Jan FranekCommented:
Well, it seems that you really need Replication server, not the triggers. It's separated product, so you probably have to contact you Sybase salesperson to get one. Sorry, I can't help you with configuration of replication environment as I have no experience in this field. I would recommend to attend some Sybase courses to get the knowledge - configuring and administering of such environment without it will be quite hard.
andyesperAuthor Commented:
BTW, the version I'm using is SQL Anywhere 9 - Adaptive Server Anywhere.

Why is it that I need replication server?  When I made the original decision to purchase this product, the reason that I went with it is that Sybase sales gave me the impression that it could do exactly what I need to do.  
Yeah, ASA is different than ASE. You just need to install and configure SQL Remote, which is part of ASE. You don't need to write any trigger or script. But installation and setup is not really straightforward. You should read the manual and think about what you need and which way is the best to achieve your requirements. The manual is at

You need to create publications and subscriptions to them. There is a tutorial in Chapter 4.

Good luck!
Joe WoodhousePrincipal ConsultantCommented:
ASA and SQL Remote *can* do what you want, but they won't do it well, and it will be tricky to setup.

SQL Remote is designed for occasionally-connected replication. Its best-case scenario is saving up all transactions eligible for replication, then connecting all at once and delivering everything as a single batch. It is not going to be very happy with constant trickle-feed on a per transaction basis.

I guess the best advice at this point is do a lot of reading of the SQL Remote manuals, then try it out to see if it will meet your needs.

Question - do the results of a local transaction depend on whether the remote sync succeeds? ie. can the user's transaction commit separately to the consolidated server? And can that commit separately to the servers in the other offices?

If the answer is yes, then it's worth re-examining why replication needs to be on a per-transaction basis. Allowing separate commits introduces the possibility that it will commit in one place but not in another, which means the servers would not be in sync after this transaction.

If the answer is no, then it sounds like you're actually wanting some from of Distributed Transaction Management, where nothing commits until it commits everywhere. This is significantly more difficult to achieve, and cannot be done with replication (either SQL Remote or Replication Server).

I'm pushing back on whether this really needs to be on a per-transaction basis because that strikes me as a little unusual. For example, what happens if you've lost a network link between two offices? Should all local transactions be halted because there's no possibility of syncing after each transaction?

If it isn't too much hassle to go into here in this thread, I guess I'd ask what was the business requirement for per-transaction synchronisation. Are the servers protected on hardware clusters? Is there redundancy of network connection? Can it be guaranteed that all servers will always be up at the same time? You can see where I'm going with this - even if there was a very simple out of the box solution for what you want, hardware, operating system and network considerations alone probably mean you can't guarantee per-transaction synchronisation without significant spending - at which point you're most unlikely to still be using ASA, you'd probably be using the enterprise database ASE instead.

So, yes, what you want *can* be done, but it is difficult, it easily defeated by failures at other levels, and quite possibly isn't what your business requires from the technology.

(Of course, that's easy to say from over here, and it doesn't help your immediate problem - sorry.)
andyesperAuthor Commented:
Ok, turns out there are only 3 databases - one on each server.  One of them will be the consolidated database, and the other two will be remotes.  

The idea is twofold -
1. The servers are completely redundant.  If a server at any one office goes down, the pc's at that office can connect to a server at one of the other offices via point-to-point T1.  The idea being that if one or two servers fail, business as usual can continue.  

2. There is some time-sensitive data that needs to be replicated immediately.  Two users could schedule the same appointment time from different locations.  Thus, at least for certain tables, the data needs to be up-to-the-instant.

The system is built around reliability, which is the reason for the three servers.  I'm not worrying about a network failure at the moment.  

Please explain how this *can* be done.  
Joe WoodhousePrincipal ConsultantCommented:
Thank you, I'm glad we could help. Let us know if it gives you any trouble, and good luck!
All Courses

From novice to tech pro — start learning today.