Update SQL Server from Cache/Iris

It depends on...

Who is sitting at the other end? A Cache/IRIS server or a third-party product?

If Cache/IRIS: Mirroring, shadowing are the catchwords, you have to look for. In case of third-party SQL-DB: how fast (how often) want to do your updates? Once a day or (nearly)realtime?

I did something like that several years ago... the procedure is (just as a starting point):

Our application uses objects, so all the involved classes have an %OnAfterSave() method, something like this

Method %OnAfterSave(insert As %Boolean) As %Status
{
   do ..addToTransfer(..%Id())
}

with some smartness, like do not add if the record is already in the transfer queue, etc.  If you use SQL instead of objects, triggers are your friend.

We have also a task,  which crates (based on the class definition) a series of INSERT/UPDATE statement(s) and does the transfer with the help of  %SQLGatewayConnection.

in order to provide efficient updates for DeepSee there is a feature named DSTIME.
It writes kind of a log about inserts, deletes, updates. This may suit your requirements.
About 2 years ago I have written an article about the subject. Take a look if this is what you need.
https://community.intersystems.com/post/synchronize-data-dstime