How hard is it to Migrate to SQL server from Access?

[Mark Carlyle via AccessMonster.com]

I have an Access database in a Server/Client environment. I currently have
approx 15 users on the DB. I would like to accomplish the following.

Increase Speed
Allow for more users (while the current database is fast with 1 user, it
progressivly gets slower as more users log on)
Allow the program to run 24/7
Not have data loss (currently records are dissappearing... we have 4000+
records that access 80,000+ notes and sometimes they will just dissapear)

I have become somewhat effecient with Access but have never worked with
SQL. My only formal programming training was Turbo PASCAL a million years
ago.

I have been able to write various VB functions in the Access DB and have
done so with the help of threads like this one, books, and online help.

Thanks for any advise.

 

[Rick Brandt]

I have an Access database in a Server/Client environment. I
currently have approx 15 users on the DB. I would like to accomplish
the following.

Increase Speed
Allow for more users (while the current database is fast with 1 user,
it progressivly gets slower as more users log on)
Allow the program to run 24/7
Not have data loss (currently records are dissappearing... we have
4000+ records that access 80,000+ notes and sometimes they will just
dissapear)

I have become somewhat effecient with Access but have never worked
with SQL. My only formal programming training was Turbo PASCAL a
million years ago.

I have been able to write various VB functions in the Access DB and
have done so with the help of threads like this one, books, and
online help.

Thanks for any advise.

Technically you could create the same table structures on SQL Server,
populate them with your existing data and then replace your current tables
with ODBC links to the SQL Server tables and your app should (mostly) just
work as it does now. However; depending on how your design is set up you
might not accomplish your first goal (speed) and it could in fact run slower
in some areas than it does now.

If you have used all the "best practices" for a multi-user Access app
regarding what you pull into forms and reports to minimize network traffic
then you are most of the way towards having a design that is optimal for a
true client/server application. If not, then you could have quite a bit of
re-work just to get performance to a break-even point compared to what you
have now.

The other reasons you list for changing to SS are the more legitimate ones,
but moving to a server engine "in and of itself" does not guarantee any
improvements in performance. That area is where additional work is often
required. Again though, it is often just a case of designing things the way
they should have been in the first place and Access was just letting you get
away with it.

 

[Albert D. Kallal]

I have an Access database in a Server/Client environment.

Unfortunately, the above is incorrect. Ms-access when used with a file
shared mdb is NOT client to server. (in fact, in your efforts to increase
performance, the move to client to server is what you desire!).

I currently have
approx 15 users on the DB. I would like to accomplish the following.

Do you have a split db? Does EACH workstation get a copy of the front end
(FE)?
Is each FE a mde file?

If you don't have the above...then you got a bad setup..and things will not
run very well at all.

Increase Speed

If your designs are good, the migration to sql server usually helps.
However, with poor designs..moving to sql server can actually SLOW things
down!

Allow for more users (while the current database is fast with 1 user, it
progressivly gets slower as more users log on)

The above is interesting. After how many users does the system get slow? 5,
or 12?

If your application runs well with 4 users, but is slow with 8, I would be
surprised!

(usually, if it runs well with 4 users..then going to 8 makes little, or no
difference here!!).

If you are saying it is fast with 1 user..and then 2 users is slow, then you
need to try the persistent connection trick.

Allow the program to run 24/7
Not have data loss (currently records are disappearing... we have 4000+
records that access 80,000+ notes and sometimes they will just dissapear)

If you got a split database, and each workstation gets a mde on EACH
computer, and you still have the above, then you do have to address this.

I have become somewhat effecient with Access but have never worked with
SQL. My only formal programming training was Turbo PASCAL a million years
ago.

I have been able to write various VB functions in the Access DB and have
done so with the help of threads like this one, books, and online help.

Learning sql server is FAR FAR easier then learning ms-access. In ms-access
you have to learn how the forms work (in sql server, you have no tools to
make forms with...so it is a simple product). You already been using tons of
sql now, and that skill transfers to sql server Further, the programming
language in ms-access is Visual Basic, and again you mentioned that you
learned that. (again, sql server has nothing remotely close to the
complexity and ability of VB).

You do have things to learn..but it certainly is MUCH less then he stuff you
learned, and mentioned that you know now...