Jet vs. Sql Server Express

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 :)
 
kcamhi said:
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.
. . .
Click to expand...

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

David
 
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.
__________________________________
 
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...
 
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
 
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.
Click to expand...

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
Click to expand...

Sorry, much as I have tried, I *really* can't decipher that...
 
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.
Click to expand...
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...
Click to expand...
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
 
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.
Click to expand...

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]
 
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
 
William (Bill) Vaughn said:
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.
Click to expand...

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.
 
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.
 
Cindy,
Also, if the user wants to use Access for reports, it's easy enough to
connect to SQL Server.
Click to expand...

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

Lol

Happy NewYear,

Cor
 
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.
__________________________________
 
Cyril said:
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
Click to expand...

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
 
[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
Click to expand...

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
 
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.
__________________________________

Otis Mukinfus said:
[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
Click to expand...

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
Click to expand...
 
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.
Click to expand...

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
 
William (Bill) Vaughn said:
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.
Click to expand...

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
 
¤ 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)
 
Back
Top