Airbyte配置SQL Server源

为 MSSQL 设置 CDC

1. 在数据库和表上启用 CDC

MS SQL Server 提供了一些内置的存储过程来启用 CDC。

• 若要启用 CDC，首先需要具有必要权限（db_owner 或 sysadmin）的 SQL Server 管理员运行查询以在数据库级别启用 CDC。

  USE {database name}
  GO
  EXEC sys.sp_cdc_enable_db
  GO

• 然后，管理员必须为要捕获的每个表启用 CDC。下面是一个示例：

  USE {database name}
  GO
  
  EXEC sys.sp_cdc_enable_table
  @source_schema = N'{schema name}',
  @source_name   = N'{table name}',
  @role_name     = N'{role name}',  [1]
  @filegroup_name = N'{fiilegroup name}', [2]
  @supports_net_changes = 0 [3]
  GO
  • [1] 指定一个角色，该角色将获得对源表的捕获列的SELECT权限。建议在此处放置一个值，以便可以在下一步中使用此角色，但也可以将 @role name 的值设置为 NULL，仅允许 _sysadmin 和 db_owner 具有访问权限。确保用于连接到 Airbyte 中的源的凭据与此角色一致，以便 Airbyte 可以访问 cdc 表。
  • [2] 指定 SQL Server 放置更改表的文件组。建议为 CDC 创建单独的文件组，但可以省略此参数以使用默认文件组。
  • [3] 如果为 0，则仅生成用于查询所有更改的支持函数。如果为 1，则还会生成查询净更改所需的函数。如果 supports_net_changes 设置为 1，则必须指定 index_name，或者源表必须具有定义的主键。

• (有关参数的更多详细信息，请参阅此存储过程的 Microsoft 文档页。

• 如果有许多表要启用 CDC，并且希望避免必须为每个表逐个运行此查询，则此脚本可能会有所帮助！

有关详细信息，请参阅有关启用和禁用 CDC 的 Microsoft 文档。

2. 启用快照隔离

• 首次使用 CDC 运行同步时，Airbyte 会执行数据库的初始一致快照。为了避免获取表锁，Airbyte 使用快照隔离，允许其他数据库客户端同时写入。必须在数据库上启用此功能，如下所示：

  ALTER DATABASE {database name}
    SET ALLOW_SNAPSHOT_ISOLATION ON;

3. 创建用户并授予适当的权限

• 我们建议不要使用 sysadmin 或 db_owner 凭据，而是创建一个具有相关 CDC 访问权限的新用户，以便与 Airbyte 一起使用。首先，让我们创建登录名和用户，并添加到db_datareader角色：

  USE {database name};
  CREATE LOGIN {user name}
    WITH PASSWORD = '{password}';
  CREATE USER {user name} FOR LOGIN {user name};
  EXEC sp_addrolemember 'db_datareader', '{user name}';

  • 将用户添加到之前在表上启用 cdc 时指定的角色：

    EXEC sp_addrolemember '{role name}', '{user name}';

  • 这应该足够了，但如果遇到问题，请尝试直接授予用户对 cdc 架构的SELECT访问权限：

    USE {database name};
    GRANT SELECT ON SCHEMA :: [cdc] TO {user name};

  • 如果可行，授予此用户“VIEW SERVER STATE”权限将允许 Airbyte 检查 SQL Server 代理是否正在运行。这是首选，因为它可确保在源数据库中的代理未更新 CDC 表时同步将失败。

    USE master;
    GRANT VIEW SERVER STATE TO {user name};

4. 延长CDC数据的保留期

• 在 SQL Server 中，默认情况下，更改表中仅保留三天的数据。除非您运行非常频繁的同步，否则我们建议增加此保留期，以便在同步失败或同步暂停时，仍有一些带宽可以从增量同步的最后一个点开始。

• 可以使用存储过程sys.sp_cdc_change_job更改这些设置，如下所示：

  -- we recommend 14400 minutes (10 days) as retention period
  EXEC sp_cdc_change_job @job_type='cleanup', @retention = {minutes}

• 进行此更改后，需要重新启动清理作业：

  EXEC sys.sp_cdc_stop_job @job_type = 'cleanup';

  EXEC sys.sp_cdc_start_job @job_type = 'cleanup';

5. 确保 SQL Server 代理正在运行

• MSSQL使用SQL Server代理

  运行所需的作业

  CDC的。因此，为了让CDC有效工作，代理必须正常运行。您可以检查

  SQL Server 代理的状态如下所示：

EXEC xp_servicecontrol 'QueryState', N'SQLServerAGENT';

• 如果您看到“正在运行”以外的内容，请关注

  Microsoft 文档

  以启动服务。