Upsizing to Microsoft SQL Server

[Guest]

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?

 

[Rick Brandt]

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.

 

[Guest]

Hi Rick. Thanks very much for all that information. It's greatly appreciated.
I think, however, that I didn't make it very clear. The reason I think I need
to upsize to SQL Server is that I believe, Access can only hold 2 gig of data
and though I realise this is a lot of information, I'm just a bit concerned
that with the user adding 60 to 80 records a day and each record containing
quite a lot of data the 2 gig will pretty soon be used up. Also, I think I'm
right in saying that Access can only have a limited amount of users at the
same time. This DB will have quite a few people using it all at once.
The reason I put time, money and effort was to do with pricing the DB. I
have no idea how to go about putting a value on it. For instance, how is a
price for a bespoke DB arrived at? Is there any wesites that would have
guidelines on this subject along with Copyright and licenses etc?

 

[Rick Brandt]

Hi Rick. Thanks very much for all that information. It's greatly
appreciated. I think, however, that I didn't make it very clear. The
reason I think I need to upsize to SQL Server is that I believe,
Access can only hold 2 gig of data and though I realise this is a lot
of information, I'm just a bit concerned that with the user adding 60
to 80 records a day and each record containing quite a lot of data
the 2 gig will pretty soon be used up. Also, I think I'm right in
saying that Access can only have a limited amount of users at the
same time. This DB will have quite a few people using it all at once.
The reason I put time, money and effort was to do with pricing the
DB. I have no idea how to go about putting a value on it. For
instance, how is a price for a bespoke DB arrived at? Is there any
wesites that would have guidelines on this subject along with
Copyright and licenses etc?

80 records a day with the largest allowable record size (2kB) would take you 34
years to hit the limit. I think a wait and see approach could easily be taken
here. Moving to SQL Server is no more difficult to do later than it is sooner.

As for the pricing issue you would need input from some of the indepedent
developers that post in here. I know that per-hour rates are more the norm than
per-project because the "final" project is so often a moving target.

 

[Tony Toews [MVP]]

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.

Umm, that's less than 20,000 records per year. Not a problem for Access. 20,000,000
per year would be a different matter.

The biggest problems are in stability of the hardware and the number of users making
changes. Reporting or inquiry only users don't make a difference. Editing/Updating
users have been successfully in the ten to fifteen range.

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 the mid
to late 80s stating to what lengths they spend over a million dollars duplicating
their IBM S/38 mini-computer in another offsite 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

 

[Guest]

Thanks for that Tony. It's certainly made me think. The data that needs to be
input is for workmen who need a permit before they can carry out any type of
work. I know that once they have input the data they need a printout straight
away. The printout is in triplicate; one copy for the workmen, one copy for
head office and another copy for their own records. This means of course that
they will have the paperwork to input any data that they might lose should
anything happen. Also, if the system was to fail, all work permits can be
written out by hand as they are now so that's not really too much of a
concern.
My main concern is how many users will be using it at any one time. I'm not
too sure if it will be more than the 10 to 15 users. I think it is as Rick
Brandt said that a wait and see approach would probably be the best way ahead
to start with before upsizing to SQL Server. Anyway, thanks again both Rick
and Tony for you help.

 

[John W. Vinson]

I'm just a bit concerned
that with the user adding 60 to 80 records a day and each record containing
quite a lot of data the 2 gig will pretty soon be used up.

Unless you have Memo fields or pictures, then each record can contain no more
than 2000 bytes (Access JET limitation).

Assuming 100 records a day, 2000 bytes each, that gives you

2147483647 / (100 * 2000) = 10,737 days = 29 years (assuming no weekend or
holiday breaks).

You've got a while before you will be forced into a decision... <g>



John W. Vinson [MVP]

 

[Tony Toews [MVP]]

Thanks for that Tony. It's certainly made me think. The data that needs to be
input is for workmen who need a permit before they can carry out any type of
work. I know that once they have input the data they need a printout straight
away. The printout is in triplicate; one copy for the workmen, one copy for
head office and another copy for their own records. This means of course that
they will have the paperwork to input any data that they might lose should
anything happen. Also, if the system was to fail, all work permits can be
written out by hand as they are now so that's not really too much of a
concern.

This sounds like a system for folks doing work on a refinery. Been there, done

My main concern is how many users will be using it at any one time. I'm not
too sure if it will be more than the 10 to 15 users. I think it is as Rick
Brandt said that a wait and see approach would probably be the best way ahead
to start with before upsizing to SQL Server.

I'd agree with that assessment.

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