Overview
A professional services firm had been running their entire client management and billing operation out of a Microsoft Access database for over a decade. By the time I was brought in, the database had grown to 4 GB — well beyond Access's reliable operating range — and was crashing at least once a day. Critical billing queries were taking 4–8 minutes to run, and the nightly backup process regularly timed out. The business was losing hours of productivity every week to workarounds and data recovery.
The engagement covered a full migration from Access to SQL Server, a complete rewrite of all critical queries as stored procedures and views, an indexing strategy designed around the actual query patterns, and a Python script to automate the initial data migration and validate row counts and data integrity across every table.
Key Deliverables
- Full schema migration from Access to SQL Server with correct data type mapping and constraint definitions
- Python migration script with row-count and checksum validation to confirm zero data loss
- Rewrite of 14 critical queries as SQL Server stored procedures with proper set-based logic replacing cursor-heavy Access VBA patterns
- Clustered and non-clustered index strategy based on execution plan analysis of the top 20 slowest queries
- SQL Server Agent jobs replacing the manual nightly backup and archiving process
- Views exposing commonly joined tables for use by reporting tools and the Access front-end during transition
Challenges & Solutions
The Access database had no foreign key constraints enforced, meaning there were orphaned records throughout — billing entries linked to deleted client IDs, invoices with no associated projects. Before migrating, I ran a full referential integrity audit in Python, flagged all orphaned records for the client's review, and built a reconciliation process to either re-link or archive them before importing into SQL Server with constraints enabled.
Several of the most expensive queries had been written using nested correlated subqueries that executed row-by-row — perfectly valid in Access but catastrophically slow in SQL Server at this data volume. Rewriting these as CTEs with proper joins and window functions reduced their execution time from minutes to under a second each.