andyesper
asked on
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?
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?
ASKER
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?
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?
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.
ASKER
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.
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 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!
You need to create publications and subscriptions to them. There is a tutorial in Chapter 4.
Good luck!
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.)
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.)
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thank you, I'm glad we could help. Let us know if it gives you any trouble, and good luck!
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.