What is Migrating?

Migrating from Access to SQL Server involves evaluating, categorizing, and moving tables, data, objects, and functionality from an Access application to SQL Server. It also requires that you preserve ”and possibly enhance ”the functionality available to the user . Migrating is not a simple matter of transferring all of the Access tables to a SQL Server database and then linking the new tables into Access. You must perform a complete analysis of the items being migrated before the migration takes place. This analysis includes evaluating queries, macros, form and report data sources, ADO, DAO, and VBA code, and tables and their properties. You should also determine how your Access objects will scale and whether their implementation should be modified or remain the same in the new system. You should consider the timing of the migration in the lifecycle of the application because it affects how much work has to be done and how that work occurs. The decisions that the analysis helps you make will have a dramatic effect on the methodology you use to migrate your application. Before I get into the specifics of the migration itself, I will examine the different types of migrations that can occur.

Many applications are prototyped in Access because it is much faster to develop a database in Access than in SQL Server. An application is prototyped in Access with the knowledge that the application will be moved to SQL Server before it is rolled out to users. Once potential issues and development changes are finalized based on the prototype, the database is migrated to SQL Server. This type of migration is often one of the easiest to perform because the original design of the application takes into account the fact that the backend RDBMS will be SQL Server. It allows a developer to follow certain practices that can make the migration easier. Also, because the application has not been rolled out to the general user community, there is often little or no data that must be migrated to the server. As you will see, this eliminates many of the potential issues that are encountered during a migration. In this appendix, I will take an in-depth look at the issues that you need to consider in order to migrate an application to SQL Server.

Migrations sometimes take place shortly after a system is rolled out into a production environment. Developers using Access for the first time or using it in a different way than they normally do, often find that Access does not give them the performance and functionality they need for the successful implementation of their system. Sometimes Access simply does not meet all of the requirements of the application. This can be the result of poor planning, but is just as commonly a result of greater use of the system than was originally anticipated. A migration immediately after implementation is usually one of the hardest migrations to deal with. Confidence in a system that is withdrawn after it is released to users can be very low. This can lead to difficulty dealing with users and management when justifying the migration and the extra development time involved. However, because the original developers are available to support the migration, the planning of the changes can take place very quickly. Less time is spent in "discovery" learning the functionality of the system because the knowledge of the system is still fresh in everyone's minds. The system is already well understood .

By far the most common time for an Access database to be migrated is after the system has been in a production environment for some time. This type of migration can occur for any number of reasons, but the most common reasons are performance and security concern. This type of migration is usually the most difficult to plan and implement for a number of reasons. Often, the original developers are not available to help out in the planning. This means that a significant amount of time is spent in learning how the application works and determining all of the purposes it fulfills. Developers must evaluate the entire application to ensure that none of the components of the system are missed in the migration. This type of migration can also be the most risky. Applications that reach the point where they need to be migrated are often critical applications in a business. If a migration fails or does not meet the new or old requirements for the system, it can end up completely destroying an application unnecessarily.

Because of the problems involved in moving from Access to SQL Server, it is imperative that the person performing the migration has a solid understanding of the Access application that is being migrated as well as the reasons the application should be migrated. In order to understand the reasons behind migrating, you need to take a look at the major differences between Access and SQL Server that cause people to want to upgrade. This will help you to better understand the specific set of problems that can be solved by moving to SQL Server.