Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
How is exactly is it accessed and moved? On a remote file share in another physical office? Are you running something like a front end to an Access database? What speed is this WAN connection it's going through? –  Bart Silverstrim May 14 '12 at 14:03
    
So say that SQL servers and such should run on a LAN for reliability isn't necessarily accurate. It depends on how it's being accessed and what the link is like. If your application is multiuser and you're using an MDB database, you might want to re-engineer your application to use a better suited engine. –  Bart Silverstrim May 14 '12 at 14:04
2  
I wouldn't want to run a MDB-based database over a WAN link myself, but if they choose to do this then isn't that their problem? I wouldn't waste time 'convincing' a customer of something, I'd just tell them my recommendations and leave them to get on with it. –  RobM May 14 '12 at 14:30
2  
Is this application multiuser? And you're using a file-based connection to run it? And is it being served from something like a Windows share? –  Bart Silverstrim May 14 '12 at 14:49
1  
I was so hoping you'd say, sure, it's a file-based database, but it's not multiuser... Every Time we've had to deal with MDB databases that aren't single-user, we end up with developers who insist the problems we run into are our network, despite having file-lock issues because they didn't use a multiuser database engine like SQL in the background. Try replacing your database engine. See if that makes your access issues go away. –  Bart Silverstrim May 15 '12 at 12:26

2 Answers 2

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.

share|improve this answer
1  
+1 Wow, you've got way more patience than I. 100% agree with everything here. –  Chris S May 16 '12 at 13:16
    
This is essentially "The Truth" –  Dan May 16 '12 at 13:41
    
How difficult to convince an enterprise to offer your app in a 'virtualised' way? ie. have it served over a Hyper-V server or similar such that the only thing transferring over the network is the display and the mouse/keyboard input, thereby avoiding any database corruption issues. –  CJ7 May 17 '12 at 4:00
    
That's not virtualization. That's using a terminal server. In order to use terminal services, you have to convince them to buy enough licenses for the client terminals, and if they have a sizable number of users, train them to understand that this desktop is their desktop while that desktop is the remote terminal. –  Bart Silverstrim May 17 '12 at 12:19
    
That's assuming your application doesn't have any other file locking dependencies also when using terminal services since you can have 20+ users (if the server is beefy enough to handle all the sessions, otherwise they have to buy a new server or servers as a cluster) and all those users are executing the program from the same system. Terminal servers can do many things but it's not a cure-all for sharing an application. –  Bart Silverstrim May 17 '12 at 12:21

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.

share|improve this answer
    
I think that if an enterprise has a policy of keeping database servers on the same LAN as the client workstations then I should be able to argue that MDB files deserve the same policy. I think they have just moved a whole lot of file shares off-site and they don't think there is anything wrong with having an MDB off-site. Are you saying it's possible a WAN could be reliable enough? I don't know whether they have a fibre link so maybe it is. –  CJ7 May 14 '12 at 14:13
    
I'm saying without knowing more facts, you can't state that the WAN approach is wrong. Accessing an Oracle database at the application layer is completely different to accessing a MDB at the file layer. It's a fundamentally different approach so you can't compare them. –  EightBitTony May 14 '12 at 14:18
    
Basically there are some issues with my app and I am trying to narrow down the cause. The issues do coincide with an upgrade of the app, but I still think the move to a WAN connection may be playing a part. The move to WAN preceded the app upgrade by several months. –  CJ7 May 14 '12 at 14:19
    
Accessing files like Word docs etc should be fine over a WAN, but an MDB file requires a strong and reliable connection. I think the network requirements of a MDB database are similar if not greater than a server based DB such as SQL Server or Oracle. –  CJ7 May 14 '12 at 14:21
    
Comments shouldn't be for extended conversation, but I'll say again, your comparison between MDB file based access and real database application access to Oracle is completely different. Oracle server processes access the data files locally, and you access the server process remotely over a network. You're talking about opening a file on a file share and running a local application. No one runs Oracle and gives it access to a data base on a file share, LAN or WAN. –  EightBitTony May 14 '12 at 14:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.