Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Replication for Temporal Tables


By:   |   Read Comments (3)   |   Related Tips: More > Replication

"SQL Server in The Cloud" - click to register for this free webcast


Problem

Let's say you have the need to replicate SQL Server temporal table data to offload reporting to another server. When you try to setup replication you notice you get an error "The use of replication is not supported with system-versioned temporal table XXX."  So, how can you complete this task?

Solution

The following information is from Books Online about doing what we want to do:

“Snapshot and transactional replication: Only supported for a single publisher without temporal being enabled and one subscriber with temporal enabled. In this case, the publisher is used for an OLTP workload while subscriber serves for offloading reporting (including ‘AS OF’ querying). Use of multiple subscribers is not supported since this scenario may lead to inconsistent temporal data as each of them would depend on the local system clock.”

SQL Server Replication Setup Error

To understand this situation fully, we will go through an example.

Here we have a temporal table called ReplTemporal and the corresponding history table is ReplTemporalHistory.

USE TestDB;
GO

CREATE TABLE ReplTemporal(
   Id INT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY,
   CustomerName VARCHAR(50),
   StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, -- Did not added hidden clause
   EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
   PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ReplTemporalHistory)) -- History table does not exist
GO   
database tree in ssms

We will go through the usual setup of replication such as setting up the distributer, the publisher, a publication.

When we get to the step where we have to add a table as an article to a publication, we right away see that the temporal history table is not eligible to be replicated. Why? One of the requirements for a table to be replicated is to have a Primary Key and since a temporal history table cannot have a primary key (one of the requirements of temporal tables) it cannot be replicated. So this is an issue already of replicating the history data.

new publication wizard

If we check the box next to table ReplTemporal, the New Publication Wizard returns the error shown below. It states that a table participating in temporal feature cannot be replicated. Basically, we just proved the BOL statement mentioned above. The second part of the message "Changed database context to TestDB" is a bug as it does not make any sense since TestDB is the database in which we created the temporal table ReplTemporal.

new publication wizard

So this solution for replicating the main table and history table will not work for us. But wait! There is a work around.

Workaround to Report on Temporal Data

Since we are only interested in the history table, let’s create a temporal table at the subscriber instead.

Let’s cleanup and drop database TestDB and start over.

On the publisher, create TestDB database and ReplTemporal table and insert some rows.

CREATE DATABASE TestDB;
GO

USE TestDB;
GO

CREATE TABLE ReplTemporal(
   Id INT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY,
   CustomerName VARCHAR(50),
   StartDate DATETIME2 NOT NULL constraint DF_StartDate Default DATEADD(second, -1, SYSUTCDATETIME()), 
   EndDate DATETIME2 NOT NULL constraint DF_EndDate DEFAULT '9999.12.31 23:59:59.9999999'
)

INSERT INTO ReplTemporal (Customername)
SELECT 'Amy Leon'
UNION ALL 
SELECT 'Nancy Kemp'
UNION ALL 
SELECT 'Harry Samuel'

SELECT * FROM ReplTemporal   

Now publish the table.

As you can see in the screenshot below, we are excluding StartDate and EndDate columns from replication. We did that on purpose because our goal is to create a temporal table at the subscriber and a temporal table generates its own system time for these date columns. If replication is setup successfully, you will be able to see the inserted data in the subscriber table.

publication properties

Now add the subscriber for this publication.

Now at the subscriber, add 2 date columns with appropriate constraints to this replicated table. Datetime2 datatype is required for temporal table system time columns and we have to add a constraint because the table already contains data at the time we are adding these columns. Also, define a period for which a particular data row is valid.

ALTER TABLE ReplTemporal    
   ADD StartDate datetime2 NOT NULL constraint DF_StartDate Default DATEADD(second, -1, SYSUTCDATETIME()) 
GO

ALTER TABLE ReplTemporal 
   ADD EndDate datetime2 NOT NULL constraint DF_EndDate DEFAULT '9999.12.31 23:59:59.9999999'
