Transaction Replication With Temporal Tables

Question

Transaction Replication With Temporal Tables

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
June 12, 2018 at 10:41 am

#351844

Hi All
We are setting up transaction replication between our Source to Staging Server (In Dev environment Both are on same instance). i.e. my source becomes Publisher and my staging server becomes my Subscriber. Replication is working fine with no issues. But on top of my Subscriber table i have enabled Temporal Table Feature.
By this, we are trying to capture the version and track history of our records.
Steps that we followed to implement are as follows:
1. Enable Publication on my Source Server.
2. Enabling Subscription on my Staging Server.
3. Once Data for first initial snapshot is available on subscriber we enable temporal table by providing SYSUTCDATETIME in StartDate column of my main subscription Table.
Till this point everything works well. Startdate and EndDate values gets initialized correctly in temporal main table.
Problems starts when we do some inserts and updates in source server and the same records gets replicated to my subscription table.
The Value which gets inserted/updated in my startdate column is incorrect i.e. is is always less than my SYSUTCDATETIME and also difference between these dates is not consistent , it varies with range of differences.
We have implemented these set up in two different machine, one which is in EST time zone and other one is in IST time zone. And problem is same with both.
Schema option for my publication table is : 0x000000000803509F
Please provide your thoughts on why SYSUTCDATETIME is not aligned correctly in my temporal table.or any additional inputs from my side.
Attaching a screenshot of my Subscription Table having Temporal Enables onto it with wrong values.

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

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

I don;t know much about temporal features, but I do know that replication tables that need to be different from their source tables because there are some things replication isn't going to want to reproduce easily, such as identity values, can really muck things up. Replication just isn't designed to handle every possible scenario in a source table, and it's possible this is one of those occasions. I suspect that the temporal change was replicated over, but, the values are then based on the timing of the actual replication insert. You would have to be able to do something on the source side like NOT FOR REPLICATION on the temporal feature, if that's possible. Again, I don't know enough about temporal features to know if that's even possible or not.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

Thanks for replying, much appreciated
We are enabling temporal on my subscription table, not on my publisher table as it is an application database we cannot perform DDL over there.

Yesterday i observed the exact issue, and which is:

StartDate column is not being updated/inserted with its default value. However i believe the time that it is inserting in the startdate column of temporal table is the last/max date of the transaction replication occurrence of the table
For e.g select top 1 entry_time from distribution.dbo.MSrepl_transactions order by entry_time desc;

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Replication is going to overwrite values for a column that is included in the replication set of stuff to be replicated. Expecting to over-write those values using temporal features just isn't realistic. If you had temporal data at the publisher instead of the subscriber, that might be a whole different kettle of fish. If you know how to examine transaction logs, or can find log reading software, I'd be very interested in seeing which SPID does each update to a record in the subscriber, in a test of this scenario. I'd be guessing that the temporal feature sees an update via the tran log, and processes that update for temporal purposes, which perhaps then gets overwritten by replication a 2nd time that isn't logged because it's a verification update so as to minimize logging for replication purposes ??? I have no idea exactly what goes on under the covers, but such wouldn't surprise me... If there's anyone that knows why this occurs, I'm all ears and stand ready to be corrected...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)