PAYATHON 2026

CRM 如何整合三个 mssql database 的实时数据

支付老李

结论

不建议通过 trigger 将三个品牌的数据同步到 CRM。具体方案要看业务对“实时”的要求：

  • 三个数据库位于同一个 SQL Server 实例，且数据量不大时，可以通过跨数据库视图和 UNION ALL 直接读取，实现成本最低。
  • 数据库位于不同 SQL Server 实例时，可以使用 Linked Server，但它更适合低频查询，不适合承载高并发的 CRM 页面请求。
  • CRM 如果需要稳定、高性能地查询统一数据，建议建立 CRM 集成库，通过 SQL Server CDC 或 Change Tracking 增量同步，再由定时任务或消息管道写入统一模型。与 trigger 相比，这种生产方案通常更容易维护。

还需要先明确“实时”的含义。如果业务允许几秒到几分钟的延迟，CDC 增量同步通常更稳妥。如果必须在事务提交后立即读取结果，就需要直接查询源数据库，同时接受源库故障和性能波动对 CRM 的影响。

统一数据时先解决主键冲突

三个数据库中的 Customer.id = 1 很可能对应三个不同的客户。因此，统一数据时不能只保存原始 ID，还必须记录数据来源。

统一表可以这样设计：

CREATE TABLE dbo.Customer
(
    id              bigint IDENTITY PRIMARY KEY,
    source_brand    varchar(20) NOT NULL,
    source_id       bigint NOT NULL,
    customer_name   nvarchar(200) NOT NULL,
    source_updated_at datetime2 NULL,
    synced_at       datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),

    CONSTRAINT UQ_Customer_Source
        UNIQUE (source_brand, source_id)
);

CREATE TABLE dbo.Invoice
(
    id              bigint IDENTITY PRIMARY KEY,
    customer_id     bigint NOT NULL,
    source_brand    varchar(20) NOT NULL,
    source_id       bigint NOT NULL,
    source_updated_at datetime2 NULL,
    synced_at       datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),

    CONSTRAINT FK_Invoice_Customer
        FOREIGN KEY (customer_id) REFERENCES dbo.Customer(id),

    CONSTRAINT UQ_Invoice_Source
        UNIQUE (source_brand, source_id)
);

CREATE TABLE dbo.Payment
(
    id              bigint IDENTITY PRIMARY KEY,
    invoice_id      bigint NOT NULL,
    source_brand    varchar(20) NOT NULL,
    source_id       bigint NOT NULL,
    amount          decimal(18, 2) NOT NULL,
    payment_date    datetime2 NULL,
    source_updated_at datetime2 NULL,
    synced_at       datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),

    CONSTRAINT FK_Payment_Invoice
        FOREIGN KEY (invoice_id) REFERENCES dbo.Invoice(id),

    CONSTRAINT UQ_Payment_Source
        UNIQUE (source_brand, source_id)
);

source_brand + source_id 唯一标识源记录，CRM 自己的 id 用于内部关联。这样可以避免主键冲突，也便于追查每条数据来自哪个数据库。

方案一：在同一实例中直接跨数据库查询

如果三个数据库都在同一个 SQL Server 实例，可以使用三段式对象名：

DatabaseName.SchemaName.TableName

先创建统一视图：

CREATE VIEW dbo.AllCustomers
AS
    SELECT
        'Brand1' AS source_brand,
        id AS source_customer_id,
        customer_name,
        updated_at
    FROM Brand1.dbo.Customer

    UNION ALL

    SELECT
        'Brand2',
        id,
        customer_name,
        updated_at
    FROM Brand2.dbo.Customer

    UNION ALL

    SELECT
        'Brand3',
        id,
        customer_name,
        updated_at
    FROM Brand3.dbo.Customer;

Invoice 和 Payment 可以用同样的方式处理：

CREATE VIEW dbo.AllInvoices
AS
    SELECT
        'Brand1' AS source_brand,
        id AS source_invoice_id,
        customer_id AS source_customer_id,
        updated_at
    FROM Brand1.dbo.Invoice

    UNION ALL

    SELECT 'Brand2', id, customer_id, updated_at
    FROM Brand2.dbo.Invoice

    UNION ALL

    SELECT 'Brand3', id, customer_id, updated_at
    FROM Brand3.dbo.Invoice;
