When to Move to SQL Server?

[access_wanna_do]

A group of users have expanded and extended Access-based reports for years
and performance is getting slow. Is there a clear point at which the whole
thing should be moved to SQL Server?

Thanks

 

[Damian S]

Hi access_wanna_do,

I have personally worked on Access DB's that have literally reached the
physical file limit for storage... These were too big, and my first
recommendation to that company was to migrate to SQL Server.

As a rule of thumb, if you have under 10 concurrent users all located at the
same physical location and a DB with under 250 MB of data, Access should be
fine.

I assume you have run "Compact and Repair" to free up space in the DB.
Also, you could look at whether you have the appropriate Indices on the
tables used for the queries that are "getting slower".

There may also be network issues slowing you down - eg: if your whole
network is getting slower (file server running out of room, poor network
design etc) then your speed may be affected then also...

Start with the simple stuff and work your way up to find the solution!!

Hope this info helps.

Damian.

 

[Rick Brandt]

A group of users have expanded and extended Access-based reports for
years and performance is getting slow. Is there a clear point at
which the whole thing should be moved to SQL Server?

Thanks

Performance is (almost) never the reason to move to SQL Server. Badly designed
apps perform badly and a poorly performing Access/Jet database will likely
perform poorly (even worse in many cases) when moved to SQL Server.

Server engines provide scalability, higher concurrency, better security, and
more robust protection from data loss and corruption. Those are the reasons to
switch, not performance.

Now, I will qualify that a bit by adding that many companies who set up a new
SQL Server box will plop down big bucks and purchase a VERY impressive box to
run their SQL Server on. In many cases this creates the impression that "the
move to SQL Server sure made that database a lot faster" when that really had
very little to do with it.

To the extent that moving to a server based engine can improve performance it is
when the design of the app is optimized to the client/server environment which
(mostly) means getting network traffic to a minimum.

 

[aaron.kempf]

Rick

you are a fucking pussy and a wuss

Performance _IS_ the only reason to DO ANYTHING.
MDB doesn't have a 'database tuning advisor'
MDB doesn't even support backup / restore
MDB doesn't support CONSTRAINTS / RULES / TRIGGERS
MDB doesn't even support Referential Integrity.

Performance IS the reason we give a shit about network performance

MDB shouldn't be used by anyone; ever- not for a single user and a
single record

grow some balls you wusses.
MDB use is unacceptable for a half dozen reasons.

 

[aaron.kempf]

I mean seriously Rick

where do you get off?
Do you work for Oracle? Are you a mole from Mr Ellison?

You are a traitor and a LIAR.

Is that why you're the biggest pussy ever and you don't know how to use
SQL Server?

Grow some balls you asshole.
Don't spread mis-information

 

[(PeteCresswell)]

Per (e-mail address removed):

MDB shouldn't be used by anyone; ever- not for a single user and a
single record

That's probably from the perspective of somebody who doesn't have to pay for the
difference in development costs.

 

[susiedba]

I disagree, Pete
there is no difference in development costs

can you 'right-click, generate scripts' in mdb?

 

[susiedba]

I sure know I can out-develop _ANY_ mdb wuss anywhere
and im a GIRL!

and my platform won't crap out once a month because of compact / repair

ROFL

 

[punjab_tom]

Pete

I agree; development is cheaper in Server of SQL because you don't need
to learn 2 different versions of SQL

And you can have multiple people writing queries at the same time

Access MDB isn't even taught out here in India

-Tom

 

[(PeteCresswell)]

Per (e-mail address removed):

I disagree, Pete
there is no difference in development costs

can you 'right-click, generate scripts' in mdb?

After doing a couple of projects both ways, I allow 30% more man hours for a SQL
Server back end. Reason: stored procedures take a *lot* longer to
develop/debug than MS Access queries.

 

[aaron.kempf]

I sdisagree with Damian

I have worked on a _25_ mb MDB file that has connection problems with
only a half dozen users

I disagree; SQL Server isn't any more difficult to use than this
retarded MDB bullshit.
find someone with some balls that can use SQL Server

 

[aaron.kempf]

how so
why is it harder to use?

ROFL

for the record:

