If I use MS Access in the back-end of a client-server type software and the database file is sent from client to server, will it create any problems in further database handling, transfer speed, or performance compared to SQL Server?
views:
144answers:
4
+5
A:
If you use MS Access as your back-end database it isn't a client-server solution. Jet Databases (The kind MS Access creates) are file based, not client-server.
If the bandwidth between the client and the DB is high (like another server on the same network) then it shouldn't pose any major performance problems related to transfer speed. However, if you were connecting over a slow WAN link to the DB from the client, it definitely could introduce a performance bottleneck.
JohnFx
2010-07-16 14:06:19
Think of it this way: if you wind up with a query that has to do a linear scan of the entire table, a client-server database will just have to read it in from the local hard drive and then return the few matching rows. On the other hand, if you try to imitate this by having an MDB file shared on a network, the "client" will have to read the entire table over the network. In fact, each "client" will. Put simply, this does not scale.
Steven Sudit
2010-07-31 01:39:39
@Steven - On the other hand, as you add more clients you, in effect, have more workers. So from that perspective it actually scales better.
JohnFx
2010-07-31 15:44:37
+2
A:
I have a article on using access over a network, and especially that of a WAN here:
Albert D. Kallal
2010-07-17 19:05:25
+7
A:
In my experience there are 4 major differences between MS Access MDB files and SQL Server performance in a small LAN based environment (where small means 20 users or less with no more than 10 concurrent user sessions)
- Security. Use of an access MDB file requires that the client have direct access to the MDB file. This architecture can't be truly secure if you need to limit data access for some users. Access user level security can be cracked. You can use file level or file share level security in the OS if that satisfies your security requirements.
- MDB files are subject to corruption as a result of network errors. The only time I've seen a SQL Server database become corrupted was as a result of hardware failure on the server.
- The upper limit for an MDB file is around 25 users, and Access is sensitive to high transaction volume for inserts, updates, and deletes.
- In most cases with Access you'll need to have all users sign out of the database to make any changes to the structure of the tables. This is much less convenient than using DDL scripts in SQL Server. If you decide to go with Access, I'd recommend getting a copy of LDBView so you can tell who you'll have to kick out of the database each time you make a routine change to the data structure.
There is a case to be made for a back end MDB file if the user audience is small and the simplicity of deployment is appealling to the client organization. But if you are starting a new project, the advantages of a SQL Server backend should be carefully considered. If you have a large user audience then SQL Server is strongly recommended.
It is unlikely that you will have a problem with transfer speed when using an MDB file with an up-to-date version of MS Access and well configured LAN.
Paul Keister
2010-07-17 19:27:38
Some systems go years without a corrupted MDB file, but others don't. The point I'm trying to make is that as a general rule a SQL Server database will only become corrupted in cases of hardware failure. The same can't be said of MDB files.
Paul Keister
2010-07-19 02:30:51
The difference between ones that corrupt and ones that don't is proper deployment architecture and regular maintenance. There's nothing magical about it.
David-W-Fenton
2010-07-19 19:58:17
I've found that transaction volume is a factor. I don't think magic has any role here, but please consider the fact that SQL Server has no "compact and repair database" procedure. I hope you aren't interpreting my remarks as a categorical rejection of MDB files as a data store; that is not what I intended. My main point is that SQL Server is a data storage platform with a big upside.
Paul Keister
2010-07-20 19:56:46
SQL Server has no "compact and repair"? Have you ever looked at the maintenance options in SQL Server? There is definitely compact in there, recovering slack space, as well as many other maintenance tasks that are equivalent to what Jet/ACE does during a compact. I'm sure that those processes also correct any structural issues, too. Every database has maintenance routines that have to be run regularly to keep the database optimally healthy and fast.
David-W-Fenton
2010-07-21 19:38:16
Yes, I am familiar with SQL Server functions to shrink the size of data files; also there are many scenarios where regular maintenance is necessary, such as replication. From your comments I will infer an assertion that Access database can be set up and configured in such a way that regular use, even heavy transactional use by 5+ users, will not cause corruption. I will consider this assertion; I'm sure it's based on your experience. My experience seems to contradic this but I will consider the possibility that I may be missing something.
Paul Keister
2010-07-22 01:28:57
You will find that there are a lot of Access developers who have no difficulty with the scenario you describe. Perhaps it's the difference between people who do it professionally and those who do it more incidentally. I've been doing professional Access development since 1996 and haven't encountered an actual corruption (as opposed to a file flagged after a dirty shutdown) in well over 5 years. Nobody has lost data in nearly 10 years, and that was only during the early days of Jet 4 and A2000, when the autonumber seed value bugs had not been fixed yet.
David-W-Fenton
2010-07-22 19:20:43
David, you've opened my eyes. I rarely use MDB files - only for small projects or projects I inherit and maintain infrequently; I was unaware of the advances in Jet stability. Thanks for your persistence.
Paul Keister
2010-07-23 05:53:12
There is no advance in Jet stability. It is just exactly the same as it was back in the Access 2 days when I started using it. If you follow best practices, run regular maintenance and have good backups, your database is reliable and there's almost no significant risk of corruption, let alone any actual data loss. There is, of course, *some* risk, and where that is unacceptable, one would choose a different database engine.
David-W-Fenton
2010-07-23 20:33:20
@David-W-Fenton - RE: Corruption. That an MDB can corrupt at all is enough to not use in an high transaction scenario. I have never encountered SQL Server corrupting TBH. Transactions rolled back? Sure, but never corruption. I have worked with every version of Access from 1.1 until 2007 or so and have built and worked with many solutions built by many different developers. In all that time, I have never once encountered a situation where the db had not corrupted at least once.
Thomas
2010-07-27 17:09:00
So far as I can tell "high-transaction scenario" is something you just added to the discussion, which certainly changes the balance, just as if you and your wife have quintuplets, you'll need to get a bigger car in place of the Mini Cooper. This is not something wrong with the Mini Cooper, it's just a change in the requirements.
David-W-Fenton
2010-07-28 18:34:01
My edits on this question were intended to soften the tone of my remarks regarding Access corruption in the presence of high transaction volume. I stand by my original remarks, but I hope that the revised version better accommodates a balanced analysis of the strengths and weakness of the data storage efficacy of MDB files. I have never said, nor do I now believe, that there is no role for the MDB file as a data storage platform. Corruption of the data file is not a show-stopping problem, but it is definitely something a programmer who is new to the format should be made aware of.
Paul Keister
2010-07-29 04:36:09
@David-W-Fenton - Even if you remove the requirement of high transactions, my statement still stands. In all the years I have worked with Jet, I have never once encountered someone using a Jet backend in daily work that had not at least once encountered a corruption. Simply put, IMO, Jet cannot scale. If a given solution never scales up, then no worries. However, that is exception rather than rule.
Thomas
2010-07-30 02:19:39
My bet is they didn't encounter actual corruption, just a file that was flagged as suspect and thus in need of the inspection that happens in a compact operation. I would alter your #2 so to say "possible corruption" because the vast majority of cases where Access tells you the database needs to be compacted are not actually corrupt (you can tell this because you can often access the data via other means, and you'll find it entirely intact).
David-W-Fenton
2010-07-30 17:55:25
I would also say that your 25 users number, while the threshold I also use, is not properly worded. It's not a hard upper limit -- many experienced Access developers are able to support far more users than that (some support as many as 100 or more) -- but to support more than 25 users, you have to program your app more carefully, so it's much harder. That said, it's possible, using the kinds of approaches to app design you'll find in the Access sample databases and templates, to bring an app to its knees with 5 users.
David-W-Fenton
2010-07-30 17:57:55
...and, of course, how many users you can support depends on what they are doing. 25 read-only users is going to be no issue whatsoever. 25 users who are adding and updating at high volume is potentially already a problem.
David-W-Fenton
2010-07-30 17:58:37
I would start your #3 with "A handy rule of thumb is that any Access app using a Jet MDB as data store that is nearing 20-25 users is a good candidate for upsizing, not because Jet can't handle it, but because it's more work to program the application to insure smooth operation than it would be with a server-based database engine." The point is that it still works, it's just more work to make it reliable. There is no hard upper limit except the 255 user limit, and I don't think anybody has ever suggested that's realistic.
David-W-Fenton
2010-07-30 18:00:25
A:
Some good answers here, already. But something that is often overlooked is that there are scenarios where using a lightweight mdb gives you much more performance than a heavy-weighted SQL server. For example, if multi-user access is not so important, but you have to do a lot of batch processing on your data, using mdb files can be much faster. On the other hand, if you have a lot of classical OLTP processing with many users, you can benefit from a real client/server database.
Doc Brown
2010-07-30 21:14:34