 SSMA for Access 4.2 Observations -
09-03-2010
, 05:17 PM
I spent a lot of time with the SQL Server Migration Assistant 4.2
for Access this week, and these are some observations that might
help others:
1. if the machine you're installing it on doesn't have Access
installed, you will have to install. The installer prompts you to
download what's needed, pointing you to the A2010 runtime if you
have no Access and to ACE/DAO if you have a version before
A2007/A2010 (I'm not entirely sure about this, as my work machine
has A2007 installed, but I was prompted to download and install
ACE/DAO, which I did, anyway).
2. for licensing, it requires a connection to the Internet unless
you can supply an existing license file. Today I was working on a
server that I thought wouldn't have an Internet connection (Internet
access was added since the last time I sat at its console, to my
shock!), so I brought along the license file form my home machine.
It worked! So, if you have one license file, you can use it for
another installation.
3. don't use the wizard -- it doesn't give you enough options and
takes you right into the upsizing without previewing it first.
Instead, cancel the wizard, and use the toolbar at the top, the
buttons of which are in a nice order for the process. You first
choose an Access database, then a SQL Server, and then skip a button
or two and choose CREATE REPORT to see what errors and warnings come
up. You can then correct them in your source MDB/ACCDB and run it
again (though I found that you sometimes need to close the project
and reopen it, as it doesn't seem to refresh with changes made to
the data file after it was originally opened).
4. Many Access validation rules don't transfer very well. I had
rules like "Not Null Or 0 Or -1 Or 9", and those caused problems --
they just got marked with warning and were otherwise ignored. After
looking at the DDL to create the database, I saw that the problem
was with the "-1" so replace those with "Not Null Or In (0,-1,9)",
and those got upsized fine. Another bad validation rule was "Not
Null Or (>=0 And <=6) Or 9" which didn't work, but "Not Null Or In
(0,1,2,3,4,5,6,9) did work. I suspect there are other valid Access
validation rules that it can't convert as well, but those were the
ones in this particular database.
5. Be careful to note which tables have bad date values in them.
Unlike earlier Access upsizing wizards, it doesn't throw out the
rows with invalid dates, but it creates the date column with
out-of-range dates as TEXT! So, you want to correct those before
upsizing. The problem is, it tells you how many records have bad
values, but not which fields, nor how many in each field
(theoretically, one record could have invalid dates in more than one
field). I was working with data that originated in dBase III going
back to 1989, and there was no date validation until 2000, when my
Access app went into production use, so there were quite a few bad
dates in there.
6. the reports it creates are HTML, but they don't work in anything
but IE (which really sucks and is really at odds with the direction
MS is taking in its actual web-based products). They are also not
accessible from the SSMA UI -- you have to browse to them in
Explorer to get back to them once you've closed them.
7. when you're re-running the report to find errors/warnings,
uncheck the tables that don't have problems so you save time. I
didn't realize this until the very end, and it was one of those real
head slappers.
Overall, it's a better tool than the old upsizing wizards, but,
frankly, it's not that great. I wouldn't want to be without it, but
it's only an incremental improvement over what we had in the past,
and its dependencies and UI leave a lot to be desired.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
 |
Linear Mode
