SQLSever CDC 实施方案.docx

SQLSever CDC 实施方案.docx

《SQLSever CDC 实施方案.docx》由会员分享，可在线阅读，更多相关《SQLSever CDC 实施方案.docx（15页珍藏版）》请在冰豆网上搜索。

SQLSeverCDC实施方案

SQLSeverCDC实施手册

1.SQLServer变更数据捕获简介

变更数据捕获可记录应用于SQLServer表的插入、更新和删除活动。

这样，就可以按易于使用的关系格式提供这些更改的详细信息。

更改表镜像了所跟踪原表的列结构，将修改的行应用到目标环境所需的列信息和元数据会被捕获并存储在更改表中。

SQL提供了一些表值函数，以便使用这对更改数据进行系统级访问。

CDC技术典型应用场景是提取、转换和加载（ETL）应用程序。

通常ETL应用程序需要以增量方式将SQLServer源表中的更改数据加载到数据仓库或数据集市。

虽然数据仓库中的源表的表示形式必须反映源表中的更改，但数据仓库中目标表的数据表现形式不一定和源表相似，因而刷新源副本的端到端技术并不适用。

相反，需要一种结构化的可靠更改数据流，以便使用者可以将其应用于不同的目标数据表示形式。

SQLServer变更数据捕获就提供了这一技术。

变更数据捕获的变更数据源为SQLServer事务日志。

在将插入、更新和删除操作应用于跟踪的源表时，描述这些更改的条目会添加到事务日志中。

日志用作变更数据捕获进程的输入来源，它会读取日志，并在所跟踪的表的关联更改表中添加有关更改的信息。

系统将提供一些函数，以枚举在更改表中指定范围内发生的更改，并以筛选的结果集的形式返回该值。

应用程序进程使用筛选的结果集通常用以在某种外部环境中更新源表示形式。

2.CDC与传统增量处理方式的对比

在没有使用CDC之前，为了确定数据更改，通常应用程序开发人员必须在其应用程序中使用触发器、时间戳列和其他表的组合来实现自定义跟踪方法。

创建这些应用程序通常涉及多项工作，导致架构更新，并且通常带来较高的性能开销。

通过触发器可以把DML操作中的INSERT/UPDATE/DELETE数据记录下来，但是触发器的维护比较困难。

另外一种常用的方式是时间戳, 它是以业务表中某一个字段的值，作为判断新旧数据的标志，每次只抽取上次抽取记录时间以后产生的数据。

时间戳方式也存在一些问题：

变化数据的捕获需要在源数据库上进行，并且只能捕获最终结果，而非整个变化历史。

时间戳的粒度通常设置到秒，在某些场合下这个粒度可能会略掉一些变化数据。

无法捕获对时间戳以前数据的delete和update操作,在数据准确性上受到了一定的限制。

 而且 要求业务系统的表必须一个可以标识新旧数据的列，并且需要对该列做索引，而某些表没有设置这种列。

CDC提供了一种机制，对源表数据的更新进行跟踪，在应用程序中使用变更数据捕获而不开发自定义解决方案来跟踪数据库中的更改具有以下好处：

∙减少了开发时间。

由于SQLServer中提供了功能，因此无需开发自定义解决方案。

∙不需要架构更改。

您无需添加列、添加触发器或创建要在其中跟踪已删除的行或存储更改跟踪信息的端表（如果无法将列添加到用户表）。

∙具有内置清除机制。

更改跟踪的清除操作在后台自动执行。

不需要端表中存储的数据的自定义清除。

∙提供功能的目的是获取更改信息。

∙降低了DML操作的开销。

开销通常会低于使用其他解决方案，对于需要使用触发器的解决方案，尤其如此。

∙提供可用于配置和管理的标准工具。

SQLServer提供标准的DDL语句、SQLServerManagementStudio、目录视图和安全权限。

3.现行数据库架构

