>We have a vertical market applications, and would like to support both a SQL and VFP back-end. I have been informed in the past that it would be a lot easier to abandon the VFP tables altogether, and simply support SQL, instead of supporting both. Generally, how daunting is it to support both? Are there issues that I should be aware of that may make it impossible or entirely impractical?
>

With a carfully constructed, multi-tiered application, you can support multiple back ends. However, this may not be realistic (or cost-effective) to rewrite your app to do this. Unless you already have, and then you are golden...

Depending on the time frame and budget you have, it may make good business sense to switch to purely SQL data. However, you are guaranteed to get some resistance from your smaller clients. One possible solution would be to use MSDE for smaller clients, and regular SQL for larger ones. MSDE is free, but has some size and concurrant connections restrictions. It may or may not work for you.


>Also, I am concerned with support. We currently release periodic updates to our clients that contain an empty set of all tables and databases (something of a data-dictionary). This is used to add and update the existing customers tables to match our current srtucture. I would like to similarly update the SQL table configuration from an update. With the security aspects of SQL, I am not sure what problems I may run into. Is it possible to reconfigure SQL tables programatically in this manner? I'm sure it must be, but I haven't found any documentation on this subject.
>

It is programmatically possible to change just about anything in a SQL DB application, either thru SQLEXEC statements or thru an application via SQL DMO. With SQLEXEC, it should be as simple as "ALTER TABLE {tablename} ALTER COLUMN" etc.

As far as building your data dictionary, I think you will have to come up with something a little more sophisticated than creating an empty DBC. You have a few options here. One would be to construct your own data dictionary. You can compare the contents of that with SQL objects (which you can retrieve info on from the system tables etc) and construct your SQLEXEC statements accordingly.

The other option would be to use a third-party modeling tool (like xCase) to handle updates. Most third-party DM tools have the ability to reverse- and forward-engineer databases.

As far as security goes, have a look at app roles in the SQL BOL. An app role would permit your update mechanism to log into the DB with DBO rights to make the changes you need.

Hope all this helps.