SQLServer CDC（sqlserver Cdc锁）

25-03-14 8

如果您想了解SQLServerCDC和sqlserverCdc锁的知识，那么本篇文章将是您的不二之选。我们将深入剖析SQLServerCDC的各个方面，并为您解答sqlserverCdc锁的疑在这篇文

如果您想了解SQLServer CDC和sqlserver Cdc锁的知识，那么本篇文章将是您的不二之选。我们将深入剖析SQLServer CDC的各个方面，并为您解答sqlserver Cdc锁的疑在这篇文章中，我们将为您介绍SQLServer CDC的相关知识，同时也会详细的解释sqlserver Cdc锁的运用方法，并给出实际的案例分析，希望能帮助到您！

本文目录一览：

SQLServer CDC（sqlserver Cdc锁）
FlinkX SqlServer CDC实时采集原理与使用丨直播预告
ogg 12.3 for sqlserver 2016/2014 CDC模式配置
SQL SERVER 开启CDC 实操详细
SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type ...

SQLServer CDC（sqlserver Cdc锁）

要捕获某个表的变更，必须先开启数据库级别的捕获，再开启表级的捕获

1. 启用数据库：在当前数据库下执行命令

EXEC sys.sp_cdc_enable_db

这个过程会在当前数据库下创建6个系统表

cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
dbo.systranschemas

2. 查询哪些数据库启用了CDC功能

select * from sys.databases where is_cdc_enabled = 1

3. 启用表，例如表名为"dbo.t1"

EXEC sys.sp_cdc_enable_table
	@source_schema = 'dbo',@source_name = 't1',@role_name = null;

注：为第一个表启用CDC后，sqlServer生成两个Agent作业
cdc.dbname_capture
cdc.dbname_cleanup

4. 禁用表("dbo.t1")

EXEC sys.sp_cdc_disable_table
	@source_schema = 'dbo',@capture_instance = 'all';

5. 禁用数据库

EXEC sys.sp_cdc_disable_db;

参考: <<sql Server 2008 宝典>>

FlinkX SqlServer CDC实时采集原理与使用丨直播预告

3月23日晚19点，袋鼠云数栈技术研发团队开发工程师——土豆，将会为大家直播分享《FlinkX SqlServer CDC实时采集原理与使用》。

课程内容主要包括FlinkX SqlServer CDC实时采集原理和FlinkX SqlServer CDC到Hive实战，通过课程大家可以熟悉FlinkX SqlServer CDC实时采集功能的使用及基本原理，有助于进一步理解和使用FlinkX工具。

本文首发于：数栈研习社

我们的b站直播活动地址：袋鼠云

我们在github上的开源项目：flinkx

ogg 12.3 for sqlserver 2016/2014 CDC模式配置

本文主要讲述ogg 12.3 通过CDC抽取mssqlserver 2016 enterprise的过程，以sqlserver为目标端投递配置相对简单，所以在此不在赘述。

针对以前的mssqlserver，是直接解析日志的模式，所以配置略有不同。

配置步骤概述

1. 解压ogg 12.3 for sqlserver软件，执行create subdirs，编辑mgr并启动。

2. 数据库安装配置，并打补丁

3. create schema ogg

4. 创建测试表

5. 创建globals文件

6. 创建同步用户

7. 确保sqlagent能正常启动

8. 在源端DB上启用CDC

9. 创建ODBC

10. add trandata

11. 创建清理历史变更数据的job

12. 建立抽取进程

13. 测试

安装过程

OGG软件及sqlserver 2016安装步骤在此省略。

补丁下载安装

安装完成sqlserver 2016 ent版本之后，还需要打补丁：

https://support.microsoft.com/en-us/help/3166120/fix-could-not-find-stored-procedure-sys-sp-cdc-parse-captured-column-l

源库上创建OGG schema

用于存放配置表，其中cdc schema由启用CDC步骤的存储过程创建。

clip_image001

语法：

USE [sourcedb]

CREATE SCHEMA [ogg] AUTHORIZATION [dbo]

创建测试表

use sourcedb;

