Roger Jennings' Access Blog: Access to SQL Server Migration: Understanding the Assessment Report and Error List

Bill Ramos posted Access to SQL Server Migration: Understanding the Assessment Report and Error List on 5/27/2011. This information will be useful to folks who watched my Upsizing Access 2010 Projects to Web Databases with SharePoint 2010 Server Webcast:

The SQL Server Migration Assistant for Access assessment report displays the results from converting a Jet based Microsoft Access solution to use SQL Server. The report displays errors, warnings, and information settings based on either the Default Project Settings or the Project settings you have when set when you run the report. For information regarding the project settings, see “Access to SQL Server Migration: Understanding SSMA Project Settings”. The Error List user interface provides a convenient way to navigate to the specific object that has the error, warning, or informational message. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template.
Running the Assessment Report

There are two ways of viewing the assessment report in SSMA. The first is way is to access the report after running the Migration Wizard as shown below.

When you run the report via the wizard, you’ve already completed the migration so there is no opportunity to correct any errors before perform the Convert Schema and Migrate Data commands. There is a nice feature within the Migration Wizard dialog that allows you to click on the hyperlink total of messages to display the Error List dialog.
The second way of launching the error report is to not use the wizard and follow these steps:

Create a New Project and complete the dialog.
Issue the Add Databases command and select the database you want to migrate.
Expand the Access-metadata node in the Access Metadata Explorer. Clear the checkboxes except for the database you want to run the report on.
Right-click on the database and select Create Report.

Elements of the Assessment Report

For the Northwind 2007 database, your assessment report will look like this.

The navigation pane allows you to drill down to see which objects had problems reported as an error.
The details pane provides a summary of the errors when you select a folder in the navigation pane or all of the errors, warnings, and info messages when you select the actual object.
The tool bar allows you to filter the lower pane by errors, warnings, or info messages by clicking on the element.
The tool bar displays the estimated manual conversion time required to resolve all of the errors displayed
The display pane allows you to group by specific error, warning, or info messages. It also provides a way to navigate the specific object and all messages associated with the object. The image below shows that happens when you select the Employees table under the “Datatype is not supported” error message.

Considerations for Migrating Access Queries

When you manually run the report by selecting all objects, you may get more errors reported because SSMA reports against potential issues when attempting to migrate Queries into SQL Server View statements. In general, you will want to take a linked table approach when migrating your solution. This approach keeps the Query definition in Access as is and relies on using the linked table references in the query to process the results using the Jet engine.
The recommended approach is to uncheck Queries in the Access Metadata Explorer before running the report. This approach also prevents SSMA for creating Views on SQL Server when you eventually issue the Convert Schema and Synchronize with Database commands.
Using the Error List

Assuming that you converted Northwind 2007 using Migration Wizard with the Linked Tables option, you will see and Error List when you close the Assessment Report and the Migration Wizard as shown below.

By clicking on the 245 Warnings and 233 Information controls highlighted above, you can show just the 6 Errors reported after the conversion.

By double clicking on the error, SSMA takes you to the object associated with the message in both the Access Metadata Explorer and SQL Server Metadata Explorer and displays the definition of the objects as shown below.

You can then see how SSMA mapped the unsupported data type in SQL Server from Access by scrolling through the Access and SQL Server table definitions.
Dealing with Attachment, Hyperlink, and ComplexLong Data Types

It’s been our observation that Access solutions that need migration to SQL Server very seldom use the problem data types used for the Northwind2007 sample database. Here are some things to consider if you do need to migrate these types.
Attachment

The Attachment type stores the file in Access database. In SQL Server, you have several options to consider. You will want to extract the files from the Access database and then consider storing links to the files in your SQL Server database or using the FILESTREAM feature to maintain the attachments. In the upcoming SQL Server code name “Denali” release, you can use the new FileTable feature.
Hyperlink

The Hyperlink data type ends up getting mapped to a nvarchar(max) type by SSMA. You may want to alter the result using the table editing feature in SSMA’s SQL Server Metadata Explorer or using SQL Server Management Studio to use a smaller value. In your Access solution, you can still use the hyperlink behavior in Forms and Reports if you set the Hyperlink property for the control to true.
ComplexLong

The ComplexLong data type is the result of using a lookup query and choosing the multi-selection option for the Long data type. Underneath the covers in Access, there is a junction table that records the results of the multiple selection. In SQL Server, you can emulate the same behavior with a junction table based on the query and then reference the junction table in your Access solution.
Conclusion

As you can see, SSMA provides two mechanisms to help you identify potential issues that you may need to deal with for migrating an Access database to SQL Server. The Assessment Report and Error List user interfaces are also part of SSMA for MySQL, Oracle and Sybase and work in a similar way. The time estimates included in the Assessment Report are just a guideline to help in estimating the scope of the changes needed and can vary greatly in your approach to solving a specific issue.
Additional Resources and References

For specific details on each of the project options, please refer to the Project Settings help topics for SSMA for Access.

Optimizing Microsoft Office Access Applications Linked to SQL Server http://msdn2.microsoft.com/en-us/library/bb188204.aspx describes in detail many of the techniques that were only mentioned briefly in this blog post.

The UtterAccess forum has a discussion group dedicated to SQL Server questions http://www.utteraccess.com/forum/Microsoft-SQL-Server-f32.html. You can post questions and get answers from Access experts who work with SQL Server on a daily basis.

The FMS Upsizing Resource Center contains links to various resources http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html.

JStreet Technology's developer downloads section http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp has useful tools and presentations to help you get up to speed.