Database speed in two locations 1

[daneharnett]

Hi,
Ive got a client with two branches. At the moment they are just copying the database from one branch and overwriting in the other branch... this is to update...

what we would like to do is to be able to have the database kept at the main branch and access the data remotely thru a VPN or the internet...

What we are worried about is the time it takes to download the data and when/if it will automatically pickup changes that are made at the other branch at the same time...

Anyone got any input?

Thanks in advance

 

[gusbrunston]

[tt]
Hi, Dane:

I maintain a database for an office where I can't stand the working conditions! I access the network over a VPN, using Windows 2000 on my computer, the network stations are Windows XP Professional. I can add and edit records over the VPN, opening and closing forms almost as fast as if I were at the target station. I think it would be even faster except for the time it takes to paint my screen over the VPN. I have broadband at my computer at home, and there is business broadband at the office.

What the VPN does is simply get me on the office network from my computer at home. In your case, one office would have the server with the data (the back end), and the other branch office would have one or more VPN's logging them into the network as if they were simply additional boxes at the "main" branch. The logging in branch would not even need the Access programs on their computers...they'd be using the programs on the workstation(s) at the "main" branch.

When I want to publish an update to the front end, I upload the new front end to "xdrive.com" and then using the VPN I download it to the office, where all the stations can copy the new front end revision to their own boxes.

You said, "What we are worried about is the time it takes to download the data and when/if it will automatically pickup changes that are made at the other branch at the same time..." You don't have to have the data at both locations. I don't download any data. I simply use a front end machine at the other end of the VPN to manipulate the data. If I want a copy of the data at my computer for some reason, I upload a copy of the back end to xdrive and then download it from xdrive to my box. The only reason to do this is if I want the data handy to work on revisions of the software.

Backup of course, is of paramount importance. We back up several times a day to a zip drive, which will soon be replaced by a USB external hard drive that will hold the daily backups of two or three years. Users at the office can backup, or I can do the backup over the VPN.

This VPN saved my sanity!

I hope this is helpful. If you have any questions about my setup that I haven't addressed, give a holler.

Cheers,[/tt]