CREATE VIEW dbo.AllPayments
AS
    SELECT
        'Brand1' AS source_brand,
        id AS source_payment_id,
        invoice_id AS source_invoice_id,
        amount,
        payment_date,
        updated_at
    FROM Brand1.dbo.Payment

    UNION ALL

    SELECT 'Brand2', id, invoice_id, amount, payment_date, updated_at
    FROM Brand2.dbo.Payment

    UNION ALL

    SELECT 'Brand3', id, invoice_id, amount, payment_date, updated_at
    FROM Brand3.dbo.Payment;

组合查询时，关联条件必须同时包含品牌和源 ID：

SELECT
    c.source_brand,
    c.source_customer_id,
    c.customer_name,
    i.source_invoice_id,
    p.source_payment_id,
    p.amount,
    p.payment_date
FROM dbo.AllCustomers AS c
INNER JOIN dbo.AllInvoices AS i
    ON i.source_brand = c.source_brand
   AND i.source_customer_id = c.source_customer_id
LEFT JOIN dbo.AllPayments AS p
    ON p.source_brand = i.source_brand
   AND p.source_invoice_id = i.source_invoice_id;

这里使用 LEFT JOIN，以便保留尚未付款的 invoice。原问题中的连接语法也应写成 INNER JOIN ... ON ...，不能把关联条件放在 WHERE 后面。

如果需要让 SQL Server 直接生成嵌套 JSON，可以使用 FOR JSON PATH：

SELECT
    c.source_brand,
    c.source_customer_id AS customer_id,
    c.customer_name,
    JSON_QUERY(
        (
            SELECT
                i.source_invoice_id AS invoice_id,
                JSON_QUERY(
                    (
                        SELECT
                            p.source_payment_id AS payment_id,
                            p.amount,
                            p.payment_date
                        FROM dbo.AllPayments AS p
                        WHERE p.source_brand = i.source_brand
                          AND p.source_invoice_id = i.source_invoice_id
                        ORDER BY p.payment_date
                        FOR JSON PATH
                    )
                ) AS payments
            FROM dbo.AllInvoices AS i
            WHERE i.source_brand = c.source_brand
              AND i.source_customer_id = c.source_customer_id
            FOR JSON PATH
        )
    ) AS invoices
FROM dbo.AllCustomers AS c
FOR JSON PATH, ROOT('customers');

这种方式直接读取源数据库中当前已提交的数据，不需要复制数据。不过，CRM 的查询性能和可用性也会直接依赖三个源数据库。

方案二：不同实例使用 Linked Server

如果三个数据库位于不同实例，可以配置 Linked Server，并使用四段式名称：

LinkedServer.DatabaseName.SchemaName.TableName

例如：

SELECT
    'Brand1' AS source_brand,
    c.id,
    c.customer_name
FROM Brand1Server.Brand1.dbo.Customer AS c

UNION ALL

SELECT
    'Brand2',
    c.id,
    c.customer_name
FROM Brand2Server.Brand2.dbo.Customer AS c;

Linked Server 部署比较简单，但使用时要注意以下问题：

  • 网络延迟会直接影响页面响应时间。
  • 任意一个源服务器不可用，都可能导致整个查询失败或超时。
  • 跨服务器 join 可能传输大量数据。
  • 权限、加密连接和凭据映射需要分别管理。
  • 查询优化器未必能将过滤条件高效地下推到远程服务器。

如果必须使用 Linked Server，应尽量先在远程端完成过滤和聚合。也可以通过 OPENQUERY 执行明确的远程查询：

