A leading provider of engineering software has a popular application deployed at thousands of customer locations. They have a plan in place to rearchitect the solution in a couple of y...
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, 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, you need to review several considerations. After that you can start migrating Microsoft Access to SQL Server.
Assessing the migration scope
First, we will need to identify the Access features used by MS 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.
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.
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. This technology 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. Simply put, they greatly complement SSMA for Access.
So, here are the DB Best tools that we’ve developed to optimize the Oracle to SQL Server migration process.
See how you can get the most out of SSMA from the company who originally created the tool for Microsoft.
Comparing two production databases for migration or an upgrade? Now you can do this with one powerful and simple tool that every DBA and database developer needs.
Our DBMSys platform collects data from multiple environments to develop a business case for modernization and chart an upgrade path while verifying servers conform to best practices.
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 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.
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 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 have a set of blog posts and data sheets to help you better understand our capabilities as the leader in database migration services. Explore your possibilities and experience our expertise in moving Access workloads to Microsoft SQL Server and Microsoft SQL Azure.
One of the world’s largest distributors of non-food consumable products wanted to rationalize their data platform and business intelligence strategy. Their current IBM DB2 database s...
Reference data sheet
Read our Database Modernization for Microsoft Technologies datasheet to find out the details of our database modernization offer. We possess the in-depth understanding of database and ...