• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Microsoft FRS

Hello,

We are using FRS to replicate files/folders between a remote site and our main office.  We've been having a little issue lately where somebody has an Access database open simultaneously at the main office and the remote site which is causing data to be lost.  I am soliciting ideas on how to prevent this from happening.  Maybe a script file that tells them if it is open in either place depending upon their location?  I dunno.  That's why I'm creating this post.

Thanks,
Cheese
0
cheesebugah
Asked:
cheesebugah
  • 13
  • 8
  • 5
2 Solutions
 
SteveCommented:
access databases are not compatible with replication of any form. You can use it as a backup if necessary but only if the database is not used during the replication time (eg overnight)

Only option is to make one read only and use it as a backup of the original. If you allow changes at both sides, the database will not replicate.
0
 
cheesebugahAuthor Commented:
totallytonto,

Well that is not entirely true as we have been using it in this manner for several months and replication has been working flawlessly up until yesterday.  I am aware of MS's recommendation to not replicate databases with FRS.  We tested this extensively before deployment and replication worked flawlessly regardless of which location the database was modified at.  However, the problem arises when it is open at both locations.  I believe if I can prevent that from happening, then we should be okay.  Do you have experience with replicating Access DB's?

Thanks,
Cheese
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

Have a look at this article!

It may throw some light on your problem - http://www.databasedev.co.uk/multi-user-applications.html

Hope it helps u...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SteveCommented:
Cheese,

Yes, It is entirely true. replication of any large, single file fails when it is changed at both sides of the replicaiton before the replication can copy the file across.

Fact

We tested this extensively before deployment and replication worked flawlessly regardless of which location the database was modified at.

saying it works 'flawlessly' is irrelevant because you were not testing it in the environment it would be used in.
If you edit it one copy, patiently wait for it to replicate, and then edit it the other copy it will work fine. but that is not a real environment and is not a valid test.

Do you have experience with replicating Access DB's?

