Comparing Table Conversion in the Upsizing Wizard and SSMA

SSMA and the Upsizing Wizard use different strategies when performing a conversion.

In our sample databases, we have intentionally inserted known problems so that you can compare how each tool handles these issues. SSMA can convert the Employees table, but it doesn't populate any of the data. This is because our table contains a UNIQUE INDEX with IGNORE NULLS. Unfortunately, this version of SSMA simply adds a UNIQUE INDEX to the table; a better strategy would be to ignore any indexing of this type.

SSMA, like the Upsizing Wizard, adds a default of 0 to any Yes/No field, which avoids the problems described earlier of Bit fields without defaults.

Unlike the Upsizing Wizard, which cannot handle Required fields, SSMA correctly maps these to NOT NULL in the database structure. SSMA can also partly handle NULL values in a Required field or illegal dates in the data. The strategy here is to attempt to migrate data that does not have problems. In the sample data, you can see the order number 10257, which contained an illegal date, 01-Jan-100. This then resulted in the order header record not being migrated, but the order detail records are migrated; this can mean that your data is inconsistent with the RI. However, you might feel that it is better to have some data rather than no data. There is no substitute for running the tests described earlier in this tutorial, for checking dates and missing values in required fields before migrating your data. SSMA produces output advising you of where data has been partially migrated, as shown here:

Preparing table Northwind_Problems_ForSSMA.[Orders]...
Migrating data for the table Orders from the database Northwind_Problems_ForSSMA...
The following error occurred during migration of the current batch:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
........
See the log for the detailed information.
Data migration complete: 818 rows of 830 migrated.
Data migration operation has finished.
    6 table(s) successfully migrated.
    2 table(s) partially migrated.
    0 table(s) failed to migrate.

SSMA provides a split window to compare the data in Access against the migrated data in SQL Server.

At one time in the distant past, the behavior of Access changed so that when you added a new text field, it always set the AllowZeroLength property to No. This was then promptly changed back to default AllowZeroLength to Yes; this setting tended to make data imports and other activities more difficult in Access. If you have an older database that has this set on all your text fields, then when converting by using the Upsizing Wizard, it ignores this attribute. In the past, SSMA generated a Check constraint to do this (which is probably not what you wanted). The new version of SSMA generates a dummy Check constraint, which does not actually perform any checking, an example of which is shown in the following code:

ALTER TABLE [dbo].[Customers]
CHECK CONSTRAINT
[SSMA_CC$Customers$Address$disallow_zero_length]
GO

This strategy is a compromise between not adding the Checks, and providing placeholders for them. If you want to avoid this, you can write some code to scan through the fields in each Access table, changing the AllowZeroLength to Yes before migrating the database.