Solved

Synchronize Sybase databases per transaction

Posted on 2004-10-22
373 Views
Last Modified: 2008-03-04
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?
0
Question by:andyesper
    9 Comments
     
    LVL 14

    Expert Comment

    by:Jan_Franek
    Hi,

    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 http://manuals.sybase.com/onlinebooks/group-as/asg1251e/sqlug - 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 http://manuals.sybase.com/rs.html.
    0
     

    Author Comment

    by:andyesper
    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?
    0
     
    LVL 14

    Expert Comment

    by:Jan_Franek
    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.
    0
     

    Author Comment

    by:andyesper
    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.  
    0
     
    LVL 13

    Expert Comment

    by:alpmoon
    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 http://download.sybase.com/pdfdocs/awg0900e/dbsren9.pdf

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

    Good luck!
    0
     
    LVL 24

    Expert Comment

    by:Joe_Woodhouse
    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.)
    0
     

    Author Comment

    by:andyesper
    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.  
    0
     
    LVL 24

    Accepted Solution

    by:
    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: http://download.sybase.com/pdfdocs/awg0900e/dbsren9.pdf

    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.
    0
     
    LVL 24

    Expert Comment

    by:Joe_Woodhouse
    Thank you, I'm glad we could help. Let us know if it gives you any trouble, and good luck!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
    If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    857 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now