劉建軍

(吉林省教育學(xué)院 網(wǎng)絡(luò)信息中心，長春130022)

1 報(bào)表服務(wù)方案

1.1 報(bào)表服務(wù)方案

圖1 報(bào)表服務(wù)整體流程

針對(duì)業(yè)務(wù)數(shù)據(jù)庫報(bào)表數(shù)據(jù)情況不同，采用兩種方式進(jìn)行數(shù)據(jù)同步。

(1)同步增量報(bào)表。對(duì)數(shù)據(jù)基數(shù)大且數(shù)據(jù)更新頻繁的業(yè)務(wù)表，則采用同步增量統(tǒng)計(jì)分析結(jié)果的方式，即同步增量報(bào)表。即在中間數(shù)據(jù)庫上，對(duì)業(yè)務(wù)表的cdc數(shù)據(jù)的同義詞按業(yè)務(wù)邏輯進(jìn)行報(bào)表分析匯總，獲得增量報(bào)表，同步該增量報(bào)表到報(bào)表數(shù)據(jù)。同步過程主要為合并過程，可理解為兩個(gè)二維統(tǒng)計(jì)表的邏輯加。

(2)同步報(bào)表。對(duì)數(shù)據(jù)基數(shù)比較小或數(shù)據(jù)更新頻率較低的業(yè)務(wù)表，則采用同步統(tǒng)計(jì)分析結(jié)果，直接同步報(bào)表的方式。即在中間數(shù)據(jù)庫上，對(duì)業(yè)務(wù)表的同義詞直接按業(yè)務(wù)邏輯進(jìn)行分析匯總后，同步報(bào)表到報(bào)表數(shù)據(jù)。同步過程主要為替換過程，可理解為先刪除再新增。

圖2 同步增量報(bào)表

圖3 同步增量報(bào)表服務(wù)業(yè)務(wù)流程

圖4 同步報(bào)表

圖5 同步報(bào)表服務(wù)業(yè)務(wù)流程

1.2 報(bào)表服務(wù)表結(jié)構(gòu)

1.3 報(bào)表服務(wù)過程及其描述

表2 報(bào)表服務(wù)存儲(chǔ)過程

2 報(bào)表服務(wù)使用步驟

2.1 初始準(zhǔn)備

(1)啟用sqlserver數(shù)據(jù)庫的SQL Server Agent代理服務(wù)。

(2)對(duì)sqlserver數(shù)據(jù)庫的對(duì)應(yīng)業(yè)務(wù)數(shù)據(jù)庫啟動(dòng)CDC即變更數(shù)據(jù)捕捉功能。

執(zhí)行SQL代碼為：EXEC sys.sp_cdc_enable_db。

(3)分析原報(bào)表服務(wù)統(tǒng)計(jì)分析SQL語句中涉及的對(duì)象范圍、數(shù)據(jù)量級(jí)別來確定是否對(duì)該對(duì)象啟用CDC。

例如：

報(bào)表服務(wù)對(duì)A1,A2,A3表統(tǒng)計(jì)匯總，A1、A2表數(shù)據(jù)量很大，A3表數(shù)據(jù)量很少，數(shù)據(jù)均有頻繁變動(dòng)，則需要對(duì)A1,A2,A3表均啟用變更數(shù)據(jù)捕捉。

報(bào)表服務(wù)對(duì)A1,A2,A3表統(tǒng)計(jì)匯總，A1、A2表數(shù)據(jù)量很大并有數(shù)據(jù)變化，A3表數(shù)據(jù)量很少且數(shù)據(jù)變化不頻繁，則只需對(duì)A1,A2表均啟用變更數(shù)據(jù)捕捉.

報(bào)表服務(wù)對(duì)A1,A2,A3表統(tǒng)計(jì)匯總，A1,A2,A3表數(shù)據(jù)量均很小，A1,A2,A3數(shù)據(jù)變化頻率低且變化數(shù)據(jù)范圍較小，則不需要啟用變更數(shù)據(jù)捕捉，而采用報(bào)表服務(wù)方式二處理.

(4)確定變更捕捉字段，確定捕捉源表中哪些字段的數(shù)據(jù)變化到變更捕獲表中。

(5)執(zhí)行EXEC sys.sp_cdc_enable_table并調(diào)整相應(yīng)參數(shù)對(duì)表及相應(yīng)字段啟用變更數(shù)據(jù)捕捉，隨即在系統(tǒng)表下生成cdc架構(gòu)下的對(duì)應(yīng)該表的變更數(shù)據(jù)捕獲表。

(6)在報(bào)表數(shù)據(jù)庫下建立報(bào)表服務(wù)涉及表的同義詞,啟用cdc有變更捕獲表則建立變更捕獲表的同義詞，沒有啟用cdc，則直接對(duì)該表建立同義詞。