SELECT *
FROM OPENQUERY(
    Brand1Server,
    'SELECT id, customer_name, updated_at
     FROM Brand1.dbo.Customer
     WHERE updated_at >= ''2026-09-01T00:00:00'''
);

不建议让每个 CRM 页面请求都临时连接三个远程实例，再执行复杂的 join。

推荐的生产方案：使用 CDC 增量同步到 CRM 集成库

对于需要统一查询、报表和高并发访问的 CRM，可以采用以下数据流：

Brand1 ─┐
Brand2 ─┼─ CDC / Change Tracking ─ 增量同步任务 ─ CRM 集成库 ─ CRM API
Brand3 ─┘

SQL Server CDC 可以记录表中的 insert、update 和 delete。同步程序定期读取新增变更，再对 CRM 表执行幂等 upsert。这样无需在业务表上维护同步 trigger，也不会让源系统的业务事务直接承担远程写入。

能否启用 CDC，取决于实际使用的 SQL Server 版本、部署方式、权限和运维策略，需要结合具体环境确认。

同步顺序通常如下：

  1. 同步 Customer。
  2. 同步 Invoice，通过 source_brand + source_customer_id 查找 CRM 中的 customer_id。
  3. 同步 Payment，通过 source_brand + source_invoice_id 查找 CRM 中的 invoice_id。
  4. 处理源表中的删除记录或作废记录。
  5. 保存每个数据库、每张表的同步检查点。
  6. 整个批次成功后再推进检查点。

向统一表写入数据时，可以使用显式的 UPDATE 和 INSERT：

BEGIN TRANSACTION;

UPDATE target
SET
    target.customer_name = source.customer_name,
    target.source_updated_at = source.updated_at,
    target.synced_at = SYSUTCDATETIME()
FROM dbo.Customer AS target
INNER JOIN #CustomerChanges AS source
    ON source.source_brand = target.source_brand
   AND source.source_id = target.source_id;

INSERT INTO dbo.Customer
(
    source_brand,
    source_id,
    customer_name,
    source_updated_at
)
SELECT
    source.source_brand,
    source.source_id,
    source.customer_name,
    source.updated_at
FROM #CustomerChanges AS source
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Customer AS target
    WHERE target.source_brand = source.source_brand
      AND target.source_id = source.source_id
);

COMMIT TRANSACTION;

同步任务必须支持重复执行。否则，任务在完成写入后、保存检查点前发生故障，重新运行时就可能产生重复记录。

如果环境不适合使用 CDC，还可以考虑：

  • SQL Server Change Tracking：它只通知同步程序哪些主键发生了变化，程序随后回源读取当前记录。
  • Transactional Replication：适合数据库级复制，但需要提前评估目标表结构、冲突处理方式和运维要求。
  • SSIS、Azure Data Factory 或自建 Worker：用于调度、转换、重试和监控。
  • 应用事件或 Outbox Pattern：如果可以修改源系统，这种方式的业务语义最清楚，但改造成本通常高于 CDC。

如何定义“最新 Payment”

“查询三个数据库”本身不能保证取到业务意义上的最新记录。必须先明确哪个字段代表最新状态，例如：

  • updated_at
  • modified_at
  • rowversion
  • CDC 的变更位置
  • 支付系统提供的事件序号

不要只依赖 MAX(payment_id)。自增 ID 通常只能表示插入顺序，无法可靠反映某笔支付最后一次修改的时间。

如果要查询每张 invoice 最后更新的一笔 payment，可以使用窗口函数：

WITH RankedPayments AS
(
    SELECT
        p.*,
        ROW_NUMBER() OVER
        (
            PARTITION BY p.source_brand, p.source_invoice_id
            ORDER BY p.updated_at DESC, p.source_payment_id DESC
        ) AS rn
    FROM dbo.AllPayments AS p
)
SELECT *
FROM RankedPayments
WHERE rn = 1;

这段查询要求源系统可靠地维护 updated_at。如果多次修改可能使用相同的时间戳，还要增加一个稳定的第二排序字段。

实施时需要注意的问题

即使三个数据库目前使用相同的表名，其字段类型、字符集规则和业务含义也可能不同。整合前应逐项确认：

  • 同名字段是否具有相同的业务语义。
  • 金额的币种和精度是否一致。
  • 时间字段保存的是本地时间还是 UTC。
  • Customer 是否可能跨品牌重复，以及应该合并还是保持独立。
  • Invoice 和 Payment 的删除采用物理删除还是状态变更。
  • 不同数据库的 collation 是否一致。
  • CRM 是否可以显示同步延迟，以及业务能接受的最大延迟。
  • 源系统发生故障时，CRM 应返回旧数据、部分数据，还是直接报错。

如果三个数据库位于同一实例，且查询量有限，可以先用跨数据库视图验证数据模型，实施起来最简单。如果 CRM 需要长期运行并承载高并发查询，则更适合通过 CDC 或 Change Tracking 建立增量同步，将统一数据保存在 CRM 集成库中。这样可以减少 trigger 带来的维护负担，也能更清楚地处理重试、监控和故障隔离。

备注：内容仅供参考。