SSMA, part 2

The SQL Server Migration Assistant for Access does a fine job in converting Access data into a SQL Server database. But there are a few issues.

One issue is that SSMA does not convert relationships where the PrimaryKey in Access is a compound index, i.e. if your PK uses more than one field, that relationship will not migrate so you'll have to create that manually.

Another issue is that SSMA does not convert relationships that do not enforce referential integrity (RI). Not a common scenario, but if you do have those relationships in Access then you'll need to create them manually in SQL Server.

A third issue relates to the Access application itself. Access represents boolean fields as -1 = True, and 0 = False. It is very common for Access developers to use this property in code or queries, for example, testing if SomeField=-1, rather than testing SomeField=True.

This will not work correctly after using SSMA, because SQL Server represents 1 = True. To fix this, you will need to inspect every query to find those occurrences and change SomeField=True. This should be done in Access before using SSMA, to preserve the Access code base so that the Access-data version is the same as the MSSQL-data version.

One final issue, if you want to call it that, is that SSMA retains all of the Access tables but renames them by bracketing the name with SSMA$ and $local. If you have a large amount of data, the resulting Access file will be somewhat larger after conversion because the linked tables add a small amount of bulk to the file. After the first conversion, it is useful to have the Access tables in the event that something does not convert as expected, but if you are using an iterative process - convert the data, test the app, modify the app as required, repeat the conversion - there is no option I found in SSMA to delete the original Access tables. That would be a nice addition to the product.