Synchronisation between SQL Server 2008 Express and VFP tables

Question

I'm looking for advices and suggestions on how to synchronise data between two databases.

The first database is a SQL Server 2008 Express that run on disconnected laptops (no network or internet access). The second database (main) is a VFP 9.0 that run on a server.

When the user connect their laptop on the network, I want the synchronisation process to go through.

Other than the different database engines, I have the following items to take into account:
1) The tables don't necessary have the same structure
2) The primary keys are not the same (GUID in the SQL Server and often a combination of character fields in VFP)
3) Synchronisation of the tables must be done in a certain order to respect the parent-child relationships
4) On some insert on the SQL Server side, a new primary key must be generated and synchronised in the VFP table
5) A bunch of validations must be made and some feedback from the user are sometimes needed
6) Not all records need to be synchronised
7) Some records on the SQL Server need to be deleted after the syncronisation
8) Need to take into account deleted records from both side
9) Minimal modifications need to be done on the VFP database

There are probably other points I'm forgotting now, but I think you get the idea of the challenge I face. My guess right now are that I will need to build a custom synchronisation module, but I want your input before I go on in case I overlooked some options and to get some tips on how to approach this.

I looked rapidly at Microsoft Sync Framework, but with all the restrictions I have and the fact that there is no VFP client already built (AFAIK), I don't think it will be of great help.

Thanks in advance for your feedback.

Update: The laptop application is a C# WinForm application and is using SQL Server 2008 Express.

Answer 1

The complexity of the situation and requirements leads me to believe you need to write a Visual FoxPro application. Visual FoxPro connects to SQL Server 2008 data easily. The complexity of the code is matching the requirements and identifying the data that needs to be synched, not the syntax. Visual FoxPro strength is in the data manipulation language and the ability to connect to almost any data source (native DBFs, ODBC, ADO, and XML).

SQL Server can read VFP 9 data via the VFP 9 OLE DB driver. You could write T-SQL stored procedures to get to the VFP data. Not sure how it would recognize the laptop being connected to the network though.

Another approach is to use SQL Server XML Diffgrams. I am not an expert by any stretch of the imagination on this approach, but it would be something you can research.

Since my expertise is with Visual FoxPro I would find it way easier to go the other way though, but that is just me. You have to go with the skillset of the resources you have for the project.

VFP reads and writes SQL Server data via a connection (DSN, ConnectionString) and any technique involving SQL Passthrough (SQLConnect(), SQLExec() and SQLDisconnect()), CursorAdapters, Remote Views, or a combination of the three.

A Visual FoxPro program can also recognize Windows Events like connecting to a network. The application could be installed on each laptop and running to recognize the Windows Event. Once the event is raised the application can attempt to connect to the SQL Server database (possible it is connecting to a network without the SQL Server available or a different network).

Once connected it runs the logic to check and synchronize the databases.

Sounds like you don't have a lot of control over the application writing to the VFP 9 data on the laptop. If you do have control over the application writing to the VFP 9 database you might consider changing the app to write to a SQL Server Express instance on the laptop and then you can use SQL Server replication to manage the synchronization. Not a trivial task though and SQL Server replication, while getting better with each release, does cause hair loss in DBAs. Definitely a lot of work going this route.

Rick Schummer Visual FoxPro MVP

Answer 2

I would encourage you to take another look at MS sync framework. We have a situation where we want to synchronize occasionally connected C# clients apps with our Java/Oracle backend. You can use the sync framework providers for the C# client and implement your own custom subclass of KnowledgeSyncProvider for the backend. This will get you half-way there, and show you a good pattern to apply for the rest.