create table dbo.test1 (id int primary key, name varchar(50));

create table dbo.test2( id int, name varchar(50), age int);

use targetdb;

create table dbo.test1 (id int primary key, name varchar(50));

create table dbo.test2( id int, name varchar(50), age int);

创建Globals文件

在OGG目录下，新建globals文件，内容如下

ggschema ogg

创建OGG同步用户

创建访问DB的用户并赋权srcogg, tgtogg

clip_image002

clip_image004

目标端用户

clip_image006

启动sqlagent

agent用于启动CDC对应的job, 所以必须要启动agent才能正常同步。

clip_image007

源库上启用cdc

use sourcedb

EXECUTE sys.sp_cdc_enable_db

创建ODBC

如果需要远程捕获sqlserver，则只要配置通过odbc能远程访问DB即可实现。

clip_image011

clip_image013

OGG trandata

GGSCI>dblogin sourcedb srcdb, userid srcogg, password srcogg

GGSCI>add trandata dbo.test1

GGSCI>add trandata dbo.test2

执行完成之后，可以看到多了一张配置表

clip_image008

ogg.OracleGGTranTables由OGG 第一次add trandata自动创建。

创建OGG clean job

需要先删除DB自带的clean job

EXECUTE sys.sp_cdc_drop_job ''cleanup''

然后在OGG安装目录下，进入命令行，执行如下语句，其中(local)是默认的sqlserver实例

ogg_cdc_cleanup_setup.bat createjob srcogg srcogg sourcedb (local) ogg

其中： srcogg为用户名及密码，source为源DB的名称，(local)为sqlserver实例, ogg为对应的schema。

此时，会再增加两张配置表:

and add a new job

clip_image010

配置抽取进程

GGSCI (DESKTOP-V8IQDQP) 6> view param exsrc

extract exsrc

sourcedb srcdb, userid srcogg, password srcogg

exttrail ./dirdat/aa

table dbo.*;

add extract exsrc, tranlog, begin now

add exttrail ./dirdat/aa, extract exsrc

测试

insert into dbo.test1 values(1, ''bc是中kos'');

clip_image015

clip_image016

可以看到正常捕获数据。

SQL SERVER 开启CDC 实操详细

1. 环境检查

1.1 版本检查

SELECT @@VERSION;

Microsoft SQL Server 2016 (SP2-GDR)

1.2 检查CDC服务开启状态

select is_cdc_enabled from sys.databases where name=''dbname'';
--0为关闭，1为开启。数据库名为dbname

2. 开启CDC

2.1 开启SQL server agent服务

sp_configure ''show advanced options'', 1;
GO -- 2.1.1
RECONFIGURE;
GO -- 2.1.2
sp_configure ''Agent XPs'', 1;
GO -- 2.1.3
RECONFIGURE
GO -- 2.1.4

2.2 开启数据库级别的CDC功能

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [sa];
-- 2.2.1 变更为sa的权限，数据库名为dbname
if exists(select 1 from sys.databases where name=''dbname'' and is_cdc_enabled=0)
begin
    exec sys.sp_cdc_enable_db
end
;
-- 2.2.2 开启语句
select is_cdc_enabled from sys.databases where name=''dbname'';
-- 2.2.3 检查是否开启成功，为1则开启
/* -- 本段注释可不看
或者
USE ERP
GO  
-- 开启：
EXEC sys.sp_cdc_enable_db  
-- 关闭：
EXEC sys.sp_cdc_disable_db
GO  
 
注释： 如果在禁用变更数据捕获时为数据库定义了很多捕获实例，则长时间运行事务可能导致 sys.sp_cdc_disable_db 的执行失败。
通过在运行 sys.sp_cdc_disable_db 之前使用 sys.sp_cdc_disable_table 禁用单个捕获实例，可以避免此问题。
 
示例：
 
USE AdventureWorks2012; 
GO 
EXECUTE sys.sp_cdc_disable_table 
@source_schema = N''HumanResources'', 
@source_name = N''Employee'', 
@capture_instance = N''HumanResources_Employee'';
*/

