Update an SQL db with changes

TL;DR
I need to monitor Sync gateway changes and push any appointments booked etc back to the primary SQL database (also on Azure). From a service on Azure I will be able to access both CB and the SQL database, so given a changed doc I can extract the data and update SQL db.

Backround:
I’m proposing Couchbase for a new project. We need to sync data between multiple mobile devices via P2P during the day and sync changes back to an existing SQL database when connectivity allows.

My current proposed solution is a small Couchbase server running on Azure. This will be refreshed nightly extracting only the data required for the following days activities from the existing SQL database. Users will sync mobile devices to CB server prior to setting off in the morning. P2P will keep the mobile devices sync’d during the day (appointment booking, customer details updated etc), with one device as a nominated server (Android NSD to advertise the service and remaining devices to discover and sync to nominated server device).

When connectivity is available (it may be constant, it may be patchy) the nominated server device will sync to the Sync Gateway on Azure.

What would be the suggested way to implement a change event handler on Azure (We currently have an .NET MVC WebApi running on there which could be extended). We are primarily .Net/Android devs.

Does this sound like a sensible use of CB. If successful I’d like to extend the data stored in CB in the future and possibly migrate away from SQL.

You can use Talend to sync couch-base server to sql server its more like creating a SSIS package.

Here you can use sync gateway or couchbase server who so ever fits better. We have used sync gateway in it and I think @shalini.garg can help you in that.

Why you want to go for all time P2P, is something I find a little less efficient. All devices can have a connection to server via Internet but when network is low you can switch to P2P. And this can be done by getting the status of pull replication. If the pull Replication status is in offline it means you don’t have connection with sync gateway. At that time you can start P2P sync by triggering a blutooth signal. Which may take time for the first sync because of the walk through done between 2 devices (This is true when you switch sync gateways too).

Simultaneously in Tale End you can do long poll to sync gateway to get the latest changes in the documents and then use that data to push into sql server and this happens almost instantly.

Apart from this if you dont want to use Talend then you can create a windows service which can use sync gateway Rest API to get deltas from sync gateway using _changes (long poll) and push that into sql server.

following is a blog which can help start things by using talend :-
https://blog.couchbase.com/mysql-couchbase-using-talend-etl/

1 Like

In Talend, you can fetch data from sync Gateway based on last sync sequence, parse required data and push into SQL server via directly inserting into tables or calling the stored procedure.
We have implemented similar kind of sync from couchbase to PostgreSQL. Long poll was not working in my project so I did this using infinite loop and sleep control in Talend.

Is this a one-way sync from Sync Gateway -> RDMBS? If so, I’d suggest a changes worker to subscribe to all new changes from Sync Gateway and write them to the RDMBS.

Here’s an example of a changes worker in Go.

If you let me know your language of choice I can try to find an example. Otherwise check the docs for the _changes Sync Gateway REST endpoint.

I haven’t used the Talend connector so I can’t comment on that one. @househippo do you have any thoughts on Talend for this use case?

@traun

Taland is great for things like taking data like this table below when relationships are some what simple.
24 PM
TO
48 PM
and vise versa.

As soon as you have some basic count/sum, advanced business rules, auditing , data transformation i.e. ETL Taland gets you only about 50-90% there. Then you have to walk(code) the rest of the way. So most people make their own mapping and business logic app: JAVA,C#, Node.js … etc.

I talked about “Integration” and Sync Gateway here in this Youtube video: https://youtu.be/HXI2RX1AqyY?t=19m25s

Thank you for the excellent responses. I shall investigate the options over the weekend. I appreciate you taking the time to assisting a developer new to CB.

There will be business logic involved between CB->SQL so I’ll definitely be writing an application to implement this in C#

@traun At the moment the _changes API seems to fit the best. Am I correct in assuming that this approach would be using webhooks to push changed documents to my own API where I can then read the document and update the SQL db ?