DB_1，DB_2，DB_3，……，DB_50是分布在各个学校的数据库。

DB1，DB2，……，DB50是集成在一个服务器上50个不同的DB。

DC与IDC的区别：

DC包含的数据表比较全，系统压力比较大；IDC基本能满足当前所涉及的表，系统压力比DC好一些。

ORACLE是最终的目标库。

当前共涉及到50个DB，每个DB约有10张表。

每张表每天的增量数据统计如下：

4.备选方案和建议

∙第一种方案：

∙在DC服务器上为50个不同的DB启用CDC。

（目前涉及10张表）

∙最后用Informatica开发ETL，从DC服务器上50个DB的变化库中循环抽取。

∙

∙影响：

∙1.启用CDC会在每个DB的系统表中会生成6张公用表。

∙为每张表启用时会多增加一个系统表。

∙50个DB共计增加800张表。

∙2.为每个DB启用CDC，都会在SQLServer代理下job中新增2个job。

∙50个DB共计增加100个job。

第二种方案：

在各个学校的DB上启用CDC。

然后为每张启用CDC对应的系统表创建分布式服务，将该系统表同步到DC服务器对应的DB的表中。

最后用informatica开发，从DC服务器上50个DB循环抽取。

影响：

1.因为启用CDC，会在每个库的系统表里新增16张表。

需要在不同的DB上为这些系统表创建分布式服务。

2.需要在DC服务器上不同数据库中创建对应的表（DB_1中的系统），来接受上游送来的数据。

建议方案

对比上述两个方案，建议采用方案1，其最主要的优点在于对源数据库无需修改，所有的更改都在DC上进行，而方案2需要对每一个源数据库分别启用CDC，对于管理员来说工作会比较繁琐，虽然总体需要增加的表的数量没变，但方案1所有的更改都集中在一个服务器上，相对容易管理一些，而且以后的升级和修改都只需要考虑一个系统，而不是像方案2那样需要考虑50个系统。

但所有的操作集中在DC上之后，也不可避免地会带来负载的增加，由于方案2仍需要在DC上建立针对各个数据库的变化表进行写入，负载的增加主要集中在日志的扫描部分，这一部分基本上是读操作，对负载的影响可能不象写入带来的影响那么明显。

另外一点，由于任务都集中在一个系统上，任务的管理会比较麻烦一些。

5.启用变更数据捕获

1.确定要添加CDC的DB，比如：

CDC。

执行如下sql

备注：

执行完成后，会在DB的系统表里新增：

6张表，一个CDC的账户，一个CDC的架构，如下图：

2.查看数据库CDC是否启用成功，执行sql如下图：

3.对数据库表进行启用CDC，执行sql如下图：

备注：

执行成功后，会新增DB系统表里新增一张表，一个角色，2个job作业。

如下图：

4.查看数据库表CDC是否启用成功，执行sql如下图：

5.对数据库，对表启用CDC后，也会在DB中新增1个函数，及若干个存储过程：

6.之上步骤执行完成后，对表进行的insert，delete，update操作都会在cdc.dbo_o_reg_bs_student_ct表中存储。

6.添加CDC之后的影响

1.不能对表使用truncate操作。

2.删除DB时，要先删除该DB对应的2个JOB，否则会报错。

3.对表启用CDC后，SQL服务器会跟踪总事务日志中的inser,update和delete操作，在对源叔据做变更时，服务器需要在变更表中同时也插入一条记录，这在一定程度上会影响服务器的性能。

如果某表的数据量每天的变量两很大，性能的影响可能会比较明显。

可以调整系统的CT表保留时长减少对性能的影响。

设置sql如下：

4.为适应固定列结构更改表，在为源表启用CDC后，负责填充更改表的捕获进程将忽略未指定进行捕获的任何新列。

如果删除了某个跟踪的列，则会为在后续更改项中为该列提供Null值。

