SQLSERVERCDC增量数据抽取..docVIP

SQL SERVER CDC 增量数据抽取.版本历史 日期 版本 描述 作者 26/04/2010 1.0 SQL SERVER CDC 增量数据抽取方案拟制 刘建军，  目录 1、概述 4 1.1、需求概述 4 1.2、场景分析 4 2、依赖关系 5 3、服务方案 5 3.1 设计的目标 5 3.2 相关关键技术说明 5 3.3、报表服务方案 11 3.4 报表服务表结构 13 3.5 报表服务过程及其描述 14 3.6 报表服务使用 14 1、概述 1.1、需求概述 本文档的主要目的是提出和测试报表服务实现，来提高Q3系统的报表统计性能。 该服务方案主要参考ERP报表服务方案，并结合Q3系统与SQLServer2008数据库的特点来实现，并尽量统一规划以提高该方案的兼容性。 1.2、场景分析 报表服务需要将要进行统计、分析的数据库某一业务相关数据在一段时间内的变化（增量数据），同步到报表服务中间库，并将统计结果报表同步更新，这就需要： 报表中间库转储和引用业务库对象。 同一数据库实例上不同数据库或分布式数据库的变更数据捕捉 获得数据增量、并根据增量形成增量报表数据同步报表中间库。 或直接同步报表中间库。 业务执行的唯一性。 抽取日志的记录的记录。 增量数据日志记录清除。 同步异常记录。 2、依赖关系 Q3系统采用SQLServer2008数据库，并且该版本数据库提供了CDC（change data capture）捕获变更数据的方法,并且提供了同义词和数据库级应用锁功能，结合排序函数，为在数据库级别上实现与ERP类似的报表服务提供了可能。 3、服务方案 3.1 设计的目标 Q3的报表服务方案，要满足如下功能性和非功能性要求。 业务数据增量抽取。 增量数据合并（算法最优）。 增量抽取数据的准确性和有效性。 数据抽取执行线程并发控制唯一性和入口统一性（采用数据库级应用锁并需要进行封装，提供与ERP一致的入口）。 跨数据库或分布式数据库的抽取实现（链接服务器）。 数据同步路径最短、同步效率最高（根据业务数据量和业务的复杂性分别采用不同的数据同步方式）。 3.2 相关关键技术说明 CDC (change data capture): ??? CDC为数据仓库常用技术手段，是当对数据库执行insert、update、delete操作的时候，捕获变更数据的方法。 CDC的原理是每次对源表(Source Tables)执行增删改操作时，数据库事务日志会记录该DML操作造成的变更数据，然后捕获处理过程将日志中源表的变更数据写入变更捕获表(Change Tables). 使用步骤： 通过SQL Server 配置管理器(SQL Server Management Studio、服务)，启用数据库的SQL Server Agent代理服务。 执行存储过程，启用了数据库的CDC特性。 EXEC sys.sp_cdc_enable_db 执行存储过程，对数据库中某一表启用CDC特性。 EXEC sys.sp_cdc_enable_table @source_schema = Ndbo , @source_name = Ntest , @role_name = Ncdc_admin , @capture_instance = Ntest_cdc1 , @supports_net_changes = 1 , @index_name = NPK_test , @captured_column_list = Ncol1,col2,col3 , @filegroup_name = NPRIMARY; 系统自动在系统表下建立对应变更捕获表，变更捕获表的命名规则是cdc.+实例名+CT. 其中其中__$operation代表的是执行的是何种DML，1=delete，2=insert，3=update（更新前），4=update（更新后） 系统建立了其他多个关于CDC函数和过程，其中涉及报表相关过程与函数: sys.fn_cdc_get_max_lsn 返回 cdc.lsn_time_mapping 系统表中 start_lsn 列中的最大日志序列号 (LSN)。您可以使用此函数为任何捕获实例返回变更数据捕获时间线的高端点。 sys.sp_cdc_cleanup_change_table 清除变更数据存储过程 手动清理：使用sys.sp_cdc_cleanup_change_table存储过程清除变更数据。 自动清理：在启用数据库CDC时，系统自动在SQL Server Agen