Client Industry: Healthcare
Technology Stack: SQL Server, Transactional Replication, T-SQL
Project Type: Database Replication & Data Synchronization
Duration: 4 Weeks
Outcome: Near Real-Time Historical Data Replication
A client required a solution to replicate data between two Microsoft SQL Server environments while supporting incremental data consumption by downstream applications.
The source environment (Server X) contained a database (D1) with a table (T1). The target environment (Server Y) needed to maintain a synchronized copy of this table for reporting and application access.
The client requirements were as follows:
The source table (T1) did not contain any audit or timestamp columns that could be used to identify newly inserted or modified records.
However, downstream applications required the following metadata columns:
crdate - Timestamp indicating when a record was initially inserted into Server Y
altdate -Timestamp indicating when a record was last modified in Server Y
Applications would use these columns to retrieve only newly inserted or modified records, significantly reducing data transfer and processing overhead.
An additional challenge was that the target database had to function as a historical repository. Therefore:
After evaluating multiple approaches, database triggers were ruled out due to concerns around:
Instead, SQL Server Transactional Replication was selected as the foundation of the solution.
The following implementation steps were performed:
Transactional Replication was established between:
A snapshot was generated and applied to initialize the subscriber database.
Immediately after snapshot initialization, the following columns were added to the subscriber table:
crdate DATETIME NOT NULL DEFAULT(GETDATE()) altdate DATETIME NOT NULL DEFAULT(GETDATE())
These columns existed only on Server Y and were not part of the source schema.
By default, Transactional Replication propagates DELETE operations to subscribers.
To satisfy the archival requirement, the generated replication stored procedure (sp_MSdel_*) on the subscriber was modified so that DELETE statements were ignored.
As a result:
This allowed Server Y to retain historical records indefinitely.
A secondary challenge emerged whenever a Snapshot Agent execution was required.
During snapshot reinitialization:
Consequently, all records received new crdate and altdate values, causing downstream applications to incorrectly interpret the entire dataset as newly inserted data.
To preserve historical timestamps during snapshot refreshes, an additional process was developed.
A full backup of the subscriber database was taken before snapshot execution.
The backup was restored to a temporary database (for example, D1_Backup) on Server Y.
The replication snapshot was generated and applied normally.
After snapshot completion, the crdate and altdate columns were recreated with default datetime values.
A custom reconciliation process was implemented after snapshot initialization.
The process compared data between:
The comparison was performed using SQL Server TABLEDIFF utility to identify records that existed in the backup database but were missing from the newly initialized subscriber database.
The reconciliation process performed the following actions:
crdate and altdate values for records that existed before the snapshot.crdate and altdate values from the backup database.This ensured that downstream applications continued to receive only genuine incremental changes.
The implemented solution successfully achieved all business objectives:
crdate and altdate tracking.By combining SQL Server Transactional Replication with custom subscriber-side enhancements, a robust historical replication framework was established. The solution enabled efficient change tracking, preserved historical records, and supported downstream delta-processing requirements while avoiding the performance concerns associated with trigger-based implementations.