By John Kullmann | Published on July 19th, 2016 | Last updated on May 19th, 2025 |
Want to try yourself? Here is how
Are you stuck up with a dilemma whether to migrate your Access database by yourself or to seek professional assistance? Then you’ve landed at the right place! This blog will guide you comprehensively through the key aspects of migrating your Access database to SQL Server, empowering you to make an informed decision. One of the first and most rudimentary considerations before you take the plunge into migration is understanding the size of your database. How large is your database? The size fundamentally influences the approach and resources you need for migration. To help you visualize this, here is a graphic representation showing database sizes based on the number of records:
Depending upon the size and properties each kind of database holds, databases may be broadly categorized as:
- Small: These databases don’t put much stress on performance, and all queries should work smoothly without specialized optimizations. Only marginal performance improvements can be expected with enhancements such as adding indexes. Small databases are typically straightforward to migrate using simple tools.
- Medium: Medium-sized databases focus more on performance and maintenance. Resources are usually allocated to maintaining the database and administering it proactively to prevent performance bottlenecks, thereby minimizing downtime. Migration of these databases requires a more careful approach to preserve relationships and data integrity.
- Large: Large databases have dedicated resources, often including database administrators, who actively maintain and improve performance. Expert intervention is often necessary to perform optimizations, and database metrics including health and status are closely monitored. Migration of large databases requires thorough planning and testing to ensure smooth transition.
- Very Large: Very large databases are critical as they store vast amounts of information, and performance becomes a mission-critical aspect. Every query is optimized to ensure data remains readily accessible. These databases typically employ sophisticated clustering or replication techniques. Migrating such databases is a complex task that requires professional expertise and specialized migration tools.
Now, with a clearer understanding of these database sizes and classifications, you should be able to identify which category your database falls into. If your database is categorized as medium size or smaller, you can confidently proceed with upsizing your Access database to SQL Server on your own, using the right tools and following best practices.
Do It Yourself
Here we list some key steps to guide you through the upsizing process and help you migrate your MS Access database to SQL Server with minimal hurdles:
- Database creation and Log devices: Begin by creating the database and log devices in SQL Server. It’s important to allocate storage at least 5 times larger than your Access .mdb file to accommodate data growth and logs. For the SQL Server master database, create the ODBC data source to pull storage information required by the Upsizing wizard.
- Determine Data and Table Relationships: Understanding relationships between tables is crucial. Use the Access Database Documenter tool by selecting Tools > Analyze > Documenter from the Access .mdb database menu. Choose the current database tab, select relationships, and click OK to generate a detailed report. This helps validate referential integrity and cascade settings that must be preserved post migration.
- Comply with SQL Server File Naming Conventions: SQL Server has strict naming conventions. Ensure your table and field names conform—limited to 30 characters, no spaces, and no illegal characters. This compliance is critical if you plan to use the Upsizing Wizard without running into errors.
- Eliminate Recursive Relationships: Remove recursive relationships before migration to avoid structural issues with the Upsizing wizard. After successfully creating the table structure, you can enforce these relationships via triggers or by creating foreign keys referencing primary keys. Also, establish the order of table dependencies to load data correctly.
- Verify the Default Values: Access shows default values before saving a record, but when SQL Server tables are attached, Access doesn’t display these defaults. It’s important to check and validate default values during migration to ensure application behavior remains consistent.
- Import Data tables into the Code file: Import all tables from your data .mdb file into your code .mdb file before running the Upsizing wizard. Be sure to include the structure, relationships, and data during import. Also, always make a backup of your data files before proceeding with the Upsizing wizard to avoid data loss.
- Run the Upsizing Wizard: When running the Upsizing wizard, ensure table relationships stay intact. For systems with referential integrity, first create the table structure only, then implement relationships using Declarative Referential Integrity (DRI) as it’s more predictable and easier than triggers. However, triggers are necessary for cascading deletes. Also, decide yourself where to place timestamps rather than letting the wizard decide. You can choose whether to generate a log report. Navigate carefully through the wizard by clicking Next, and Finish when done.
- Print and Check the Log: Once Upsizing is complete, print and review the log for any aliasing queries or other errors. You might find that some table-level validation rules did not upsize properly, requiring you to write triggers manually. Triggers are also needed for cascading events, recursive relationships, and table-level validation rules.
- Backup the new database and load Access data to SQL Server: Finally, backup your new SQL Server database fully. Now, load your Access data into SQL Server table by table. Thoroughly test your new database for all combinations, rules, and performance to ensure a successful migration.
Even though you are using the Upsizing wizard for SQL Server migration, utmost care should be taken throughout the process to avoid data loss or performance issues. If your database is large or complex, or if you want to ensure a seamless transition without risk, you can always seek professional assistance. Feel free to contact us to learn more about our migration services. Visit our website for detailed case studies and success stories involving large and complex database migrations.
John is the Director for Special Projects at Macrosoft. In that capacity, he works with new and existing clients to clearly understand their requirements and translate them for the software development teams. John has extensive experience in Six Sigma, Lean Engineering and managing international operations. His background has allowed him to be responsible for ensuring ongoing client satisfaction. John consistently provides excellent customer service, ensuring the highest quality.
John is a frequent speaker at industry events and is the Chairman of the Morris County Chamber of Commerce Tech Talk Forum.
Though John always takes his work very seriously, he does not take himself so serious. Outside of work, John sits on the Board of Directors for Family Nature Summits. Additionally, he plays tennis and enjoys every outdoor activity.
