Migrate Microsoft Access to SQL Server

First, we will need to identify the Access features used by MS Access and whether or not the Access databases are split into “Front-End”/“Back-End” pairs. If the Access database is using Access Forms, VBA Modules, and Access Reports, the functionality will need to be recreated elsewhere. For Access reports, SQL Server Reporting Services (SSRS), SharePoint Access Web Apps and Power BI offer excellent visualizations and reporting functionality. Additional 3rd Party reporting tools should be considered.

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.

Third, the Access application will need to be reviewed for dependencies on other databases or data sources. Access allows integrating data from other Databases via Linked tables via OLE DB and ODBC connectors. Depending on the business requirements, in SQL Server, the same data sources may need to be re-integrated using ETL tools or Linked Servers.

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.

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.

DB Best can help you profile your existing environment and then grade your migration against that profile.