MS Access: Local Front End, Cloud Back End?

[dgunner71]

I have a client who would like to keep his db front end hosted locally (on several 'clients') while putting the back end in the cloud. The reasoning for this is as follows:

1. with data hosted in the cloud, it will be easier to access from anywhere, and will be less likely to be subject to data loss if their server should go down. (this recently happened which is the driver behind this - we recovered all data from nightly backup but their operation limped along for a few days until we could get the server back up)
   
2. with the front end hosted locally, the db will still be able to interface with their accounting program (currently PeachTree but may be switching to QB).

As always, any help, insight, or direction is very much appreciated.

Best regards.

Gunner

[NeoPa]

Hi Gunner.

The problem with that approach is the perennial one of running Access across a network. It's inherently problematical. Once a packet is dropped in Access then the link dies. You have to close down and start up again. By its very nature a cloud-based BE would be running across a network.

OTOH I've heard that ODBC connections (Not Jet/ACE at this point.) in the latest version are correctly handling restarting of failed connections. So something like an SQL Azure BE with an up-to-date Access FE may work for you.

As I say I've heard it discussed, but don't have a lot of personal experience with it working so can't vouch for it at this stage. There's a lot of excitement out there about it though, among those that have been asking for this for a while. I know that.

[jforbes]

You may want to check this Question out, MS Access Frontend/SQL Backend, What madmax262 is talking about is similar to what you are considering. In fact, it looks like he's already done what you are talking about and found that it didn't work all that well. It seems that he did make it work by using Stored Procedures to retrieve and store data.

The one thing that is very important to understand about Access is that the performance can degrade quickly with the increase in distance (or Latency) between the FrontEnd and BackEnd. This happens regardless of BackEnd type, Jet or ODBC. For example, a large Form that takes 3 seconds to open and display data will take 30 seconds if the BackEnd is moved 300 miles away. Stacking Bandwidth will not help this Form load any faster, it's load time is dictated primarily by the Latency.

It sounds like you have three goals you are looking to satisfy:

1. Greater accessibility to the data outside your company.
2. More reliable backups.
3. Running the client locally to allow integration to Peachtree.

With this list of goals, I would recommend implementing:

• A pair of Virtualization Servers and installing an Instance of SQL Server on one of them.
• Opening the Ports SQL Server needs on the Firewall. I would also map a Dedicated IP address to forward to these Ports, so that there is a static and dedicated Internet IP address that can be used for your ODBC connection for the FrontEnds that are connected over the Internet.
• Setup SQL Server to create a nightly backup of your Database. You can also setup a Scheduled Task to copy this Backup off-site at night.
• If you have a failure with your database, spin up a copy of the Database Server on whichever Virtualization Server happens to survive and then load the latest copy of the database. You could be back up in a Day, or an hour if you get good at it.

The largest negative that this approach has is that there are more secure ways to go about this. All of which take more time to implement, but making your SQL Server Visible on the Internet scares the crap out of a lot of people, because you are talking about one login that needs to be figured out and then a hacker has some pretty high level access to your Data (and possibly your network). In most of your more secure scenarios, you will want to use Stored Procedures to retrieve and modify data, a lot like what madmax262 is talking about in his Question. Another option is to wrap these Stored Procedures in Web Services and hide the SQL Server behind the WebServer.

I know I've kind of rambled in this post. The most important thing to takeaway is that you will want your FrontEnds as physically close to your Backends as possible. So if your Cloud Service provider doesn't have a Database Service in your State, you may want to create a Proof of Concept Database and test things out first.

[NeoPa]

JForbes:
I know I've kind of rambled in this post.

I think you've done a stunningly good job in a complicated area.

In fact, I've made a note of the post and plan to link to it in future if I see similar questions (Problems with Back Ends (BE) Across a Network).

[dgunner71]

Thanks so much for all your great insight.

Based on these posts, I'm thinking I may try to host both Front End and Back End in 365 (cloud), providing db access remotely. As for the importing into the accounting system, I believe I should be able to write a procedure to do a daily batch download from the online db to a local db. We can do the (uni-directional) 'push' into the accounting program from the local db. It's not as seamless but it should be effective and negate performance issues (the accounting portion of the process is not necessarily mission critical).

Thanks again for your valuable insight!

Gunner

[PhilOfWalton]

I know it's all wrong, and I am talking about a comparatively small db, and by and large a single user, but I have been getting away with my BE in Dropbox, and I'm amazed how fast it seems

Phil

[NeoPa]

That's not entirely unusual Phil. Particularly with small and single user. Eventually though, there's a good chance that it will cause a crash and often that will result in a corruption of the db. It's very likely that can be fixed with a C&R but it helps to know these things are lurking out there to get you.

[jforbes]

@Gunner Glad we could help. Your plan sounds pretty interesting. I don't fully understand what you are planning on doing, and I don't really need to, but it sounds like a unique solution.
I think it would be great if you could post back here after you have things in place to let us know what you did and how things are working for you.

@NeoPa Thanks! Glad you found it useful.

@Phil It doesn't surprise me that your DropBox solution is responsive. The DropBox folder is a local folder, and the BackEnd is most likely on the same hard drive as your FrontEnd. ...I would make sure you have a daily backup scheme in place, just in case. =)

[PhilOfWalton]

@NeoPa

True, and yes I have an automatic backup running

Thanks as ever for your input. It's always nice to be taught by a real expert.

Phil

[dgunner71]

@jforbes - I will circle back. Essentially, I'm going to try to host the front and back ends in the cloud via 365.

The db solution that is hosted in cloud will have a feature to export/download a certain data subset to the local computer (i.e. any unbooked/unposted work orders).

I will have a locally hosted database with very limited functionality on the local computer to import the data into the accounting program.

I'll circle back with an update in a few weeks.

Thanks again all!

Gunner