By John Kullmann | July 19th, 2016 |
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 land at the right place, this blog will guide you through the migration aspects. Before you take plunge into migration one rudimentary aspect you need to consider is the size of your database. How large your database is? Here is a graphic image showing the database sizes based on the number of records:
Depending upon the size and properties each kind of database hold, databases may be categorized as:
- Small: Small databases don’t stress much on performance and all queries should work without specialized optimizations. Only marginal performance improvement is expected with enhancements such as indexes.
- Medium: Medium databases focus on performance and maintenance. Usually resources are allocated to maintain database and administer to prevent performance issues so as to minimize downtime
- Large: Large databases have dedicated resources to maintain and improve performance and it requires experts to perform optimizations. Database metrics comprising of health and status of database are closely monitored.
- Very Large: Very Large databases are critical as it stores vast amounts of information and performance matters. Each query should be executed to ensure that data remains readily accessible. Performance optimizations are significant and these databases make use of sophisticated clustering or replication techniques.
Now, you should have a clear understanding of the database sizes and which category your database falls into. If your database is categorized in medium size or smaller, you can proceed with upsizing your Access database to SQL Server on your own.
Do It Yourself
Here we list some steps to guide you through the up sizing process:
- Database creation and Log devices: Make sure you create database and log devices that are at least 5 times larger than the .mdb file. For SQL Server master database, create the ODBC data source that pulls storage information for the Upsizing wizard.
- Determine Data and Table Relationships: To gather information about table relationships in the database, Access Database Documenter is the recommended tool. Once you’ve installed Access Documenter select Tools>Analyze>Documenter from Access .mdb file database menu. Select the current database tab, choose relationships and hit Ok button to generate the relationships in the database. It helps in validating the referential integrity and cascade set.
- Comply with SQL Server File Naming Conventions: Make sure the table and field names are SQL Server compliant. This is helpful if you are using Upsizing Wizard. The table and field names should be limited within 30 characters and shouldn’t contain any space or illegal character.
- Eliminate Recursive Relationships: Remove all recursive relationships to avoid problems for creating table structure using Upsizing wizard. Once the structure is created, enforce recursive relationships by trigger or by creating foreign keys that reference primary keys. Also determine the table dependency order for loading data successfully.
- Verify the Default values: Note that Access displays default values before you save a record. If there is a 0 value in Access interface, when SQL Server tables are attached Access no longer displays the default values. So it is important to check the default values during migration.
- Import Data tables into the Code file: Before you run the Upsizing wizard, import all the tables from data .mdb file into the code .mdb file. Make sure you include the structure, relationships and data during import. Also, make a backup copy of your data files before you run the Upsizing wizard.
- Run the Upsizing Wizard: Ensure table relationships remain intact when you run the Upsizing wizard. If you have a system with referential integrity, create the table structure only. For relationships use Declarative Referential Integrity (DRI) instead of triggers as it is more predictable and easier to use. However triggers are required for cascading deletes. Don’t let the wizard decide where to put the timestamps during the upsizing process. You can decide whether to create a log report or not, navigate through the wizard after considering all your requirements by clicking the Next button. Click Finish button once you are done.
- Print and Check the Log: Once you run the Upsize wizard, print the log to check if there are Aliasing queries or other errors. You may find that the table level validation rules didn’t upsize and you may need to write triggers for it. Triggers are also required for relationships with cascading events, recursive relationships and table level validation rules.
- Backup the new database and load Access data to SQL Server: Backup your master database and run a full SQL Server database backup. Now you can load the Access data to SQL Server table by table. Make sure you test your new database thoroughly for all combinations and rules.
Even though you are using Upsizing wizard for SQL Server migration, utmost care should be taken throughout the process. If you think your database is large and you need professional assistance, you can contact us to know more about our migration services. Visit our website to know more on the complex projects involving large database that was successfully migrated.
John is the Director for Special Projects at Macrosoft, serving as the executive business contact for clients he is responsible for client satisfaction. Consistently provide excellent customer service to accounts, as well as represent client needs and goals within the organization to ensure quality. Deliver strong and sustainable results, over-coming the many competitive challenges of multiple industries and technologies.
