Replication and Temporal tables


As a bit of background, there are a few good articles out there relating to problems and solutions when mixing replication and temporal tables. These articles are all related to getting temporal history onto the subscriber. Here we’ll go into that a bit and look at other options like getting source data onto the subscriber¬†and how they relate.

Temporal History at the Subscriber

At first before doing any research I thought that you could replicate the temporal history table as an indexed view to the subscriber but although this is possible, I realised that this isn’t a great solution! This is because of the way you are meant to interact with temporal tables.
There are several TSQL clauses eg “FOR SYSTEM_TIME ALL” to help you get the data you need, and these effectively union the source table and history table data then apply filters to the whole data.
If you don’t use these TSQL clauses and instead just select data from the history table directly you’ll see that inserts into the source table aren’t registered in the history table (because the union mentioned above means that this particular data¬†comes from the source table), so after replication of the history table you have only a partial view of changes sent to the subscriber ie you’ll see the deletes and updates only.

As this approach doesn’t provide all the info we need, the general solution is to not have temporal data on the publisher and make the subscriber table versioned, replicate the source data and have SQL Server generate the history there. Good article here:

Source (versioned) Table at the Subscriber

If the problem is pretty much the opposite – you need a copy of the system-versioned temporal table at the subscriber rather than the history – the solution is easier. First of all , you can not replicate a system-versioned temporal table – it is one of the restrictions in place when dealing with temporal tables:

However in this case indexed views does provide us with a viable solution.

The method is that we create an indexed view on the publisher and add it to a publication.

If you haven’t done this before you’d assume that we can go ahead and publish this indexed view as though it was a table. However if you do that only the view definition is replicated (as an empty table) and there is no consequent flow of data. This behaviour is not properly controlled in SSMS – it is one of those cases where the full article properties are not exposed in the GUI. To get the data flowing, we script out the publication and manually update the article parameter in sp_addarticle:

@type = N’indexed view logbased’

Then we create the replication publication from this script and it’s ready to go.

Once this table is created on the subscriber, you’ll find that there are no keys or constraints or indexes, so you might want to add a clustered index on the PK and add any other non-clustered indexes which existed on the source table. This could be done manually, or ideally in a post-snapshot script.

Source (versioned) Table and Temporal Table at the Subscriber

As a final point, if you really must have both the source and history tables on both environments, theoretically you could achieve this using 2 indexed views – one of the source table and one of the history table.
However although the data would all be there at the subscriber, you’d need to roll your own version of all the temporal TSQL clauses like “FOR SYSTEM_TIME ALL” because the source table isn’t truly a versioned one.

To be able to use all the TSQL history-related clauses on the publisher and the subscriber, the simplest way is to set up a read-replica on an Always On Availability Group.