By: Ameena Lalani | Comments (4) | Related: > Replication
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.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
