What is the Microsoft Jet database and is it appropriate for a small app with sensitive information?

views:

278

answers:

+2 Q:

What is the Microsoft Jet database and is it appropriate for a small app with sensitive information?

Hi all,

I'm planning a new ASP.NET project that will become a product that is installed by techinical staff that work in various companies. The program will be storing sensitive information that shouldn't be easily accessible to staff including the technical staff.

I need to balance an easy straight forward installation with security and reliability. The default db these days seems to be MS SQL for C# developers but some companies that will be installing this may not have their own SQL server so someone has suggested I use Microsoft JET database. What is MS JET is it a product or a technology? The database will be quite basic just a couple of tables, I don't need all the power of SQL, are there any db suitable that don't require an installation like SQL (i.e. a simple file)?

Thanks J

The default engine for the MS Access application is know by the generic term 'Access Database Engine'.

'Jet' refers specifically to versions of the Database Access Engine prior to Access2007. For what it is worth, the version-specific term for Access2007 is 'ACE'.

I'm not going to advise as to whether it's advisable to use the Access Database Engine other than to say that it's good for small standalone apps with very few users.

David Stratton 2009-08-27 19:17:39

Jet != MS Access. That is an incredibly ignorant assertion.

David-W-Fenton 2009-08-28 02:19:44

More like an incomplete assertion typed in a hurry. I really have to learn to stop doing that. But you're right, my answer was brief enough not to be of any good use and probably deserved a down vote or two.

David Stratton 2009-08-28 02:25:58

+1 to counteract tonight's wave of rude people. Is it a full moon or something?

MusiGenesis 2009-08-28 04:12:16

It's rude to downvote inaccurate answers?

David-W-Fenton 2009-08-28 23:24:00

The Microsoft JET ODBC Drivers are used when connecting to a Microsoft Access (*.mdb) Database. It's one way of supporting an embedded database.

I would recommend against using Access in favor of something like SQLLite or SQL Server Compact Edition. Using either of those you'll get more robust support for SQL syntax and, more than likely, improved performance.

Justin Niessner 2009-08-27 19:19:53

Microsoft JET database is the technology that Microsoft Access uses. I would strongly recommend using something like SQL Server Express (free download at Microsoft - http://www.microsoft.com/express/sql/default.aspx) instead. SQL Server Express has a few limitations (max. number of simultaneous connections, 2 GB database size, etc.) but I think it's a lot better than JET. Also, if your application gets a lot of use and needs a 'serious' database you can always upgrade to the regular version of SQL Server.

TLiebe 2009-08-27 19:20:22

+8 A:

The preferred way to do serverless database work in .NET these days is to use SQL Server Compact - it has database-in-a-file, and doesn't require any server processes - all code runs directly inside your process as a simple DLL.

Using Jet as a general-purpose storage backend is not recommended. It used to be a reasonable default choice, but now Jet is exclusively owned by Microsoft Access team, and will be further developed and tailored for their purposes.

Pavel Minaev 2009-08-27 19:20:44

Is Microsoft still using Jet in Access 2007?

ZippyV 2009-08-27 19:30:58

Yes, and that's the only thing it is supposed to be used for.

Pavel Minaev 2009-08-27 19:46:14

"The only thing it is supposed to be used for..." Well, except when it isn't -- Jet 4 is still a part of Windows, as it's used by Active Directory.

David-W-Fenton 2009-08-28 02:14:05

That's a very different kind of JET. Access is JET Red. AD and Exchange use JET Blue. Both are totally different. The latter was never referred to as "Jet" publicly - as an API, it's known as "Extensible Storage Engine". On the other hand, the Access version was actually known as Jet outside MS.

Pavel Minaev 2009-08-28 03:57:39

Reference: http://en.wikipedia.org/wiki/Extensible_Storage_Engine

Pavel Minaev 2009-08-28 03:58:13

Yes, of course AD is Jet Red, but Jet 4 is *still* part of Windows and the reason given for it being part of Windows by employees of Microsoft is that it is used for AD. I don't understand the exact relationship (perhaps there's a Jet Red ISAM that Jet Blue is using), but it's a fact that Jet 4 is part of Windows and the codebase is managed by the Windows team.

David-W-Fenton 2009-08-28 23:26:12