(7)在報(bào)表數(shù)據(jù)庫下建立報(bào)表數(shù)據(jù)存儲(chǔ)表,用作存儲(chǔ)執(zhí)行報(bào)表、或者增量報(bào)表數(shù)據(jù)。報(bào)表服務(wù)采用MERGE方法同步數(shù)據(jù)該報(bào)表數(shù)據(jù)存儲(chǔ)表。

2.2 報(bào)表服務(wù)實(shí)現(xiàn)步驟

(1)調(diào)用過程Prcd _Reg，注冊(cè)報(bào)表服務(wù)信息到日志記錄表。

(2)調(diào)用過程Prcd_AppLock，對(duì)業(yè)務(wù)存儲(chǔ)過程加應(yīng)用程序鎖，以保證該過程執(zhí)行的唯一性。

(3)調(diào)用過程Prcd_ClearCDC，清理變更表中失效數(shù)據(jù)。

(4)調(diào)用過程Prcd_GetLSN,獲得變更捕獲表中的最大最小LSN。

(5)同步增量統(tǒng)計(jì)報(bào)表結(jié)果到報(bào)表數(shù)據(jù)存儲(chǔ)表。

獲得增量數(shù)據(jù)方法：

第一類，例如報(bào)表服務(wù)業(yè)務(wù)SQL:

select a.column1,sum(a.column2),sum(b.column3) from a join b on a.column1=b.column1 .

其中column1為業(yè)務(wù)主鍵，column2,column3為數(shù)值類型字段

只需要將表a,b替換，

select a.column1,sum(a.column2),sum(b.column3) from

(select column1,sum(case __$operation when 1 then -column2 when 2 then column2 when 3 then - column2 else column2 end) from a where __$start_lsn >0 and __$start_lsn< ? group by column1) a

join

(select column1,sum(case __$operation when 1 then -column3 when 2 then column3 when 3 then - column3 else column3 end) from a where __$start_lsn >0 and __$start_lsn< ? group by column1) b

on a.column1=b.column1 .

另外需要加條件加以控制增量統(tǒng)計(jì)范圍，__$start_lsn為LSN事務(wù)序列號(hào)。

第二類，例如報(bào)表服務(wù)業(yè)務(wù)SQL:

Select column1,sum(column2) from a group by column1

其中column1為字符類型字段，column2為數(shù)值類型字段，且column1不一定為主鍵。Column1字段可能會(huì)變更并影響報(bào)表結(jié)果。

Select column1,sum(column2) from

(select column1,sum(case __$operation when 1 then -column2 when 2 then column2 when 3 then - column2 else column2 end) from a where __$start_lsn >0 and __$start_lsn< ? ) a

group by column1

MERGE同步數(shù)據(jù)：

示例SQL腳本如下(需要將上述增量計(jì)算SQL替換【源結(jié)果集】位置，將報(bào)表數(shù)據(jù)存儲(chǔ)表名替換到【目標(biāo)表】位置)：

merge 目標(biāo)表 as t

using( 源結(jié)果集 ) as s

on(s.關(guān)聯(lián)鍵值 = t. 關(guān)聯(lián)鍵值)

when matched then

update set t.字段 = s. 字段+ t.字段

when not matched then

insert values(s.字段) ;

(6)根據(jù)業(yè)務(wù)復(fù)雜度可能重復(fù)上述同步增量統(tǒng)計(jì)數(shù)據(jù)過程。

(7)調(diào)用過程Prcd_RunLog，將執(zhí)行信息寫入到日志表。

(8)調(diào)用過程Prcd_AppUnLock，對(duì)業(yè)務(wù)存儲(chǔ)過程解應(yīng)用程序鎖。

(9)如對(duì)數(shù)據(jù)基數(shù)小且變更不頻繁的報(bào)表服務(wù)業(yè)務(wù)表作統(tǒng)計(jì)分析，因?yàn)槲撮_啟變更數(shù)據(jù)捕捉，則使用步驟類似但有所區(qū)別，所不同的是不需采用獲得增量數(shù)據(jù)過程，而是直接執(zhí)行merge同步統(tǒng)計(jì)分析數(shù)據(jù)過程。

(10)當(dāng)報(bào)表存儲(chǔ)過程業(yè)務(wù)流程處理過程中出現(xiàn)異常，則調(diào)用存儲(chǔ)過程Prcd_ErrorLog存儲(chǔ)過程記錄異常到異常記錄表。

[1] 楊志國.SQL Server2005數(shù)據(jù)庫管理精講[M].北京:電子工業(yè)出版社, 2007.

[2] Kalen Delaney.Microsoft SQl Server 2005技術(shù)內(nèi)幕:存儲(chǔ)引擎[M].北京：電子工業(yè)出版社，2010.

[3] 陳振.基于日志分析的SQL Server數(shù)據(jù)庫變更數(shù)據(jù)捕獲方法的研究與實(shí)現(xiàn)[D].廣州：暨南大學(xué),2010.

[4] 孫燕.異構(gòu)數(shù)據(jù)庫數(shù)據(jù)同步的關(guān)鍵技術(shù)研究[D].唐山：華北理工大學(xué)，2015.