Microsoft Access to SQL Server migration
Keep the Access front end your people already know, and move the data to Microsoft SQL Server for reliability, security and room to grow.
Same application, stronger foundation
In an upsized system the forms, reports and VBA stay in Access. The tables move to SQL Server, and Access connects to them over ODBC. Users see the screens they have always used. Behind them, the data is held by a database server designed for many concurrent users, large volumes and managed backups.
For many organisations this is the most cost-effective improvement available. It removes the main limits of an Access back end without the cost and disruption of replacing the whole application.
Why moving the data to SQL Server is a good idea
Access is a capable front end, but its built-in data file was designed for small workgroups. As a system gains users, data and importance, the data file becomes the weak point. Moving the data to SQL Server addresses that directly.
More users without the slowdown
An Access back end is a shared file that every PC reads and writes across the network. That works well for a few users and strains as numbers grow. SQL Server does the work centrally and sends back only the results, so it copes with many people working at once.
Far less risk of corruption
A shared file can be damaged when a PC crashes or a connection drops in the middle of a write. SQL Server manages every write itself and records it in a log, so an interrupted update is undone cleanly and the database stays intact.
Room for the data to grow
An Access database file cannot exceed 2 GB, and performance usually suffers well before that. SQL Server removes the ceiling. The free Express edition alone holds 10 GB per database, and the paid editions hold far more.
Proper security
Anyone who can reach the folder can copy an Access data file. SQL Server keeps the data behind logins and permissions, so you decide who can read or change each part of it, and the data can be encrypted.
Backups while people work
An Access file is only reliably backed up when everyone is out of it. SQL Server takes scheduled backups while the system is in use, and can restore to a chosen point in time if something goes wrong.
A foundation for what comes next
Once the data is in SQL Server, other tools can use it: reporting and dashboards, integration with other business systems, remote and multi-site working, and in time a new front end if you ever need one.
Not every system needs it. A well-built Access database with a few users on a reliable network can run happily as it is. An Access Health Check will tell you which side of the line yours is on.
Moving the tables is the easy part
An Access application written against a local Access back end makes assumptions that no longer hold once the data is on a server. Queries that were fast can become slow because Access pulls whole tables across the network to process them locally. Forms that open every record can stall. Code that relied on Access-specific behaviour can fail.
A migration should be properly engineered rather than simply moving tables and hoping performance improves. That means deciding which work belongs on the server, converting the queries that need it, choosing data types and keys carefully, and testing with realistic data and user numbers.
Talyon uses tools and techniques its founder has developed over many years of Access and SQL Server work to speed up the repetitive parts of upsizing. That keeps the time and cost down and leaves more of the effort for the parts that need judgement.
Experience includes
- Linked SQL Server tables
- ODBC
- Pass-through queries
- Stored procedures
- Views
- SQL Server authentication strategies
- Data migration
- Query conversion
- Performance optimisation
Transaction processing: all of it happens, or none of it does
Many business operations are really several updates that belong together. Posting an invoice might write a header, its lines, a stock movement and a ledger entry. In a typical Access application these are separate writes issued one after another from VBA. If a PC crashes or the network drops part-way through, some are saved and others are not, and the data no longer agrees with itself.
SQL Server lets those steps be wrapped in a transaction, usually inside a stored procedure. Either every step is committed or the whole operation is rolled back, so the database is never left half-updated. Upsizing is the natural point to move the operations that matter onto that footing.
This is selective work. Not every form needs it. The effort goes into the operations where a half-finished update would cost the business.
What transaction processing brings
- Multi-step updates that complete in full or not at all
- Automatic rollback when an error or a dropped connection interrupts the work
- Constraints and foreign keys enforced by the server, whatever writes the data
- Consistent results when several users update related records at the same time
- A transaction log that allows recovery to a point in time, not only to the last backup
How a migration is carried out
The work is planned so that the live system keeps running until the new arrangement has been proven, with a way back if it is needed.
Your data comes first
A database migration is not successful simply because the new application opens.
Record counts, key totals, relationships and business-critical results are checked against the existing system so that the migrated data can be verified before users are switched across. The existing system is retained until the new arrangement has been proven, providing a controlled fallback if one is needed.
- AssessReview the tables, queries, forms and code to find what will and will not translate directly.
- DesignDefine the SQL Server schema, data types, keys, indexes and the authentication approach.
- Migrate and convertMove the data, relink the front end, and convert queries to views, pass-through queries or stored procedures where it matters.
- TestCheck results against the existing system and measure performance with real data volumes.
- Cut overSwitch users across at an agreed time, with a rehearsed plan and a fallback.
A foundation, not a commitment
Once the data is in SQL Server, other tools can use it: reporting, integrations, and in time a new front end if the business needs one. None of that is required. Plenty of organisations run an Access front end on SQL Server for many years.
Read about staged modernisationNot sure whether to repair, upgrade or replace your Access system?
Start with an Access Health Check. You get a practical, written assessment of the system you have, the risks it carries and the sensible options, before committing to any larger project.