但是，如果现有列只是更改了其数据类型，则会将更改传播到更改表。

5.变更数据捕获和事务复制可以共存于同一数据库中，但在启用这两项功能后，更改表的填充处理方式将发生变化。

变更数据捕获和事务复制始终使用相同的过程sp_replcmds从事务日志读取更改。

当单独启用变更数据捕获时，SQLServer代理作业会调用sp_replcmds。

在同一数据库中启用这两项功能时，日志读取器代理会调用sp_replcmds。

此代理将填充更改表和分发数据库表。

7.注意事项

1.性能

尽管变更数据捕获通过获取进行DML更改的方面和更改的实际数据，提供用户表的历史更改信息。

更改是使用异步进程捕获的，此进程读取事务日志，并且对系统造成的影响较小。

但对本身负载已经比较中的服务器，建议评估启用CDC后对数据库性能的影响，以及启用变更数据捕获所需的数据表和日志需求的增加。

2.安全

配置和管理

若要为数据库启用或禁用变更数据捕获，sys.sp_cdc_enable_db（Transact-SQL）或sys.sp_cdc_disable_db（Transact-SQL）的调用者必须是sysadmin固定服务器角色的成员。

若要在表级启用或禁用变更数据捕获，要求sys.sp_cdc_enable_table（Transact-SQL）和sys.sp_cdc_disable_table（Transact-SQL）的调用者必须是sysadmin角色成员或数据库databasedb_owner角色成员。

仅限服务器sysadmin角色成员和databasedb_owner角色成员能够使用存储过程来支持变更数据捕获作业管理。

更改枚举和元数据查询

若要获取对与捕获实例关联的更改数据的访问，必须为用户授予关联源表中的所有捕获列的选择访问权限。

此外，如果在创建捕获实例时指定了访问控制角色，调用者还必须是指定访问控制角色的成员。

所有数据库用户可通过public角色访问用于访问元数据的其他常规变更数据捕获功能，但返回的元数据访问通常也是使用基础源表的选择访问权限以及任何定义的访问控制角色成员控制的。

对启用了变更数据捕获的源表执行的DDL操作

为表启用变更数据捕获后，只能由固定服务器角色sysadmin成员、databaseroledb_owner成员或databaseroledb_ddladmin成员将DDL操作应用于该表。

如果为用户显式授予了对表执行DDL操作的权限，这些用户在尝试执行这些操作时将收到错误22914。

8.数据类型

变更数据捕获支持所有基列类型。

下表列出了几个列类型的行为和限制。

列类型

在更改表中捕获更改

限制

稀疏列

是

不支持在使用列集时捕获更改。

计算列

否

不跟踪对计算列的更改。

在更改表中该列将显示为相应类型，不过其值为NULL。

XML

是

不跟踪对单个XML元素的更改。

时间戳

是

更改表中的数据类型将转换为binary。

BLOB数据类型

是

仅当BLOB列本身更改时才存储该列的上一映像。

9.监视变更数据捕获进程

通过监视变更数据捕获进程，可以确定更改是否正以合理的滞后时间正确写入更改表中。

监视还可以帮助您标识可能发生的任何错误。

SQLServer包括两个动态管理视图，用于帮助您监视变更数据捕获：

sys.dm_cdc_log_scan_sessions和sys.dm_cdc_errors。

标识包含空结果集的会话

sys.dm_cdc_log_scan_sessions中的每一行表示一个日志扫描会话（ID为0的行除外）。

一个日志扫描会话等同于执行一次sp_cdc_scan。

在会话期间，扫描可以返回更改，也可以返回空结果。

如果结果集为空，则sys.dm_cdc_log_scan_sessions中的empty_scan_count列将设置为1。

如果有连续的空结果集（例如，当捕获作业正在连续运行时），则最后一个现有行中的empty_scan_count将递增。

