版本：2.x

SQL Server

SQL Server 数据库是 Microsoft 开发设计的一个关系数据库智能管理系统（RDBMS）。在创建 SQL Server 连接前，您需要在跟随本文完成前置准备工作，完成操作后即可创建连接并在数据复制/开发任务中使用该数据源。

支持版本

SQL Server 2005、2008、2008 R2、2012、2014、2016、2017

作为源库

提示

由于 CDC 支持从 SQLServer 2008 开始支持，对于较早的版本，您需要使用 Custom SQL 功能来模拟更改数据捕获，在从旧版本复制数据时，需要考虑以下几点：

源表必须有一个更改跟踪列，比如 LAST_UPDATED_TIME，它在每次插入或更新记录时都会更新。
创建数据同步任务时，任务的同步类型选择为全量，将重复运行自定义 SQL设置为 True，同时在映射设计上提供适当的自定义 SQL。

以管理员（例如 sa）身份，登录到 SQL Server Management Studio 或 sqlcmd。
依次执行下述格式的命令，创建用于数据复制/转换任务的用户。
```
-- 创建登录账户
CREATE LOGIN login_name WITH PASSWORD='passwd', default_database=database_name;

-- 创建数据库操作用户
CREATE USER login_name FOR LOGIN login_name with default_schema=schema_name;
```
- login_name：登录名，即用户名。
- passwd：用户密码。
- database_name：与登录关联的默认数据库，即要登录的数据库名。
- schema_name：数据库架构名称（例如 dbo），它充当对象（例如表、视图、过程和函数）的命名空间或容器。相关资料，见创建数据库架构。
下述示例表示创建一个名为 tapdata 的用户，指定登录的数据库为 demodata，架构为 dbo：
```
-- 创建登录账户
CREATE LOGIN tapdata WITH password='Tap@123456', default_database=demodata;

-- 创建数据库操作用户
CREATE USER tapdata FOR LOGIN tapdata with default_schema=dbo;
```

为刚创建的账号授予权限，您也可以基于业务需求自定义权限控制。

仅读取全量数据
读取全量+增量数据

-- 授予读取指定架构下所有表的权限
GRANT SELECT ON SCHEMA::schema_name TO login_name;

-- 授予读取指定架构下所有表的权限
GRANT SELECT ON SCHEMA::schema_name TO login_name;

-- 授予读取变更数据捕获的权限，其 Schema 固定为 cdc
GRANT SELECT ON SCHEMA::cdc TO login_name;

login_name：登录名，即用户名。
schema_name：数据库架构名称（例如 dbo），它充当对象（例如表、视图、过程和函数）的命名空间或容器。

下述示例表示授予 tapdata 用户，拥有 dbo 架构和 cdc 架构下所有表的读取权限。

GRANT SELECT ON SCHEMA::dbo TO tapdata;
GRANT SELECT ON SCHEMA::cdc TO tapdata;

如果您需要获取源库的数据变更以实现增量同步，您还需要跟随下述步骤完成数据库设置。
1. 启用 SQL Server 代理服务。
2. 确定数据库的日志文件大小限制，可通过 sys.master_files 官方文档进行查询。
  
  如果日志文件大小设置过小，可能导致日志无法继续增长，进而影响 CDC 功能的正常运行。
3. 选择执行下述命令，启用变更数据捕获能力。
  - 启用数据库级别的 CDC，在执行命令时CREATE LOGIN ，您需要替换 database_name 为真实的数据库名。
    -- 启用变更数据捕获能力 USE database_name GO EXEC sys.sp_cdc_enable_db GO -- 查看是否启用变更数据捕获，is_cdc_enabled 值为 1 即表示已启用该功能 SELECT [name], database_id, is_cdc_enabled FROM sys.databases WHERE [name] = N'database_name' GO
  - 启用表级别的 CDC。
    USE database_name -- 有主键表 GO EXEC sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @capture_instance = NULL, @role_name = N'role_name', @supports_net_changes = 1 GO -- 无主键表 GO EXEC sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @capture_instance = NULL, @role_name = N'role_name', @supports_net_changes = 0 GO
    - database_name：数据库名称。
    - schema_name：架构名称，例如 dbo。
    - table_name：数据表的名称。
    - role_name：可以访问更改数据的角色，如不希望使用设置角色，可将其设置为 NULL，如果在启用增量复制时指定了角色，则需确保数据库用户具有适当的角色，以便 TapData 可以访问增量复制表。
    - capture_instance：默认值为 NULL，由系统自动生成。如果存在残留的 CDC 资源，可能会因 capture_instance 冲突导致无法启动表的 CDC。此时可以通过指定一个新的 capture_instance 来启用。

