Bill Ramos published Access to SQL Server Migration: Understanding SSMA Project Settings on 5/17/2011:
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 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 v.50, 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” (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.
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.
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 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.
No comments:
Post a Comment