Migrating MS Access to SQL Server
Most companies eventually out-grow the built-in limitations of MS Access — signs can include: slow performance, error messages,
unstable application, security issues, and data corruption and loss.
Adding more forms, more reports, additional users and more data into MS Access will place additional burdens on MS Access, and accelerate
shorten its lifespan. If you need to continually enhance MS Access to keep pace with business growth needs, it's only a matter of time when
it becomes imperative to migrate the MS Access database to SQL Server. And the longer you delay this decision, the more expensive
and riskier this becomes.
MS Access Limitations
MS Access has built-in technical limitations that cannot be overcome with
workarounds or special programming techniques — limitations that can only be overcome by converting your MS Access to SQL Server.
The following chart will help you determine when you can expect to run into issues with MS Access i.e. slow performance, corrupt database, application crashing, etc, and when you know it is time to convert MS Access to SQL Server:
|
|
OK |
CAUTION |
DANGER |
|
Concurrent LAN Users |
< 5 |
5-10 |
> 10 |
|
Internet Users |
0 |
3 |
> 3 |
|
Size of Datastore |
< 100 MB |
< 300 MB |
> 300 MB |
|
Application Complexity |
Simple |
Moderate |
Complex |
|
|
|
|
|
Convert MS Access to SQL Server
Microsoft's flagship relational database SQL Server 2014 is designed to
handle the data administration and rigorous workload balancing requirements
of more demanding business applications. Converting MS Access to SQL Server can be challenging
or fairly straightforward, depending upon a number of factors. Over the years we have developed
a very efficient "recipe" for migrating MS Access to SQL Server — a proven approach that is cost-effective, and ensures
the highest protection of your existing business data.
Avoid the MS SQL Server Migration Trap
A common mistake is attempting to re-write the entire system (back-end
Database and front-end MS Access application). This is unnecessary, and the most costly
and risky approach that inexperienced developers often pursue. The correct approach is
to follow a phased-approach, addressing very specific areas of the system, and in the right order. Attempting
to take on too much at one time, or doing things out of order will only add unnecessary cost and risk to the effort.
In most cases, the MS Access user interface can simply be reconnected to a SQL Server database.
MS Access Upsize Wizard — Garbage In, Garbage Out
The MS Access Upsize Wizard is often the MOST EXPENSIVE path to SQL Server. In most cases, the resulting SQL Server database design
needs a much more work, than if you simply created a new SQL Server database from
scratch. SQL Server is a completely different and far more sophisticated database engine than MS Access, and the
MS Access Upsizing Wizard does a very poor job of creating a proper SQL Server database design. Ask anyone who has attempted
to use the MS Access Upsize Wizard — and they will tell you it just created much more
work for them in the long-run.
The Hidden Cost of MS Access
Many professionals believe that developing applications in MS Access is less expensive than
alternatives like .NET and MS SQL Server. While MS Access is certainly easier
to understand and use, in the wrong hands MS Access only adds many unnecessary development, maintenance
and support costs to your business; costs that are difficult to track, and add up over time.
In our experience — from developing
hundreds of custom database solutions with MS Access, MS SQL
Server and .NET — developing custom solutions with MS Access is MOST EXPENSIVE approach.
With MS Access projects, we tend to spend far more time on 'defensive
engineering' to overcome MS Access limitations, compatibility issues, User Interfaces constraints,
and difficulties associated with deploying and controlling MS Access in a
distributed business environment. And after you have invested all this the extra time and effort, the system does not scale, and
you will eventually end up writing off your entire development expense.
PCA engineers spend on average one-third less time developing custom applications on
on MS SQL Server and .NET than with MS Access. The results are far better and more scalable. Time is money, so applications that
are built on the right components end up costing you MUCH LESS in the long run. MS Access is not the lowest cost/cheapest
solution, just because it ships free with every copy of Office Professional.