[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.
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.
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.
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.
The following table shows the impact of the conversion options settings based on the mode you select.
Add primary key
Add timestamp columns
Specifies whether SSMA should create a timestamp value if it is required.
Include a data assessment report with conversion assessment reports[ Updated:Include a data assessment report ]
Includes a data assessment in the assessment report.
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.
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.
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.
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 (*).
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.
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.
With regards to the above options, here are a few points to consider.
The following table shows the impact of the data migration settings based on the mode you select.
Specifies whether SSMA should check constraints when it adds data to tables.
Specifies whether SSMA should fire insertion triggers when it adds data to SQL Server tables.
Specifies whether SSMA preserves Access identity values when it adds data to SQL Server. If this value is False, SQL Server assigns identity values.
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.
Specifies whether SSMA locks tables when it adds data to tables during data migration. If the value is False, SSMA uses row locks.
[Added: in SSMA 5.2]
Replace unsupported dates
Specify how to handle dates that are out of range of target data type
[Added: in SSMA 5.2]
The number of rows ot insert into a table in a single transaction
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.
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.
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”.
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.