例如，如果sys.dm_cdc_log_scan_sessions已经包含与返回了更改的扫描相对应的10行，并且存在五个连续的空结果，则该视图包含11行。

最后一行在empty_scan_count列的值是5。

若要确定有空扫描的会话，请运行以下查询：

SELECT*fromsys.dm_cdc_log_scan_sessionswhereempty_scan_count<>0

确定滞后时间

sys.dm_cdc_log_scan_sessions管理视图包括一个用于记录每个捕获会话滞后时间的列。

滞后时间是指在源表上提交的事务与在更改表上提交的最后一个捕获的事务之间所经过的时间。

只为活动会话填充滞后时间列。

对于其empty_scan_count列的值大于0的会话，滞后时间列将设置为0。

以下查询返回最近进行的会话的平均滞后时间：

SELECTlatencyFROMsys.dm_cdc_log_scan_sessionsWHEREsession_id=0

可以使用滞后时间数据确定捕获进程正在以多快或多慢的速度处理事务。

当捕获进程连续运行时，该数据最有用。

如果捕获进程正在按计划运行，那么，由于在源表上提交的事务与按计划时间运行的捕获进程之间存在滞后，因此滞后时间可能会很长。

捕获进程效率的另一个重要度量值是吞吐量。

它是在每个会话期间每秒处理的平均命令数。

若要确定会话的吞吐量，请将command_count列中的值除以持续时间列中的值。

以下查询返回最近会话的平均吞吐量：

SELECTcommand_count/durationAS[Throughput]FROMsys.dm_cdc_log_scan_sessionsWHEREsession_id=0

使用数据收集器收集抽样数据

SQLServer数据收集器用于从任何表或动态管理视图中收集数据的快照，并生成性能数据仓库。

对数据库启用变更数据捕获时，最好按固定时间间隔取得sys.dm_cdc_log_scan_sessions视图和sys.dm_cdc_errors视图的快照，以便随后进行分析。

以下过程设置一个数据收集器，用于从sys.dm_cdc_log_scan_sessions管理视图收集示例数据。

配置数据集合

1.启用数据收集器，并配置管理数据仓库。

2.执行以下代码，为变更数据捕获创建自定义收集器。

Transact-SQL

USEmsdb;

DECLARE@schedule_uiduniqueidentifier;

--Collectanduploaddataevery5minutes

SELECT@schedule_uid=（

SELECTschedule_uidfromsysschedules_localserver_view

WHEREname=N'CollectorSchedule_Every_5min'）

DECLARE@collection_set_idint;

EXECdbo.sp_syscollector_create_collection_set

@name=N'CDCPerformanceDataCollector',

@schedule_uid=@schedule_uid,

@collection_mode=0,

@days_until_expiration=30,

@description=N'ThiscollectionsetcollectsCDCmetadata',

@collection_set_id=@collection_set_idoutput;

--Createacollectionitemusingstatisticsfrom

--thechangedatacapturedynamicmanagementview.

DECLARE@paramtersxml;

DECLARE@collection_item_idint;

SELECT@paramters=CONVERT（xml,

N'

SELECT*FROMsys.dm_cdc_log_scan_sessions

cdc_log_scan_data

'）;

EXECdbo.sp_syscollector_create_collection_item

@collection_set_id=@collection_set_id,

@collector_type_uid=N'302E93D1-3424-4BE7-AA8E-84813ECF2419',

@name='CDCPerformanceDataCollector',

@frequency=5,

@parameters=@paramters,

@collection_item_id=@collection_item_idoutput;

GO

3.在SQLServerManagementStudio中，展开“管理”，然后展开“数据收集”。

右键单击“CDC性能数据收集器”，然后单击“启动数据收集组”。

4.在步骤1配置的数据仓库中，找到表custom_snapshots.cdc_log_scan_data。

该表提供日志扫描会话中的数据的历史快照。

此数据可以用于分析与时间有关的滞后时间、吞吐量和其他性能度量值。