Learn what it takes to migrate Microsoft Access to SQL Server
Why migrate
Initially, Microsoft simply did not design Access for more than a handful of concurrent users.
Driven by the need to support more complex database applications and a growing population of users, IT departments are looking for simplified ways to consolidate and migrate their current Microsoft Office Access applications. Using Microsoft SQL Server, ABCloudz can help your IT department take advantage of new business opportunities, providing increased performance, scalability, availability, security, reliability, and recoverability.
Typical challenges that we see
Depending on the version of Access, you need to review several considerations. After that you can start migrating Microsoft Access to SQL Server.
Challenge | Benefit |
|---|---|
Assessing the migration scope | First, we will need to identify the Access features used by Microsoft Access. Also, we take a look at whether or not the Access databases are split into “front end”/“back end” pairs. Then we discover if your Access database is using Access Forms, VBA Modules, and Access Reports. In this case, you will need to recreate their functionality elsewhere. For Access reports, SQL Server Reporting Services (SSRS), SharePoint Access Web Apps and Power BI offer excellent visualizations and reporting functionality. Also, you should consider additional 3rd Party reporting tools. |
Addressing the back end | Second, before upsizing the “back end” code, and depending on the destination version of SQL Server, the code in the VBA modules and Access forms will need to be reviewed. Ideally, the module code will either be converted to SQL Stored Procedures, User Defined Functions or other SQL Server Database objects. The review will need to identify Access data types that may not be supported in SQL Server and their equivalent. |
Checking dependencies | Thirdly, you will need to review the Access application for dependencies on other databases or data sources. Access allows integrating data from other Databases via Linked tables via OLE DB and ODBC connectors. So, depending on the business requirements, you may need to re-integrate the same data sources in SQL Server. You can use either ETL tools or Linked Servers to accomplish this task. Also, the Access application will need to be reviewed for other dependencies. For example, custom DLLs or 3rd Party tools. In the case of 3rd Party tools, licensing considerations may also need evaluation. |
Post-Migration Database Capacity Validation | Prior to the migration, you should have a good understanding of your database workload including any performance data and frequency of heavy load. Key to a proper comparison is a way to replay a load such as through Distribute Replay Controller or your own load harness. Once under load, you can evaluate your new technology by monitoring pressure on CPU, Memory, IO Operations, and network traffic. By monitoring the times the system is under pressure you can determine if your technology is properly sized. ABCloudz can help you profile your existing environment and then grade your migration against that profile. |
Getting started
Sometimes you need a helping hand to get you started. Here are some of our offers to help with assessments, using SQL Server Migration Assistant (SSMA), developing a future-state roadmap, or modernizing your data-consuming applications.
How to upgrade Microsoft Access 97 Jet database engine applications
One of our customers has a popular application deployed at thousands of customer locations. They plan to rearchitect the solution in a couple of years to use a modern application architecture with a SQL Server back end. Then they faced a problem deploying the application on newer versions of the operating system. The problem occurred because the app was using an outdated Microsoft Access 97 Jet database engine.
So, they were blocked from being able to install the software because of the inherent security risks of using the old database technology. We found a way to remove the dependency on the legacy data access technologies. Finally, we upgraded their Access databases to the latest version. In this video, we demonstrate our first approach on how to upgrade Microsoft Access 97 Jet database engine applications to the latest version of the Jet engine that runs on the latest versions of Windows.
Supporting your SQL Server solutions after the migration
As part of our migration project, we offer training, support, consulting, and managed services for your SQL Server solutions. Obviously, you don’t want to go through the hassle of maintaining the day-to-day operations of your databases and applications? If not, be sure to check out our database health check offers.