2.3 添加CDC专用的文件组和文件

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(''dbname'');
-- 2.3.1 查询dbname库的物理文件
ALTER DATABASE dbname ADD FILEGROUP CDC1;
-- 2.3.2 为该库添加名为CDC1的文件组
ALTER DATABASE dbname
ADD FILE
(
  NAME= ''dbname_CDC1'',
  FILENAME = ''D:\DATA\dbname_CDC1.ndf''
)
TO FILEGROUP CDC1;
-- 2.3.3 将新增文件，并映射到文件组。重复2.3.1查询操作

2.4 开启表级别CDC

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 0;
-- 2.4.1 查询未开启的表
IF EXISTS(SELECT 1 FROM sys.tables WHERE name=''AccountBase'' AND is_tracked_by_cdc = 0)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = ''dbo'', -- source_schema
        @source_name = ''AccountBase'', -- table_name
        @capture_instance = NULL, -- capture_instance
        @supports_net_changes = 1, -- supports_net_changes
        @role_name = NULL, -- role_name
        @index_name = NULL, -- index_name
        @captured_column_list = NULL, -- captured_column_list
        @filegroup_name = ''CDC1'' -- filegroup_name
END;
-- 2.4.2 为dbname.dbo.AccountBase开启表级别CDC，文件组为CDC1
DECLARE @tableName nvarchar(36)  -- 声明变量
DECLARE My_Cursor CURSOR --定义游标
    FOR (SELECT ''new_srv_workorderBase'' name
union select ''tablename1''
union select ''tablename2''
union select ''tablename3''
 ) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_cdc_enable_table
         @source_schema = ''dbo'', -- source_schema
         @source_name = @tableName, -- table_name
         @capture_instance = NULL, -- capture_instance
         @supports_net_changes = 1, -- supports_net_changes
         @role_name = NULL, -- role_name
         @index_name = NULL, -- index_name
         @captured_column_list = NULL, -- captured_column_list
         @filegroup_name = ''CDC1'' -- filegroup_name;
    FETCH NEXT FROM My_Cursor INTO @tableName;
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
-- 2.4.3 游标批量开启表
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 1 ORDER BY NAME;
-- 2.4.4 查询已开启的表

2.5 单表开启测试范例（仅供参考，可略过）

create table test_hht
(id varchar(36) not null primary key,
city_name varchar(20),
userid bigint,
useramount decimal(18,6),
ismaster bit,
createtime datetime default getdate()); -- 测试表test_hht
IF EXISTS(SELECT 1 FROM sys.tables WHERE name=''test_hht'' AND is_tracked_by_cdc = 0)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = ''dbo'', -- source_schema
        @source_name = ''test_hht'', -- table_name
        @capture_instance = NULL, -- capture_instance
        @supports_net_changes = 1, -- supports_net_changes
        @role_name = NULL, -- role_name
        @index_name = NULL, -- index_name
        @captured_column_list = NULL, -- captured_column_list
        @filegroup_name = ''CDC1'' -- filegroup_name
END; -- 开启表级别CDC
insert into test_hht(id,city_name,userid,useramount,ismaster)values(''1'',''wuhan'',     10,1000.25,1);
insert into test_hht(id,city_name,userid,useramount,ismaster)values(''1A'',''xiangyang'',11,11000.35,0);
insert into test_hht(id,city_name,userid,useramount,ismaster)values(''1B'',''yichang'',  12,12000.45,0); -- 插入数据测试
select *  from dbname.dbo.test_hht; -- 数据表
SELECT * FROM [cdc].[dbo_test_hht_CT]; -- CDC日志表

2.6 开启成功说明

dbname库出现cdc模式，并有CT系列表。

/*
cdc.<capture_instance>_CT   可以看到，这样命名的表，是用于记录源表更改的表。

对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
*/

2.7 DDL操作：DDL操作需要重新收集表的信息(以测试表test_hht为例)

