Discussion - Jet vs. SQL Server

  • Thread starter Thread starter Joel Wiseheart
  • Start date Start date
J

Joel Wiseheart

This post is meant as more of a debate, not a specific
question.

Okay, I've been programming in Access for years, and am
just now learning the new SQL Server back-end stuff
incorporated into the new versions of Access. It seems
like a bunch of different ways of doing the exact same
thing.

I'd be interested in seeing reasons (A bullet-point list
is preferable) of WHY SQL Server is supposed to be better
than Jet, other than Bill Gates says so. I keep hearing
it's better, but WHY?

I'd be interested in hearing viewpoints on features (What
SQl Server can do that Jet can't), performance, cost,
installation, etc.

Opinions welcome! Thanks for your input!
 
Joel Wiseheart said:
This post is meant as more of a debate, not a specific
question.

Okay, I've been programming in Access for years, and am
just now learning the new SQL Server back-end stuff
incorporated into the new versions of Access. It seems
like a bunch of different ways of doing the exact same
thing.

I'd be interested in seeing reasons (A bullet-point list
is preferable) of WHY SQL Server is supposed to be better
than Jet, other than Bill Gates says so. I keep hearing
it's better, but WHY?

I'd be interested in hearing viewpoints on features (What
SQl Server can do that Jet can't), performance, cost,
installation, etc.
Click to expand...

From my Why would I want to upgrade my Access app to SQL Server? page

The biggest problems are in stability of the hardware and the number
of users making changes. Reporting or inquiry only users don't make a
difference.

Also your big concerns are how mission critical is the data and can
the data be rekeyed if you lose a day.

Mission critical means can you afford to lose an hour if the database
is down. Frequently the data can't be rekeyed. A classic example being
a call centre where you are receiving incoming calls.

I recall a posting by someone working for a large casino/hotel
operation in the mid to late 80s stating to what lengths they went to
ensure they would never lose a reservation. They spend over a million
dollars duplicating their IBM S/38 mini-computer in another offsite
location with data inserts and updates being copied from the main
system to the backup system in under a second.

Their attitude was that they could never afford to lose a room
reservation. Imagine the mess if they lost a days worth of phone
calls. <shudder> And the newspaper stories by the upset clients. And
expenses while they placate the customers so they don't go to the
newspapers. <smile>

Another option, if you have the Office Developers version is to use
MSDE but there are other considerations here. MSDE is SQL Server
without many tools and with a limitation of five connections. Which is
*not* the same as five users.

Should you require access via slow connections, defined as any
connection speed less than 10 mpbs, SQL Server can also be a good
solution. Once the Access app is fully converted to using stored
views rather than Access queries SQL Server handles the selection,
updating and appending of records rather than Access. Which will
minimize network traffic.

However, depending on the speed of the connection, this may not be
sufficient. You may wish to also investigate Terminal Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Joel,

Her are a few point to consider:

1. SQL Server is a true client/server relational database system. Access is
not. Access needs a file server to send data to the client and the client
does the query work. SQL Server sends only the records in the result set.

2. SQL Server has far superior security and can run at C2 security level.
Access files must be shared on the file server and can potentially be
deleted by any database user.

3. SQL Server has superior backup and restore capability including the
ability to recover to a point in time which Access can not do. Also Access
does not have full ACID properties and cannot support atomic transactions
(it can not back out of transactions because it does not have a transaction
log).

4. SQL Server supports stored procedures and cached execution plans for
queries that allow faster data access. Access does not.

5. SQL Server has a variety of tools for ETL and data analysis that Access
does not have.

6. SQL Server has far superior scalability. It can handle terabytes of data
and 10's of thousands of transactions per hour and 1000s of simultaneous
connections. It has a TPC-C benchmark score higher than Oracle or DB2. It
smokes Access. Access can only grow to 2GB and is constantly in danger of
corruption in a multi-user environment.

Those are some of the reason why SQL Server is a superior RDBMS. Note: It
does not mean that Access is not better suited to some activities. Only
comparing the two as RDBMS.

-Daran
 
What was neglected here is that the SQL-Server comparison below is not with
Access, but with the JET database engine. There are a few corrections to the
post, but it is basically correct as to the differences. It does, however,
present "Access" in a condescending tone, by someone who appears to be only
barely familiar with the JET engine, something which should be considered
when judging its accuracy. In fact, the Access program can be used with
either the JET engine, or the SQL-Server engine, and, now comes with both
JET and the MSDE which is a "crippled" version of SQL-Server.

Typically, the JET engine is more than satisfactory for low numbers of users
(under 20 to 40 depending upon design and version) and smaller databases
(under 200 to 300 MB). For these smaller databases, the main considerations
should be cost (JET will cost a fraction of what it will cost to design and
implement SQL-Server), security (SQL-Server is really the only truly secure
engine of the 2), and service (SQL can run 24/7 for as long as the server is
running without any extra down-time for maintenence, while JET should be
maintained by compaction at least once per week for 5 to 10 minutes).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top