在任何情况下，MS Access都是比SQL Server更好的选择吗? [英] Are there any cases where MS Access is a better choice than SQL Server?

问题描述

这个问题的灵感来自我大约一年前问的一个问题-

1. 仅使用数据库组件(Jet/ACE)

2. 还使用应用程序开发功能(报告，脚本等)

毕竟，如果您的应用程序可以承受数据库方面的限制，那么使用某些应用程序开发功能可能会有好处.

(仅作记录，在这场战斗中我没有狗，我是一个满意的SQLite用户.)

解决方案

我将回答您要问的问题，而不是您实际发布的问题(您的意思是Jet/ACE，而不是Access).

是的，在许多环境中，Jet/ACE是合适的数据存储.我要说的主要问题是您将要拥有多少用户.对于最多15-20个用户的任何应用，Jet/ACE都可以正常工作.唯一不会出现这种情况的情况是，如果您不知道自己在做什么.在以下情况下，您可能没有任何线索

:

1. 您将创建一个具有表和表单/报表等的单个整体MDB/ACCDB文件.

2. 您尝试在多个用户之间共享单个整体文件.

3. 您明智地将数据库应用程序划分为前端(表单/报表/等)和后端(仅表)，但尝试在多个用户之间共享前端.

所有这些情况都是失败的秘诀，但不是Jet/ACE出了问题，而是那个白痴从不费心去学习如何设计和分发Access应用程序.

性能较差的Access应用程序的另一个常见特征是将表格绑定到完整表，而不是绑定到选定的记录子集.基本上，您将应用程序设计为一次检索最少的数据量，以便允许用户执行其工作.例如，编辑一条记录的用户不需要将其他10000条记录加载到表单后面.

总而言之，具有Jet/ACE后端的Access应用程序在15/20以上的用户不处于繁重的数据输入/编辑模式的情况下仍然可以很好地运行.如果大多数用户为只读用户，则最多可以支持50个用户.

但是，如果我遇到这种情况，我可能会开始敦促升级到SQL Server.但是需要注意的是，与后端的简单文件相比，SQL Server增加了显着的管理开销.使用完整的SQL Server自动化这些任务也比使用SQL Server Express自动化要容易得多，因此对于不熟悉编写和计划自己的SQLCmd脚本的任何人来说，推荐使用SQL Server Express都不是一个好选择. /p>

安全性也可能更加复杂.这是由于您可以使用SQL Server安全性做更多的事情的结果，但是在升级时仍然必须在前端解决该问题.

在具有管理专业知识的环境中，您可以将用户数量用作确定何时进行升级的唯一基准.在缺乏专业知识和基础架构的小型办公室中，通常可以更好地利用资源来尽可能长时间地使用Jet/ACE.

就其价值而言，我有十几个具有Access应用程序的活动客户端，目前只有两个针对SQL Server运行.在其余的组件中，甚至只有两个是候选组件，并且由于它们的用户群很小，并且没有性能或可靠性问题，也没有重大的安全问题，因此并没有太多令人信服的理由来扩大它们的大小.

这实际上提出了其他几点:

如果以下一个或多个问题很重要，那么即使对于单用户应用程序，SQL Server也可能更合适:

1. 数据很敏感，需要保护，超出Jet/ACE所能提供的范围.基本上，如果您需要的数据安全性超出了Excel电子表格的处理能力，则需要基于服务器的数据库引擎.

2. 某些应用程序处理的数据如此之多，以至于它们确实可以从服务器数据库引擎中受益，无论是容量还是将数据库操作移交给完全不同的CPU的能力.

3. 某些应用程序需要24/7可用，并且没有停机时间或丢失1个字节数据的任何风险是可以接受的.在这种情况下，建议使用基于服务器的数据库.

根据我的经验，大多数人大大高估了他们对这三个方面的需求，而低估了Jet/ACE处理数据和维护可靠性的能力.

对我来说，一种极具吸引力的方案.

假设您有一个三人办公室，没有文件服务器，只有3台PC.你会

1. 告诉他们购买独立服务器，将其设置为SQL Server(也可能将其配置为文件服务器)，然后让他们使用它.

2. 在一个对等工作站上安装SQL Server，并让他们使用针对该对等工作站运行的应用程序.

3. 仅使用Access.

在前两种情况下，需要完成许多维护和管理工作(尽管Jet/ACE后端也需要维护).谁去做?

如果您选择#1，那么随着时间的流逝，该服务器的成本，安装它的劳动力以及维护和管理它的劳动力将流向何方?

如果您选择#2，那么如果没有足够的工作站同时充当SQL Server和工作站该怎么办?

