Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Blundering around with Replication ... any advise |
Fri, Apr 22 2011 6:40 AM | Permanent Link |
Adam Brett Orixa Systems | Apologies in advance for the vagueness of this post ...
I have been working on linking my EDB & DBISAM systems to the Amazon Cloud. Connecting this way is surprisingly easy using a conventional Client Server model. However, I want to develop a brief-case model, i.e. a framework for applications where each user carries the database around on their own computer and can add data without need for a permanent connection. The framework then periodically (or when a connection is available) connects to the "main" database (probably on the cloud) and uses a replication model to update the cloud server with new stuff from the user, and update the user with new stuff. There is a useful example of this on the EDB site, the "salesperson" example project, using EDB's excellent STORE & replication capabilities. ... I haven't used the advanced SQL / DML capabilities of EDB much, so I am very unsure how best to pursue making this work. I want it to be reasonably invisible to the user. I am considering having a JOB, which runs every 30 min while the user is signed on, or when a user chooses to "Save", or which runs at the start & end of a users session. 1. Can EDB run a replication job, copying the contents of STORES up to a server & back from a server while the user is logged on & editing data, or is it best to do this while the user is inactive, i.e. call the user to "Save" and run the ? 2. Beyond this what happens if replication jobs for 2 users overlap? ... i.e. user 1 starts a replication read / write, and during this a second user does the same. The "salesperson" example suggests deleting the replication file from the STORE after use ... is it possible that additional changes are already logged in this STORE & might therefore be lost? 3. A tougher question: Changes in DB structure, (I realise this is a big question) if a piece of SQL is run on the server which alters the DB structure (for example adding a new TRIGGER) does this change in the DML SQL of the DB propagate through the STORE or not? 4. The "salesperson" example suggests the fairly straightforward model of incrementing ID's according to a users "seed ID" ... so that Tables in User 1's DB start from ID 1, while tables in User 2's DB start from ID 1,000,000. This seems really clean & straightforward. Is anyone using a different mechanism than this & if so why? Thanks in advance for any responses. |
Thu, Apr 28 2011 10:20 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< 1. Can EDB run a replication job, copying the contents of STORES up to a server & back from a server while the user is logged on & editing data, or is it best to do this while the user is inactive, i.e. call the user to "Save" and run the ? >> Sure, EDB uses the proper locking to ensure that the saving and loading of updates works seamlessly. << 2. Beyond this what happens if replication jobs for 2 users overlap? ... i.e. user 1 starts a replication read / write, and during this a second user does the same. The "salesperson" example suggests deleting the replication file from the STORE after use ... is it possible that additional changes are already logged in this STORE & might therefore be lost? >> You're not quite understanding how the replication works with respect to update files. Update files are generated by the SAVE UPDATES statement, and are a capture of the logged updates for a set of published tables since the last SAVE UPDATES. The logged updates themselves are stored in the .edbpbl file attached to each table. Once an update file has been successfully loaded by a specific database, it is perfectly safe to delete it. This has no effect upon any other update files that may have been added to the incoming store since that time, and has no bearing on any updates currently being logged for the published tables in the database where the updates are being loaded. In fact, the load operation *may* cause more updates to be logged for the published tables, but that, again, has no bearing on the update files in the incoming store. << 3. A tougher question: Changes in DB structure, (I realise this is a big question) if a piece of SQL is run on the server which alters the DB structure (for example adding a new TRIGGER) does this change in the DML SQL of the DB propagate through the STORE or not? >> No, DDL changes are not propagated at this time. << 4. The "salesperson" example suggests the fairly straightforward model of incrementing ID's according to a users "seed ID" ... so that Tables in User 1's DB start from ID 1, while tables in User 2's DB start from ID 1,000,000. This seems really clean & straightforward. Is anyone using a different mechanism than this & if so why? >> If you want to use IDENTITY columns, then the only way is to partition them into logical groups by either offsetting them with a base value, or doing like the sales quote sample application. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, May 16 2011 2:36 AM | Permanent Link |
David Cornelius Cornelius Concepts | I haven't been on the newsgroups for a while and just saw this post and
thought I'd chime in with a few things. I'm just finishing a major update to an application that will take my customer's small application from remote-only browsing, reporting, and editing, to local browsing and reporting with remote editing. It's been a long struggle and I've come to understand why so few people attempt it: it's REALLY hard to get it right! (Not from the database engine side, but from the application side managing the database.) I started out with segmented auto-inc IDs like Tim was describing. It would've worked except somehow, someone didn't upgrade in the right order or there was a script that had a bug or something. In any case, the IDs got mixed up and I immediately had a big mess on my hands with duplicate IDs, missing database updates and so on. I ended up using GUIDs for ALL primary keys--that should forever eliminate any such problems I had before. The next update still had other problems though. With multiple people editing locally and the customer getting more adept at using their software and bouncing around from record to record, we had problems with edits getting overwritten. I tried the idea of putting a record into a "check-out" mode, but there were headaches involved with that and I didn't go down that path very far. I've settled on the idea of remote editing (insert/update/delete) and local browsing and reporting. If a record gets added or changed, it has to happen on the remote server only and it will trigger an immediate SAVE UPDATES. Then the client computer will download it and refresh the data set. I'm not completely sure how it's all going to turn out yet as I'm still finishing it, but I think it will allow the customer to have immediate changes reflected everywhere while allowing me to finally get some sleep! For a core office group that works directly against the server with remote salespeople only ADDING new records occasionally, it's fairly simple. In my customer's situation, there's only one person in the "office" with the central server. All other users are spread out across the US and they all need to be able to add/edit/delete any record at any time--and of course see each other's changes immediately with the speed of local browsing and reporting. That has been my nightmare. I wish you luck--and LOTS of design and testing. David Cornelius Cornelius Concepts "Adam Brett" wrote in message news:4803B3AD-ED6E-4D7C-9A5A-8AAA14093885@news.elevatesoft.com... Apologies in advance for the vagueness of this post ... I have been working on linking my EDB & DBISAM systems to the Amazon Cloud. Connecting this way is surprisingly easy using a conventional Client Server model. However, I want to develop a brief-case model, i.e. a framework for applications where each user carries the database around on their own computer and can add data without need for a permanent connection. The framework then periodically (or when a connection is available) connects to the "main" database (probably on the cloud) and uses a replication model to update the cloud server with new stuff from the user, and update the user with new stuff. There is a useful example of this on the EDB site, the "salesperson" example project, using EDB's excellent STORE & replication capabilities. ... I haven't used the advanced SQL / DML capabilities of EDB much, so I am very unsure how best to pursue making this work. I want it to be reasonably invisible to the user. I am considering having a JOB, which runs every 30 min while the user is signed on, or when a user chooses to "Save", or which runs at the start & end of a users session. 1. Can EDB run a replication job, copying the contents of STORES up to a server & back from a server while the user is logged on & editing data, or is it best to do this while the user is inactive, i.e. call the user to "Save" and run the ? 2. Beyond this what happens if replication jobs for 2 users overlap? ... i.e. user 1 starts a replication read / write, and during this a second user does the same. The "salesperson" example suggests deleting the replication file from the STORE after use ... is it possible that additional changes are already logged in this STORE & might therefore be lost? 3. A tougher question: Changes in DB structure, (I realise this is a big question) if a piece of SQL is run on the server which alters the DB structure (for example adding a new TRIGGER) does this change in the DML SQL of the DB propagate through the STORE or not? 4. The "salesperson" example suggests the fairly straightforward model of incrementing ID's according to a users "seed ID" ... so that Tables in User 1's DB start from ID 1, while tables in User 2's DB start from ID 1,000,000. This seems really clean & straightforward. Is anyone using a different mechanism than this & if so why? Thanks in advance for any responses. |
Mon, May 16 2011 4:53 PM | Permanent Link |
Uli Becker | Adam,
> I have been working on linking my EDB & DBISAM systems to the Amazon Cloud. Connecting this way is surprisingly easy using a conventional Client Server model. That's interesting! Have you some more information on how to do this? Regards Uli |
Fri, May 20 2011 12:13 PM | Permanent Link |
Adam Brett Orixa Systems | Dear David,
Thanks for this feedback! I am still really only at the stage of implementing EDB applications that work at each of my clients, but replication / ability for staff to remote work is going to be important in the future. I am seriously considering GUIDs, because it does basically solve the problem ... though you lose some useful things, like sequential numbers. I can imagine a situation where a new user is taken on, installs the system messes up their primary seed ID & the whole thing explodes ... which would be a disaster! Several of my systems are only in 2 or 3 offices though, so there would not be random installation happening all the time. The problem is requirement creep ... as soon as I give my customers replication between offices I know they will want to be able to disconnect staff members (home-workers etc.) & have them work on their applications & replicate to it ... GUIDs help to solve this problem longer term. Also, I have structured all my tables to try to avoid a situation where data is _ever_ overwritten. i.e. a "Customers" object consists of a root table "Organisations" with a Customers table beside it & 1 to many relationships to other tables called things like "contacts" "phones" "addresses" etc., in that way my staff can update more volatile details without over-writing each others work. ... I am hoping that this segmentation will help to reduce the problems you talk about ... time will tell. |
Tue, May 24 2011 1:11 PM | Permanent Link |
David Cornelius Cornelius Concepts | Yes, GUIDs will help significantly.
There are other ways to keep track of sequential numbers. I inherited my application from an old FoxPro for DOS program and all the contacts started with either C or V (customer or vendor) and then had a sequential number. At that time, the 5 users of the program just kept track among themselves, but wanted something better when I took over the project. I accomplished that by have a general "system" table that kept the latest customer and vendor numbers (separate) and any time anyone wanted to add a new contact, it would first query that system table to find out what the next assigned number would be. I still use that in the application, but it's not a primary key--the GUID is. My first attempt segmented the IDs, and like you worried below, something got messed up and everything exploded! I had to quickly revert everyone back to working directly over the internet connected to the remote database (very slow). I recently tried browsing from the local computer and editing only on the server. But I had to get changes down immediately so setup triggers to instantly replicate for any add/edit/delete. Of course that turned out to be painfully slow for any simple edit. I've come around to an older idea I had but with a small enhancement. I am setting up a "check out" system where each table will have two new fields: a BOOLEAN "Locked" and a GUID "LockedBy". Then when a user wants to edit a record, the program will set these two values on the server and make the changes locally. When finished, the locally edited copy will clear this "lock" and the replicated record will go up to the server, thus clearing the lock for everyone else. (Just before editing, the "Locked" value will be checked, of course.) This also assumes the server and all the clients are saving updates and exchanging with the server frequently. I should have this ready to test in a few days. David Cornelius Cornelius Concepts "Adam Brett" wrote in message news:25B64C8E-9451-45B6-900D-367C50552ED1@news.elevatesoft.com... Dear David, Thanks for this feedback! I am still really only at the stage of implementing EDB applications that work at each of my clients, but replication / ability for staff to remote work is going to be important in the future. I am seriously considering GUIDs, because it does basically solve the problem ... though you lose some useful things, like sequential numbers. I can imagine a situation where a new user is taken on, installs the system messes up their primary seed ID & the whole thing explodes ... which would be a disaster! Several of my systems are only in 2 or 3 offices though, so there would not be random installation happening all the time. The problem is requirement creep ... as soon as I give my customers replication between offices I know they will want to be able to disconnect staff members (home-workers etc.) & have them work on their applications & replicate to it ... GUIDs help to solve this problem longer term. Also, I have structured all my tables to try to avoid a situation where data is _ever_ overwritten. i.e. a "Customers" object consists of a root table "Organisations" with a Customers table beside it & 1 to many relationships to other tables called things like "contacts" "phones" "addresses" etc., in that way my staff can update more volatile details without over-writing each others work. ... I am hoping that this segmentation will help to reduce the problems you talk about ... time will tell. |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |