Jet vs. Sql Server Express

[Guest]

Greetings -

I'm working on a new application and need to select a database for it. I
was wondering if anyone had any views on tradeoffs between going with
Jet/Access vs. SQL Server 2005 Express.

The database is going to sit on a server. I'm planning to implement .net
remoting from my client objects to a wrapper around the database, so the
database will be transparent to the distributed client apps.

I'll also access the database for ad hoc reporting and data manipulation.

I'm experienced with Access but haven't used SQL Server, so I would tend to
lean toward using Access/Jet. However, it seems most of the attention is on
SQL Server in everything I see about VS 2005.

Is Jet still getting attention?

What are the pros/cons of SQL Server vs. Jet?

Thanks for any help

kc

 

[john smith]

You definately want to go for SQL Server. It's a FAR better system in every
aspect, it's faster, more powerful, scales FAR better (although scaling
better than Access is hardly an accomplishment), you get real RDBMS features
(sprocs, triggers, a real security model, etc, etc), you get to use the
"full-featured" SqlClient which is great, etc, etc. Jet is pretty much the
very last DB I ever would use (well after Oracle, SQL Server of any flavor,
DB2, PostgreSQL, Sybase, etc etc). The answer isn't so much "use SQL Server"
as much as "anything BUT Jet"! The 2 don't even compare, one is a
office-worker/end-user type toy-ish DB, and the other is a serious,
high-quality/reliability, powerful, scalable, enterprise-grade RDBMS system
with support, an upgrade path, good tools, etc, etc.

Hope that helps

 

[David Browne]

Greetings -

I'm working on a new application and need to select a database for it. I
was wondering if anyone had any views on tradeoffs between going with
Jet/Access vs. SQL Server 2005 Express.

The database is going to sit on a server.
. . .

I didn't even read the rest of your post. If the database is on a server,
use SQL Server. Not Jet.

David

 

[William \(Bill\) Vaughn]

SQL Server Express Edition has come a long way since the first versions of
MSDE. It has a real team at MS working on its functionality and usability. I
think the choice becomes more "why use JET" when it's been found unsuitable
for databases that contain healthcare or other data that must be secure.
It's not designed to be used in a server--it never was. It is best accessed
through its native interface (DAO)--not ADO, ODBC or OLEDB or ADO.NET. It's
COM-based and dependent on the MDAC stack which is of itself problematic.
No, SQL Server Express is not as simple to use as JET. Deployment is more
difficult, connecting is more difficult, management is more difficult.
However, given the more stringent requirements of today's secure and
high-performance systems where customers actually expect their data to be
secure and accessible by the right people, using SQL Server is a given.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[john smith]

