Remote or local Database - benefits snd problems ?

[carju1]

Hi All,

I have a client who as part of a larger project wants a fairly standard Management Information System Database. It will contain the standard tables (Client details, orders, products) and will be mainly queried for the 'which postcode buys most of product X' - 'what were the geoggraphics of sales on April 8th'. So nothing exceptional and nothing to difficult.

They have an office with 4 pc's networked and a laptop. My initial thoughts were for a simple MS Access database on the network but during discussions the possibility came up of them using the database when on the road at shows etc. This lead me to wonder if a remote server with 'mySQL??' would be a better option.

My question is what are the benefits and disadvantages between the local and remote options?

Regards
Julian

[davuart]

I would look towards using MySql for the work you mention, it is easy to build such a database with the correct relations, the queries are easy to implement and using a front end with PHP allows for a cross browser access system.

It will also allow for easy implementation at a later date of new tables and queries.Back up's can be easily scheduled through cron jobs. I suppose the biggest advantage of this compared to a local network access system is its worldwide usability.

The downside would be that the client woud have to go online each time to access the database, so broadband would be a must from the office, with dial being acceptable for displays.

I suppose the most important thing to look at is security, so make sure access to each page is protected for both the entry and retrieval pages, as you will be storing sensitive data which under the data protection act must not be freely accessible. Also one to think about is placing the entry pages under SSL another requirement to conform to the act.

[gwhite]

You can setup replication for (newer versions of)MySQL (there is a version for Windows as well), which would allow a database to be kept on the local server and one on the external public server. The MySQL system would handle keeping both DB's in sync, then you would have automatic backup and access when and where they needed it.

Access is not a very good choice, especially if there is even a moderate amount of concurrent updates (multiple users updating the system) going on. It is best suited for a single person creating the data and a few accessing it.

[makeyoursoftware]

Dear ,
Keep It 100% Online will make your client dependent on Broadband, regular space cost is also going to add up to your client & security (these days it 99% safe to keep the database safe) but it is not more safe than in clients office.

Keeping local is a better option.

Ask the client what they need during shows from database.

If they need specific thing you can code that partcular thing to made available , they can always update at that time.

Other thing is , when on shows will they carry client or server or both , if it is server they are going to carry, we already solved it.

Anyway bye - Have a nice day.

[andyj]

I spent 3 years at TXU writing and supporting an MS Access client connecting to an Oracle database. The main issue I had was support. If any changes occured to the front end processing it had to be rolled out to all 60 desktops. This entailed ensuring that each PC was running the same version of Windows with same patch level. This took a lot of time. If the automated roll out crashed we then had 60 desktops to restore to ge the customers working again. Also if ever a PC crashed we then had to get a new machine built and have all the software reloaded which generally took an IT rep a day.

Since then, when I design systems for clients I recomend them to have a browser front-end and database hosted on an Apache web server (local or on the internet). As the software is tested for all the latst browsers their are no operating system issues and updates are just loaded to the server. Using a good hosting company will also take the responsibility away from your customer for backing up the database.

Going down the browser front-end MySql route is so much easier to support and will enable you to give your clients a much better service. Also you don't have to worry whether they have backed their PC up.

I have one client who started off with a client database just for the staff in his office. Because I recomemended and developed a web based system for him he has now got another office and one person working form home without having to spend any more on his Client database system.

Web based database applications are the way forward.

[Fendermate]

My team and I have the same problem. I can understand that using MySQL on line has more benifits than most other solutions and we're leaning in that direction.
The problem that we're wrestling with is having the ability to work with MySQL to merge data for off line office work with other MS Office programs.
MS Access has no problem in simplifying that job but it's not a good program for on-line entry and retrieval.

gwhite said;

'You can setup replication for (newer versions of)MySQL (there is a version for Windows as well), which would allow a database to be kept on the local server and one on the external public server. The MySQL system would handle keeping both DB's in sync, then you would have automatic backup and access when and where they needed it.'

Would that be a possible solution?

I have also looked a using converters like 'MyDB Converter 2.0' available from http://www.softguru.net/
This would allow the use of Access in the office and MySQL on line. But is that a wise choice?

The other piece of flexibility that I have to keep in mind is that, in the future, parts of our database may have to be made available to specific clients who may not have MS Office components.
Your advice will definitly be appreciated.

[paulduffield]

What a great question for starters! I think these sorts of issues are going to be cropping up for a lot more users over the next few years.

I have a number of clients with similar issues at present. They are relatively small ( 4 - 20 staff) and with differing stages of IT literacy, ability, budget and database requirements on and offline versions.

I have identified a few key issues that come into play in this arena which I will attempt to cover here for people as issues to consider.

Access is a great tool for developing and running front ends and for up to three or four users works well as a back end relational database too. Issues with multiple records being locked (compared to individual record locking in SQL server) come up much beyond that sort of user numbers in Access.

Access is very versatile and powerful with a lot of functionality that makes front end and relational back end database development relatively easy and cost effective.

mySQL and php I have personally found a little more restrictive as it is somewhat harder to track down programmers for this work. It is slightly outside of my domain and although it is popular with many (and many will bite my head off for this) – it is unsupported by Microsoft and that can have implications for standard Microsoft systems. (I think only about 3% of browsers are not IE ? if memory serves me right for example.) Having said that its open source etc and some may prefer that route.

