When to stop with Access and go to SQL Server

[RLN]

Our dept has several Access applications that could have the potential to
grow with user base and no doubt grow with data.

Does anyone have a good list of 'checkpoints' to know when an application is
beyond Access as far as usefulness goes and is ready to be migrated over to
SQL Server 2005?

thnx....
RLN

 

[RLN]

Let me qualify a statement I made in my original post.
"...as far as usefulness goes"
should have been phrased,
"...as far as performance goes"

....thnx

 

[Tony Toews [MVP]]

Let me qualify a statement I made in my original post.
"...as far as usefulness goes"
should have been phrased,
"...as far as performance goes"

Performance? I have databases on servers with hundreds of thousands
of records. To me other factors are more important.


However your big concern is 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.

Losing a day means that if you have to restore from backup do your
users have the paperwork in place so they can rekey the data? Are
there enough staff to re-enter that data?

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

Thier 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>

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

 

[Allen Browne]

As Tony says, you won't gain performance moving to SQL Server. What you gain
is flexibility, security, and stability.

As a broad guideline, you need SQL Server instead of Access if any of the
following are true:

a) The database will have hundreds of simultaneous users.

b) The database must run 24 x 7 (i.e. cannot be taken off-line to do a
backup.)

c) Security is crucial.

d) You envision millions of records in some tables before you design the
database.

e) You plan to use the database over an unstable network, and you don't want
to merely use Terminal Server sessions.

 

[RLN]

Tony & Allen:

I appreciate you both taking the time to respond back, thank you. I concur
with the commentary from both of you. Mission critical is a key point, as is
the flexibility and security. I realize Access lacks security and ability to
hold concurrent users of a large number. SQL Server more than makes up for
those.
While I am a long time Access developer who is integrating SQL Server 2005
with my current development efforts , I admit not having enough SQL Server
experience to make a really solid judgement call on -the- right time to
switch, but you both have provided good points here. I will save your points
made for future referral.

RLN

 

[Aaron Kempf]

if you've got ONE user or ONE record you should move to SQL Server

avoid the headaches.

move to Access Data Projects

 

[Aaron Kempf]

so tony.. how do you FORECAST how a database is going to be used for the
next 10 years?

rather than REWRITE it-- do it RIGHT THE FIRST TIME

ADP development is _EASIER_ than MDB

I don't need to use workarounds and pull Tony's scripts every 30 seconds

MDB has been obsolete for 10 years

 

[Aaron Kempf]

tony and allen are full of _CRAP_

you _WILL_ gain performance movnig to SQL Server

thigns like 'Analysis Services' make 90% of canned reports 100% obsolete

 

[Aaron Kempf]

this:

e) You plan to use the database over an unstable network, and you don't want
to merely use Terminal Server sessions.

means that you're a slave to Microsoft Terminal Services licensing.
Give me SQL Server licensing any day of the week over Terminal Services.

Running MDB over a Terminal is just goddamn riduclous

 

[Aaron Kempf]

ADP works _GREAT_ over VPN, WAN, WIRELESS or the PUBLIC INTERNET

eventually-- instead of blaming 'the network' you're going to have to blame
the piece of crap MDB database

 

[Susie Johnson]

Tony

stop spreading lies

MDB doesn't handle more than a single record reliably

if you have ONE record or ONE user then move to SQL Server.

you're right tony.. if you have an EMPTY table and ZERO users than maybe MDB
would scale enough

 

[Susie Johnson]

You guys are full of crap and you don't know how to use SQL Sever

SQL Server has things like PROFILER and INDEX TUNING WIZARD (aka DB TUNING
WIZARD IN SQL 2005)

Because of this; SQL Server is _ALWAYS_ faster

 

[Susie Johnson]

Then learn SQL Server kid

get a certification or 10