Synchronize Access Databases by the Record, Dfs?
Hello All, I am looking for advice on sharing an Access Database across a WAN.
We have win2k3 servers at two locations on either side of a VPN. These servers are domain controllers for a common domain broken up into two sites. Both sites need to be able to create reports from and make changes to the data in a common Access DB. With the DB stored on one server, working from the other location (across the VPN) is painfully slow. We have implemented a Distributed File System to share other data and is working very well as expected. It would work very well for our database aswell except that whenever any record is changed, the entire file must be transfered to synchronize the copy at either end. This creates an oppertunity for error in that two people at either location could be working with their respective database, and somebody's work will be overwritten.
Now on to my question; is there a utility within Win2k3, or a piece of 3rd party software that will synchronize duplicate access databases by the record in in real time?
Thanks for your advice,
- Justin
We have win2k3 servers at two locations on either side of a VPN. These servers are domain controllers for a common domain broken up into two sites. Both sites need to be able to create reports from and make changes to the data in a common Access DB. With the DB stored on one server, working from the other location (across the VPN) is painfully slow. We have implemented a Distributed File System to share other data and is working very well as expected. It would work very well for our database aswell except that whenever any record is changed, the entire file must be transfered to synchronize the copy at either end. This creates an oppertunity for error in that two people at either location could be working with their respective database, and somebody's work will be overwritten.
Now on to my question; is there a utility within Win2k3, or a piece of 3rd party software that will synchronize duplicate access databases by the record in in real time?
Thanks for your advice,
- Justin
ASKER
Hello Rick Thank you very much for the reply, I have a question for you though; where should these replicated databases reside? Should they reside in a standard folder on each server or in a folder that is being replicated by FRS (as in the distributed file system we tested with)?
As I understand your message, Access itself will handle keeping both copies in the replica set up to date? If for example we make a change to a record in the copy at our site, Access will immediatly send instructions to the replica at the other site to update that record?
How does the DESIGN change get replicated? Does Access handle that aswell?
Using Terminal Services (remote desktop) here is a good suggestion, but currently we can't justify the extra licensing costs to implement it. Another type of solution would be preferred, like the above (provided I understand you correctly).
Thanks for your help
- Justin
As I understand your message, Access itself will handle keeping both copies in the replica set up to date? If for example we make a change to a record in the copy at our site, Access will immediatly send instructions to the replica at the other site to update that record?
How does the DESIGN change get replicated? Does Access handle that aswell?
Using Terminal Services (remote desktop) here is a good suggestion, but currently we can't justify the extra licensing costs to implement it. Another type of solution would be preferred, like the above (provided I understand you correctly).
Thanks for your help
- Justin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The downside to this approach is that only one copy may make DESIGN changes to the application. The upside is that deploying these design changes are snap. Basically the developer makes the design changes then synchronizes with the server. As soon as the satellite locations synchronize with the server they inherit any design changes that get passed along at the same time they send and receive any data that has been changed or updated on either side.
If you use a replication server the process gets even better as the servers between locations can be taught to periodically compare notes and update each other with the most current design changes and data on both sides. Additionally there is a built in method for resolving conflicts (ie: two users from different lactations update the same data, same field, same record where the changes made do not match. In cases like these you have a simple interface that allows you to accept either users change as the overriding choice or offer an entirely new one of your own that overrides everyone.
This should solve your network lag issues in that everyone at each location is in effect working with a local copy that is as current as the last date/time when it was last synchronized. Furthermore these replicas serve another role. They are sort of like off sight backups. If one should become corrupted for any reason spawning a new replica is trivial and the only data lost is that data entered into one of the replicas between the time it was last synchronized and when it got corrupted.
One more benefit to this approach is that it doesn't require you to write a single line of code to implement as the feature is built into access.
**************************
If for some reason you find the above method objectionable there are other alternatives. One of course you could use remote desktop connections to connect your machine to another at a remote location. In cases where there is plenty of bandwidth this can work fine but in cases where there is not it rapidly becomes undesirable.
**************************
The replication option above, however, can be made to work even when the satellite locations lack persistent connections or for that matter a fast connection. To put this in perspective, I've deployed replicated databases in scenarios where the satellites had nothing more than a 28.8 baud modem to periodically connect themselves to the internet. Updates can take a few minutes but that’s only when the database is getting it’s updates, the rest of the time, (that being when the user is actively using the app), performance is high because they have in fact a local copy from which they do their work.