[tt]Gus Brunston - Access2002(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[daneharnett]

gusbrunston,

Thanks for your input.

what i mean by 'download data' is the loading time of the data over the internet... or the loading of the data into the remote computers ram...

the remote computer must have access on it or else it wont be able to run the database...

you may be talking about VNC, which is not what we want to do... because the ppl in the remote office must be able to print off reports and the like to a computer at thier office...

We are thinking in the end we will be using a web-based interface and having the ppl in the remote office using it that way... but that is a while off yet...

Anything else?

 

[gusbrunston]

[tt]
Well...

You wrote, "...what i mean by 'download data' is the loading time of the data over the internet... or the loading of the data into the remote computers ram...

the remote computer must have access on it or else it wont be able to run the database..."

Wrong.

I do not load the data over the net. I simply use the front end on a box at the office to manipulate the data at the office, through the VPN. I don't use a VNC--I use a VPN. I repeat, I don't even need the front end on my remote computer. The data never has to go into the ram on my home box. I can view, add, edit the data, and the data never touches the net, except as a display on my monitor.

I can print reports, etc. either on a printer at the office, or on a local printer connected to my home computer. I simply choose the printer as I would if I were choosing between two or more printers attached to a standalone computer.

In effect, I am logged on as a work station just as if I were sitting in front of a workstation at the office.

It works for me. Sorry that I can't explain it better.[tt]

[tt]Gus Brunston - Access2002(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[danvlas]

Gus, you're using something like:
-Remote Desktop (cheapest, but needs Windows XP)
-PC Anywhere (good but expensive)
-Remote Administrator (good and relatively cheap)

That is in effect a server on the 'work computer' which deals with transmitting the desktop to the remote location and getting the mouse movements/clicks and keyboard strokes from the remote computer.

This is sometimes good, but other times it is impracticable: remote user takes full control over the workstation and no other user can get into it 'peacefully'.

Dane: Access (as client) is not designed to work over VPN or Internet. It can do it, but it's painfully slow. Access is a great desktop database system that also works acceptable on a LAN, provided the number of users is relatively small. But not over the Internet.
ASP is the way to go to use the Access back-end file over the Internet or VPN. But if you move to ASP, you may consider upsizing to SQL Server, as it's more stable, more scalable and supports much more simultaneous connections than Access.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[gusbrunston]

[tt]
Dear Daniel:

Thanks. Yes, I am using Remote Desktop. XP at the office, Windows 2000 at home. A dedicated work station at the office. There is nothing painfully slow about it, unless I try to edit and save a table or form. Using Pentium IV's equipment with lots of memory.

Thanks again for your pointing out the right term.

Cheers,[/tt]

[tt]Gus Brunston - Access2002(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[gusbrunston]

[tt]
Once more, there's nothing slow unless I try to edit the "design" of a table or form over the remote connection.[/tt]


[tt]Gus Brunston - Access2002(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[danvlas]

I didn't say it was slow for this type of connection (which is not an Access connection). I'm using Remote Administrator myself and I'm absolutely happy with it, even though I can't print the reports on my home printer.

I said it was sometimes impracticable.
Moreover, user who's currently at the office desk will be frightened to death if you start moving the mouse, clicking buttons, starting/stopping applications without him/her knowing what's going on

Trust me, I did that and the secretary thought it was her fault-of course the first thing that came to her mind: VIRUS!

You can do that only if you're sure nobody else is using the computer.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[daneharnett]

Yeah,
what i meant when i said VNC was a program that is similar to remote desktop etc.. VNC is a free program that allows the same as remote desktop check it out at

http://www.tightvnc.com

...

Back to the problem,
We dont have a dedicated computer for the remote office to access the db...
For now we would like to be able to remotely access the database thru access while a webbased interface is being built...
How will this run? with record locking and the like...

Thanks for everyones help so far

 

[danvlas]

What's the bandwidth between locations?


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[daneharnett]

danvlas,
I suppose it would be 256/64 ADSL at either end...
But could be dial up... im not sure about the remote branch.

 

[danvlas]

It will be very slow.
And the locking thing will slow down even people working in the office where the db is located.

You may have to consider replicating the database, then ftp or email the replica, then synchronize it with the master dd. I've never done this, maybe someone who did would give you more details.

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[mikestan]

Thanks for the input.
The organisation is only very small and doesn't have the resource for SQL and a dedicted server (at this stage).
The databade was on a standalone PC which we've just updated to 2 systems. Both offices office will be updated to 256 ADSL with a VPN.

We want to use the VPN to access the data live (MS Access)from the remote location (stock info & booking data).
How can we keep the network traffic over the VPN to a min?

 

[rockgeneral]

This thread has been inactive since September 10, 2003 so if the problem has already been resolved please disregard.

My partner and I have been developing Access applications for use over the LAN for sometime. We currently have some projects that require an Access application be used over the VPN. We have found that direct VPN access over the average business class DSL to be quite slow. We have found the solution that we are going to go with now and in the future, and I thought it might be helpful in your situation.

We are porting the back-end of the DB into SQL Server 2000 Desktop Edition, which is a free*** version of SQL Server 2000 designed to give companies an inexpensive/free migration path to the full, expensive version of SQL Server. In form and in function it is almost identical to SQL Server and databases designed in the Desktop Edition can be added to a full SQL Server without any changes.

I wouldn't suggest using this product if your application is going to be used by a "ton" of users concurrently, but it sounds like it will more than accomodate what you are doing. Because it is SQL server the query processing is done on the "server" machine and not on the client. This eliminates a good portion of the data swapping that Access would normally perform over the very slow VPN links.

*** SQL Server 2000 Desktop Edition is free for use and distribution by developers who legally own any one (1) of a number of Microsoft Development products. We develop with Microsoft Office XP Developer Edition which is one of those products. I believe VB.Net, Visual C+, Visual Studio as well as some other products qualify you to use this version of SQL Server. The software is available for download on Microsofts website as well as the rules and conditions for use.

I apologizes if this is of no use for you, but I thought it might help.

Regards,

Michael George
Rock General Systems, LLC.