GO

ALTER TABLE ReplTemporal 
   ADD PERIOD FOR SYSTEM_TIME (StartDate, EndDate); 
GO
   

Next, enable system_versioning to turn this table at the subscriber into a temporal table.

ALTER TABLE dbo.ReplTemporal SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ReplTemporalHistory));   

To test replication, insert few more rows at the publisher.

INSERT INTO ReplTemporal (Customername)SELECT 'Sandy Leon'
UNION ALL
SELECT 'Veron Kemp'
UNION ALL 
SESELECT 'Bailey Samuel'   

Check the data at the subscriber.

SELECT * FROM ReplTemporal   

We can see the data below that has been replicated.

query results

Make some more updates at the publisher.

DELETE FROM ReplTemporal WHERE Id = 10

UPDATE ReplTemporal SET CustomerName = 'Sharon Kemp' WHERE Id = 11   

Verify the data at the subscriber.

SELECT * FROM ReplTemporal
SELECT * FROM ReplTemporalHistory   

We can see the data in the primary table and the history table.

query results

Not only the changes from the publisher made it to the subscriber (as evident from the first result set), but the history table keeps up with the changes made to the published table (the second result set).

Now query all data for ReplTemporal using the ALL clause of the System_Time at the subscriber.

SELECT * FROM ReplTemporal FOR system_time ALL ORDER BY EndDate Desc   

We can see this contains all of the current and historical data.

query results

Wherever you see EndDate not equal to ‘9999-12-31 23:59:59.9999999’ this means the data is from the history table and is not the current value. This query shows you the combined picture of the current as well as history table.

Summary

Replication for a temporal table is not supported, but we can take advantage of this work around where the temporal table can be created at the subscriber and we can use the history table to report on changes to the published table on a daily, weekly or monthly basis depending on the business need.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Ameena Lalani Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, June 13, 2018 - 4:09:11 AM - Shiv Mohan Back To Top

Hi Ameena,

The article has been very helpful in implementing a solution for a client where we need the transactional replication applied from source to staging, where temporal tables have been applied at staging to capture all the data changes from source.

We envisioned Staging environment to be server where the analytics team can do analysis on the latest version of the data as well as on the as of date version of the data.

The issue we encountered using the above mentioned approach is that the temportal startdate column that intents to capture the UTC date time for the date time the record has been moved into the staging area, the start date is not capturing the utc date time, but the date time of the previous transaction replication ends with couple of more seconds added.

This is evident when we do number of cycles of updates in the source that are replicated to the staging. Please refer to the distirution db system table 

[dbo].[MSrepl_transactions]

for e.g. i added a new row into source at datetime 13/6/2018 09:00:00 AM (consider it as UTC), my version start date will be somewhat like  13/6/2018 08:56:00 AM i.e. couple of minutes and seconds off from the actual UTC time when the records are inserted.

similarly if i waited for full one day to insert new records again at 14/6/2018 09:30:00 AM the version start date in my temporal will be 13/6/2018 09:00:05 AM i.e. my yesterdays couple of seconds added to the date time where my previous transaction ends.

The behaviour for temporal tables is only evident when used with replicatio and not if the temporals used independently.

Please suggest why is this behaviour there and is there any short way the issue can be resolved and force the Startdate to reflect the UTC date time when the records are inserted.

Thanks & Regards,

Shiv


Tuesday, February 06, 2018 - 11:47:03 AM - mike good Back To Top

Very nice article.  Thank you.


Tuesday, January 16, 2018 - 8:21:21 AM - DCT Back To Top

Correct me if I'm wrong but this approach doesn't truly capture the history as the dates stored on the subscriber are the dates generated when the record is introduced into the system and not necessarily the actual date when the history record when the event took place.  This could be misleading if you wanted to use the dates for report trending and such. 

 Having said that, I would probably use an ETL tool like SSIS to pull the data as is into an ODS or EDW as opposed to replication to get a true representation of the history data.

 

 


Learn more about SQL Server tools