[__jem]

I'm not sure how this interacts with temporal tables, but I know that I've run into issues before just trying to track something with a high watermark, in that long running transactions might add records "in the past" behind your watermark that you track, so you need some mechanism to make sure you're capturing those (i.e., you can't just track an auto-inc primary key). Unfortunately, I think CDC is really the best way to handle this.

[rjbwork]

I agree that it is probably the best choice, I'm just not willing to >quadruple my server costs for the feature, unfortunately. I appreciate you taking the time to reply, and I did some further research.

It looks like as long as transactions are not taking excessive time to complete, that temporal tables will be sufficient, since the history tables get marked with the transaction begin times. I'll use a sliding window approach and dedupe.

EDIT: I also found this https://learn.microsoft.com/en-us/sql/relational-databases/s... which seems like it's supported on Express and Web versions and should be a fairly robust solution.

[dagss]

We made mssql-changefeed for this purpose:

https://github.com/vippsas/mssql-changefeed

V1 requires to run a sweeper procedure in the background, but an upcoming v2 version does without the sweep loop. Unfortunately too fresh for readme to be updated, but relevant lines in tests to show it off:

https://github.com/vippsas/mssql-changefeed/blob/v1-lazy/go/...

https://github.com/vippsas/mssql-changefeed/blob/v1-lazy/go/...

https://github.com/vippsas/mssql-changefeed/blob/v1-lazy/go/...

Library itself:

https://github.com/vippsas/mssql-changefeed/blob/v1-lazy/mig...

[dagss]

The "in the past" problem can be solved in a number of ways (misc locking patterns). Have played with a number of such schemes in mssql-changefeed (see sibling post).

CDC has 20-40 second delay for mssql which makes it unusable for many purposes.