call us toll-free +1 855 855 3600
 
Home > Services > Migrate Microsoft Access to SQL Server

Migrate Microsoft Access to SQL Server


Utilizing SSMA DB Best can help migrate Microsoft Access to SQL Server, achieving the performance, scale, and security of mission-critical applications you want.

Why migrate


Microsoft Access was simply not designed 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, DB Best 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, several considerations will need to be reviewed before we can start to migrate Microsoft Access to SQL Server.

Assessing the migration scope

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.

 

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

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.

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.

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

A leader in Microsoft Access to SQL Server migrations


The DB Best team has developed a unique methodology for migrating Access applications to SQL Server which has been tested and proven many times over with hundreds of customers. Being an architect and the original creator of SQL Server Migration Assistant (SSMA) for Access, the DB Best team is THE database migration expert you can trust with your project.

Based on our vast migration experience, the DB Best team developed our in-house products Database Compare Suite and DB Best Migration Platform, which greatly complements SSMA for Access.

Here are the DB Best tools that we’ve developed to optimize the Oracle to SQL Server migration process.

GETTING STARTED


Sometimes you need a helping hand to get you started. Here are some of our offers to help with assessments, using SSMA, or developing a future-state roadmap.

Everybody is talking about the need for a digital transformation, but how do you get there? See how our future-state architecture design can use cutting edge technology to meet your organization’s needs.

Trust the team who built the SQL Server Migration Assistant to provide you with SSMA support in migrating your Oracle, SAP ASE, IBM DB2, MySQL, and Microsoft Access databases to SQL Server or Azure SQL Database.

Looking to upgrade your old SQL Server databases or move them to the cloud? Our Data Management Optimization for Microsoft Data Platform service provides you with recommendations and an ROI model to provide a future path for your digital transformation initiatives.

Do you like what you see? Please contact us today to see how we can get started!

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. They faced a problem deploying the application on newer versions of the operating system because it uses the 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 and upgrade 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. If you don’t want to go through the hassle of maintaining the day-to-day operations of your databases and applications, be sure to check out our Operational Data Management service.

Are your DBA's stretched thin? Offload your IT needs to a dedicated team with industry-proven processes and world-leading tools and products

Check out the database consulting services we have used to help customers like you get the most out of their database platform.

Our DevOps services introduce cultural changes, best practices, and new technologies to improve collaboration between business, development, and operations to create an agile application development lifecycle.

We offer training in-person at your site, at a third party location, or remotely based on your needs. We can tailor our programs to your needs.