My 0.5 cents as well.
>- is there any type of data that does not match up between VFP and SQL?
SQL server or one of the other well known backends, all have datatype support for VFP and beyond (even there is support for the general field but I believe nobody use general in VFP). You are asking this specifically for SQL server. You should be aware of depending on driver and what you use (RV,SPT, CA) you might not get what you expect from the backend. There is no "empty" date\datetime and FWIW you must set your date\datetime to accept null.
If I were you, I would choose postgreSQL as the backend. If you ask why, there are plenty of reasons that may be too much to discuss here, so maybe just to keep it short it is said to be "most advanced open source RDBMS" - a truly open source one, you are free to do anything with it commercial or not. (as per the datatypes it has much more types than MS SQL server and not only that, much more built-in functions for them).
>- can all types of queries bring down the same data to VFP i.e. selecting into an array, local cursor etc or do some things need rewriting?
You would change how you look into data. Instead of bringing all the data 'down the wire', you get what you need. Even in case of browsing all data there is support for fetching.
>- does referential integrity need to be handled by the back-end or if the logic is in the front end, can it stay there for now?
If all the data is managed through your app then it is not too much important where it is handled. However, it is common to have RI in server (it doesn't matter if you still also control in frontend).
>- do the table structures need to change at all e.g. if the table uses a primary key generated from a "keys" table (as the framework I use does), does it have to change to an AUTOINC field etc?
Structures DO need to change. Generating keys from a table can of course stay. (and AUTOINC is not the only way for generating keys automaticall). In SQL server there is SEQUENCE too for example. But that is relatively new and most of the SQL server developers don't use it (not so easy to use in MS SQL server - in postgreSQL however it is as easy as 1,2,3 and behind the scenes it keeps the values and some other info for keys in a table).