Forgot to add that link with some more infos about JET's limitations:
http://www.aspfaq.com/show.asp?id=2195 I think that page alone will convince
you to never use JET ever again There are tons of MS KB articles
recommending SQL Server over JET one could link to as well... Keep in mind
SQL Server Express is MUCH better than MSDE too (and less limited). I
personally have many gripes over JET (from excessive network traffic,
ridiculously slow, files' security goes broken once you try "compact and
repair", etc etc). I find the amount of people still using/putting up with
JET nowadays pretty amazing...

 

[Cor Ligthert [MVP]]

Kcamhi,

Completely agreeing with the text from William Vaughn, do I want to add the
aspect of reports to it.

If your user is a good Office MS Access user and you want to let him make
his own reports with that. Than that can be a reason to do it in MS Access.

And you understand it than of course, if you want to isolate that, than
..........................

I hope this helps,

Cor

 

[Mark Rae]

If your user is a good Office MS Access user and you want to let him make
his own reports with that. Than that can be a reason to do it in MS
Access.

Yes indeed, but only for development purposes. SQL Server Reporting Services
can import Access reports cleanly, so there's still no reason to use Access
for anything more than prototyping.

And you understand it than of course, if you want to isolate that, than

Sorry, much as I have tried, I *really* can't decipher that...

 

[Cor Ligthert [MVP]]

Mark,

Yes indeed, but only for development purposes. SQL Server Reporting
Services can import Access reports cleanly, so there's still no reason to
use Access for anything more than prototyping.

Does a good (expirienced) Office MS Access user direct know how to handle
the SQL Server Reporting Tool, AFAIK not, howeve maybe I make a mistake in
that?

Sorry, much as I have tried, I *really* can't decipher that...

If you want an expirienced MS Office Accer user isolate from making reports
with your data, than you should not start to give him a database with a Jet
engine

Cor

 

[mablejune]

On Fri, 30 Dec 2005 15:04:02 -0800, kcamhi

[snip]

I'm experienced with Access but haven't used SQL Server, so I would tend to
lean toward using Access/Jet. However, it seems most of the attention is on
SQL Server in everything I see about VS 2005.

If you are experienced with Access, then you won't have much trouble
learning MS SQL Server 2005. After you begin to understand stored
procedures you will only use Access for single user stand alone
applications. Go ahead and make the switch. You'll have much more
pleasure than pain...
[snip]

 

[Cyril Gupta]

Hello,

I have worked a lot on Jet and a little on SQL Server and MySQL. In my book
the choices are really simple. If it's an app that you need to redistribute
to a lot of people (read packaged applicatin) use Jet, if it's something
that will rest on one PC or is made with a server architecture in mind use
SQL.

I don't why so many guys dislike Jet, but I think it is a pretty nice data
storage paradigm, specially for packaged applications. Of course that
doesn't mean it gives users the performance or functionality of SQL Server,
but you can't pack SQL Server in your app's setup either.

Cheers
Cyril Gupta

 

[Miha Markic [MVP C#]]

SQL Server Express Edition has come a long way since the first versions of
MSDE. It has a real team at MS working on its functionality and usability.
I think the choice becomes more "why use JET" when it's been found
unsuitable for databases that contain healthcare or other data that must
be secure. It's not designed to be used in a server--it never was. It is
best accessed through its native interface (DAO)--not ADO, ODBC or OLEDB
or ADO.NET. It's COM-based and dependent on the MDAC stack which is of
itself problematic. No, SQL Server Express is not as simple to use as JET.
Deployment is more difficult, connecting is more difficult, management is
more difficult.

I am not sure if I agree on deployment and management. It might be true but
only if everything works fine.
And with Access it can go wrong, very wrong as it is very much affected by
dll hell.

 

[Cindy Winegarden]

Hi KC,

Just to add, if you use SQL Express then upgrading to a full version of SQL
Server is simple. Also, if the user wants to use Access for reports, it's
easy enough to connect to SQL Server.

 

[Cor Ligthert [MVP]]

Cindy,

Also, if the user wants to use Access for reports, it's easy enough to
connect to SQL Server.

True, stupid me, thanks for making a correction on my message.

Lol

Happy NewYear,

Cor

 

[William \(Bill\) Vaughn]

Ah, while it's a compact DBMS engine, it's not secure. Most of the companies
I work with require a secure paradigm--one that the IT department can manage
when necessary. JET does not fall into that category. I would choose SQL
Mobile over JET any day.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[George Gomez]

Hello,

I have worked a lot on Jet and a little on SQL Server and MySQL. In my book
the choices are really simple. If it's an app that you need to redistribute
to a lot of people (read packaged applicatin) use Jet, if it's something
that will rest on one PC or is made with a server architecture in mind use
SQL.

I don't why so many guys dislike Jet, but I think it is a pretty nice data
storage paradigm, specially for packaged applications. Of course that
doesn't mean it gives users the performance or functionality of SQL Server,
but you can't pack SQL Server in your app's setup either.

Cheers
Cyril Gupta

SQL Server Express 2005 is redistributable. You can also check out the
Microsoft SQL Server Management Studio Express - Community Technology
Preview to manage the database.

As far as Access versus SQL server go with SQL server because it will be
a served database.

George

 

[Otis Mukinfus]

[snip]

SQL Server Express 2005 is redistributable. You can also check out the
Microsoft SQL Server Management Studio Express - Community Technology
Preview to manage the database.

As far as Access versus SQL server go with SQL server because it will be
a served database.

George

You're right about SQL Server Express, George.

However, if you know that your application will be used by only one
user and you can't be sure about his/her ability to understand
maintaining a SQL Server database, then I believe Access is a good
solution. In fact you can even compact and repair one when the user
closes the application and they will never know you did it for them.

Naturally I'm not talking about even the smallest enterprise
application ;-).

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[William \(Bill\) Vaughn]

Sure. But why compact/repair when the user turns off the system or it loses
power? This is not required in SQL Server. SQL Express is designed for this
kind of application too. Sure, it has more power than you need, but it also
has a lot more stability, security and less worries than any JET rig. Still
think it's too much? Consider the SQL Mobile edition. It's light, fast and
small and does not share JET's litany of issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

[snip]

SQL Server Express 2005 is redistributable. You can also check out the
Microsoft SQL Server Management Studio Express - Community Technology
Preview to manage the database.

As far as Access versus SQL server go with SQL server because it will be
a served database.

George

You're right about SQL Server Express, George.

However, if you know that your application will be used by only one
user and you can't be sure about his/her ability to understand
maintaining a SQL Server database, then I believe Access is a good
solution. In fact you can even compact and repair one when the user
closes the application and they will never know you did it for them.

Naturally I'm not talking about even the smallest enterprise
application ;-).

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[Otis Mukinfus]

Sure. But why compact/repair when the user turns off the system or it loses
power? This is not required in SQL Server. SQL Express is designed for this
kind of application too. Sure, it has more power than you need, but it also
has a lot more stability, security and less worries than any JET rig. Still
think it's too much? Consider the SQL Mobile edition. It's light, fast and
small and does not share JET's litany of issues.

You raise some good points Bill. I hadn't thought of SQL Mobile,
although I did build an app that used it once for the PPC.

Thanks for the input.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[David Browne]

Ah, while it's a compact DBMS engine, it's not secure. Most of the
companies I work with require a secure paradigm--one that the IT
department can manage when necessary. JET does not fall into that
category. I would choose SQL Mobile over JET any day.

I think you mean SQL Server Express Edition, not SQL Server Mobile Edition.
Mobile is only available for

Microsoft Windows CE 5.0,
Microsoft Windows XP Tablet PC Edition,
Windows Mobile 2003 Software for Pocket PC,
and Windows Mobile 5.0
http://www.microsoft.com/sql/editions/sqlmobile/sysreqs.mspx

In particular not Windows XP Home or Professional and not Windows Server
2003.

David

 

[Paul Clement]

¤ Greetings -
¤
¤ I'm working on a new application and need to select a database for it. I
¤ was wondering if anyone had any views on tradeoffs between going with
¤ Jet/Access vs. SQL Server 2005 Express.
¤
¤ The database is going to sit on a server. I'm planning to implement .net
¤ remoting from my client objects to a wrapper around the database, so the
¤ database will be transparent to the distributed client apps.
¤
¤ I'll also access the database for ad hoc reporting and data manipulation.
¤
¤ I'm experienced with Access but haven't used SQL Server, so I would tend to
¤ lean toward using Access/Jet. However, it seems most of the attention is on
¤ SQL Server in everything I see about VS 2005.
¤
¤ Is Jet still getting attention?
¤
¤ What are the pros/cons of SQL Server vs. Jet?
¤
¤ Thanks for any help

Given the fact that you're working in a distributed environment and don't require database features
other than a data store, I would recommend using SQL Server.

An Access database is typically high maintenance and requires a fair amount of attention when used
in a multi-user or distributed environment. It wasn't designed to be used in a distributed
environment and does not scale well.

If this was a simple client/server web or desktop app with a limited number of users then Access
might be a suitable solution. But that doesn't sound like what you're describing.


Paul
~~~~
Microsoft MVP (Visual Basic)