A large client that uses my MDB based application has moved the MDB file off-site so that my application connects to it over a WAN.
How can I convince them to put the MDB file back on-site on to the LAN so that the application can run reliably?
Can I say that all databases must be on a LAN, no matter whatever what sort of database? Can I say that SQL Servers, Oracle etc., are all run over a LAN rather than a WAN, for reliability reasons?
You don't get into details on what your application is or what your relationship is with the client. So here's my feedback based on past experience (and one incident in particular), with the following in mind:
1) You're an application developer with a product sold to a client, whether for your own company or under contract (you're not an in-house developer.)
2) Your application is using what is basically an Access engine behind a front end.
3) Your client is paying you for this multiuser application using the MDB file, stored now on a Windows share which multiple client applications are accessing.
4) Your client has some kind of IT department with even a minimum familiarity with databases, and if not, they'll catch on quick.
5) Your client's only issue is with your application over this WAN link (i.e., decent bandwidth, mostly reliable, works for other applications for the most part in a stable manner.)
Here's where I'm coming from. We had a vendor sell us a multiuser product that was periodically, seemingly randomly, failing to update the database at the central server. I was charged for our organization with working with the vendor to get the issue sorted out, as we had several unhappy people being affected when this failed, and our users have little to no patience for excuses before resorting to a paper tracking method.
The vendor insisted this application worked fine in other (similar) organizations, so the problem was with our network or systems. We replaced systems. They insisted on putting a mini-switch into a nearby room to help "isolate" traffic because our traffic was too high on the network, causing latency issues (even though only their application had trouble, nothing else was showing blatant signs of latency issues.) They then blamed power, insisting we put UPS's on the workstations and their mini-switch to condition the power. Each time the random failure would crop up again.
We went through each of these hoops the developers insisted would fix it, just to show them that NO, that's NOT THE PROBLEM. The problem was fairly obvious. A two-minute Google of the error message showed it was a file locking problem, and a quick dissection of their application revealed it was using an MDB database; basically the application was a front-end to an Access engine. MDB files on a file share simply are not designed for multiuser access. When enough transactions were flying, eventually two of our client workstations would hit the database application at a point where it couldn't properly lock a record for updating and it would error out.
I'm not a programmer, I'm not a DB administrator, this is just what I've found in researching, and I've had a number of people agree. Did this situation change in more recent versions? I don't know. But I do know that I found a number of people who simply said they're doing it wrong. "When you have a number of clients on a database, use a network database solution designed to handle multiuser access." Access is fine for Grandma to keep her cookie recipes. Not so good for handling 20 users with a need for transaction integrity at the same time over a network.
Now with your situation...in dealing with this vendor, I can say that when a vendor is insisting the client is wrong, despite documentation otherwise (the network is fine, the bandwidth is fine, your application is the only thing failing, the error itself is pointing to a flaw in your design...) they will have little patience with you making them jump through hoops to prove to you that their network is reliable enough for everything but your application. All our vendor ended up doing was making themselves look foolish; we dropped them for a competitor after tiring of clowning around with them, and they refused to even acknowledge the possibility of the error being their application's fault.
We jumped through every hoop they wanted us to jump through and still basically said it was our fault and we were incompetent. Whether a vendor thinks we're incompetent or not, it's usually not wise to tell the people signing the purchase order that they're incompetent, let alone dismiss their own findings about the issue offhand.
Next, the file based database is basically relying on other mechanisms to help protect it in transactions. A true DB admin might correct me here, but at the time, as I understood it, this database being accessed by multiple clients over the network on a server were dealing with the filesystem, the abstracted filesharing filesystem (Windows sharing), and an engine that didn't handle proper locking of the database for multiple users simultaneously. "True" databases (MySQL, MSSQL, MongoDB, etc.) handle this better because they're made to do this. Access is great for keeping track of your recipes and movie collections. Don't use it for businesses to handle point of sales or large helpdesk databases.
Why does it work within the LAN? If you tell them (and they have an IT department with half a wit among them) that all databases should be within a LAN to work properly, they will probably wonder what your experience is with databases that you'd make such claims unless you knew for a fact that they are setting up their WAN with the equivalent of Hayes modems over POTS lines. The truth may very well be that using the MDB file is rickety and error-prone; keeping it in the LAN is reducing some of your contention issues below a threshold where it seems to (usually) work. If they increase the number of client applications using that database, even within a LAN, you'll probably increase the number of "random issues" cropping up with the application, and their IT department will quietly curse you for having a crappy application every time they have to restart something or deal with your application when it has a hiccup.
In other words, telling them they must put it on a local server will only be putting a band aid over a gaping wound. If it's a file contention or engine problem, they'll still have random issues, and increasing the number of clients will hit another wall in performance and/or decrease the time between random failures of the application.
Not to mention that if they reorganized their server shares for reasons known in-house but your application needs to be special, something else to be documented or handled differently for some reason, your application will not be favorably looked upon by the IT staff. If you want a good relationship with them, why antagonize them?
(Also...if they have a WAN connecting offices, are you sure the remote users will never need to use your product? Or they may move people to have some remote users using your application? Then you'll have the split-network issue crop up all over again.)
In the end, the true solution is to change to MSSQL, MySQL, or some other "true" database engine that handles proper multiuser access. This will make your application more scalable, more flexible, and reliable. It will make your product, and you, look better.
One workaround is to create a kind of "proxy" application that serializes queries from the clients, processes them, then doles responses to the client machines, assuring that from the database point of view everything looks to be handled locally. This will be a choke point for performance and scaling your application, and it's a clumsy workaround, and in the end it's an utterly wrong solution, but if you write it quickly and it works you might use it as a stopgap for your issues until you can implement a proper database migration. If the administrators have that half a wit I mentioned they will probably seriously question your database experience, but they will at least know that you're working on a proper migration path to a working solution.
Maybe your client's WAN connection is unreliable. Maybe there is some other issue that moving the database file to a local network system will improve things. Maybe I'm completely off in how your application is designed. But if anything in your application is similar to the situation we were in, your application needs to be re-engineered. If you have a proper abstraction between the database and the application then replacing the database engine shouldn't be quite a huge undertaking, and should yield a huge benefit. Their competitor was based on MS SQL Express. No problems. I'm not even sure if the original vendor we had an issue with is still in business, despite the insistence they had numerous happy clients with no issues with their product. Maybe it was another thing they lied about...
But I really think you're asking the wrong question. It shouldn't be how to convince a client to work around a potential flaw in the application's design, but rather how to smooth things over with your client and convince them to hold on to your product as you work on implementing a proper solution to their issues? That's my experience. It was free. Take it as you will.
You don't tell us the performance characteristics of the WAN. I access plenty of applications over a WAN, assuming you mean a wide-area network? There's nothing inherently wrong with doing so.
If you have concerns, you will need to collect evidence (number of times the WAN connection was down, number of times the slow performance impacted the application, total time taken to complete Action X over the LAN vs the WAN, etc.)
Your statements about SQL Servers are all run over a LAN rather than a WAN isn't entirely accurate. Proper database servers run the server processes on a machine which has direct access to the storage (and by direct, I include SAN, iSCSI, etc.) Applications then talk to those server processes, be that on the LAN or the WAN. The disk data transfers are all local to the server.
I assume what you're really talking about is that they've moved the physical file to a file share which is presented over a wide area network, and that file sharing (SMB or whatever) over that connection is slow and unreliable? If that's true - gather evidence and show them why it works better locally, but don't use the same analogy as true database servers, because you're not replicating that process at all.
Despite all the discussion my answer is still the same - you can't state a policy, you shouldn't compare MDB file based access with real database access for things like Oracle of DB2, and your best bet is gather actual physical evidence and present that.
