By Mary Chipman
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.
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:
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.
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:
For the complete list, see Reserved Keywords (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms189822.aspx in SQL Server Books Online.
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.
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:
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.
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.
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.
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.
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.
[Native Code: 30149]
[ASEOLEDB]The command has timed out.
[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: 
site: Void ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)
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 Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
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.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.
If the problem persists, please contact Microsoft support at firstname.lastname@example.org.