a) SQL books online is a real resource, real help; not some boner
integrated system
b) there are real SQL Server training resources in the world; your
local community college
c) I can copy and paste sprocs in ADP just as well as you can in MDB
d) i can FILTER the list of sprocs I see-- can you do that in MDB?
e) I can handle PARAMETERS; can you? ROFL mdb queries sucks balls
f) stacking query on top of query in MDB doesn't randomly crap out like
MDB
g) I can re-use logic; as user defined functions on the database side
h) I dont need to set connection strings; and link / refresh tables
i) maybe you should stop hiring retarded MDB developers and start
hiring some REAL sql developers

I mean seriously; please give me-- in great detail-- evidence to back
up your HOGWASH.
just because Larry Linson is a flaming faggot and doesn't know how to
use SQL Server-- does that make SQL Server more difficult to use? No--
it makes Larry a fucking retard.

have you used Access Data Projects?
have you used Query Analyzer?
have you used SQL Server Management Studio?
have you used Visual Studio?

Can Access queries even use Visual Source Safe?

YES-- you _CAN_ charge more-- because customers percieve the inherent
VALUE of using SQL Server. But that doesn't mean that it's harder to
do; or harder to learn; or that it takes more time.

YES, SQL Server developers generally have balls and spend more time
TESTING than your junior retard senile MDB developer-

I just know first hand that I can out-develop any MDB fag out there.

MDB is for losers and retards; eat shit wussies
I mean seriously here

why in the hell do you think that it's 'easier to write mdb queries
than sprocs / views'??

I use VIEWS a _LOT_ more than most developers... because I develop with
Analysis Services im basically required to.

-Aaron

 

[(PeteCresswell)]

Per (e-mail address removed):

how so
why is it harder to use?

When I get into stored procedures, it's usually with a lot of logic and multiple
recordsets - in order to reduce the number of trips to the server for screens
that have a parent record and many child tables.

Given a lot of logic, and the inability to put in breakpoints and step through
the code; it just takes longer to debug a stored procedure than a block of VB
code that invokes a series of queries.

 

[Tony Toews]

After doing a couple of projects both ways, I allow 30% more man hours for a SQL
Server back end. Reason: stored procedures take a *lot* longer to
develop/debug than MS Access queries.

Very interesting numbers. Thanks.

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

 

[aaron.kempf]

I disagree whole heartedly.

Double-click on a sproc in ADP and you enter the parameters.
Or single-click for all I care.

Yes; Sprocs ARE MORE POWERFUL but that doesn't make them more complex.
Unless you're a weenie MDB script kiddie

Any use of MDB anywhere in the year 2006 is laughable.
It became obsolete - completely- circa 1999- with the release of Office
2000.

queries in MDB crap out when they 'get too complex'
queries in MDB pull the WHOLE TABLE ACROSS THE NETWORK

get real kids; grow some balls
Sprocs aren't more difficult than MDB queries; they're just more
powerful.

If MDB had 'Query Analyzer' where you could create a whole bunch of
queries at once-- then MDB _MIGHT_ be usable.

But as it is; MDB is only used by retards that don't have the capacity
to learn SQL Server.

I don't spend all my time copying queries and tables and lookup tables
and linking and connection strings.

I keep all of my DATA and all of my SPROCS / VIEWS -
_ON_THE_DATABASE_SERVER_ which is where they belong.

Architecturally it's 10 times simpler.

Step through the code?
What do you mean.. step through the code?

The VBA code?

Now you're arguing about functions; not sprocs-- are you purposefully
trying to change the subject because you realized what a little baby
you are?

Stored procedures- you should almost never use multiple recordsets with
them.
reduce the number of trips?

It's like.. MDB is a tricycle; you're trying to get to Wally World--
and SQL Server is a supersonic jet.. .that doesn't cost more; that
isn't harder to drive. It's just faster, better; and it can hold
multiple people.

MDB doesn't work for jack shit in multi-user environments

-Aaron

 

[punjab_tom]

Tony;

They dont even teach MDB in India.. is it not obsolete?

-Tom

 

[punjab_tom]

Aaron

you need to be nice to people

-Tom

I disagree whole heartedly.

Double-click on a sproc in ADP and you enter the parameters.
Or single-click for all I care.

Yes; Sprocs ARE MORE POWERFUL but that doesn't make them more complex.
Unless you're a weenie MDB script kiddie