alter  table test_hht add   product_count decimal(18,2);
-- 2.7.1 增加新的一列测试
insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values(''2'',''wuhan'',     20,2000.25,1,2.5);
-- 2.7.2 插入数据测试
SELECT * FROM [cdc].[dbo_test_hht_CT];
-- 2.7.3 CT表无新的一列，CDC正常捕获到之前的列变化
EXEC sys.sp_cdc_enable_table
@source_schema = ''dbo''
,@source_name = ''test_hht''
,@capture_instance =''dbo_test_hht_v2'' -- 给一个新的名字
,@supports_net_changes = 1
,@role_name = NULL
,@index_name = NULL
,@captured_column_list = NULL
,@filegroup_name = ''CDC1'';
-- 2.7.4 为表dbo.test_hht开启一个新的CDC捕获
insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values(''2A'',''xiangyang'',21,121000.35,0,12.5);
-- 2.7.5 插入数据测试
EXEC sys.sp_cdc_disable_table @source_schema = ''dbo'',@source_name = ''test_hht'', @capture_instance = ''dbo_test_hht'';
-- 2.7.6 SQL SERVER最多允许两个捕获表，所以多次改变时需要先禁用之前的表

3. 关闭CDC

EXEC sys.sp_cdc_enable_table
@source_schema = ''dbo''
,@source_name = ''test_hht''
,@capture_instance =''dbo_test_hht_v2''
-- 3.1 单表禁用
USE dbname
GO
EXEC sys.sp_cdc_disable_db
GO
-- 3.2 全库禁用（禁用后cdc的模式消失）

到此这篇关于SQL SERVER CDC开启实操详细的文章就介绍到这了,更多相关SQL SERVER CDC开启实操内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

您可能感兴趣的文章:

SQLServer数据库中开启CDC导致事务日志空间被占满的原因
SQLServer 2008 CDC功能实现数据变更捕获脚本

SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The

SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type ...

查询 SQL SERVER 中某张表结构，sql 语句如下：

SELECT
	tb.name AS tableName,
	col.name AS columnName,
	col.max_length AS length,
	col.is_nullable AS isNullable,
	t.name AS type,
	(
	SELECT
		TOP 1 ind.is_primary_key
	FROM
		sys.index_columns ic
		LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE ''PK_%''
	WHERE
		ic.object_id = tb.object_id AND ic.column_id= col.column_id
	) AS isPrimaryKey,
	com.value AS comment
FROM
	sys.TABLES tb
	INNER JOIN sys.columns col ON col.object_id = tb.object_id
	LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id
	LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id
	AND com.minor_id = col.column_id
WHERE
	tb.name = ''表名''

该 sql 可以正常执行，但是当把 sql 放到 jdbcTemplate 中执行时报一下错误：

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.

原因是 sql 语句 select 后面有 sql_variant 类型的属性，在 JDBC 中不支持它。使用 sp_columns 命令最终查出 sys.extended_properties 表的 value 属性的 TYPE_NAME 是 sql_variant 类型的，sql 如下：

sp_columns extended_properties

解决方法是使用 CONVERT 函数将该属性转成 varchar 类型。

CONVERT 函数的用法参考：SQL Server 中 CONVERT () 函数的使用。

修改后的 sql 语句为：

SELECT
	tb.name AS tableName,
	col.name AS columnName,
	col.max_length AS length,
	col.is_nullable AS isNullable,
	t.name AS type,
	(
	SELECT
		TOP 1 ind.is_primary_key
	FROM
		sys.index_columns ic
		LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE ''PK_%''
	WHERE
		ic.object_id = tb.object_id AND ic.column_id= col.column_id
	) AS isPrimaryKey,
	CONVERT(varchar(200), com.value) AS comment
FROM
	sys.TABLES tb
	INNER JOIN sys.columns col ON col.object_id = tb.object_id
	LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id
	LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id
	AND com.minor_id = col.column_id
WHERE
	tb.name = ''表名''

参考：

com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.

关于SQLServer CDC和sqlserver Cdc锁的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于FlinkX SqlServer CDC实时采集原理与使用丨直播预告、ogg 12.3 for sqlserver 2016/2014 CDC模式配置、SQL SERVER 开启CDC 实操详细、SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type ...的相关知识，请在本站寻找。

本文标签：