Why upgrade to MS SQL Server (be it 2000 or 2005) from Access (say, 97)?

[NickName]

Probably this question has been asked hundreds of times and yet net
search has not generated satisfactory enough answer, at least, to me.
And OK, let's assume your organization has more than 200 employees,
just one measure to indicate that it's not small and data processing
needs are quite extensive (for both OLTP and OLAP).

We've heard so much about concurrency support, stability and
performance. Are there any real persuasive paper out there to talk
about it? Now, let me also put it in another perspective, say, you're
a Microsoft sql server sales guy or gal for that matter for new
accounts. What you got?

Thanks.

[Ol!v!é]

NickName wrote:

Probably this question has been asked hundreds of times and yet net
search has not generated satisfactory enough answer, at least, to me.
And OK, let's assume your organization has more than 200 employees,
just one measure to indicate that it's not small and data processing
needs are quite extensive (for both OLTP and OLAP).

We've heard so much about concurrency support, stability and
performance. Are there any real persuasive paper out there to talk
about it? Now, let me also put it in another perspective, say, you're
a Microsoft sql server sales guy or gal for that matter for new
accounts. What you got?

Thanks.

One good reason is that using SQL Server is that the network load is
much less than compared to using linked tables.
I've executed the following query from the VBA-Direct window:
UPDATE table SET column = 'value'
table contained approx. 7000 records. The packet count for executing
this with SQL Server was exactly 9. When using MS Access as a backend,
the packet count was more than 65000.
Also, I believe that Access can only work with 25 connections at most,
while SQL Server can have virtually unlimited connections. Please
correct me when I'm wrong.
When using the database locally (i.e. not over a network), upgrading to
SQL Server would be extra overhead imho.

Olivié

[Trevor Best]

Ol!v!é wrote:

Also, I believe that Access can only work with 25 connections at most,
while SQL Server can have virtually unlimited connections. Please
correct me when I'm wrong.

That would depend on how well/badly the Access application was written,
the topology used (monolithic MDB, split Code/tables, unbound forms,
etc). YMMV a lot.

To the OP:
SQL Server will handle more users and support larger databases, both in
the physical limits and practical limits, has better security, is much
more robust and AFAIK doesn't lose pointers to text (memo) columns like
Jet does :-)

[david epsom dot com dot au]

> Also, I believe that Access can only work with 25 connections

The multi-user characterisation for Access 95 used 32 automated
workstations, which hit the database a bit harder than 32 human
users would.

UPDATE table SET column = 'value'
table contained approx. 7000 records. The packet count for
this with SQL Server was exactly 9. When using MS Access as
None of my clients would let you near their SQL Servers!

If you have your 25 (min) SQL Server users doing updates
like that, all the real users will be out to lunch while
they wait for their applications to respond!

Building applications for 25 users means not writing
queries like that.

(david)
""Ol!v!é"" <[e-mail]> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ... NickName wrote:

Probably this question has been asked hundreds of times and yet net
search has not generated satisfactory enough answer, at least, to me.
And OK, let's assume your organization has more than 200 employees,
just one measure to indicate that it's not small and data processing
needs are quite extensive (for both OLTP and OLAP).

We've heard so much about concurrency support, stability and
performance. Are there any real persuasive paper out there to talk
about it? Now, let me also put it in another perspective, say, you're
a Microsoft sql server sales guy or gal for that matter for new
accounts. What you got?

Thanks.

One good reason is that using SQL Server is that the network load is much
less than compared to using linked tables.
I've executed the following query from the VBA-Direct window:
UPDATE table SET column = 'value'
table contained approx. 7000 records. The packet count for executing this
with SQL Server was exactly 9. When using MS Access as a backend, the
packet count was more than 65000.
Also, I believe that Access can only work with 25 connections at most,
while SQL Server can have virtually unlimited connections. Please correct
me when I'm wrong.
When using the database locally (i.e. not over a network), upgrading to
SQL Server would be extra overhead imho.

Olivié

[Ol!v!é]

david epsom dot com dot au wrote:

UPDATE table SET column = 'value'
table contained approx. 7000 records. The packet count for
this with SQL Server was exactly 9. When using MS Access as

None of my clients would let you near their SQL Servers!

If you have your 25 (min) SQL Server users doing updates
like that, all the real users will be out to lunch while
they wait for their applications to respond!

Building applications for 25 users means not writing
queries like that.

The query I wrote was only for the demonstration of the difference in
network load between MS Access and SQL Server, not as an example on how
to write queries.

Olivié

[NickName]

I appreciate all the inputs. Do any of you have some pointer to some
well-thought-out paper on the topic?