Hi, I have an Access database that our business uses for all its major functions ie stocks, orders etc. We need this database to be accessible from all 4 of our locations around the country. We have been using a VPN and replicas up til now, but this is proving to be very unreliable, besides which we would prefer it to be real time.
So we are wondering what the options are. I realise most people would say to rewrite the database using a server DBMS instead of Access, and that is a possibility, but first we'd like to explore options that let us keep the current Access DB. So our questions are:
1. Would hosting the DB on a web/ftp server work, and be fast enough to be usable? Maybe if we split the DB and had local front ends for each location and just put the back-end on the server? All our stores have a 512/512 ADSL connection. This would seem our best bet given our current situation, but we have doubts as to whether it would be fast enough to bother
2. A VPN might still be an option, despite not being real-time, if there was a better solution that using the inbuilt VPN servers on the Billion routers at each store (side note: don't buy Billion!)....is there a more stable (and cheap!) VPN solution out there?
3. If we were to rewrite the DB somehow to be more internet/network-friendly, what should we use? SQL Server back-end and some kind of web interface maybe? I really have no idea... Most importantly, what solution would be the easiest as far as converting our current (large) Access database goes?
4. Any other thoughts/advice/opinions?
I know we're in a bad situation, hence why I'm asking for help. Any help you can give will be greatly appreciated!
Would hosting the DB on a web/ftp server work, and be fast enough to be usable? Maybe if we split the DB and had local front ends for each location and just put the back-end on the server? All our stores have a 512/512 ADSL connection. This would seem our best bet given our current situation, but we have doubts as to whether it would be fast enough to bother
We had a system on our network that had a local front end on each PC and the backend on a networked drive. This system is SLOW.
This system was recently moved on to a Citrix server client and it now runs atleast 15x faster.
If you have a network backend - any changes to the tables (any reading of the tables in fact) will have to be sent down the network so this takes time.
Having a published client means you are running the front-end on the server next to the backend so any processing is done on the server - this is generally quicker.
If you go with the published client (which is a great idea) be sure that you do just that, publish your client. Treat it just like you would putting the front end on individual PCs. Make sure it is a copy of the production front end. That way you don't have to have everybody off the database to do maintence to the database.
Another thing to think of would be to separate various functions into separate front ends. This way only the back end database has a lot of traffic, rather than both the back end and front end. Of course, if you can separate some back end functions, that would help too, but would be much more difficult. An idea of some functions to separate out would be reporting actions vs data entry actions.
Hey thanks for the replies guys....
So you think a Citrix server would be a good option? Unfortunately I don't know much about how to do that, nor am I 100% on what a "published client" is. Would you be able to give me a brief rundown on what would be involved in setting it up this way and using it please, just a basic idea of what I'd need to do?
Oh well I generally leave that to the infrastructure lads
"Citrix MetaFrame Presentation Server is a technology that can allow remote users to connect to applications that are actually installed on a remote computer. It uses a mix of technology that results in that remote application looking and behaving just as though it was installed on the local machine." More information here
Basically, it's running somewhere else but it appears just as it would on your own machine.
I use Windows 2003 Terminal Server connecting a Central London to another London site and a New York site to London over VPN tunnels. I know that Citrix is very good however the TS solution works very well. I might add that we also have 13,500 + images attached to the database forms and reports. Some of these images are 100K and I can edit 50MB images via Photoshop.
Our set-up also not only involves the database and images but also all documents and Exchange Server. The Printers are set-up each the Session but Networked Printer Ports must be named LPT_nnn.nnn.nnn.nnn rather than IP_nnn.nnn.nnn as Windows Remote Desktop won't map Network printers unless you tell that they are parallel ports.
Don't use Windows 2000 Terminal Server if images are involved as it only supports 256 colours and we need 24million. We have two servers then main server 750GB and the Terminal Server for remote access. SDSL is preferred for us simply for the images - 100K images render remotely in under a second.
Another factor is the time packets take between sites but if you have VPN then traceroute will at least tell you if you have any bootlenecks. You could use remote access to the database or host it on a web service but the former does not take into account all the business documents that may be needed remotely and the latter performance issues impact upon everyone not just the remote users.