DATABASE CHOICES
Migrating to SQL Server
 The
ease of use and power of Microsoft Access comes at a price. As a company or the functionality
required grows, the issues of data security, reliability, and management become increasingly
problematic. The volumes and response requirements of an Access database will often exceed
the original concept. Database migration is essential for systems to move to a more secure
and robust environment.
By keeping the application within the Microsoft family of products (Microsoft Access and SQL
Server), and engaging an experienced consultant, the upgrade process can be manageable and
cost effective.
Before your upsizing project is deployed, there should be an administrative plan in place
for the new SQL Server system. The database administrator (DBA) needs to create backup strategies,
recovery, administrative procedures, automation, optimisation, etc.
Linking Microsoft Access to SQL Server
One of the key benefits of Access when it comes to upsizing is that you can redesign your
application to continue to use the forms, reports, macros and code you have already designed
in Access, and replace the Jet engine with SQL Server. This allows the best of both worlds:
the ease of use of Access, with the reliability and security of SQL Server.
Microsoft Access has the ability to link (using ODBC) to SQL Server for table data. All table
data is moved to SQL Server, leaving all forms, reports, queries and logic in the existing
Access database. Because the existing application logic is largely unchanged, this is the
most cost-effective migration technique. For a small effort, the benefits (reliability, security,
maintenance, etc) of SQL Server can be achieved.
The disadvantage of this approach is that all access to SQL Server occurs through the Jet
engine. The Jet engine must translate every query and data access operation to SQL Server
compliant commands. This adds overhead in performance, and additional SQL Server license connections
are required.
This alternative is best for Microsoft Access applications with a small number of users and
small database sizes.
Using ADO
For greater efficiency, some of the SQL Server Tables may need to be accessed using the ADO
and OLEDB technologies. Some changes are needed - the Jet database engine uses different data
types, and a different SQL grammar from SQL Server.
A combination of Linking small Tables and using ADO for large Tables is most practicable.
This can be a phased implementation, as inefficiencies are identified in Linked Table usage.
Using .NET technologies
If Microsoft Access is no longer able to keep up with an organisation's requirements, the
project will have to be redesigned from scratch. New technologies such as Visual Basic.Net
and ASP.Net can be used to rewrite the application.
The key advantage of this approach is flexibility. You can create an application that can
target Windows desktops or the Web. It is perfectly feasible and cost effect to use an ASP.Net
Website for an administration system, especially where the users are geographically dispersed.
MySQL
MySQL is a relational database management system (RDBMS) with more than 11 million installations.
It is most commonly used in small to medium shops, where its appeal relates to its simplicity
and ease of use. MySQL is cheap and has the ability to run on multiple operating system platforms
- but it requires hands-on scripting and a full-time programmer. Multi-server deployment is
required for high volume, performance and reliability. With the takeover bid by Oracle (from
Sun Microsystems), the future of MySQL is hazy.
The basic features of MySQL and SQL Server are similar, but Microsoft SQL Server is much more
sophisticated and has a far superior security system. Conversion to Microsoft SQL Server is
far easier than with MySQL.
|