Reasons to Migrate

Now that we've reviewed the differences between Access and SQL Server, let's discuss the reasons that people migrate their Access applications to SQL Server. Although there are almost as many reasons to migrate as there are Access databases, you should be prepared to justify your reasons for upgrading to yourself as well as others. Making the move with poorly defined reasons can cause you to address the wrong areas for improvement when you plan your upgrade.

Size

Probably the most common reason that people have for migrating an application from Access to SQL Server is the amount of data. Many users notice that the performance of a networked Access application tends to degrade once the database expands to between 10MB to 20MB. This is because Access must constantly deal with the passing of large amounts of data back and forth between the database and the client. When a database reaches 100MB, it can take a very long time to run complicated queries against the data. Despite the absolute 2GB physical limit of the size of a database, performance is usually degraded long before the limit is even approached. This is a very good reason to migrate an application to SQL Server.

Number of Users

Due to Access's problems with multi- user access, SQL Server is often a viable alternative to using Jet engine. Access tends to run into problems with more that 10 concurrent users. SQL Server can handle thousands of concurrent users and can help administer them much better than Access can. If you are building a large system where there are many different types of users, SQL Server can make it easier for administrators and users to add and remove logons with appropriate permissions.

Network Traffic

As previously mentioned, SQL Server reduces the amount of data traffic between the database and the client. If you are planning on running an application over a slow network, it is best to reduce, as much as possible, any traffic on the system. This frees resources for other users. Even if you have a fast network, if Jet engine-based applications are a significant portion of the network's number of requests or packets, then a migration might extend the life of the system.

Response Issues

SQL Server is much faster at working with and returning data across a network than Access is. This is because of the reduced amount of data that must be transferred across the network. If you have a table with hundreds of thousands of records in it, Access has to return the entire contents of the table. SQL Server only returns the requested data, and it will retrieve it faster than Access can. This is because SQL Server can take advantage of the processing capability of the computer on which it resides. The computer that requests the data must do all of the processing of an Access database. The server running SQL Server can also handle all of the indexing and updating of the data, freeing the client to implement the rules required by the business case.

Maintenance and Administration

The centralized administration interfaces provided by SQL Server can be used to justify a migration. They allow one entry point for all activities including the ability to schedule jobs, which can guarantee the state of the database and provide the added security of constant backups . SQL Server also supports rollbacks when the server fails. If SQL Server stops unexpectedly, once it is restarted, it attempts to restore all of its databases to the last known good state. This means that any transactions that were committed before the crash remain in the database. When this occurs in Access, a corrupted database will often have to be recovered from a physical backup if one exists. The data changes that took place since the previous backup are lost. Although this is also possible with SQL Server databases, it is much more likely that the server will be able to recover from the damage and only those changes that were not complete at the time of failure are lost.

Replication

Access does not have the native capability to replicate to other RDBMSs as SQL Server does. If an application needs to retrieve data from multiple systems with a minimum of user intervention or development effort, migrating to SQL Server may be able to solve this issue. For example, SQL Server can be configured to replicate its data to another data source that is used to do reporting on actions within multiple systems. You can also use SQL Server 7 with any OLE DB compliant data source to bring data together into one place. You can replicate external data into SQL Server or replicate SQL Server data to these data sources. The number of OLE DB drivers available is growing and includes such systems as AS/400, Oracle, Sybase, Btrieve, and Informix. Any of these systems can be replication partners with SQL Server using the built-in capabilities of SQL Server. If multi-system replication is a requirement for a system, SQL Server's capabilities can be used to justify a migration.