Hi All,
We have an application that may be distributed between multiple sites. Each site will have its own server and database so that they can run independently (if no Network/Internet connection) but when they are connected on the Internet we want each site server to synchronise their databases with the other servers.
This is intended to help where sites have slow/intermittent connections to Internet, but we still have one common database between all sites. It should also help with performance of application since users always connect to a local server.
We would like a Servoy programmed solution rather than using third party tools to synchronise databases as we can deploy our application to different types of SQL servers depending on Customer. We thought of using Batch Processors and RESTful web services to send the updated data between servers on a scheduled basis but are not sure if this is the best option.
Has anybody else tackled a similar problem or knows of a good way to proceed? Any advice would be greatly appreciated.
irennie:
Hi All,
We have an application that may be distributed between multiple sites. Each site will have its own server and database so that they can run independently (if no Network/Internet connection) but when they are connected on the Internet we want each site server to synchronise their databases with the other servers.
This is intended to help where sites have slow/intermittent connections to Internet, but we still have one common database between all sites. It should also help with performance of application since users always connect to a local server.
We would like a Servoy programmed solution rather than using third party tools to synchronise databases as we can deploy our application to different types of SQL servers depending on Customer. We thought of using Batch Processors and RESTful web services to send the updated data between servers on a scheduled basis but are not sure if this is the best option.
Has anybody else tackled a similar problem or knows of a good way to proceed? Any advice would be greatly appreciated.
You may read this info about clustering the application server: http://wiki.servoy.com/display/DOCS/Clustering+servers . If you have separate servers databroadcast between them will not work. So, you want some sort of databroadcast between database servers for any change that happens from your application ?
Thanks for the reply. I have looked at the clustering but my understanding is that the cluster uses common databases and repository so the servers need to be permanently connected to each other and one common sql server. I can’t guarentee that, as the sites may lose Internet connection altogether. In that case they need to cache all data changes locally and then synchronise when they get a connection. I see databroadcasting will not work if servers are separate so I think I need to program some kind of databroadcast myself that can be scheduled and retry if it cannot connect. The question is, what is the best way to do that?
irennie:
Thanks for the reply. I have looked at the clustering but my understanding is that the cluster uses common databases and repository so the servers need to be permanently connected to each other and one common sql server. I can’t guarentee that, as the sites may lose Internet connection altogether. In that case they need to cache all data changes locally and then synchronise when they get a connection. I see databroadcasting will not work if servers are separate so I think I need to program some kind of databroadcast myself that can be scheduled and retry if it cannot connect. The question is, what is the best way to do that?
Yes, you are right about application server clustering. I would first investigate the clustering capabilities of different databases (or of the one you have to use for this application). Manual approach is complicated (to make a robust synchronizing mechanism), but doable. I think you can use the onDataBroadcast event from some batch client to see the changes, cache them, and send to all other servers. Clear from cache when the server replied that data was properly handled. Data can be sent in various ways, not sure which would be the best approach. But, as I said, best option is to find this functionality in some database engine.
Thanks for the ideas. I haven’t used that OnDataBroadcast event before so it may be useful.
Using a database engine that gives this functionality is possible but the problem is that we deploy to different sql servers for different customers and so we would need to work this out for each of the different types (and on some it may not be possible). Also, there would probably be a lot more configuration work every time we install a system. But I take your point about the manual approach being complicated.
lvostinar:
Data can be sent in various ways, not sure which would be the best approach.
Assuming we can work out a programmable way of capturing and caching the data to be broadcast, the question is really how can this data be sent between the servers. I think we would need a headless batch client on each server and they would communicate with each other. Is that possible?
irennie:
Thanks for the ideas. I haven’t used that OnDataBroadcast event before so it may be useful.
Using a database engine that gives this functionality is possible but the problem is that we deploy to different sql servers for different customers and so we would need to work this out for each of the different types (and on some it may not be possible). Also, there would probably be a lot more configuration work every time we install a system. But I take your point about the manual approach being complicated.
lvostinar:
Data can be sent in various ways, not sure which would be the best approach.
Assuming we can work out a programmable way of capturing and caching the data to be broadcast, the question is really how can this data be sent between the servers. I think we would need a headless batch client on each server and they would communicate with each other. Is that possible?
Yes, is possible in different ways. Maybe the simplest way is to use sockets (see udp plugin). Or use a jsp with headless client ( data is sent as parameter in url); use http plugin to invoke the jsp on each server. Or use a webservice (here would have to investigate best implementation/which plugins wo use). Also have to think about serialization/deserialization - conversion of data.
I’ve done synchronization with Servoy Runtime, but honestly, its pretty messy and very complicated. Essentially you can write your own plugin to connect to the other server via RMI. Then in the plugin, you can convert the DataSet object into a IDataSet object (see com.servoy.j2db.dataprocessing.IDataSet) because it is serializable. Then you can send the IDataSet object over RMI and let it be processed on the other side. But again, its a lot of work because you are writing your own logic to figure out what needs to be synced. So you have to take lots of other things into consideration, like all of your table PK’s need to be UUID, and you need to track creation/modification/deletion somehow to figure out what to sync.
I would tend to agree with Scott. I have done my own synchronization for individual user of a solution (150 throughout the UK) using the servoy offline solution. This is done using a datemodified script on each record and is therefore done table by table. Even with 5 tables and not many instances of different sites modifying records, it still gets complicated.
One approach I looked at with Graham Greensall for a project was using the servoy logging function to recreate all the SQL commands issued on the local database between sycronisations. Again this potentially works, but then you also have to look at the conflicts, where a record has been modified on two separate sites between syncronisations, so again gets very messy, especially the larger the solution.
Its a difficult issue, especially for us when we have doctors who go to different venues and even in say a Regus office which is meant to have a 100Mb pipe, you cannot yet be certain of a decent internet connection every time.
Roll on the time when we just take good web connections everywhere for granted!
Scott, David,
Thanks for the advice. It would be nice if all sites had fast reliable connections. Our application can be used off-shore so we have satellite latency to deal with as well as low bandwidth and unreliable connection. That’s one of the reasons we are looking at a local server and then synchronising when connected.
If it helps, I’ve been looking at this open source tool called Symmetric DS: http://symmetricds.codehaus.org/ Its open source and is supposed to handle the syncing for you. It doesn’t support Sybase, but since Servoy now defaults to PostgreSQL, it may be something worth looking into. I’ve considered writing a plugin/module to assist with setup and integrating with Servoy, but it would be a lot of work. Maybe a new ServoyForge project if we get enough interested people to help