SQL Server Migration Assistant (SSMA) Team's Blog

How-to articles, tips-and-tricks, and guidelines for migrating from Oracle/Sybase/MySQL/Access to SQL Server and SQL Azure

Access to SQL Server Migration: Handling Schema Migration Issues

Access to SQL Server Migration: Handling Schema Migration Issues

  • Comments 3

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) http://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) http://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) http://msdn.microsoft.com/en-us/library/ms190612.aspx and Autocommit Transactions http://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 http://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 http://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 http://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 http://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 http://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? http://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 http://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 http://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

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

  • Microsoft Access Database and Migration Challenges (Channel 9)

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

This site, http://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 http://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446/.



  • While attempting to migrate from Sybase 15 to MS we receive an error on a very large table.  We are not able to find any information and MS seems to have the help yourself model only.  Any ideas on the below error?  It would seem the process times out on large tables.

    Preparing table dekalb.dbo.budget_transaction...

    Preparing table dekalb.dbo.budget_transaction_detail...

    Preparing table dekalb.dbo.burb...

    Data migration operation has finished.

               0 table(s) successfully migrated.

               0 table(s) partially migrated.

               109 table(s) failed to migrate.

    An unexpected error occurred. Please send the log file to product support. For more information, see "Getting SSMA Assistance" in the product documentation.

    [00000]

    [Native Code: 30149]

    [ASEOLEDB]The command has timed out.

    LOG FILE:

    [Generic: Mandatory] [372/1] [2011-04-18 16:03:44]: SQL Server Migration Assistant 2008 for Sybase v4.2.2277

    [Gui: Error] [372/7] [2011-04-18 20:03:10]: Exception: [00000]

    [Native Code: 30149]

    [ASEOLEDB]The command has timed out.

    site: Void ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)

    source: ASEOLEDB

    error code: -2147467259

      at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

      at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)

      at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

      at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

      at System.Data.OleDb.OleDbCommand.ExecuteScalar()

      at Microsoft.SSMA.Framework.Generic.Utilities.DbUtilities.DbCommandWrapper.ExecuteScalar()

      at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)

      at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteScalar()

      at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.GetTableRowCount(String tableName, IDbConnection connection)

      at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.ProcessNode(IDataMigrationContext context, DataMigrationState state, XNode node)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.DefaultTreeTraverser`3.ProcessNode(TContext context, TState state, XNode node)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)

      at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.TraverseTree(TContext context, TState state, XNode sourceNode)

      at Microsoft.SSMA.Framework.Generic.DataMigrators.Default.DefaultTreeDataMigrator.DoMigrateData(IDataMigrationContext context, XNode subtree)

      at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.Sybase2SqlServerDataMigrator.DoMigrateData(IDataMigrationContext context, XNode subtree)

      at Microsoft.SSMA.GUI.Generic.LowLevel.AppCommandProcessing.Commands.SsmaAppDataMigrationCommand.StartMigrateData(Object context)

  • 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.

  • Using SSMA for MySQL, I can transfer the database schema (table & column names) from MySQL to SQL 2012. But I cannot migrate any data. I tried first migrating the whole database, then just one table. Both times I get the same error, which looks like this:

    Migrating data...

    Analyzing metadata...

    Preparing table herschel_valu_bak.address_book...

    Preparing data migration package...

    Starting data migration Engine

    Starting data migration...

    Data migration operation has finished.

    0 table(s) successfully migrated.

    0 table(s) partially migrated.

    1 table(s) failed to migrate.

    A few questions:

    1) Am I required to root/sa access on the MySQL and/or SQL Server machine to migrate the data? I have DBO access on the database in question but I am do not have "sa" access on the SQL Server machine.

    2) Does it matter which version of ODBC I use to connect to MySql? I tried the following ODBC connections:

    MySQL ODBC 5.2 Unicode Driver (v5.2.6); 64 bit

    MySQL ODBC 5.1 Driver (v5.1.13); 64 Bit; trusted

    I should have the newest versions of software as I just downloaded them last week (1/9/14).

    SQL Server Migration Assistant for MySql v5.2.1258

    thanks,

    Randy

    rvernon@spindustry.com

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post