Synchronization Between SQL Server 2008 Express and VFP Tables
I am looking for tips and suggestions on how to sync data between two databases.
The first database is SQL Server 2008 Express, which runs on disconnected laptops (no network or internet access). The second database (main) is VFP 9.0, which runs on the server.
When the user connects their laptop to the network, I want the sync process to go through.
Apart from the different database mechanisms, I have to consider the following elements:
- Tables don't necessarily have the same structure
- Primary keys do not match (GUID on SQL Server and often a combination of character fields in VFP)
- Synchronization of tables must be done in a specific order to respect the parent-child relationship
- In some SQL Server side insert, a new primary key needs to be created and synchronized in the VFP table
- A bunch of validations have to be done and sometimes some user feedback is required.
- Not all recordings need to be in sync.
- Some SQL Server entries need to be deleted after syncronisation
- Deleted entries on both sides must be considered.
- The VFP database requires minimal changes.
There are probably other points that I am forgetting now, but I think you have an idea of the problem I am facing. My guess right now is that I will need to create my own sync module, but I want your input to be there before I continue in case I am missing some options and get some advice on how to approach this.
I took a quick look at Microsoft Sync Framework, but with all the limitations I have and the fact that the VFP client is not already built (AFAIK), I don't think it will be very useful.
Thanks in advance for your feedback.
Refresh . The laptop app is a C # WinForm app and uses SQL Server 2008 Express.
a source to share
The complexity of the situation and requirements leads me to believe that you need to write a Visual FoxPro application. Visual FoxPro easily connects to SQL Server 2008 data. The code complexity meets the requirements and identifies the data to be synchronized, not the syntax. The power of Visual FoxPro is its data processing language and the ability to connect to virtually any data source (native DBF, ODBC, ADO, and XML).
SQL Server can read VFP 9 data using the VFP 9 OLE DB driver. You can write T-SQL stored procedures to get VFP data. Not sure how it will know if the laptop is connected to the network.
Another approach is to use SQL Server XML-Diffgrams. I am not an expert in any degree of imagination on this approach, but it will be something you can explore.
Since my experience with Visual FoxPro, it would have been easier for me to go the other way, but that's just me. You should go with the skill set of resources that you have for the project.
VFP reads and writes SQL Server data over a connection (DSN, ConnectionString) and any technique involving SQL Passthrough (SQLConnect (), SQLExec () and SQLDisconnect ()), CursorAdapters, Remote Views, or a combination of the three.
Visual FoxPro can also recognize Windows events such as network connectivity. The application can be installed on every laptop and launched to recognize a Windows event. After the event is raised, the application can try to connect to the SQL Server database (possibly a network connection with no SQL Server or other network available).
Once connected, it runs logic to validate and synchronize the databases.
It looks like you don't have much control over the application writing to VFP 9 data on a laptop. If you have control over the writing of the application to the VFP 9 database, you might consider modifying the application to write to the laptop instance of SQL Server Express, and then you can use SQL Server Replication to control the synchronization. However, this is not a trivial task, and SQL Server replication, while improving performance with each version, causes hair loss for DBAs. There is definitely a lot of work going down this route.
Rick Schummer Visual FoxPro MVP
a source to share
I would encourage you to take another look at the MS sync framework. We have a situation where we want to synchronize intermittent C # client applications with our Java / Oracle backen. You can use the sync framework providers for the C # client and implement your own KnowledgeSyncProvider subclass for the backend. This will help you halfway and show you a good pattern to apply for a vacation.
a source to share