Any use of MDB anywhere in the year 2006 is laughable.
It became obsolete - completely- circa 1999- with the release of Office
2000.

queries in MDB crap out when they 'get too complex'
queries in MDB pull the WHOLE TABLE ACROSS THE NETWORK

get real kids; grow some balls
Sprocs aren't more difficult than MDB queries; they're just more
powerful.

If MDB had 'Query Analyzer' where you could create a whole bunch of
queries at once-- then MDB _MIGHT_ be usable.

But as it is; MDB is only used by retards that don't have the capacity
to learn SQL Server.

I don't spend all my time copying queries and tables and lookup tables
and linking and connection strings.

I keep all of my DATA and all of my SPROCS / VIEWS -
_ON_THE_DATABASE_SERVER_ which is where they belong.

Architecturally it's 10 times simpler.

Step through the code?
What do you mean.. step through the code?

The VBA code?

Now you're arguing about functions; not sprocs-- are you purposefully
trying to change the subject because you realized what a little baby
you are?

Stored procedures- you should almost never use multiple recordsets with
them.
reduce the number of trips?

It's like.. MDB is a tricycle; you're trying to get to Wally World--
and SQL Server is a supersonic jet.. .that doesn't cost more; that
isn't harder to drive. It's just faster, better; and it can hold
multiple people.

MDB doesn't work for jack shit in multi-user environments

-Aaron

 

[David W. Fenton]

MDB shouldn't be used by anyone; ever- not for a single user and a
single record

What an idiot.

<PLONK>

 

[David W. Fenton]

(e-mail address removed) wrote in

They dont even teach MDB in India.. is it not obsolete?

They don't teach Urdu in most schools in the US. Does that mean its
obsolete?

 

[(PeteCresswell)]

Per (e-mail address removed):

They dont even teach MDB in India.. is it not obsolete?

To the extent that the really large projects get outsourced to India and small,
department-level apps tend to be developed in-house, it might be obsolete in
India.

..MDB back ends are definitely not industrial-strength/massively-scalable
solutions and .MDB front ends don't have the cache' that .NET front ends do.

But both are significantly cheaper to develop than .NET front ends/SQL Server
back ends and, in spite of what some IT people say/think, they do an acceptable
job at a certain scale for much less money. *MUCH* less money....

To wit, I have developed two applications that, some years later, were taken
over by IT and "done the right way".

The first one was a charitable endowment manager. Somebody with a lot of money
wants a tax break right now, but doesn't want to allocate the money until later
- so they set up a charitable endowment fund that acts like a tax exempt
charity. They get the tax break as soon as they turn the money over to the
charity and they get to decide how it is given out later. I wrote a system to
keep track of donors, donations, and allocations and to serve as a tool for
recruiting new donors.

I delivered that application for a tad over $71,000. The user was happy with
it for several years. It did everything they wanted and they got enhancements
quickly and painlessly.

Then one year a server got goofey and the app started going down in the middle
of the day. Moving to a different server would have fixed the problem in an
hour or so - but the company had everything so locked down that it wasn't an
option until almost a year of constant problems had passed.

So, the action taken was for IT to take the app, use it as a prototype, an
re-develop it the "right way" using and Oracle back end and a .Net front end.

Total cost for the clone/rewrite: over two million bucks. No additional
functionality.


The second one was a bond trading application that I developed over a period of
about seven years working with a mutual fund's bond trading desk. Total billed
hours were a little over $225,000 and, over a period of nine years the only time
it went down was when somebody pulled a cable in the LAN closet - and it was
back up and running within the hour.

Upper management decided that several other trading desks should also be on such
a system - but they elected not to enhance the MS Access app that I wrote.

Instead they had IT rewrite it from scratch. I participated in that process
for about two years. There was additional functionality in the replacement,
but no more than 40%. Last time I looked in on it after about five years of
IT's working on it, it was going to be delivered sometime in the next year and
the sunk cost so far was over 24 million dollars.

The original users expressed consternation that they had to use the new system.


SO: bottom line, MS Access applications may not be industrial strength - but
they certainly cost the user significantly less and, at a certain level, can do
everything the user wants.

You wouldn't buy a three-axle dump truck when a VW Golf would do the job at hand
faster and cheaper.