Access vs. SQL

How can I justify the cost moving an access database to SQL Server? I need
to advise a manager, but all I come up with is more stable and faster (the A
ccess database has been stable) and size limitations are higher. Is there a
study I can quote with qua
ntifiable data?
Thankshttp://www.aspfaq.com/2195
http://www.aspfaq.com/2345
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"D. McCue" <dcmccue@.hotmail.com> wrote in message
news:DF85F8C2-3561-4882-8233-506259B21A64@.microsoft.com...
> How can I justify the cost moving an access database to SQL Server? I
> need to advise a manager, but all I come up with is more stable and faster
> (the Access database has been stable) and size limitations are higher. Is
> there a study I can quote with quantifiable data?
> Thanks|||Hi ,
May be SQL is not the solution for your situation. Why do you think you
should upgrade to MS SQL?
Typically people talk about
Scalability: The ability to have more people accessing the database at the
same time without issues
Redundancy: Mirroring DB or Clustering so the DB system will be there 24/7
Speed: Fast response times no matter how many people connect to the DB
system
Growth of Data: SQL server can handle millions of rows of data with ease
There no use in moving just because and it's quite expensive.
____________
Greg
"D. McCue" <dcmccue@.hotmail.com> wrote in message
news:DF85F8C2-3561-4882-8233-506259B21A64@.microsoft.com...
> How can I justify the cost moving an access database to SQL Server? I
need to advise a manager, but all I come up with is more stable and faster
(the Access database has been stable) and size limitations are higher. Is
there a study I can quote with quantifiable data?
> Thanks|||if cost is an issue, consider upsizing to Microsoft Data Engine (MSDE)
First.
MSDE is the SQL Server Engine throttled to 10 user maximum.
this is FAR Better than Access and provides all the benefits of the SQL
Server Engine.
Once upsized to MSDE, a transition to True SQL Server is a snap.
Just my 2 cents.
Greg Jackson
PDX, OR|||> MSDE is the SQL Server Engine throttled to 10 user maximum.
This is not entirely accurate. The engine is throttled at 5 *concurrent
workloads* ... which is not necessarily the same thing as 5 users. You
could easily have 200+ users connected to MSDE without triggering the
governor. You could also have one user with a poorly-designed
multi-threaded app taking up 5+ threads and triggering the governor. It has
nothing at all to do with number of users.
In addition to 5 user workloads, MSDE will support up to 3 system threads
which can be performing work concurrently, so you can actually have up to 8
concurrent workloads before the governor will be triggered. Not sure where
you got the number 10 you quoted, do you have a source for that limitation?
Note that the governor is a lot more complex than just asking "how many
workloads are happening, okay let's cut the power", so you may have
difficulty creating a reliable repro that (dis)proves any of the above.
For a more thorough understanding of the workload governor in MSDE, please
download the official Word document from
http://www.microsoft.com/sql/msde/t...WorkLoadGov.asp

> this is FAR Better than Access and provides all the benefits of the SQL
> Server Engine.
Actually, there are some features missing (SQL Mail, Olap, fully featured
replication, > 2 GB databases). But for the most part you are right. See
http://www.aspfaq.com/2343 and for official documentation you can start
here: http://msdn.microsoft.com/library/e...ar_sa2_9gz4.asp
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Stability, data recovery, and performance are the major advantages. Can you
afford to lose a days worth of work? Is your data expected to grow, how mu
ch? How many users are in the database, is your user base expected do grow?
Are you going to web-enab
le your application? You have to do the groundwork and answer these questio
ns and decide if the cost involved in upsizing your database is worth it.|||Thank you all for the great information.
We have 4 full-time users of the database and 8 more part-time users|||RE/
>How can I justify the cost moving an access database to SQL Server?
- Point-in-time recovery instead of last night's backup
- Offloading DB relibility to the IT group that manages SQL Server DBs
- Ability to server more than a certain number of concurrent users.
What number? I'll let others discuss that... My chosen number is 10.
- Immunity to being corrupted by things like running out of resources on
a user's machine, DLL conflicts, flakey NICs, server problems, loose
cables, heavy LAN traffic, power surges, and so-forth
- Simpler security in that it can be integrated with Windows/LAN IDs
- Stringer/more flexible security in that access to tables can be limited to
certain processes and not just anybody that can ODBC connect with the
right ID/PW.
There's more, but it doesn't come to mind right now...
But you have to offset the strengths of SQL server with both it's
downsides and JETs strengths.
- SQL Server is a *lot* more expensive to maintain. In most organizations
there's an entire priesthood devoted to it.
- SQL Server takes more manhours to develop in than JET. Table relationshi
ps,
RI enforcement are a snap to set up in JET - yet a significant effort in
SQL Server.
- That point-in-time recovery doesn't come for free. Plans have to be
established and code written...compared to JET where you can get last
night's DB back just by doing a file restore.
- Writing an app to SQL Server involves doing some things differently in
order to minimize the performance hit - so moving after-the-fact may
cost more than just reconnecting the tables...but maybe not...
- With a light load and a decent LAN, JET is *fast*...One of this NG's more
eminant posters made the comparison between a VW Golf GTI and a diesel
locomotive. Without a big load, guess which one is faster? But
start loading them up and eventually there's a crossover point...
--
PeteCresswell|||> - SQL Server takes more manhours to develop in than JET. Table
> relationships,
> RI enforcement are a snap to set up in JET - yet a significant effort in
> SQL Server.
I disagree. Planning should be the largest part of this effort, and that
shouldn't change based on deployment platform. Foreign key relationships,
constraints, etc. are fairly trivial to write in T-SQL, and I don't see what
the "significant effort" could be, except looking it up in BOL (2 minutes)
when you forget the exact syntax. You can also use the GUI in Enterprise
Manager and it's even easier (I don't know how Jet is easier than that,
maybe you can elaborate), though personally I don't make any changes to the
database that I can't script out, store in SourceSafe, reverse quickly, etc.
A|||RE/
> Foreign key relationships,
>constraints, etc. are fairly trivial to write in T-SQL, and I don't see wha
t
>the "significant effort" could be, except looking it up in BOL (2 minutes)
>when you forget the exact syntax. You can also use the GUI in Enterprise
>Manager and it's even easier (I don't know how Jet is easier than that,
>maybe you can elaborate)
With Jet, it's drag-drop-click in a GUI.
OTOH, your observation about scripting could lead me to another consideratio
n
where one could say SQL Server is preferable to JET: elevating system chang
es.
I'm sure that one can "script" JET relationship/RI enforcement changes by
writing DAO code...but it's not something I'd choose to do...so when it's
elveation time, those changes would probably be made "by hand" following a
written procedure.... and that's not what most IT people would feel the mos
t
comfortable with. The scripting thing seems to be a point in favor of SQL
Server.
--
PeteCresswell