SQL Server or Access?
Access utilizes a File Server design while SQL Server employs a Client/Server
approach. This is a fundamentally different architecture which has many, many
implications.
The main benefits of SQL Server over Access are improved reliability,
better performance, reduced network traffic and increased scalability. Drawbacks
are an increased deployment cost and a more complex support environment. For
small workgroups of up to a dozen users on a Local Area Network with modest data
requirements (no more than 50,000 records) and without ultra-high reliability
requirements then Access is probably your best bet. Outside of these parameters
you a client/server solution such as SQL Server is likely to be the best option.
The following comes from Microsoft article Q300216:
"Microsoft Jet is a file-sharing database system. A file-sharing
database is one in which all the processing of the file takes place at the
client. When a file-sharing database, such as Microsoft Jet, is used in a
multi-user environment, multiple client processes are using file read, write, and
locking operations on the same shared file across a network. If, for any reason,
a process cannot be completed, the file can be left in an incomplete or a
corrupted state. Two examples of when a process may not be completed is when a
client is terminated unexpectedly or when a network connection to a server is
dropped.
Microsoft Jet is not intended to be used with high-stress, high-concurrency,
24x7 server applications, such as Web, commerce, transactional, and messaging
servers. For these type of applications, the best solution is to switch to a
true client/server-based database system such as Microsoft Data Engine (MSDE) or
Microsoft SQL Server. When you use Microsoft Jet in high-stress applications
such as Microsoft Internet Information Server (IIS), customers have reported
database corruption, stability issues such as IIS crashing or locking up, and
also a sudden and persistent failure of the driver to connect to a valid
database that requires re-starting the IIS service."
|