SqlServer CDC的使用（OGG）
启用数据库：在当前数据库下执行命令
1 2 3 | USE CDRDB; GO 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功能
1 | select * from sys.databases where is_cdc_enabled = 1 |
启用表，例如表名为”dbo.t1”
1 | EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 't1', @role_name = null; |
注：为第一个表启用CDC后，SQLServer生成两个Agent作业
cdc.dbname_capture
cdc.dbname_cleanup
查看表是否启用了CDC
1 | select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('dbo.t1') |
禁用表(“dbo.t1”)
1 | EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 't1', @capture_instance = 'all'; |
禁用数据库CDC
1 | EXEC sys.sp_cdc_disable_db; |
一些异常情况的处理
(1) 数据库从2005恢复到2008，打开表级别的附加日志报错
未针对数据库 'AdventureWorks2014' 启用变更数据捕获。 请确保已设置正确的数据库上下文，然后重试该操作。若要报告数据库已启用变更数据捕获这一情况， 请查询 sys.databases 目录视图中的 is_cdc_enabled 列.
解决办法：
1 | ALTER AUTHORIZATION ON DATABASE::[数据库名] TO [sa] |
CDC表状态的含义：
$operation=2的情况，表示新增
$operation=3或者4，表示更新，3表示旧值，4表示新值
__$operation=1的情况，表示删除
根据发布批量生成表
1 2 | SELECT 'EXEC sys.sp_cdc_enable_table @source_schema = N'''+b.source_owner+''',' +'@source_name='''+b.source_object+''','+'@role_name=''cdc'',@supports_net_changes = 1'FROM dbo.MSpublications a,dbo.MSarticles bWHERE a.publication_id=b.publication_id AND a.publisher_db=b.publisher_dband a.publication ='his_repl' |
根据系统表批量生成表
1 | select 'EXEC sys.sp_cdc_enable_table @source_schema = ''dbo'', @source_name = '''+name+''', @role_name = null;' from sysobjects where xtype='U' and category ='0' |
systranschemas (Transact-SQL)
The systranschemas table is used to track schema changes in articles published in transactional and snapshot publications. This table is stored in both publication and subscription databases.
| Column name | Data type | Description |
|---|---|---|
| tabid | int | Identifies the table article on which the schema change occurred. |
| startlsn | binary | LSN value at the start of the schema change. |
| endlsn | binary | LSN value at the end of the schema change. |
| typeid | int | Type of schema change. |
