SQL Server单向数据同步需求：A至只读库B定时同步及软删除处理

Alright, let's break down how to solve this sync scenario properly. You need one-way data sync from database A to read-only database B every 10 minutes, covering 70-80% of your tables. Key requirements include triggering soft deletes in B when records are removed from A, plus handling data transformations along the way. Below are three practical solutions, sorted by reliability and scalability:

方案一：变更数据捕获（CDC）+ SQL Agent作业（企业级推荐） #

CDC is SQL Server's native change tracking feature—it’s designed exactly for scenarios where you need to track and sync incremental changes without reinventing the wheel.

1. 开启数据库A的CDC #

First, enable CDC at the database level, then turn it on for each table you want to sync:

USE [A];
GO
-- Enable CDC on the database
EXEC sys.sp_cdc_enable_db;
GO

-- Example: Enable CDC for dbo.Table1 (repeat for all target tables)
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Table1',
    @role_name = NULL, -- Adjust if you need restricted access to change data
    @supports_net_changes = 1; -- Captures "net" changes (e.g., skip multiple updates to the same row)
GO

2. 构建SQL Agent作业的同步逻辑 #

Create a SQL Agent job with steps that handle the sync, including temporary read-only bypass, data sync, soft deletes, and cleanup. Here’s the core logic:

