SQL server vs Access

[Samantha]

All,
Am trying to convert my dept over to SQL server instead of Access. I need
to present reasons for the change. Basically I need pros and cons of using
sql server vs Access to justify cost etc..
Please help.
thanks
sam

[kevin knudson]

Well first the costs can be minimized by using MSDE for the DB, depending
on the number of active users. Buy the developer edition for development,
and deploy on MSDE.
SQL pros
Robust and scalable
Much better reliablity, and recoverablity, Point in time recoverablity.
Full future support, MS is showing signs of retiring Access(at least
the Jet engine portion)

Cons
Cost, the full version is expensive. But it you suffer more than
a couple of days of down time due to Access problems, SQL could quickly pay
for itself by limiting, or eliminating the down time and or data loss.






"Samantha" <mermaidhaven@hotmail.com> wrote:
>
>All,
>Am trying to convert my dept over to SQL server instead of Access. I need
>to present reasons for the change. Basically I need pros and cons of using
>sql server vs Access to justify cost etc..
>Please help.
>thanks
>sam
>

[kevin knudson]

Well first the costs can be minimized by using MSDE for the DB, depending
on the number of active users. Buy the developer edition for development,
and deploy on MSDE.
SQL pros
Robust and scalable
Much better reliablity, and recoverablity, Point in time recoverablity.
Full future support, MS is showing signs of retiring Access(at least
the Jet engine portion)

Cons
Cost, the full version is expensive. But if you suffer more than
a couple of days of down time due to Access problems, SQL could quickly pay
for itself by limiting, or eliminating the down time and or data loss.

IMHO if you have more than 5, maybe 10 users on an Access DB you are
playing with fire. Locking, updating etc. will lead to a crash and/or data
corruption.

KlK, MCSE

Sorry for the double post, I accidentally posted half way through.





"Samantha" <mermaidhaven@hotmail.com> wrote:
>
>All,
>Am trying to convert my dept over to SQL server instead of Access. I need
>to present reasons for the change. Basically I need pros and cons of using
>sql server vs Access to justify cost etc..
>Please help.
>thanks
>sam
>

[Steve Jackson]

another post gave some good pros and cons. Here's some other comments,

SQL Server logs all updates/inserts/deletes to the database. If the
database is corrupted, or the disk crashes, you can recover all
updates made to the time of the crash. (assuming the log is on a
different disc) If Access is corrupted or crashes, you can only
restore to a previous file backup.

For large databases, Access needs to be frequently defragmented, SQL
server handles this more automatically.

CONS: Sql server requires more software installed on the client
computer, and you have to deal with license counts.

In short, SQL is a a database management system (DBMS). Access is a
file based database with some scripting and programming capability.



Steve Jackson

On 17 Jan 2002 13:25:57 GMT, "Samantha" <mermaidhaven@hotmail.com>
wrote:

>
>All,
>Am trying to convert my dept over to SQL server instead of Access. I need
>to present reasons for the change. Basically I need pros and cons of using
>sql server vs Access to justify cost etc..
>Please help.
>thanks
>sam
>


Steve Jackson, Enterprise Section Leader

[ed]

"Samantha" <mermaidhaven@hotmail.com> wrote:
>
>All,
>Am trying to convert my dept over to SQL server instead of Access. I need
>to present reasons for the change. Basically I need pros and cons of using
>sql server vs Access to justify cost etc..
>Please help.
>thanks
>sam
>

[ed]

SQL MSDE is free and you can have databases up to 2 gig, more than enough
for anyone converting from Access. Also using ODBC you can link the SQL
tables to an access application and your application in access will run as
if it is still using an access database except the performance will be greatly
improved. This will mean one down time and no converting of projects. As
you learn more about SQL and TSQL you will be able to improve you projects
to take full advantage of SQL SERVER


ed

"Samantha" <mermaidhaven@hotmail.com> wrote:
>
>All,
>Am trying to convert my dept over to SQL server instead of Access. I need
>to present reasons for the change. Basically I need pros and cons of using
>sql server vs Access to justify cost etc..
>Please help.
>thanks
>sam
>

[Chris Jones]

Hi Samantha,

I have been dealing with Access from the early versions and have since seen
the light of SQL Server. I have been very frustrated with Access in the
past becuase it is a fantastic tool for the small business, but severely
lacks the scalability and robustness of a serious RDMS. Others hint at the
problems, but it can be narrowed down to a few key elements.

Small business owner: (Recommendation Access)
Medium to Large business Owner: (Recommendation SQL Server all the way)
__________________________________________________________________________
Access Pros, Access may very well be the way to go for the small business.
It supplies versatility and powerful application interface creation. It
is VBA compliant and can therefore do most anything that you need it to programmatically,
it is one program rather than a few applications. (To use SQL as you have
been using Access you need: SQL Server, additional application to interace
with server from clients.)

Access Cons, Access has a severe problem when it is used too much in a 24/7/365
environment. It tends to inflate itself to astronomical sizes and must therefore
be maintained constantly. (PS This can be circumnavigated, to some degree,
by connecting into it with ADO)

SQL Server Pros,
Near zero administration once your set up with all your databases, very low
data loss, very low downtime, backup and restore to the hour if needed (Usually
to the day, depends on your needs), In house data security, etc.

SQL Server Cons,
Training of Administrator/Developer (Generally an unneed expense in the small
business environment), Overall Cost, No Application interface (ODBC connectivity
assures users of being able to get the data, but there may be nothing you
can do with it if you have no program to manipulate it. Office however is
rich with tools that can collect and manipulate the data from SQL Server.
So you need SQL Server and Another Program to get the most out of SQL Server.),
etc.
__________________________________________________________________________

The pro and cons in the medium to large business are usually subtly different
than the small. What I mean is the issues are still there in either case,
but in the larger business environement Accesses lack of real scalability
severly hampers its ability to compete with SQL Server. The only problem
that may arise out of using SQL Server in the larger business is that you
usually have to provide much more training for an administrator/developer
of SQL Server. While administration of SQL is usually very small, the setup
and analytical processing of data becomes much more difficult and time consuming.

Depending on your needs, all that data really should be used to the best
of your administrations ability. In order to get that data you need to be
able to make sense of it all, and present it in a manor that makes sence
to the untrained eye. A good administrator/developer will help this along
greatly.

I hope this helps at least a little,

Chris Jones
Database Administrator / Programmer + Internet


"Samantha" <mermaidhaven@hotmail.com> wrote:
>
>All,
>Am trying to convert my dept over to SQL server instead of Access. I need
>to present reasons for the change. Basically I need pros and cons of using
>sql server vs Access to justify cost etc..
>Please help.
>thanks
>sam
>