Overview

To comply with client data retention and cleanup requirements, an automated solution was designed and implemented to securely archive, permanently remove, and restore customer data across multiple database tables within a Microsoft SQL Server environment.

The solution enables controlled data purging based on customer identifiers provided by the client and ensures that the deleted data can be fully restored whenever required.

Business Requirement

The client periodically requests the removal of customer-related information from the production database. These requests are provided in the form of CSV files containing one or more Customer IDs.

The primary objectives were:

• Archive all customer-related data before deletion.
• Perform hard deletion of customer data across multiple dependent tables.
• Maintain the ability to restore the deleted data on demand.
• Eliminate manual intervention and reduce operational risk through automation.
• Ensure data consistency and auditability throughout the process.

Technical Challenges

The customer information was distributed across multiple relational tables with complex dependencies. Manual data cleanup presented several challenges:

• Risk of accidental data loss.
• Possibility of orphaned records and referential integrity issues.
• Time-consuming manual execution.
• Difficulty in restoring deleted data when requested by the client.
• Need for a repeatable and auditable process.

Solution Architecture

An automated archival and cleanup framework was developed using SQL Server Integration Services (SSIS) and Microsoft SQL Server.

Step 1: Backup Table Creation

A one-time database script was developed to create backup tables corresponding to each production table involved in the cleanup process.

Key Features:

• Backup tables maintain the same schema structure as source tables.
• Designed to store complete customer-related records before deletion.
• Provides a secure recovery mechanism for future restoration requests.

Step 2: Customer ID File Processing

The process accepts a CSV file containing one or more Customer IDs supplied by the client.

Key Features:

• Automated ingestion of Customer IDs into a staging/session table.
• Validation of input data before processing.
• Supports bulk customer cleanup requests.

Step 3: Data Archival

Before any deletion occurs, all records associated with the specified Customer IDs are copied from the production tables to the corresponding backup tables.

Key Features:

• Preserves complete customer data.
• Maintains relationships between dependent tables.
• Ensures recovery readiness.

Step 4: Data Purge (Hard Delete)

After successful archival, customer data is permanently removed from the production environment.

Key Features:

• Automated deletion across multiple related tables.
• Dependency-aware execution sequence.
• Transaction-controlled processing to ensure data integrity.

Step 5: Data Restoration (Rollback)

Whenever the client requests recovery of previously deleted customer data, the archived records are restored from the backup tables back into the production tables.

Key Features:

• Complete data restoration capability.
• Preserves original relationships and dependencies.
• Minimal downtime and manual effort.

Automation Framework

The entire workflow was automated using SQL Server Integration Services (SSIS).

SSIS Responsibilities

• CSV file ingestion.
• Customer ID validation.
• Data archival processing.
• Data purge execution.
• Logging and error handling.
• Restoration workflow support.
• Audit trail generation.

Technology Stack

Database Platform - Microsoft SQL Server

ETL & Automation - SQL Server Integration Services (SSIS)

Input Source - CSV Files

Backup Repository - SQL Server Backup Tables

Recovery Mechanism - Automated Restore Scripts / SSIS Workflow

Benefits Achieved

Operational Efficiency

• Eliminated manual data cleanup activities.
• Reduced processing time significantly.

Data Protection

• Ensured no customer data is permanently lost.
• Enabled rapid restoration when required.

Auditability

• Complete tracking of archived and deleted records.
• Improved compliance and governance.

Scalability

• Supports single or bulk customer cleanup requests.
• Easily extendable to additional tables and business domains.

Reliability

• Reduced human error.
• Improved consistency and repeatability of operations.

Outcome

The implemented solution provided a fully automated customer data archival, purge, and recovery framework within the Microsoft SQL Server ecosystem. By leveraging SSIS and structured backup repositories, the organization achieved secure data management, faster execution, improved compliance, and a reliable rollback mechanism for future business requirements.

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.