Temporal Table Replication in SQL Server: Common Barriers and Solutions

Introduction

Transactional replication is a SQL Server feature that copies and synchronizes data and database objects across servers. It generally begins with a snapshot of the publication database objects and data. After this initial snapshot, any data changes and schema modifications made at the Publisher are delivered to the Subscriber as they occur, typically in near real time. These data changes are applied to the Subscriber in the same order and within the same transaction boundaries as at the Publisher, maintaining transactional consistency within a publication

Standard transactional replication in SQL Server does not provide support for system-versioned temporal tables. This constraint presents difficulties for organizations aiming to replicate historical data maintained in temporal columns, such as ValidFrom and ValidTo. The challenge persists even when system versioning is disabled, yet there remains a requirement to retain the original values within the target database.

Understanding Temporal Tables

System-versioned temporal tables are a specialized form of user table designed to retain a comprehensive record of all data modifications. These tables facilitate point-in-time analysis by automatically recording historical changes. Each temporal table contains two datetime2 period columns that specify the validity duration for each row. In addition to the current table, an associated history table preserves previous versions of rows whenever updates or deletions take place.

Scenario & Challenge

In one of the migration scenarios, the customer faced an issue where system versioning was disabled, but there was still a requirement to replicate data from the ValidFrom and ValidTo columns to the target database without modification. Although temporal tables are commonly used for auditing and historical analysis, replicating them within a transactional replication setup can present specific technical challenges:

• System managed period columns complicate schema compliance.
• Mismatch in ValidFrom and ValidTo columns across environments can compromise audit reliability.

As transactional replication currently does not support temporal columns, we devised the following solution to address this requirement.

Common Error Example

• When configuring replication for an article that includes a system-versioned temporal table, the setup process may encounter failures due to SQL Server limitations related to system-generated columns.

• In certain situations where system versioning is disabled, it may still be necessary to replicate the initial values of the ValidFrom and ValidTo period columns on the target system. However, during the configuration of transactional replication, the snapshot application process can fail on these columns, resulting in the following error:

Error message:

This issue arises because SQL Server considers these columns system-generated and restricts direct inserts, including during replication. The following workaround addresses this situation.

The Workaround

To successfully replicate temporal tables, follow these steps:

Note:  This approach will work in case of scenarios when there is a scope of minimal downtime.

1. Predefine Table Schema on Target: Ensure that the source table schema exists on the target and matches with the source schema.
2. Disable System Versioning Temporarily: Before configuring replication, disable system versioning on the temporal table. This allows replication to treat it like a regular table.

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

      3. When you set SYSTEM_VERSIONING = OFF and don't drop the SYSTEM_TIME period, the system continues to update the period columns for every insert and update operation. Use the below script to remove the period for system time.

ALTER TABLE dbo.Department DROP PERIOD FOR SYSTEM_TIME;

     4. After this step, we can use the below script step by step to configure replication.

Replication Setup Steps

• Set a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

use master
GO

exec sp_replicationdboption 
@dbname = N'SourceDBNAme', 
@optname = N'publish', 
@value = N'true'
GO

• Create a transactional publication. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName]
GO

exec sp_addpublication 
@publication = N'PublicationName', 
@description = N'Transactional Replication publication of database',
@sync_method = N'concurrent',
@retention = 0, 
@allow_push = N'true', 
@allow_pull = N'true', 
@allow_anonymous = N'true', 
@enabled_for_internet = N'false',
@SnapShot_in_defaultfolder = N'true', 
@compress_snapshot = N'false', 
@ftp_port = 21,
@allow_subscription_copy = N'false', 
@add_to_active_directory = N'false',
@repl_freq = N'continuous', 
@status = N'active', 
@independent_agent = N'true', 
@immediate_sync = N'true', 
@allow_sync_tran = N'false', 
@allow_queued_tran = N'false',
@allow_dts = N'false', 
@replicate_ddl = 1, 
@allow_initialize_from_backup = N'false', 
@enabled_for_p2p = N'false', 
@enabled_for_het_sub = N'false'
GO

• Create the Snapshot Agent for the specified publication. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName]
GO

exec sp_addpublication_snapshot
@publication = N'PublicationName',
@frequency_type = 1,
@frequency_interval = 1,
@frequency_relative_interval = 1, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 8, 
@frequency_subday_interval = 1, 
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0, 
@active_end_date = 0,
@publisher_security_mode = 0, 
@job_login = N'',
​@job_password = N'', 
@publisher_login = N'', 
@publisher_password = N''

• Create an article and add it to a publication. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName]
GO

exec sp_addarticle 
@publication = N'PublicationName', 
@article = N'ArticleName',
@source_owner = N'Source Schema Name', 
@source_object = N'SourceTableName',
@type = N'logbased',
@description = null,
@creation_script = null,
@pre_creation_cmd = N'truncate', 
@schema_option = 0x000000000803509F, 
@identityrangemanagementoption = N'manual', 
@destination_table = N'Destination Table Name',
@destination_owner = N'Destination Schema Name',
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboEmployee',
​@del_cmd = N'CALL sp_MSdel_dboEmployee', 
@upd_cmd = N'SCALL sp_MSupd_dboEmployee'
GO

• Add a subscription to a publication and set the Subscriber status. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName]
GO

exec sp_addsubscription 
@publication = N'PublicationNAme', 
@subscriber = N'Azure SQL DB Server NAme', 
@destination_db = N'Target DB Name', 
@subscription_type = N'Push', 
@sync_type = N'automatic', 
@article = N'all', 
@update​_mode = N'read only', 
@subscriber_type = 0
GO

• Add a new scheduled agent job used to synchronize a push subscription to a transactional publication. This stored procedure is executed at the Publisher on the publication database.

Use [SourceDBNAme]
GO

exec sp_addpushsubscription_agent 
@publication = N'PublicationNAme', 
@subscriber = N'Azure SQL DB Server NAme', 
@subscriber_db = N'Target DB Name', ​
@job_login = N'', ​
@job_password = null, 
@subscriber_security_mode = 0,
@subscriber_login = N'', 
@subscriber_password = null, 
@frequency_type = 64, 
@frequency_interval = 1, 
@frequency_relative_interval = 1, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 4, 
@frequency_subday_interval = 5, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, 
@active_start_date = 0, 
@active_end_date = 0, 
@dts_package_location = N'Distributor'
GO

5. Once you have performed all the above steps and completed the data migration on target database you need to stop/delete the replication and again add period for system_time on the target table and enable system versioning.

ALTER TABLE dbo.Department ADD PERIOD FOR SYSTEM_TIME(<ValidFrom>,<ValidTo>);

ALTER TABLE [dbo].[Department] SET (SYSTEM_VERSIONING = ON);

Note: 

The <ValidFrom> and <ValidTo> columns are datetime2 columns defined as PERIOD FOR SYSTEM_TIME, using GENERATED ALWAYS AS ROW START and ROW END. Request to refer the period column names you have created while creating the temporal table and use the same while adding the period columns in the above script.

Conclusion

Migrating temporal tables within a transactional replication environment involves managing system-versioned features appropriately. Temporarily disabling system versioning and removing the SYSTEM_TIME period allows for adherence to schema requirements and facilitates data replication. After completing replication on the target platform, re-enabling system versioning reinstates temporal table functionality while maintaining data integrity.

This workaround ensures that your replication strategy remains robust while preserving the audit trail and historical insights offered by temporal tables.