Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Synchronize Sybase databases per transaction

Posted on 2004-10-22
9
Medium Priority
?
438 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
Comment
Question by:andyesper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12387809
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
ID: 12395905
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
ID: 12395978
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:andyesper
ID: 12396175
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
ID: 12397219
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
ID: 12397826
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
ID: 12402104
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:
Joe Woodhouse earned 1500 total points
ID: 12407625
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
ID: 12440818
Thank you, I'm glad we could help. Let us know if it gives you any trouble, and good luck!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

WooCommerce is becoming the most powerful e-commerce plugin for Wordpress. And why not. The platform comprises of numerous core plugins that may come in handy, powerful options to make your website development task much easier.
Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

610 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