DATABASE CHOICES
Reasons for upgrading to SQL Server
Microsoft's Access Database is extremely popular and is adequate for many applications.
However, as applications and businesses grow, Access developers soon discover that
companies need a more robust database system such as SQL Server.
There is less Chance of Data Corruption
In Access, the MDB file is opened directly. If the network connection is unexpectedly
broken or a client or server breaks down, there is a good chance data will be corrupted.
SQL Server runs as a service - the user does not have direct access to the file.
If the server shuts down unexpectedly or the network connection is broken, the integrity
of the data will be maintained.
Database Log Files Enable Data Recovery
SQL Server has a distinct advantage over Access in that all transactions (database
updates, insertions and deletions) are kept in a log file. The log records the changes
to the data and enough information to undo the modifications made during each transaction.
So in the event of system failure, the log file can be used to recover the data.
Support for More Concurrent Users
Access supports a maximum of 255 concurrent users. In the real world, it is common
to experience major performance issues with as few as 10 users attempting to use
the Access database simultaneously over a network. SQL Server supports a concurrent
user base that is limited only by available system memory. Because of its optimised
query-processing engine and ability to utilize simultaneously multiple computers,
processors and hard drives, it can scale to meet any requirement.
Support for a Larger Database
Access supports a maximum database size of 2 gigabytes plus linked tables. Although
use of linked tables theoretically enables more much data to be stored, it is common
to experience performance issues due to the amount of data being processed. Consider
upsizing an Access database if the size reaches 100 megabytes. SQL Server, on the
other hand, has vastly improved storage capabilities, allowing for 1,048,516 terabytes
of data to be stored efficiently across multiple devices. It also has self-repairing
and self-compacting features, making it a very robust database solution.
Licensing
If there are multiple databases that use the Jet engine, a current license is needed
for each database. Moving an Access database onto SQL Server can reduce the license
costs for an organisation.
Performance and Administration
Access has limited backup features and does not support point-in-time restores.
Access does not have performance monitoring features. SQL Server 2000 provides wizards
that allow the database administrator to monitor and tune performance. It also contains
tools to automate data backup and secure data.
But ...
The upgrade to SQL Server using ADP is reasonably straightforward. Setting up SQL
Server properly and securely is another matter. A new set of skills is required
to keep SQL Server databases running efficiently, and the learning curve is steep.
|