[Update: 2/9/2012 Selina JIa-Microsoft SQL Server Migration Assistant (SSMA) for Access v5.2]

In many cases, the Migration Wizard for Access will successfully migrate your Jet based Access solution to SQL Server. You may find a need to change your project options for the desired result. In this blog, I’ll describe how to use the project options available in the SQL Server Migration Assistant (SSMA) for Microsoft Access. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template. In addition, I’m using SSMA for Access v.50 [Updated:  Please obtain the lastest SSMA for Access] that is now available for download.

Getting Started with Project Options

For all versions of SSMA, there are two levels of project options: Default Project Settings and Project Settings. Changing the Default Project Settings affects subsequent projects that you create in SSMA. These settings can be overridden using the Project Settings option from the Tools menu and saved with your project. If you are using the Migration Wizard and you want to control the project settings, you should use the Default Project Settings dialog before running the wizard.

New with SSMA for Access 5.0, the Default Project Settings has a Migration Target Version that allows you to select SQL Server 2005, SQL Server 2008 (the default), SQL Server "Denali"[ Updated: SQL Server "Denali" is changed to SQL Server 2012 in SSMA 5.2] (the next major release of SQL Server) or SQL Azure. The global options you set will be specific to the target you select when you create a new project.

Default Project Settings

Project Options for SQL Azure

If you are migrating to SQL Azure, an extra page is available as shown below.

01 SQL Azure Default Project Settings

The Heartbeat Interval allows you to adjust the time interval that SSMA uses to keep the project connection alive to the SQL Azure database. The SQL Azure Server Suffix value allows you to adjust the server name suffix for connecting to your SQL Azure server.

Using the Mode Option for Controlling Conversion and Migration Project Settings

Before you go about making changes to your settings, SSMA provides four ways to control the settings used for the Conversion and Migration project settings that you can select using the drop down list control shown below.

02 Mode options

The four options are called Default, Full, Optimistic and Custom. These modes have to do with the level of tolerance that SSMA applies to the settings. When you change the value, all of the values are adjusted to one of the three default settings. If you make any changes, from one of these default settings, SSMA changes the mode to Custom. If you want to revert your custom settings back to one of the three default modes, just select the desired node from the drop down.

Impact of Mode Settings for Conversion Options

The following table shows the impact of the conversion options settings based on the mode you select.

Setting Description Default Optimistic Full

Add primary key

Creates a new primary key in the SQL Server or SQL Azure table if an Access table has no primary key or unique index False False True

Add timestamp columns

Specifies whether SSMA should create a timestamp value if it is required.

Let SSMA decide Never Let SSMA Decide

Include a data assessment report with conversion assessment reports[ Updated:Include a data assessment report ]

Includes a data assessment in the assessment report.

True False True

Message type when a primary key includes nullable columns

Specifies the type of message (Warning, Error, or Nothing) that SSMA shows in the Output pane when it finds primary keys with nullable columns.

Warning No message Error

Message type when foreign key columns are of different sizes

Specifies the type of message (Warning, Error, or Nothing) that SSMA shows in the Output pane when it finds an incorrect TEXT foreign key.

Warning No message Error

Message type when memo columns are indexed

Specifies the type of message (Warning, Error, or Nothing) that SSMA shows in the Output pane when it finds an index that contains a memo column.

Warning No message Error

Warn when a complex query uses a wildcard (*)

Displays a warning in the Output pane and Error List when a column name in a SELECT statement is a wildcard (*).

True False True

Warn when identifier name is changed

Displays a message in the assessment report and in the Output pane when an object identifier name is changed by SSMA.

True False True

Warn when identifier will be quoted

Displays a message in the assessment report and in the Output pane when an object identifier name will be quoted by SSMA. Quoting identifiers is necessary when the name is a keyword or contains special characters.

True False True

With regards to the above options, here are a few points to consider.

  • Add primary key. This is generally a good option to have set true to make sure that you can uniquely identify records for update and delete actions. SSMA will provide you a notification for tables that it adds a primary key to so that you can double check the result.
  • Warn when a complex query uses a wildcard. This option is generally good to consider setting as true to help in limiting the data based between SQL Server and Access. You will need to look at the objects like forms and reports that refer to the query to specify the columns really needed for your application.
  • Add timestamp column. This option helps applications interacting with the table to implement optimistic concurrency. The timestamp data type is marked for deprecation and is replaced by the rowversion data type. SSMA still uses the old timestamp data type. You can still use the timestamp data type for SQL Server code named “Denali” so there is no need to update change to rowversion for now.

Impact of Mode Settings for Migration Options

The following table shows the impact of the data migration settings based on the mode you select.

Setting Description Default Optimistic Full

Check constraints

Specifies whether SSMA should check constraints when it adds data to tables.

False True False

Fire triggers

Specifies whether SSMA should fire insertion triggers when it adds data to SQL Server tables.

False True False

Keep identity

Specifies whether SSMA preserves Access identity values when it adds data to SQL Server. If this value is False, SQL Server assigns identity values.

True True False

Keep nulls

Specifies whether SSMA preserves null values in the source data when it adds data to SQL Server, regardless of the default values that are specified in SQL Server.

True False True

Table locks

Specifies whether SSMA locks tables when it adds data to tables during data migration. If the value is False, SSMA uses row locks.

True True True

 [Added: in SSMA 5.2]

Replace unsupported dates

 Specify how to handle dates that are out of range of target data type

Do nothing Do nothing Replace with nearest supported date

  [Added: in SSMA 5.2]

Batch sise

 The number of rows ot insert into a table in a single transaction

10000 10000 10000

The mode setting does not impact the options for Replace unsupported dates and Batch size.

With regards to the Keep identity option, I recommend always keeping this set to True to make it easier to validate the result with primary and foreign key values for related tables.

Loading Objects Project Settings

There are five settings as shown below for controlling how SSMA attempts to load objects into SQL Server and for synchronizing your project as shown below.

04 Loading Objects

You can generally stick with the default options for Synchronization for SQL Server items.

The Attempts option may need adjustment if you have complex foreign key relationships. SSMA loads objects into SQL Server typically using multiple passes. Objects that fail to load in the first pass, such as foreign keys, might successfully load in the next pass. You may need to increase this value for your Project Settings if SSMA reports errors while loading objects.

GUI and Type Mapping Options

The GUI tab allows you to configure the number of rows that SSMA displays using the data tab for the source and target tables. In general, there is little need to change these default values. Just be aware of them when you are comparing data using SSMA that you will only see the first 100 rows by default.

For information about the Type Mapping options, please refer to the blog post “Access to SQL Server Migration: Understanding Data Type Conversions”.

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.