One client I have had a small database, started about 4 years ago for two users. This became an issue as the number of staff entering data increased to about five or six. So we used the upsizing wizard to convert the back end to SQL server (packaged with SBS on the in house server). One or two bugs were quickly solved by a skilled programmer for not a lot of money.

For occasional remote working by staff and my own troubleshooting/support of issues I accessed the back end by VPN logging onto a Draytek router with a fixed IP (marvellous kit the Draytek stuff by the way) using the Access Front end.

This proved to be OK for occasional work but a little slow on ADSL though at 256kb up/512kb down speed for the connection.

When it went to SQL server performance was better and VPN still provided adequate access and for situations where just a few records need to be updated remotely it works fine. For the half a dozen girls in the office though entering data at breakneck pace all day long it would not prove to be OK though. We tested this by using the office ADSL and connecting a front end to a remote web based server lent to us with a temporary copy of the in house database on it.

There are three offices in three different countries USA, Canada and UK, for this company although the London one is busiest for data entry but we would like to make more of remote workers as office space in London is expensive.

The main problem with ADSL is that (in the UK at least) it has an upload speed of just 256kb and typical download of 512 (This is soon to become 1mb as standard down speed but still 256 up) Typically the cost for this would be about £30. pcm. (US$50. ish ) The problem is no matter how fast the download facility the 256 up from the server in the office on VPN dictates performance.

So two options on from here… A co-located/hosted server somewhere in a data centre. And/or a 2MB SDSL connection in the office. (SDSL = same up and down speed)

The main office in this case is in London UK and SDSL is available at about £2-400 pcm (2mb/2mb) depending on who you buy it from with up to £650 for connection too, so the cost is significant for a small business. As is the hosted server option of course by the time you ad on rackspace, firewall, bandwidth, backup service and remote boot service.

The SDSL over comes the “upload” bandwidth problem (remote workers are still restricted in data upload from their end though unless they have SDSL (unlikely at present and expensive at present) but the data retrieval of larger recordsets should be largely overcome if they have a 1MB ADSL connection.

For the main office the benefits of SDSL open up some options … A remote server on the web (managed remotely – worldwide performance) can be accessed up and down with good speed (Tests on the ADSL indicate the SDSL should function OK if the speed demos are as good as claimed by British Telecom) But also the option of hosting a web site with SQL server on the same machine for no additional cost (albeit the DNS/security issues etc come into play) in the actual office. (We have a few clients we host web sites for too so the cost is offset slightly) As an aside no web server is then necessary and the office server would save the cost of a machine and O/S etc for this service.

The lure of a fully functional SQL server database remotely accessible on the web is going to be the way forward for this particular company as the benefits are likely to outweigh the expense and configuration/set up issues. For a lot of busy web hosting another server may be necessary in a data centre but not for this instance.

I suspect from the little information we have in this thread from Julian that a VPN solution with an Access front end accessing either an Access backend or SQL server would be best for his client depending on their network setup. Why…?

*The office workers can perform at regular network speeds.
*The database is still accessible remotely at road shows/wherever via VPN (albeit performance is dependant on connection speeds)
*Access front ends provide a very useful tool for creating the required queries and reports for the information they display. (There is a cost per user for a copy for Access of course unless a runtime version is produced.)
*It can be scaled up relatively easily if the setup proves its worth.

The web based SDSL version we are looking at for our client I will feedback on here as soon as we have finally installed and tested it.

There are a number of other things my client will be doing with the new setup though and may provide some ideas for other options that are available.

At present the in house SQL server BE /Access FE database exports a couple of subsets to a mySQL database to display the client(s) file/job status and individual/custom pricing for the international clients. This is then uploaded via an ftp utility to reside on an internet based Linux box (Doesn’t run Access of course hence the need for the conversion to mySQL) to display the client information to the client. A php web interface displays the client information to a client upon their entering a username and password. They can then review the status of their own jobs and order new ones from their version of the individual/custom price list.

The plan is to migrate to Windows 2003 for this web based server or use the in house one on an SDSL connection to do the same. As the database is on the same box/platform then as the staff update the information in house it is automatically available to clients as they log on. They will see immediately the exact same version of the database which is in house/on the web via SDSL.

The main database could arguably reside on a server in a data centre or in the main office but if remote physical access/24*7 reboots/backup services/firewall and VPN issues all come into play again in cost/performance ways. However, as observed if the web hosting creates a lot of traffic this may be necessary or more than one server required. (Most likely scenario at the moment if things get any busier)

A dream scenario for all this would be in house users logging on to a web based server with a .NET ASP system with in house users updating and editing the database (client job status / price lists) online via a browser interface. The clients accessing the same database to read information via their browsers and order online as they do at present. The resources/costs of rewriting the front end Access database are prohibitive for the client at present but I am sure in time it will occur.

Hopefully this will highlight some issues I have already encountered and experienced and will provide food for thought on some of the related issues.

[gwhite]

There are ODBC drivers for MySQL, so you can actually open your MySQL database with Access (or Excel for that matter), do your query and report writing in Excel, but let the replication and robustness of the MySQL database do what it is best at.