|
For the vast majority of
small business or departmental scale users, Access is robust and reliable,
but if your application has grown to where it's now running not just your
department but your whole company, it may be time to upsize. The easy way to
do this is to move the data into SQL Server and keep your investment - by
using Access as a "front end" to this data. You can hang on to all your
Access reports and business logic with a bit of work from us.
When is it time to consider up-scaling to SQL Server ?
According to Microsoft at
http://www.microsoft.com/sql/solutions/ssm/access/accessmigration.mspx
and
http://download.microsoft.com/download/5/d/0/5d026b60-e4be-42fc-a250-2d75c49172bc/when_to_Migrate_from_Access.doc
Access can support a
maximum database size of 2 Gig, "with as many as 20 concurrent editors
(users updating information ) and 100 users running reports.”
“ For example, a professionally
designed and well-tuned Access application can support up to 20 concurrent
users who are updating data with good performance. Databases that are used
largely for running read-only reports can scale up to about 100 users”
“The Jet engine supports up to 255
concurrent users but the performance of the application depends on the
design of the application.” They go on to note that for database size
“…this limit is theoretical rather than practical:“ This is due to data
being processed locally (with Access) rather than on the server (as with SQL
Server )
There are other issues as
well, ones MS call Architectural:
“Because Access uses the Jet
engine for database management, it cannot scale well by definition. Jet is
limited to run on a single CPU, whereas client/server solutions such as
Microsoft SQL Server can support multiple CPUs.
Additionally, Jet queries
always run on the client computer, which eliminates the centralized query or
data optimization necessary for a scalable solution.” [Again, scaleable here
means many tens to hundreds of connected users. We have personal experience
of ten to twenty connected users on our applications with no problems at
all]
On the issue of data corruption:
“ Because Access uses a file
share model, all users are concurrently holding active connections to data.
If any one of those users unexpectedly loses the connection during a data
update process, the database may become corrupt" (This has become much less
common in the more recent versions of Access. In fact I don't recall this
happening for 5, 6, (7+ ?) years )
And on the issue of backups:
“Because Access uses the file share
model, the entire database is locked at the file level as soon as the first
user accesses it. This means that no reliable mechanisms exist for
performing backups of the database file unless all users are disconnected.“
(This is not quite true either, as one can just copy the whole .MDB file
while it is open)
What to do if you feel you’ve
outgrown Access ?
Microsoft believes that in 90
percent of the cases Access is just fine, and upsizing is a waste of
time and resources ( This is an amazing figure and tells me that MS has
gotten a lot of bad feedback from disappointed users investing in SQL Server
needlessly). Look, rather, to tuning Access better. We can give you a
hand here.
In 7 percent of the cases they
suggest moving only the data over to SQL Server “Because Access can link to
SQL Server for table data, migrating only the data is one of the best
balances between cost and benefits. In this scenario, you move all table
data to SQL Server and leave all forms, reports, queries, macros, and logic
in the existing Access database.”
There are a number of possible
scenarios here- Access now supports the project structure in which data is
stored in SQL Server files and Access is used as a front end, and Stored
Procedures and database diagrams are administered from Access.
In the other cases MS suggests
what amounts to a partial or complete re-write of the application. See the
above MS documents for more information.
We would be happy to assist
in any of these cases Call us for more details.
|
