How do I replicate a temporal table

Question

I have a temporal table, and I want to replicate it using transactional replication. The history table cannot have a primary key required for transactional replication. When I try replicating the current table, replication fails because it cannot insert into the GENERATED ALWAYS AS ROW START or GENERATED ALWAYS AS ROW END columns.

Answer 1

Microsoft Documentation states:

Snapshot and transactional replication: Only supported for a single publisher without temporal being enabled and one subscriber with temporal enabled.

Here is an example temporal table with some dummy data:

CREATE TABLE [dbo].[TemporalTest]
(
    [EmployeeID] CHAR(6) NOT NULL,
    [EmployeeName] VARCHAR(50) NOT NULL,
    [EFF_STRT_TS] DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    [EFF_END_TS] DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([EFF_STRT_TS],[EFF_END_TS]),
CONSTRAINT [PK_TemporalTest] PRIMARY KEY CLUSTERED ([EmployeeID] ASC),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTest_HIST]));
GO

INSERT INTO [dbo].[TemporalTest]
([EmployeeID],[EmployeeName])
VALUES
    ('000001','Jane Doe'),
    ('000002','John Smith'),
    ('000003','John Deer'),
    ('000004','Dear John')

DELETE FROM [dbo].[TemporalTest]
WHERE [EmployeeID] = '000003'

UPDATE [dbo].[TemporalTest]
SET [EmployeeName] = 'Jane Smith'
WHERE [EmployeeID] = '000001'

Before replicating, turn off SYSTEM_VERSIONING.

ALTER TABLE [dbo].[TemporalTest]
SET (SYSTEM_VERSIONING = OFF);

Set up transactional replication, and exclude the period columns [EFF_STRT_TS] and [EFF_END_TS]. On the replicated side, add period columns.

ALTER TABLE [dbo].[TemporalTest]
ADD [EFF_STRT_TS] DATETIME2(7) NULL,
    [EFF_END_TS] DATETIME2(7) NULL

Using SSIS, copy the history table [TemporalTest_HIST] from the publisher to the subscriber. Also using SSIS, overwrite the current [TemporalTest] table from the publisher to the subscriber so the time period column values match exactly and are not null. Afterward, alter the columns on the subscriber side to make the period columns NOT NULL, and set them as the PERIOD FOR SYSTEM_TIME.

ALTER TABLE [dbo].[TemporalTest]
ALTER COLUMN [EFF_STRT_TS] DATETIME2(7) NOT NULL

ALTER TABLE [dbo].[TemporalTest]
ALTER COLUMN [EFF_END_TS] DATETIME2(7) NOT NULL

ALTER TABLE [dbo].[TemporalTest]
ADD PERIOD FOR SYSTEM_TIME ([EFF_STRT_TS],[EFF_END_TS])

Both on the publisher and the subscriber side, set SYSTEM_VERSIONING = ON.

ALTER TABLE [dbo].[TemporalTest]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTest_HIST]));

From now going forward, the publisher and the subscriber will each maintain their own system-versioned temporal table. The whole temporal table structure isn't replicated, so the period columns may not align exactly depending on how long replication takes.

Answer 2

We needed to replicate only the current table (the opposite of Microsoft's assumed scenario). We created a non-temporal indexed view on the "current" table, and replicated the indexed view. We skipped the system versioned columns in the view (not that I think it really matters).

Answer 3

In my case turning off SYSTEM_VERSIONING was not enough. I had to drop perdiod columns also:

ALTER TABLE TemporalTest DROP PERIOD FOR SYSTEM_TIME

Excluding them in Publication properties only, finished with the following error during subscriber initialization

Msg 13504, Level 16, State 1, Line 36 Temporal 'GENERATED ALWAYS AS ROW START' column definition missing.

After initialization you need to add period columns and turn on system versioning

ALTER TABLE [dbo].[TemporalTest]
ADD PERIOD FOR SYSTEM_TIME ([EFF_STRT_TS],[EFF_END_TS])

ALTER TABLE [dbo].[TemporalTest]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTest_HIST]));