Realtime Transfer of Updated and Deleted data to Cloud

[SriramChellappan]

I am looking for ways to transfer data from local server to cloud realtime. I am interested in only selected few tables. I need to send created, updated and deleted records to cloud for the cloud to update the corresponding tables. This is not for backups however to build Apps on top of the transferred data for analytics and supplemental functions.

[joes]

Our Implementations team may be able to provide some advice. You can contact them via email at Implementations@OpenDental.com.

[jordansparks]

"for the cloud to update the corresponding tables?" That means you are talking about two way transfer. The only way to do that is to use our API.
https://www.opendental.com/manual/fhir.html

[SriramChellappan]

I am looking for read only from OD database to keep the cloud tables updated.

[jordansparks]

Oh, well that's a lot easier. You would probably install a small program on the MySQL server. It would carefully query the database at certain intervals and pass the information up to your cloud. There are lots of programs that can do this out of the box, or you could write one.

[SriramChellappan]

Yes, that's what I was thinking of. However few challenges

1. I can lookup using the Primary key (Patnum for patients), as they are incremented when rows are added, to identify if any new rows were added since last sync.
2. I can use UpdateDateTime Stamp of the table to see what rows were modified since the last sync.

However for deleted row I wouldn't be able to identify them.

The other issue is realtime capture of changes. I can run the checks every few minutes using the primary key and time stamp, this shouldn't put a load on the server. However it will still be near realtime not realtime.

[SriramChellappan]

I am thinking of maintaining tables corresponding to each table (that we are interested in) in OD with just the Primary ID as their only column. This table will reside in the MySQL server as OD in a separate database.

every few minutes will check for updated rows, inserted rows using the lastupdatetime in the OD table to ship it to cloud. Compare the our local tables against the corresponding OD table for any deleted rows and send the deleted row info to cloud.

Is there any concerns in maintaining our own tables in the same MySQL as OD in a separate database?

[jordansparks]

It's fine to have your own database on the same server as OD. Each table is different. Rows never get deleted from the patient table, so you don't have to think about that. There's also a table called deletedobject that will get a new row inserted if an appointment is deleted.

[SriramChellappan]

No rows are deleted in patients table, is this true for all other tables as well? In that case I don't need to maintain a separate set of table to identify a deleted row in any table.

[jordansparks]

No, that's not true for most other tables. I think it's probably true for definition and provider.

[SriramChellappan]

Change Data Stream (CDC) appears to be the buzz word in replica or propagation of changes to secondary database. Currently exploring how to implement it in OD MySQL server. Any advises based on your experience implementing such functionality in MySQL server. I believe this CDC solution relies mainly on MySQL replication services.

[jordansparks]

Yeah, it just sounds like replication. It's possible.

[SriramChellappan]

I have read either in the blogs or manual cautions about replication. Certain queries or reports are recommended NOT to run in multiple of computers at the same time while replication is turned on. Is there a topic where there is a consolidated list of things to be caution about while replication is turned on?

Or replication can be configured to log only CUD operation not read operation to avoid read only queries can avoid being logged.

[jordansparks]

There are almost no restrictions. Just about the only problem I can think of is if you run a CREATE TABLE command for the same table on both servers. This can happen if a report needs to create a temporary table to store some data and then that table gets deleted at the end of the report. The solution is to add a random string to the end of the table name so that you won't end up with two tables of the same name. There are no other restrictions that I can think of, and the above restriction is only if you are running the report from both servers separately.