作为目标库

以管理员（例如 sa）身份，登录到 SQL Server Management Studio 或 sqlcmd。
依次执行下述格式的命令，创建用于数据复制/转换任务的用户。
```
-- 创建登录账户
CREATE LOGIN login_name WITH PASSWORD='passwd', default_database=database_name;

-- 创建数据库操作用户
CREATE USER login_name FOR LOGIN login_name with default_schema=schema_name;
```
- login_name：登录名，即用户名。
- passwd：用户密码。
- database_name：与登录关联的默认数据库，即要登录的数据库名。
- schema_name：数据库架构名称（例如 dbo），它充当对象（例如表、视图、过程和函数）的命名空间或容器。相关资料，见创建数据库架构。
下述示例表示创建一个名为 tapdata 的用户，指定登录的数据库为 demodata，架构为 dbo：
```
-- 创建登录账户
CREATE LOGIN tapdata WITH password='Tap@123456', default_database=demodata;

-- 创建数据库操作用户
CREATE USER tapdata FOR LOGIN tapdata with default_schema=dbo;
```
为刚创建的账号授予权限，您也可以基于业务需求自定义权限控制。
```
-- 授予建表权限
GRANT CREATE TABLE TO login_name;

-- 授予增删改查所有表的权限
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON SCHEMA::schema_name TO login_name;
```
- login_name：登录名，即用户名。
- schema_name：数据库架构名称（例如 dbo），它充当对象（例如表、视图、过程和函数）的命名空间或容器。
下述示例表示授予 tapdata 用户，在 dbo 架构建表并对所有表执行增删改查的权限：
```
GRANT CREATE TABLE TO tapdata
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON SCHEMA::dbo TO tapdata;
```

下一步

连接 SQL Server 数据库

扩展阅读

如遇到本文未覆盖到的问题，请参考微软官方文档。

清理 CDC 日志

SQL Server 不会自动清理增量数据日志，需要进行如下设置开启清理任务。

--retention 的单位为分钟，本处设定清理周期为2天
USE AdventureWorks2012;  
GO  
EXECUTE sys.sp_cdc_change_job   
    @job_type = N'cleanup',  
    @retention = 2880;  
GO 

开启全库 CDC

-- 全局替换 将 TAPDATA 替换为实际的数据库名
-- 全局替换 将 INSURANCE 替换为实际的 schema 名称
USE TAPDATA
GO
EXEC sys.sp_cdc_enable_db
GO

declare @table_name varchar(100)
declare @database_name varchar(100)
declare @schema_name varchar(100)

set @database_name = 'TAPDATA'
set @schema_name = 'INSURANCE'

declare my_cursor cursor for SELECT TABLE_NAME
                             FROM TAPDATA.INFORMATION_SCHEMA.TABLES
                             where TABLE_CATALOG = @database_name
                               and TABLE_SCHEMA = @schema_name;
open my_cursor
fetch next from my_cursor into @table_name
while @@FETCH_STATUS = 0
    begin
        begin try
            exec sys.sp_cdc_enable_table
                 @source_schema = @schema_name,
                 @source_name = @table_name,
                 @role_name = NULL
        end try
        begin catch
            print('[ERROR] ' + @table_name)
        end catch

        fetch next from my_cursor into @table_name
    end
close my_cursor
deallocate my_cursor

关闭全局 CDC

-- 全局替换 将 TAPDATA 替换为实际的数据库名
-- 全局替换 将 INSURANCE 替换为实际的 schema 名称
USE TAPDATA
GO

declare @table_name varchar(100)
declare @database_name varchar(100)
declare @schema_name varchar(100)

set @database_name = 'TAPDATA'
set @schema_name = 'INSURANCE'

declare my_cursor cursor for SELECT TABLE_NAME
                             FROM TAPDATA.INFORMATION_SCHEMA.TABLES
                             where TABLE_CATALOG = @database_name
                               and TABLE_SCHEMA = @schema_name;
open my_cursor
fetch next from my_cursor into @table_name
while @@FETCH_STATUS = 0
    begin
        begin try
            EXEC sys.sp_cdc_disable_table
                 @source_schema = @schema_name,
                 @source_name = @table_name,
                 @capture_instance = 'all';
        end try
        begin catch
            print ('[ERROR] ' + @table_name)
        end catch

        fetch next from my_cursor into @table_name
    end
close my_cursor
deallocate my_cursor

EXEC sys.sp_cdc_disable_db
GO

支持版本​

作为源库​

作为目标库​

下一步​

扩展阅读​

支持版本

作为源库

作为目标库

下一步

扩展阅读