Partager via

Access to SQL Server Migration: Handling Schema Migration Issues

  • Article

By Mary Chipman

Overview

In this blog post I’ll discuss some of the data migration issues you may encounter when migrating your Access schema to SQL Server. These consist of issues that you should address prior to migrating your data, and others are behavioral differences that you should be aware of. In my experience, it’s better to take a proactive approach and solve any known issues sooner rather than later. Fixes and workarounds after the fact are always more expensive and time-consuming than doing things the right way to begin with. The simple fact is, Access shields you from much of the complexity involved creating in a database application, and once you leave its protective umbrella for SQL Server, you face an infinitely more challenging database engine with a much higher learning curve. You can use the assessment reports in the SSMA Migration Assistant (SSMA) to identify potential issues before you migrate your data, which will not only smooth data migration, but also simplify application development later on.

Taking a preventive approach to data cleansing

You can also use the SSMA Migration Assistant to create an assessment report. This will help you determine how much of the migration will be successful, and give you guidance for issues that you may need to address before migrating your data. Follow these steps to create an assessment report:

  1. In the SQL Server Migration Assistant, expand the Access-metadata node in the Access Metadata Explorer.
  2. Clear the checkboxes except for the database you want to run the report on.
  3. Right-click on the database and select Create Report.
  4. The Assessment Report window will open when the report is complete. You can click on the plus (+) signs next to the errors and warnings to see more detailed information, as shown in the screenshot below.

clip_image002

Sometimes it isn’t possible to fix data problems in Access, or you may want to convert from an Access-compatible data type to take advantage of features and functionality that is only available with the new types in SQL Server 2008. For more information, see Data Type Conversion (Database Engine) https://msdn.microsoft.com/en-us/library/ms191530.aspx in SQL Server Books Online.

Handling Access object names, special characters, and SQL Server reserved keywords

Access and SQL Server have different lists of reserved keywords and special characters. There are two excellent reasons to fix object names in Access to conform to SQL Server rules before you migrate:

  1. It’s easier to make these changes in Access. Altering tables and columns in SQL Server can be a cumbersome process.
  2. If you don’t fix them, all of your future queries and Transact-SQL code will require square brackets [] around non-conformant object names. This also includes names with spaces or other special characters in them. Having illegal names not only creates extra work, it also your application more difficult to support.

