How to do data synchronisation between two applications?

Question

Our client has a CRM application (let's call it A) and a custom built Windows application (VB.NET) (let's call it B) that directly writes to and reads from the CRM database.

Application A is used (mainly) to manage clients and their orders. Application B is used to manage all the products our client sells.

Our client wants us to build a webshop (C) using ASP.NET WebForms so they can sell the products they manage with B . All of their clients and orders should be present both in A and in C.

Since B will take care of all the communication to A, will have to come up with a way to let B and C synchronise their data.

This is something relatively new to us. We have done something like this in the past, but I really don't like the way it was solved there:

When someone orders something in the webshop, C puts the order in an XML-file, zips the XML-file and sends it to an FTP-server in the network of A. A monitors the directory on the FTP-server, unzips each new file, processes the XML, creates a new XML-file, zips it and sends it to an FTP-server in the network of C. C monitors the directory on the FTP-server, unzips each new file and processes the XML.

In addition to this, each night A generates an XML-file for each table in its database, zips them and sends them to the FTP-server in the network of C. C unzips the files and lets SSIS process the XML-files to update the database of C.

I really don't like that.

There must be other ways, right? We probably need single record sychronisation and batch synchronisation. How is this usually tackled? Webservices? Something custom like Rhino ETL? Something else?

Update: It would be nice if you could also give a short explanation of how the technology you propose is typically used and how it impacts the different applications.

Answer 1

This is very bad, I've drawn a diagram to understand the situation better:

                                   (Foobar: XML/FTP/ZIP)
 ------<-----------> (A)<-|----------------------------------------> (C)
 | db |                   |
 ------<-----------> (B)  |  

Its pretty clear that "Foobar" needs to somehow directly access the db. The two options are:

• Bypass (A) and directly access the db
• Application (A) give you the API (web service, RESTful, SOAP,etc) to the db

I believe the first option is unlikey (but talk to your clients).

This leaves option two, which means the burden is on your client to develop the API to allow you to access their db.

Of course you can maintain an "offline" transaction db which can used to store transactions when the connection is down between "Foobar" and then sync when its up again, but this is optional.

I hope that helps.

EDIT:

Propose this solution:

 ------<-----------> (A)  |
 | db |                   |
 ------<-----------> (B)  |  
    ^                     |         (WEB SERVICE)
    |______________> (W)<-|------------------------------ (C) (ASP.NET Store Front)

They will need to host the Web Services that exposes access to their db . This is the standard practice.

Now who builds the web services is for you and your client to decide.

Answer 2

Have you considered a service oriented architecture? I don't mean a big enterprisey thing. I mean creating RESTful services that allow you to encapsulate parts of your domain (customers, products, etc) from each other. In a SOA, there isn't synchronization, there's collaboration between properly decoupled services.

Sending zipped XML files by FTP would just be a humorously terrible thedailywtf-style anti-solution were it not (amazingly) considered reasonable by real people somewhere.

Answer 3

You can use Windows Communication Foundation (WCF). One point publishes modifications, the other points subscribe and receive notification:

• The product manager (B) publishes product changes. The web shop (C) subscribes. B needs to be modified so it will trigger WCF during 'save'. C needs to be modified by adding event handler.
• C publishes sales information. B subscribes.

If you need guaranteed delivery, you should explore Microsoft Message Queuing (MSMQ) as the data transport (WCF can use HTTP, MSMQ, etc for data transport, you can choose).