BEGIN TRY
    -- Step 1: Temporarily make DB B writable (skip if B's read-only is app-level only)
    USE master;
    ALTER DATABASE [B] SET READ_WRITE WITH ROLLBACK IMMEDIATE;
    GO

    -- Step 2: Sync inserts/updates with data transformation
    USE [B];
    GO
    MERGE INTO dbo.Table1 AS target
    USING (
        -- Pull net changes from A's CDC logs
        SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Table1(
            (SELECT LastSyncLSN FROM A.dbo.SyncLog WHERE TableName = 'dbo.Table1'),
            sys.fn_cdc_get_max_lsn(),
            'all'
        )
    ) AS source
    ON target.Id = source.Id
    WHEN MATCHED THEN
        UPDATE SET 
            target.Column1 = source.Column1,
            -- Example transformation: Convert int status to human-readable text
            target.StatusDesc = CASE source.Status 
                                WHEN 1 THEN 'Active' 
                                WHEN 0 THEN 'Inactive' 
                                ELSE 'Unknown' 
                                END,
            target.LastUpdated = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (Id, Column1, StatusDesc, DeletedDateTime, LastUpdated)
        VALUES (source.Id, source.Column1, 
                CASE source.Status WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' ELSE 'Unknown' END,
                NULL, GETDATE());
    GO

    -- Step 3: Handle soft deletes (map A's deletes to B's DeletedDateTime update)
    UPDATE target
    SET target.DeletedDateTime = GETDATE()
    FROM dbo.Table1 AS target
    INNER JOIN (
        SELECT Id FROM cdc.fn_cdc_get_all_changes_dbo_Table1(
            (SELECT LastSyncLSN FROM A.dbo.SyncLog WHERE TableName = 'dbo.Table1'),
            sys.fn_cdc_get_max_lsn(),
            'all update old'
        )
        WHERE __$operation = 3 -- CDC code for delete operations
    ) AS source ON target.Id = source.Id
    WHERE target.DeletedDateTime IS NULL;
    GO

    -- Step 4: Update sync log to avoid reprocessing changes
    USE [A];
    GO
    MERGE INTO dbo.SyncLog AS target
    USING (SELECT 'dbo.Table1' AS TableName, sys.fn_cdc_get_max_lsn() AS LastSyncLSN) AS source
    ON target.TableName = source.TableName
    WHEN MATCHED THEN UPDATE SET LastSyncLSN = source.LastSyncLSN, SyncTime = GETDATE()
    WHEN NOT MATCHED THEN INSERT (TableName, LastSyncLSN, SyncTime) VALUES (source.TableName, source.LastSyncLSN, GETDATE());
    GO

    -- Step 5: Restore DB B to read-only
    USE master;
    ALTER DATABASE [B] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
    GO
END TRY
BEGIN CATCH
    -- Ensure B is restored to read-only even if sync fails
    USE master;
    ALTER DATABASE [B] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
    -- Throw error to alert SQL Agent of failure
    THROW;
END CATCH

3. 配置作业调度 #

Set the SQL Agent job to run every 10 minutes. Make sure the job account has sufficient permissions (db_owner or sysadmin on both databases).

方案二：自定义定时脚本（小型场景快速实现） #

If CDC feels overkill, you can build a custom sync using timestamps and SQL Agent. This works well for smaller datasets.

1. 为A库的表添加变更追踪字段 #

First, add a LastUpdated column to tables you want to sync, plus triggers to update it on changes:

USE [A];
GO
-- Add LastUpdated column to Table1 (repeat for target tables)
ALTER TABLE dbo.Table1 ADD LastUpdated DATETIME DEFAULT GETDATE();
GO

-- Trigger to update LastUpdated on insert/update
CREATE TRIGGER trg_Table1_UpdateTimestamp
ON dbo.Table1
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE t SET t.LastUpdated = GETDATE()
    FROM dbo.Table1 t
    INNER JOIN inserted i ON t.Id = i.Id;
END
GO

-- Create a delete log table to track records removed from A
CREATE TABLE dbo.DeletedRecords (
    RecordId INT,
    TableName VARCHAR(100),
    DeletedAt DATETIME DEFAULT GETDATE()
);
GO

-- Trigger to log deletes from Table1
CREATE TRIGGER trg_Table1_LogDelete
ON dbo.Table1
AFTER DELETE
AS
BEGIN
    INSERT INTO dbo.DeletedRecords (RecordId, TableName)
    SELECT Id, 'Table1' FROM deleted;
END
GO

2. 构建同步作业逻辑 #

Similar to the CDC approach, but uses timestamps and the delete log instead:

BEGIN TRY
    -- Make B writable
    USE master;
    ALTER DATABASE [B] SET READ_WRITE WITH ROLLBACK IMMEDIATE;
    GO

    -- Sync recent inserts/updates with transformations
    USE [B];
    GO
    MERGE INTO dbo.Table1 AS target
    USING (
        SELECT 
            Id, Column1,
            CASE Status WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' ELSE 'Unknown' END AS StatusDesc
        FROM A.dbo.Table1
        WHERE LastUpdated >= DATEADD(MINUTE, -10, GETDATE())
    ) AS source
    ON target.Id = source.Id
    WHEN MATCHED THEN
        UPDATE SET 
            target.Column1 = source.Column1,
            target.StatusDesc = source.StatusDesc,
            target.LastUpdated = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (Id, Column1, StatusDesc, DeletedDateTime, LastUpdated)
        VALUES (source.Id, source.Column1, source.StatusDesc, NULL, GETDATE());
    GO

    -- Handle soft deletes
    UPDATE target
    SET target.DeletedDateTime = GETDATE()
    FROM dbo.Table1 AS target
    INNER JOIN A.dbo.DeletedRecords AS source 
        ON target.Id = source.RecordId AND source.TableName = 'Table1'
    WHERE target.DeletedDateTime IS NULL;
    GO

    -- Clean up processed delete logs
    DELETE FROM A.dbo.DeletedRecords WHERE TableName = 'Table1' AND DeletedAt <= GETDATE();
    GO

    -- Restore B to read-only
    USE master;
    ALTER DATABASE [B] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
    GO
END TRY
BEGIN CATCH
    USE master;
    ALTER DATABASE [B] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
    THROW;
END CATCH

3. 配置作业调度 #

Set the SQL Agent job to run every 10 minutes, same as above.

方案三：事务复制（低延迟自动同步） #

If you need near-real-time sync instead of strictly 10-minute intervals, transaction replication is a solid choice. It automatically syncs changes as they happen, but requires more setup:

• Configure publication on DB A: Select the tables you want to sync, and set up a publication.
• Configure subscription on DB B: Create a push subscription from A to B.
• Handle soft deletes: Replace the default delete logic with a custom stored procedure that updates DeletedDateTime in B instead of deleting records.
• Data transformations: Use transformable subscriptions (available in SQL Server Enterprise) or add triggers on B's tables to apply transformations during sync.

This is great for high-throughput environments where 10-minute intervals are too slow, but it’s more complex to maintain than CDC or custom scripts.

内容的提问来源于stack exchange，提问作者Mihai