I have 162 customers each having approximately 42 VFP tables each What are your thoughts as to the best way to migrate VFP tables to Azure SQL?
- Use wwsql to call stored procedures to insert data into the sql tables?
- Use wwsql to insert data from a cursor?
- Use a 3rd party utility such as DBConvert, which has some limitations?
- None of the above
I have used very successfully ESF Database Migration Toolkit at https://www.dbsofts.com/
We only had our shop to convert to MSSQL on Premise. And it looks like they do support FoxPro to Azure. (https://www.dbsofts.com/articles/foxpro_to_azure/)
The cool thing and I think good for your multiple site situation is you make scripts that you can reuse. My memory is a little foggy on the details, but you might need to make a script for each table. Each table's columns can be customized in terms of datatypes and so forth. Then when you upsize you can just click each script and it fires off in a separate instance and is very, very fast.
ESF is not free and looks to be $400 USD.
You can use the FoxPro Upsizing wizard, either to create a SQL database locally first and then upsize that to Azure, or go directly to Azure. Azure SQL is just SQL Server with some limitation essentially so for all intents of purposes it's just another SQL Server connection.
If all you need to do is move tables over then the upsizing wizard works fine - if you need to do data mapping and integrity checking etc. then some other tool might be warranted but I find those things dubious as it is unlikely to properly establish relationships coming from FoxPro tables.
+++ Rick ---
It looks like the upsizing wizard only works with database containers and not individual tables. At least the version on my computer doesn't have the option to select individual tables. Is there a later version available that provides more options?
You can drag and drop all your tables into a DB Container to create one. Drag and drop from a or multiple folders.
+++ Rick ---
Is the VFPX upsizing wizard any better than the native FoxPro wizard? I created a dbc and set up a successfully tested Azure connection and got this error when I tried to add a table "upsizing wizard cannot continue. Server query failed, server error 40515"
The VFPX version should be better. AFAIK, it's made by the same person, with several enhancements.
However, VFP doesn't have any 5 digit error codes, so the error must come from Azure.
Try to Google azure error 40515
If I were you I would upgrade to SQL Server first, then to Azure from there. SQL Server has native upsizing tools that will point out the conversion issues which the VFP Upsizing wizard is not going to know about as it assumes it's updating to SQL Server.
The two are mostly compatible but there are a few subtle differences in type support and commands that you can run. It's possible that one of the VFP Wizard commands is not supported against Azure so it would likely work for SQL Server though.
The SQL -> Azure Conversion tools are part of SSMS if I recall correctly (presumably also in Azure Data Studio).
+++ Rick ---