Migrating Ms Access To SQL Server
It is quite common for companies to outgrow Ms Access and need to convert access to SQL Server due to slow performance, error messages, instability, security issues, and data corruption. These are all common symptoms that a database application is near or has exceeded the built-in limitations of Ms Access and when converting your Ms Access to SQL Server makes sense.
The reason to convert to SQL Server is that Ms Access has several important technical limitations that cannot be overcome with workarounds or special programming techniques. Use the following chart to determine when you can expect to run into the symptoms mentioned above:
Usually, it is not necessary to completely re-write the entire system to migrate from Access to SQL Server. This is an unnecessary and more costly change that inexperienced developers often pursue.
Ms Access Limitations
The following chart will help you determine when you can expect to run into issues with Ms Access i.e. slow performance, corrupt database, application crashing, etc, and when you know it is time to convert Ms Access to SQL Server:
Convert Ms Access To SQL Server
Converting Ms Access to SQL Server can be challenging or fairly straightforward, depending upon a number of factors. Over the years we have developed a very efficient “recipe” for migrating Ms Access to SQL Server — a proven approach that is cost-effective, and ensures the highest protection of your existing business data.
Avoid The Ms SQL Server Migration Trap
A common mistake is attempting to re-write the entire system. This is unnecessary, and the most costly and risky approach that inexperienced developers often pursue. The correct approach is to follow a phased-approach, addressing very specific areas of the system, and in the right order. Attempting to take on too much at one time, or doing things out of order will only add unnecessary cost and risk to the effort. In most cases, the Ms Access user interface can simply be reconnected to a SQL Server database.
Ms Access Upsize Wizard — Garbage In, Garbage Out
The Upsizing Wizard used by less experienced developers will move data from Ms Access to SQL Server. However, in most cases, the resulting SQL Server database design needs a lot of work, much more work than if you simply created a new SQL Server database from scratch.
The Ms Access Upsize Wizard is often the most expensive path to SQL Server. We often finds that designing a SQL Server database from scratch is the more efficient and costs the client less. We can advise about which method will yield the best results and lowest costs.
Even when designing the SQL Server database from scratch, we can help you avoid complete rewrites by carefully analyzing and planning throughout the application development process itself. This is a process with which we have a lot of experience and does not take lightly. We feel that the process of understanding your business needs is an integral component of going with the right solution.
SQL Server is a completely different and far more sophisticated database engine than Ms Access, and the Ms Access Upsizing Wizard does a very poor job of creating a proper SQL Server database design.