This question was inspired by one I asked almost a year ago - any-orms-that-work-with-ms-access-for-prototyping - which has recently become active again, but as an Access vs SQL Server debate.

There seem to be a lot of Access haters out there, and the main rap seems to be that it doesn't scale well (though some people seem to have been able to make it work).

For those of you who have used both technologies, are there cases where you would use Access over SQL Server?

Why?

And how can you improve your odds of success?

For example, on a desktop app that would have one, or a small number of users - might Access be a better choice?

Or, to take the reverse tack, when should you avoid Access from the get go?

Again, why?

Edit When I say "Access", I'd like to get feedback on two things:

1. Using just the database component (Jet/ACE)

2. Using the app development features as well (reporting, scripting, etc)

After all, there might be advantages to using some of the app dev features, if your application can live with the limitations of the DB side.

(Just for the record, I have no dog in this fight - I'm a satisfied SQLite user.)

解决方案

I'll answer the question you meant to ask, not the one you actually posted (you meant Jet/ACE, not Access).

Yes, there are plenty of environments where Jet/ACE is a suitable data store. I would say the main issue is how many users you're going to have. For anything up to 15-20 users, Jet/ACE will work just fine. The only circumstances in which it won't is if you just don't have a clue what you're doing. You may not have a clue if:

1. you create a single monolithic MDB/ACCDB file with both tables and forms/reports etc.

2. you try to share that single monolithic file among multiple users.

3. you wisely split your database application into front end (forms/reports/etc.) and back end (tables only), but try to share the front end among multiple users.

All those scenarios are recipes for failure, but it's not Jet/ACE that's at fault, but the idiot who never bothered to learn how to design and distribute an Access application.

Another common characteristic of poor-performing Access apps is to have forms bound to full tables instead of to selected subsets of records. Basically, you design your app to retrieve the minimum amount of data at a time in order to allow the user to do her work. A user editing one record doesn't need the other 10000 records loaded behind the form, for instance.

All that said, an Access app with a Jet/ACE back end can still perform well with more than 15/20 users if those users are not in heavy data entry/editing mode. If there are mostly read-only users it's pretty easy to support up to 50 users.

However, were I in that situation, I'd likely start urging upsizing to SQL Server. But one needs to note that SQL Server adds significant administrative overhead in comparison to a simple file on the back end. It's easier to automate those tasks with full SQL Server than with SQL Server Express, too, so the recommendation of going with SQL Server Express is not a very good one for anyone who is not already comfortable with writing and scheduling their own SQLCmd scripts.

Security can also be more complicated. This is a consequence of there being a lot more you can do with SQL Server security, but it still has to be addressed on the front end when upsizing.

In an environment where administrative expertise is available, you can use number of users as your only benchmark for deciding when to upsize. In small offices that lack that expertise and infrastructure, it's very often a better use of resources to stay with Jet/ACE as long as possible.

For what it's worth, I have a dozen and a half active clients with Access apps and only two currently running against SQL Server. Of the remainder, only two of them are even candidates, and there simply aren't very many compelling reasons to upsize them, as they are small user populations and they have no performance or reliability issues, and no significant security concerns.

That actually raises a couple of other points:

SQL Server might be a better fit even for a single-user app if one or more of these issues is significant:

1. data is sensitive and needs to be secured beyond what's possible in Jet/ACE. Basically, if you need the data secured beyond what you could do with an Excel spreadsheet, you need a server-based database engine.

2. some applications crunch so much data that they really benefit from a server database engine, both in capacity and in the ability to hand off the database operations to a completely different CPU.

3. some applications need to be available 24/7 and no down time or any risk of the loss of even 1 byte of data is acceptable. In that case, a server-based database is advisable.

In my experience, most people vastly overestimate their needs for all three of these, and underestimate Jet/ACE's ability to process data and maintain reliability.

EDIT: A scenario that to me is compelling for Access.

Say you have a 3-person office with no file server, just 3 PCs. Would you:

1. tell them to buy a standalone server, provision it as a SQL Server (and perhaps as a file server for them, as well), and then have them use that.

2. install SQL Server on one peer-to-peer workstation and have them use their application running against that.

3. simply use Access.

In the first two cases, there's a lot more maintenance and administering that needs to be done (though there's maintenance required for your Jet/ACE back end, too). Who is going to do that?

If you choose #1, where is the money going to come for for that server and the labor to set it up and the labor to maintain and adminster it over time?

If you choose #2, what if there's no workstation that's sufficiently equipped to act as both SQL Server and workstation?

这篇关于在任何情况下，MS Access都是比SQL Server更好的选择吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！