SQL Server Replication for Temporal Tables

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   

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.

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.

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.

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.

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.

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.

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

• Read more SQL Server Transaction Replication tips.
• Learn to Query Temporal Tables from here.

Ameena Lalani

Ameena Lalani is a SQL Server veteran and started her journey with SQL Server 2000. She is a Microsoft Certified Solution Associate on SQL Server 2016. She is a Chapter leader of the Chicago SQL Server User Group. She has implemented numerous High Availability and Disaster Recovery solutions at various companies. Ameena loves to share her knowledge about SQL Server and learn from others who like to share their knowledge. She really believes that no one is smarter than all of us combined. She volunteers and speaks at SQL Saturday events throughout the United States.