First of all, Jet Red is Access. Jet Blue is AD and Exchange - not the other way around. As for the rest of it - can you provide a reference for the claim that "codebase is managed by the Windows team? My references would be this: http://msdn.microsoft.com/en-us/library/ms810810.aspx#mdac technologies road map old_topic5 - " The Microsoft Jet Database Engine 4.0 components entered a state of functional deprecation and sustained engineering, and have not received feature level enhancements since becoming a part of Microsoft Windows in Windows 2000."

Pavel Minaev 2009-08-29 00:14:02

And this: http://blogs.msdn.com/access/archive/2005/10/13/access-12-s-new-data-engine.aspx

Pavel Minaev 2009-08-29 00:15:15

The situation with Jet 4 versus Jet 4.5, er, I mean, ACE, was detailed quite clearly in the runup to the release of Access 2007 on the Access team's blog, cited by Pavel. Sorry about mixing up Red vs. Blue -- it has always seemed like a useless distinction to me. Jet Blue is a really crappy database engine, especially in comparison to *real* Jet.

David-W-Fenton 2009-09-02 02:22:13

Also, keep in mind that the "data access roadmap" you cite is for developers using platforms other than Access and Office. And it's badly out of date since it doesn't at all reflect the existence of the ACE (i.e., what is more or less "Jet 4.5").

David-W-Fenton 2009-09-02 02:23:44

Yes, of course. When they speak of Office and specifically Access, they always say "ACE" now, and refer to "Jet" as deprecated. ACE - Jet next gen - isn't deprecated, of course, but you can't use it with ADO.NET either (well you can, but this is also deprecated), so in the context of the question it is effectively deprecated for all relevant purposes.

Pavel Minaev 2009-09-02 03:05:12

@David W. Fenton: "Jet Blue is a really crappy database engine, especially in comparison to real Jet" -- From that Wikipedia article: "ESE has write ahead logging and snapshot isolation for guaranteed crash recovery... ESE database may contain up to 232 pages, or 16 terabytes of data... Temporary tables can be used for partial and complete result sets..." Sound like it has some nice features but because it lacks a SQL gateway I've never used it. Have *you*? ;)

onedaywhen 2009-09-02 13:35:06

+3 A:

Sounds like SQLite may be a good option, very easy install. You can implement security independent of your db platform, through authentication, encryption, etc.

RedFilter 2009-08-27 19:20:51

I would stay away from Jet. It has performance issues compared with pretty much every other database out there. Also, it uses a non-standard version of SQL (Jet-SQL), which means you'd have a difficult time porting your application to a different database once you decide to stop hitting yourself in the head with a hammer. It's true that even T-SQL isn't universal across databases, but Jet-SQL is really different.

Plus it's Access, which means your peers will laugh at you every time you mention it. This might not be completely fair, but it will happen.

MusiGenesis 2009-08-27 19:34:26

Jet/ACE is actually a stellar performer. My experience is that it's only in the hands of the ignorant that it bogs down. That said, it's not appropriate for all applications, but that doesn't mean it is seldom or never an excellent choice.

David-W-Fenton 2009-08-28 02:19:13

I notice you didn't say anything about people laughing at you if you use Access.

MusiGenesis 2009-08-28 02:36:00

That's generally an indication of either ignorance or stupidity.

David-W-Fenton 2009-08-28 23:23:27

+2 A:

I'm a big fan of Access and Jet/ACE, but for sensitive data, Jet/ACE is not a good choice, as it can't really be secured like a server database engine can.

That said, many people who think their data is sensitive and needs the highest level of security technology are just fooling themselves.

Security is almost always as much a people problem as it is a technology problem, and even with the most secure technology, you still have to trust people in order to give them access to the data they need to do their work. Your database may be totally locked down, but if you can trust your sysadmin, you're not secure at all.

Jet/ACE may be secure enough for a particular application.

And in terms of cost/benefit ratio, it may be a better choice, taking everything into account.

But as the original question was framed and knowing nothing else about the application, I'd definitely recommend against using Jet/ACE.

David-W-Fenton 2009-08-28 02:23:07

+1 because you downvoted me, and Jesus said to turn the other cheek (he didn't say which kind of cheek, though). :P

MusiGenesis 2009-08-28 02:34:39

ansaurus

tags:

views:

answers:

What is the Microsoft Jet database and is it appropriate for a small app with sensitive information?

related questions