Yes, I have many times and it always fails. I've dealt with many companies whose software is based on a single database file, not just Access, and it cannot be replicated in a multi-writable environment without risking data loss.
As advised above (and by Microsoft's recommendations that you are already aware of), Access databases should not be replicated.

The only situation in which it can work is if one copy is a backup or read only, which I have had working fine on several occasions.

As you are effecitvely using more than one copy of the same file, I doubt you'll find a way to stop someone accessing one copy if the other is already open, but that is your only way to get around this issue.
If you do find a solution, you'll also need to build in a delay. This is because the replication needs to complete after the user finishes editing the file before you can let someone open the other copy or you will still get a conflict (where both copies have changed and FRS will not know which version to keep.)
0
 
cheesebugahAuthor Commented:
roopeshreddy,

Thank you for the article.  Those selections are already in place.  I greatly appreciate your help.

Thanks,
Cheese
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

Oh k!

How about this article - http://www.databasedev.co.uk/multi-user-application-record-locking.html

Since you already have these settings, it should be working fine!

My Suggesstion is to have different copies of files in different places and you can replicate at once in maintenance time!

Hope it helps u...
0
 
cheesebugahAuthor Commented:
roopeshreddy,

I'm not sure that your suggestions follow along with what is actually happening here.  We have two databases that are being replicated.  They are the same database, but in two different locations using Microsoft's FRS to replicate them.  How are the two locations going to act as one?  I just don't see this applying to what we're doing here.  I could see it for one location of the database, but not two separate locations.

Thanks,
Cheese
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

Exactly! My suggestion is having same databases in two different places and update each other in the maintenance time!

Hope it helps u...
0
 
cheesebugahAuthor Commented:
Hi,

I greatly appreciate your help.  What do you mean by maintenance time?  We are replicating in real time.

Thanks,
Cheese
0
 
cheesebugahAuthor Commented:
Hi,

How do the two separate databases indentify if one or the other is open?  Would this apply to anybody who opens either of them?  I see that this fix applies to the program itself.  Maybe an explanation is in order?

Thanks,
Cheese
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

Maintenance time is nothing but the server down time, which will be for 30 mins-1hr! Every database application will have maintenance time(may be monthly)!

In the Maintenance time, you will clean up the database or replicate etc.,

Hope it helps u...
0
 
cheesebugahAuthor Commented:
Hmmm.......well this is my situation.  We have our main office and a remote site.  We have the same database in each location being replicated via FRS.  We need them replicated in real time so if it is opened at either location, current data is there.  We cannot wait for maintenance time to replicate the databases.  What I am trying to prevent is it being opened in both locations at the same time, which causes problems.

Thanks,
Cheese
0
 
cheesebugahAuthor Commented:
totallytonto,

Let's say I make the database read only at my office location.  I am hoping that those permissions will not be replicated to the remote location.  Would that be a true statement?

Thanks,
Cheese
0
 
SteveCommented:
Hi Cheese,

I believe FRS can be amended to stop it copying permissions, but its normally easier to put the restriction in a place that wont be replicated.

Assuming you have different shares for different purposes, just set the share to read only, as this requires no changes at all to the files themselves.

One alternative to all this:
In a situation where we had trouble with one of these Access-style databases, I've sucessfuly used a terminal server in remoteapp mode in the past. The users use their software in remoteapp mode, but are all actually accessing the DB in a single location vie the terminal server. as a bonus, it also helped when upgrading individual PCs without worrying about compatibility with the old application in use becuase the TS is the only machine actually running the software.
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

Yes! Since you applied the permission to one, so it won't affect the other!

You can set the same kind of permission for both the databases in two location!

Hope it helps u...
0
 
cheesebugahAuthor Commented:
totallytonto,


We too have used the remote desktop as you suggested.  As a matter of fact, we are currently using it for many remote users who are not at a large jobsite (we're a construction company).  It works great!  For a larger jobsite which this one is, we like to put a server at the jobsite and put in a point-to-point connection and use FRS.  It really speeds up things for the onsite users and they don't have to use VPN either.  It makes their jobs easier and more productive.  Our only problem is this dang Access database which is integral to job.  I cannot use the share permissions as you suggested as there is a lot more data on the share that is used by other personnel.  I get where you're going with the idea though.  I'll look into it.  Thanks again for your suggestions.  I'll keep you posted

Cheese
0
 
cheesebugahAuthor Commented:
roopeshreddy,

I'm not sure I understand your post?  It seems a bit contradictory to me.  I greatly appreciate the help though.  I think you're trying to tell me that we can have different permissions at both locations, but not sure.  I can't set read only permissions in both locations for obvious reasons.

Thanks,
Cheese
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

No, Set same permissions in both the places. I mean the Record Locking, if it is in editable mode!

You can have a maintenance time every day in your case to replicate! In the maintenance time, make sure no record is updating in both the location and replicate accordingly!

I see this is the feasible solution as of now!

Hope it helps u...
0
 
cheesebugahAuthor Commented:
roopeshreddy,

It is not feasible if we are looking for real time, current, up to date replication to occur, which is what we are ultimately seeking.  If that can't be done, then we'll seek other methods of having current data available in both locations.  Right now, remote desktop is looking to be the only viable option unless somebody has something.  We have been using a terminal server in the past and were trying to go to something new and slick.  The last time we used FRS was back in 2003 with horrible results.  We figured we'd give it a try again hoping things had improved, which they have, but not for databases, which is an integral part of the puzzle unfortunately.

Thanks,
Cheese
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

Yeah!

Finally what you think will be the feasible solutions for you?
0
 
SteveCommented:
Hi Cheese,

As an aside, it's worth upgrading to DFS instead of FRS in general as it is a much more efficient system for replicating data (available on Server 2008 R2 onwards I think), but it wouldnt help with your Access DB issue.

Where possible I'd recommend seperating your shares for exactly this reason, so you can control elements for different sets of data independantly.
Otherwise, you are quite right that replication would be more efficient for your circumstances that a TS, its just this access DB issue that will get in the way. Dont suppose there is a SQL alternative to the Access DB is there?

I hope my advice has been useful, even if it wasnt the answer you were hoping for. Sorry!
0
 
cheesebugahAuthor Commented:
totallytonto,

I greatly appreciate your advise.  It has helped us come to a decision on this sooner than if we had continued to try to make this shoe fit.  I have one more resource to pull advise from and then I'll submit more comments.  Thanks again for all of your expert advice.

Thanks,
Cheese
0
 
cheesebugahAuthor Commented:
Can anyone recommend a third-party software that will accomplish this task without any hiccups?
0
 
Roopesh ReddyIT AnalystCommented:
Hi,

No idea of third party software!

After some googling, i got this link- http://support.microsoft.com/kb/182886

Check whether it helps u!
0
 
SteveCommented:
Blimey, Havent seen that document in a while! (1998??)

That is a fair point that I hadnt considered, as the Access system does have limited replication facilities built in but I believe they have been diminished in newer versions of Access (2007 onwards I think)

Problem with this is that the DB needs to have been designed for replicaiton in the first place as the tables etc. need to be setup in a very specific way for replicaiton to work. Retrospectively making an existing DB work with the built in replicaiton may be a tough job, and you'll probbaly need a fair bit of Access programming knowledge to get it to work. You may need to contract an Access DB programmer if you go down this road.
0
 
cheesebugahAuthor Commented:
We have decided to use our Citrix environment to publish the Access database through, which is essentially the terminal services solution provided by totallytonto.  The suggestions provided by roopeshreddy are valid here, but just don't apply to our situation.  I will split the points though.

Thanks,
Cheese
0

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 13
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now