pompeyjim said:
I've designed a database in Access. It is quite a large database and
about 60 to 80 records a day will be input into it so I think I'm
going to need to upsize it to Microsoft SQL Server. How easy is this
to do? Also, I have no idea how to go about pricing it? Do I make a
one-off charge for the time it's taken me to do it or do I charge, in
relation, to the time, money and effort it's going to save the user.
There's also the question of support contracts, copyrights etc. Can
anybody suggest a website where I can get all this type of
information?
This is a very broad question.
Does the client already have a SQL Server or do you intend to have them set up a
new server to run it on? If the latter, do they have anyone who can administer
that box and SQL Server instance?
The basics are...
Set up tables on SQL Server identical in structure to your Access tables.
Move all data to the SQL Server tables.
Replace all tables (or table links) in your current MDB with ODBC links to the
tables on SQL Server.
That's it for the basics. If your lucky everything should work. Now...will it
work WELL? That is another question and is a two part question. Part one is
"Does it work as well as it did before the move to SQL Server?" Part two is
"Does it work on SQL Server as well as it could potentially work?"
You could find a few things that won't work at all without some re-work. That
should be minimal though and there's a good chance that nothing will fall into
this category. There is a higher chance that some areas will work, but be
slower than they were before the change. Slow enough to be an issue? Hard to
say, but a decent all-Access app doesn't necessarily translate to a decent
client/server app.
If a competant, experienced Access developer creates an all-Access app that he
knows will be networked and multi-user he will most likely produce an all-Access
app that will also perform pretty well if the tables are moved to SQL Server
because the "best practices" for both are similar.
If a power user throws together an app largely built with the wizards and macros
and that app evolves over time to a rather complex app that the company comes to
rely on (a more common scenario than many people think), that is likely to be an
app that will require an extensive re-write to make it work well with SQL Server
tables.
The primary issue is getting most of the work done on the server and minimizing
the amount of traffic over the network. Access/Jet/ODBC does an amazingly good
job at this, but developing with those things in mind is certainly a huge
factor. The truth is that Access/Jet is so good that you can get away with some
pretty horrible designs that will fall on their face in a client/server
situtation. If your app has few or none of those design problems then the
transition can be pretty painless.
Access has a white paper on their web site (at least they used to) that
discusses "When should I move to SQL Server?" that you should read. Keep in
mind though that MS is very heavily SQL Server biased so they will lean in the
direction of upsizing sooner or with fewer reasons than many seasoned developers
do.
I personally very seldom use Jet back ends. My rule is "if a SQL Server is
available then there is almost never a reason to use a Jet back end". Often the
question is not "When should I use SQL Server?" so much as "When should I
install a new server, set up SQL Server on it, pay someone to administer it, and
pay for the additional licensing costs?". In an organization large enough to
already have staff for supportng a network and servers this is usually a
no-brainer. For small outfits running a peer-to-peer network there is a much
higher threshold.
One question. Why do you think this move is going to save the user "...time,
money and effort..."? The user of the app couldn't care less (as long as it
works) and there could very well be more time, money, and effort required for
whoever pays the bills.
You move to SQL Server because you need or desire the advantages of a more
robust, secure, database engine. Not to save time, money, and effort.