As I have talked about many times here, in newsgroups and in my sessions, books and articles, IMHO JET/Access is not suitable in a business application. I've detailed the problems that include its physical IO over the network (which makes it prone to data loss), its inability to be backed up while any application is still connected, its need to be periodically compressed, it's lack of scalability (which means you have to start over when the customer outgrows it), it's (very) limited capacity and the number of users it can support. It should never be used as a web host unless you don't mind terrible performance--that's because it's a "client" engine--not a service designed to support parallel operations. One of the most serious problems is its inability to encrypt data in place and its fragile security system. This makes it "officially" unsuitable to manage HIPA-compliant data. The fact that you access JET over a very problematic MDAC stack is and always has been another problem. Each time the stack is changed, your application might or might not work. Jet is also accessed over a COM-based OLE DB interface--in unmanaged code.
The alternatives? There are several:
* SQL Server Express edition: This is a free DBMS engine that can be used as a server service visible to selected clients on the network--with or without a domain. It has a 4GB upper DBMS limit, can support hundreds of users and can execute the entire set of TSQL commands executable by the expensive versions. It requires no compression, can be backed up live, is undisturbed by power loss on the system and can support multiple simultaneous threads. It's fine to use this engine as an IIS-hosted DBMS. Unlike MSDE, SQL Server express has no "governor" and comes with (SP1) a set of developer tools that are very similar to those shipped with the other SKUs. SQL Server supports column encryption as well as a rich set of operators that are identical to those implemented on the upscale versions. This means applications written for SQL Express will be able to be easily ported to the upscale versions with virtually no conversion. SQL Express can be implemented as a client engine or as a service. The newly designed setup and deployment schemes make it far safer and easier to deploy on client systems (if necessary) or on a central “server” (which can simply be one of the client systems that’s always visible to the others).
* SQL Server Express--Advanced Services edition: This is SQL Server Express with additional functionality that includes Reporting Services and Full Text Search. While this increases the DBMS footprint, it makes it ideal for small-businesses that need a central reporting engine or want to create more sophisticated applications.
* SQL Server Everywhere: This is essentially the well-know SQL Mobile/SQLCE engine that was originally designed for PDAs and Smart Phones. Its strength is its ability to replicate to a SQL Server (Workgroup edition or better) server to keep data on the remote device in sync. It has a very small footprint and its database can be totally encrypted. While just cleared (license-wise) for use on Windows desktop systems, its architecture and DBMS engine was never designed for use on a website. It supports a subset of the T‑SQL used in the other editions (for example, it does not have stored procedures), but it’s ideal for many lightweight single-user situations where you simply need a fast, light data repository that can handle SELECT, JOIN and the other basic relational DBMS functions. I think this version has a lot of promise as a local cache engine, sophisticated business rule management schemes and many other uses.
All All of the SQL Server versions are closely integrated into the Visual Studio toolset. The MDF databases can be deployed with an application or installed separately. When it comes time to update the engine, it's included in the Windows Update list of applications to update.
Yes, many people still rely on JET. Sometimes it's because it's all they know how to use--it's what they have been telling their customers to use for a decade--they have a stake in this intellectual and business investment. Yes, customers think it's okay until their data is lost or compromised or they outgrow it. I’ve dealt with a flood of people who have found JET has infiltrated their businesses—making debacles like the VA analyst’s loss of 26.5 million records a reality.
No, any new design should not use JET. Existing designs should move to a safer, more scalable and better supported DBMS. Keep in mind that Microsoft is trying to help customers world-wide to move away from JET and get out of the hole they’ve dug for themselves. There are plenty of Microsoft and non-Microsoft alternatives that make more sense. Unfortunately, Visual Studio best supports SQL Server. If that's your development platform, it's best to stick with some version of SQL Server.
I'll be discussing these issues in my sessions at TechEd Boston and in my ADO.NET workshop at VSLive in Las Vegas. I hope to see you there.
