I have a feeling a lot of your design decisions were made around the limitation of VFP that you couldn't add/remove fields without exclusive access to the table. In SQL Server that limitation is removed and that should simplify some of your rewrite decisions.
I'm assuming in your app you used generic char fields and then in your metadata tracked what type of data was actually being stored (numeric, date, etc.). In SQL server, because you can add fields on the fly, the fields types can actually match the data that is being stored.
Brandon Harker
Sebae Data Solutions