[AccessD] Migrate to SQL Server

I am beginning the long hard task of migrating a client from Access data stores (many many) to SQL 
Server.  This is my client, the data is mostly mine etc.  The client is on board that we do this and 
in fact is investing in the server, OS and SQL Server software to do this.

My problem is that while I have used SQL Server a ton over the last few years it has not been in the 
normal parent / child / grandchild, enforce referential integrity, enforce uniqueness and all that 
jazz.  So I need to learn some stuff like how to enforce unique values in a column.

I also need to discover how to migrate data from Access to SQL Server.  The data migration wizard in 
SQL Server is actually quite good however AFAICT it does not pull relationships in, in fact it does 
not even capture the fact that the PK is an autonumber and PK.  It also seems to default to nvarchar 
whereas I prefer varchar.

Thus importing data using that wizard does work but it is pretty labor intensive fixing up the 
identity and setting the PK to a PK, editing mappings and so forth.

I would like to start a thread on this aspect of moving to SQL Server.  What has been your 
experience in this data migration, what tools have you used, what gotchas have you run into etc.

Thanks,

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it