Case Study: Implementing Historical Data Replication with Change Tracking in Microsoft SQL Server

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

Key Benefits Delivered

• Implemented near real-time data replication between SQL Server environments.
• Enabled efficient delta-based data consumption for downstream applications.
• Preserved historical records by preventing source-side deletions from propagating to the archive database.
• Eliminated the need for triggers, avoiding additional overhead on the source system.
• Maintained timestamp integrity even during replication snapshot reinitialization.
• Delivered a stable and scalable solution that has been running successfully in production for several years.

Background

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.

Business Requirements

The client requirements were as follows:

• Replicate data from Server X to Server Y.
• Applications and users should perform all read operations against Server Y.
• Support incremental (delta) data retrieval instead of full-table reads.
• Preserve historical records in the target database even when records are deleted from the source system.
• Minimize performance impact on the source environment.

Technical Challenge

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:

• INSERT and UPDATE operations must be replicated.
• DELETE operations occurring on Server X must not be reflected on Server Y.
• Records removed from the source system must remain available in the target environment for historical and reporting purposes.

Solution Design

After evaluating multiple approaches, database triggers were ruled out due to concerns around:

• Performance overhead
• Increased transaction latency
• Maintenance complexity
• Potential impact on source system workloads

Instead, SQL Server Transactional Replication was selected as the foundation of the solution.

Initial Replication Configuration:

The following implementation steps were performed:

Step 1: Configure Transactional Replication

Transactional Replication was established between:

• Publisher: Server X
• Subscriber: Server Y

A snapshot was generated and applied to initialize the subscriber database.

Step 2: Add Audit Columns

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.

Step 3: Prevent DELETE Replication

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:

• INSERT operations continued to replicate.
• UPDATE operations continued to replicate.
• DELETE operations were effectively suppressed.

This allowed Server Y to retain historical records indefinitely.

Snapshot Reinitialization Challenge

A secondary challenge emerged whenever a Snapshot Agent execution was required.

During snapshot reinitialization:

• Subscriber tables were dropped and recreated.
• Existing data on Server Y was removed.
• All records were reinserted as new rows.

Consequently, all records received new crdate and altdate values, causing downstream applications to incorrectly interpret the entire dataset as newly inserted data.

Snapshot Preservation Strategy

To preserve historical timestamps during snapshot refreshes, an additional process was developed.

Step 1: Backup Subscriber Database

A full backup of the subscriber database was taken before snapshot execution.

Step 2: Restore Backup to Temporary Database

The backup was restored to a temporary database (for example, D1_Backup) on Server Y.

Step 3: Execute Snapshot Job

The replication snapshot was generated and applied normally.

Step 4: Recreate Audit Columns

After snapshot completion, the crdate and altdate columns were recreated with default datetime values.

Step 5: Reconcile Historical Data and Timestamps

A custom reconciliation process was implemented after snapshot initialization.

The process compared data between:

• Current subscriber database
• Backup database restored before snapshot execution

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:

• Restored historical crdate and altdate values for records that existed before the snapshot.
• Identified records that were missing after snapshot initialization.
• Reinserted the missing records from the backup database into the current subscriber database.
• Allowed genuinely new records introduced by the snapshot to retain their current datetime values.

Logic Applied

• Existing records retained their historical crdate and altdate values from the backup database.
• Records identified as missing through the TABLEDIFF comparison were reinserted from the backup database.
• Newly introduced records retained the current datetime values assigned during snapshot initialization.
• Historical data integrity was preserved across snapshot refresh operations.

This ensured that downstream applications continued to receive only genuine incremental changes.

Results

The implemented solution successfully achieved all business objectives:

• Reliable near real-time replication using SQL Server Transactional Replication.
• Incremental data consumption through crdate and altdate tracking.
• Historical data retention despite source-side deletions.
• Elimination of trigger-based overhead on the source system.
• Preservation of change history during snapshot reinitialization events.
• Stable production operation over an extended period without reported issues.

Conclusion

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.