For the complete list, see Reserved Keywords (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms189822.aspx in SQL Server Books Online.

Understanding Autonumber/identity column behavior and default values

SSMA migrates Autonumber columns in Access to identity columns in SQL Server, however identity columns in SQL Server behave differently. In Access, an Autonumber is supplied immediately when a user navigates to a new row. If the user cancels before saving, then the Autonumber value is discarded. In SQL Server, the identity column value is only available after the row has been saved.

SSMA also migrates default values, but their behavior is similar to that of identity columns. In forms bound to Jet or ACE tables or queries, default values appear when a user navigates to a new row. Once the tables are in SQL Server, default values defined on the server aren’t added to the row until it is submitted to the server.

SQL Server transactions use a two-phase commit to ensure data integrity. A large part of the overhead of SQL Server consists of ensuring that the ACID properties of a transaction are enforced. Understanding how transactions work is fundamental to working successfully with your data once it is migrated. For more information, see Transactions (Database Engine) https://msdn.microsoft.com/en-us/library/ms190612.aspx and Autocommit Transactions https://msdn.microsoft.com/en-us/library/ms187878.aspx in SQL Server Books Online.

Managing indexes, primary and foreign keys

SSMA migrates both primary and foreign keys to SQL Server. Access allows you to have tables without a primary key, but in SQL Server every table should have a primary key or unique index to ensure the correct updating of data[KR1] . If your tables do not have a primary key, SSMA Migration Assistant can add one for you during the conversion process. Here are some additional tips regarding indexes:

  • If your table has a primary key or unique index with multiple null values, the conversion will fail. Change the index type or, preferably, remove the null values. For more information, see Allowing Null Values https://msdn.microsoft.com/en-us/library/ms189265(v=SQL.105).aspx in SQL Server Books Online.
  • SQL Server has 900-byte limit for the maximum total size of all index key columns. If your Access table has large indexes, remove them before upsizing. For more information, see Maximum Size of Index Keys https://msdn.microsoft.com/en-us/library/ms191241.aspx in SQL Server Books Online.
  • SQL Server does not support primary/foreign key relationships where the field sizes differ, or relationships where the referenced table does not have a primary key or unique index. You should fix those issues before attempting to migrate your data. For more information, see Creating and Modifying FOREIGN KEY Constraints https://msdn.microsoft.com/en-us/library/ms177463.aspx in SQL Server Books Online.

Indexing your tables efficiently can be a complex process, depending on your data load, among other factors. Indexes that you created in Access may or may not be of much use when the data is migrated to SQL Server. To get started with creating the right indexes, see General Index Design Guidelines https://msdn.microsoft.com/en-us/library/ms191195.aspx in SQL Server Books Online. Also see the mssqltips.com site listed in the Additional Resources and References section below.

Revising incompatible validation rules, formatting, input masks, lookups, and captions

SSMA will not convert format and input mask properties, table or field caption properties, and table lookup fields. Access is able to incorporate functionality, such as expressions and even VBA functions, in Jet or ACE tables that are not available in SQL Server. Since these are part of the Access application, and not the Jet/ACE database engine, they cannot be migrated to SQL Server.

In general, validation rules can be implemented as CHECK constraints in SQL Server, but the rules for constraints aren’t as liberal as they are in Access. You should implement formatting, input masks, and captions in the client application, or presentation tier, not in the database, where it adds unnecessary overhead. For more information on CHECK constraints, see Creating and Modifying CHECK Constraints https://msdn.microsoft.com/en-us/library/ms179491.aspx in SQL Server Books Online.

Additional Resources and References

This third-party paper, What are the main differences between Access and SQL Server? https://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html provides sample code and workarounds for many conversion issues.

SQL Server Profiler is an indispensable tool for "looking under the hood" at the differences between Access/Jet/ACE and SQL Server. You can set up a trace and filter only the activities and databases you are interested in monitoring. It will show you the SQL statements that are sent to the server for processing, and help you troubleshoot performance problems and bottlenecks. For more information, see Introducing SQL Server Profiler https://msdn.microsoft.com/en-us/library/ms181091.aspx.

FMS has been providing developer tools for Access and SQL Server for many years, and their Upsizing Resource Center https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html has link to papers and videos that you may find useful.

  • Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/video.html

  • Microsoft Access Database and Migration Challenges (Channel 9)

https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/MSDN-TV.html

This site, https://www.mssqltips.com, contains tips and tutorials. It allows you to search for SQL Server tips by category, which is extremely useful when you’re looking for targeted information. Tip categories include constraints, indexing, database design and development, and many, many more. The authors include industry experts and Microsoft MVPs (Most Valuable Professionals), who are recognized for volunteering their time helping people succeed with Microsoft technologies and products.

Bio

Mary Chipman is an independent consultant who has written and spoken extensively about Microsoft data platforms and technologies. She was awarded MVP status (Most Valuable Professional) from 1994 through 2004 when she joined Microsoft, working as a programmer/writer until 2010. She has authored and presented award-winning SQL Server and .NET courseware for Application Developers Training Company (AppDev.com) and spoken at industry conferences, most recently TechEd 2008 and 2009. She is co-author of the classic Microsoft Access Developer's Guide to SQL Server https://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446/.

Comments

  • Anonymous
    April 21, 2011
    The comment has been removed

  • Anonymous
    April 29, 2011
    Sounds like your Connectivity provider (Sybase ASE OLEDB?) is timing out and causing data migration to fail. Can you try using the latest SSMA v5.0 released on April 28th 2011, to see if the problem goes away. We have introduced new feature to support ODBC/ADO.Net connectivity providers and have also added parallel data migration. blogs.msdn.com/.../sql-server-migration-assistant-v5-0-is-now-available.aspx If the problem persists, please contact Microsoft support at ssmahelp@microsoft.com.

  • Anonymous
    January 13, 2014
    The comment has been removed