An Access to SQL Server migration usually means one thing for a growing Australian business: keep the Access forms and reports people already know and move the data into an Azure SQL Database built for concurrency, backups and growth. Done in stages with a tested cutover, it removes the corruption risk and the 2 GB ceiling of a file based database without forcing anyone to learn new software.
This guide covers when migration is the right call, what SSMA actually converts, the step by step process and realistic timelines and costs. It also covers the traps we see most often when businesses attempt it themselves.
Table of Contents
Should you migrate your Access database to SQL Server?
Migrate when a business critical Access database is shared by several users and showing strain: slowing forms, locking conflicts, corruption scares or a file creeping toward its size limit. If one person does most of the work in it and the file is small, a proper split, compact and tidy up is often enough.
The hard ceiling is real. Microsoft’s Access specifications list a maximum database size of 2 gigabytes, minus the space needed for system objects, and a theoretical maximum of 255 concurrent users. In our experience the practical multi user limit arrives far earlier. The Access databases we get called about are typically shared operational systems, jobs, quotes and client registers, with somewhere between 6 and 15 daily users, and they start misbehaving well before either number on the spec sheet.
Databases like this rarely sit alone. They feed spreadsheets, reports and other systems, which is why we treat migration as part of the wider picture covered in our business systems integration guide.
The table below is the quick sense check we run with clients deciding between a full migration and a simpler fix.
| Situation | Migration is usually the right call | A simpler fix may be enough |
|---|---|---|
| Growth pressure | Data volume keeps growing and performance keeps dropping | The file is small but poorly maintained |
| Multi user load | Several people rely on it at the same time and hit locking delays | One primary user does most of the work |
| Risk profile | The system is operationally critical and downtime hurts | The database is helpful but not essential |
| Data safety | Corruption scares or nervous backup routines are already happening | Backups are routine and restores have been tested |
What changes for users when the Access front end stays?
Almost nothing changes on screen. The most common outcome of an Access to SQL Server migration is an Access front end linked to an Azure SQL Database back end, so people open the same file, use the same forms and run the same reports. What changes is where the data lives and how safely it is stored.
The first question every client asks is whether staff need retraining. They do not, and Microsoft’s own guidance supports the approach: per the Migrate an Access database to SQL Server support article, you can still maintain the front end Access database with linked tables to the SQL Server tables.
What stays the same:
- Access forms, navigation and the way people work day to day
- Core workflows and data entry screens
- Reports and exports, at least initially
What changes:
- Tables live in an Azure SQL Database instead of an Access file on a shared drive
- Backups become automatic on the Azure side rather than someone copying a file
- Permissions are managed at the database level rather than by file access
- Some screens need tuning to perform well over a network connection
What is SSMA and what does it not migrate?
SSMA, the SQL Server Migration Assistant for Access, is Microsoft’s free tool for moving Access schema and data to SQL Server or an Azure SQL Database. It assesses the database, converts tables and relationships, migrates the data and flags problems. It does not touch the parts of the application your business logic lives in.
The limits matter more than the features. Microsoft’s Access migration support documentation is direct about scope: SSMA mainly migrates tables and select queries with no parameters, and forms, reports, macros and VBA modules are not converted.
That last sentence is where do it yourself migrations go wrong. In the systems we migrate, the queries and VBA carry years of accumulated business rules, and after SSMA has done its part someone still has to review, rework and test them against the new back end. Microsoft’s baseline process is published in its Access to Azure SQL Database migration guide, and it is a good map of the tool’s role: assess, convert schema, migrate data, then validate. Budget the real effort for what happens after that.
How do you migrate Access to Azure SQL step by step?
A safe Access to SQL Server migration is a sequence of small controlled steps with a rehearsal before the real switch. Compressed timelines and single big bang conversions are where corruption risk and downtime creep back in. This is the sequence we run.
- Clarify the goal. Stability for multiple users, faster screens, safer backups or groundwork for later modernisation. The goal decides how much rework is worth doing now.
- Run a lightweight audit. Who uses the database, from where, which workflows must not break, which reports drive decisions and what it integrates with. Integrations deserve first class attention because they are where Access systems hide their complexity. Our API and integration services team scopes this step on most migrations.
- Prepare the Access database. Backups, compact and repair, then fix the obvious data issues such as missing primary keys and inconsistent field types. SSMA rewards a clean source.
- Create the Azure SQL Database target. Keep sizing simple, you can scale later. Test connectivity from where people actually work, because office, home and VPN connections behave differently. Choosing between tiers and options is covered in our Azure cloud SQL database guide.
- Convert and migrate with SSMA. Convert the schema, migrate the data, then validate with boring repeatable checks: row counts on key tables, totals on important numeric fields and spot checks of critical records.
- Link the front end and test real workflows. Connect the Access front end to the new tables and test the workflows people rely on most, from the locations they work in. Expect tuning. That is normal, not a failure.
- Rehearse, then cut over. Run a test migration end to end first. The cutover itself is a defined window, usually half a day: freeze data entry, take final backups, run the final sync, repoint the Access links, smoke test the critical workflows and reopen with a rollback path ready.
Which SQL target should you choose?
Azure SQL Database is the default answer for most Access migrations we do, but it is not the only option. The right target depends on hosting requirements, compatibility needs and whether the front end is staying in Access or being rebuilt.
The table below compares the four targets we discuss most with clients.
| Target | Best fit | Watch out for |
|---|---|---|
| Azure SQL Database | Most Access back ends. Managed platform, automatic backups, no server to maintain | Screens built for local file access need tuning for network round trips |
| SQL Server on premises | Strict data residency or hosting policies, existing server infrastructure | You own patching, backups and hardware, the exact burden most clients want to leave behind |
| Azure SQL Managed Instance | Larger estates needing high SQL Server compatibility | Higher running cost than a single database, overkill for a typical Access back end |
| Dataverse with Power Apps | Browser and mobile workflows, external users, a front end rebuild | A rebuild project, not a data layer swap. A later decision, not a prerequisite |
The scale gap between the file you have and the platform you would move to is worth stating plainly. Access stops at 2 GB. Microsoft’s Hyperscale FAQ states a Hyperscale database supports up to 128 TB of data. No Access back end we migrate needs that headroom, which is the point. Growth stops being the thing you plan around. Microsoft’s Azure SQL Database overview also notes the service always runs on the latest stable version of the SQL Database Engine with a patched OS, so server maintenance stops being your problem too.
If browser access or external users are on the horizon, the front end can later move to Power Apps while the data stays in Azure SQL. That is a separate project, and our Power Apps services page covers what it involves.
How long does an Access to SQL Server migration take?
A small, tidy, well structured Access database is typically a couple of weeks from audit to cutover. Complex systems take longer, and the driver is almost never the data transfer itself. It is the rework and testing of queries, VBA and integrations that SSMA does not convert.
The variables that stretch a timeline, in the order we see them:
- Heavy VBA business logic that assumes local tables and needs review against a networked back end
- Parameter queries and action queries that SSMA leaves behind for manual conversion
- Integrations, exports and scheduled jobs nobody documented
- Data quality problems such as missing keys, duplicate records and inconsistent types
- Testing access to the people who actually use the system, which is a scheduling problem, not a technical one
A rehearsal migration is the best predictor. Once a test run has been completed and validated, the production cutover is usually a half day window rather than a leap of faith.
What does an Access to SQL Server migration cost?
Two cost lines, not one. The one off migration work, audit, SSMA conversion, query and VBA rework, testing and cutover, is the larger line and scales with the complexity drivers above. The ongoing line is Azure consumption, which for a typical Access sized back end runs on a modest monthly compute and storage model.
We deliberately do not publish dollar figures in a guide because they date fast and every database is different. What we can say from the migrations we run is that the quoting conversation is really about the audit findings: user count, VBA depth, integration surface and data quality. A focused audit turns the cost question from a guess into a scoped number, which is why it is where every engagement of ours starts. This kind of staged move off an ageing platform is the core of our legacy modernisation service.
What goes wrong most often?
Most failed or painful Access to SQL Server migrations trace back to a handful of avoidable mistakes, and almost all of them happen after SSMA has finished running. The tool moves the data reliably. The risk lives in the queries, screens and connections that surround it.
- Treating SSMA output as a finished system. Tables arrive, then the untested queries and VBA quietly break workflows in week two.
- Slow screens blamed on Azure. The real culprit is usually a form pulling an entire table over the network or joining on unindexed fields. Index the join fields, filter early and open smaller record sets.
- Testing only from the office. The system feels fine on the LAN and then crawls for the person on home NBN over VPN. Test from every location that matters before cutover.
- No rollback plan. A cutover without a tested way back turns a small failure into a business outage. Rollback is a requirement, not an option.
- Ignoring the integration surface. Exports, imports, scheduled emails and API calls fail silently after the data moves, which is why the audit treats them as first class scope.
Case study: stabilising a jobs and quoting database
A professional services firm in Victoria ran its jobs, quotes and work orders through an Access database shared by its office team. The symptoms were the classic trio: forms and reports slowing as the file grew, backup routines that made everyone nervous and corruption scares that had already cost recovery time.
We kept the front end and migrated the data layer to an Azure SQL Database using SSMA, following the staged process above: a focused audit of the workflows that mattered, schema and data migration, query and index tuning for network performance, then a rehearsed cutover with a rollback path.
The outcomes were the ones the firm actually wanted rather than a rebuild it did not. Multi user access became stable, the everyday screens got noticeably faster and backups moved from a nervous manual routine to the platform’s automatic backups with point in time restore. Staff kept the forms and reports they already knew, and the business kept its options open for modernisation later.
Frequently asked questions
Is Microsoft Access being discontinued?
No end of life has been announced for Access in Microsoft 365, where it continues to receive updates. The 2026 date behind the rumour is just Access 2021 retiring on 13 October 2026, a version ending, not the product, and Microsoft’s lifecycle pages show Access 2024 supported to October 2029. The practical risk for most businesses is not Access disappearing, it is a single shared file carrying a critical workload.
What is a cutover in an Access to SQL Server migration?
A cutover is the planned switch from the old Access data source to the new SQL back end during a defined window, usually half a day. Data entry is frozen, final backups are taken, the last sync runs, Access links are repointed and critical workflows are smoke tested before the system reopens. A tested rollback path is part of the plan, not an afterthought.
Does SSMA convert my queries and VBA code?
Mostly no. Microsoft’s migration support article states SSMA mainly migrates tables and select queries with no parameters, and that forms, reports, macros and VBA modules are not converted. Everything else needs manual review, rework and testing, which is where the real effort in a migration sits.
Can the Access front end connect to Azure SQL from home or over VPN?
Yes. The front end connects to Azure SQL through linked tables over ODBC, and it works from any location with a reliable connection. Performance depends on design rather than distance: forms that open entire tables will crawl anywhere, while screens that filter early and join on indexed fields stay responsive over VPN and home connections.
Do we have to rebuild in Power Apps instead of migrating?
No. Moving the data layer to Azure SQL and rebuilding the interface are separate decisions, and doing the first does not commit you to the second. Stabilise the data now, then move to Power Apps later only if browser access, mobile workflows or external users genuinely justify a rebuild project.
How much bigger can an Azure SQL Database grow than Access?
An Access database is capped at 2 gigabytes minus system overhead under Microsoft’s Access specifications, while an Azure SQL Database in the Hyperscale tier supports up to 128 TB per Microsoft’s Hyperscale FAQ. The comparison is not about needing 128 TB. It is about removing the ceiling entirely.
Get a clear recommendation
If you want a straight answer on whether an Access to SQL Server migration fits your database, book a free consult. We will talk through how the system is used, what is causing the pain and what a sensible path looks like. You will leave with a clear recommendation to migrate now, stabilise first or plan a staged approach, the likely complexity drivers and a realistic timeframe range for your situation.
Book a free consult and bring your worst form load time with you.
