Access to SQL Server Migration: Understanding SSMA Project Settings

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

Project Options for SQL Azure

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

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.

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.

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 https://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 https://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 https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html.

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