Replicating to a Temporal Table

Question

Replicating to a Temporal Table

richard.gardner87

Old Hand

Points: 333
More actions
November 4, 2019 at 11:54 am

#3694275
Hi, Just wondered if anyone has any experience of this?
Our problem is that our ERP is ancient, there is no primary key between the sales header and line tables, there is no timestamp on the sales line table, so we can't tell when an event happens without hitting our rather overworked and highly contended audit table on the production box.
So, what seems to be an elegant solution is to replicate to a temporal table, allow SQL to track the creation and changes in order lines, then harvest the history table for status changes, bake all the status change times into a register then only retain data in the history table for a week or so, so things don't get too lumpy.
Does anyone have any experience of doing this? I'm particularly interested in three questions :
1. Reading into it it seems that you can only have one temporal subscriber to a publisher. My current understanding is that the publisher can be any SQL version (we're running on 2012 and that's not going to change any time soon).
2. Another thing I can't find any clarity on is whether we can retain a non-temporal subscriber at the same time as running the temporal subscriber (otherwise we'll have to create another publisher on a creaky box).
3. I don't have a lot of experience in querying Temporal tables - yes, I've seen a lot of code which tells me how to infer the status of a record at a certain point in time, but what I really need is to find the first instance of that record turning up and then follow it's lifecycle. It seems pretty doable, just wondered whether there are some performance killers in there (it'll be a pretty busy table).
I was originally just going to put a trigger on the replicated table and dump each event to a log table, then rebuild my order lifecycle from that log, but this seems to be a better approach (?)
Any advice appreciated.
Thanks
Rich
- This topic was modified 5 years, 1 month ago by richard.gardner87.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

richard.gardner87 wrote:

Our problem is that our ERP is ancient, there is no primary key between the sales header and line tables, there is no timestamp on the sales line table, so we can't tell when an event happens without hitting our rather overworked and highly contended audit table on the production box.

If there's nothing direct, as you say, then there's a "Bridge Table" somewhere that has the key for the header table and the keys for the detail tables. Look for that. You can probably find it just by looking at FK's for both tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

richard.gardner87 Old Hand Points: 333 More actions · Answer 2

Thanks for the thought, if only it was that simple 😉 The actual problem is that we're having to recycle order numbers every year or so, so although the design intention for an (unconstrained) PK was there originally, in practice we need to start generating a surrogate key if we want to look at data over more than a year.

Coupled with the lack of fidelity on the change tracking and we're in the position where it's far more useful to just create an operational data store than it is to try and fix the original problem (there are already thousands of lines of archiving code in operation), also it's kind of step one in decoupling our Business Intelligence logic from our application (which you can probably appreciate needs to happen if we're ever to migrate to something else).