DATABASE CHOICES
Reasons for upgrading to SQL Server
 Microsoft's Access
Database is extremely popular and is adequate for many applications. But it is meant
for small projects with few users. As applications and volumes grow, the limitations
of Microsoft Access soon become apparent. Slow performance, error messages, unstable
applications, security issues, and data corruption are common symptoms.
When the application needs to support more features, more data, and more users,
a robust database system such as SQL Server is required.
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 utilise 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.
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 ...
Ugrading a Microsoft Access BackEnd database to SQL Server using ADO and an OLEDB
connection is reasonably straightforward. Setting up SQL Server efficiently and
securely requires a new set of skills, and the learning curve is steep.
|