SQL Server 多表資料增量擷取和釋出 2.2

資料庫環境：

1、SQLServer 2008R2

2、SQLServer 代理打開

一、建立一個資料庫

建立資料庫 Incremental_DB

image.png

二、建立倆張測試表

資料庫腳本連結

--建立使用者表
CREATE TABLE [dbo].[Person](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Name] [NVARCHAR](120) NULL,
    [Age] [INT] NULL,
 CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--建立部門表
CREATE TABLE [dbo].[Department](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Name] [NVARCHAR](50) NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
           

三、實作資料變更捕獲

一、對目标庫顯式啟用CDC

USE Incremental_DB
GO
EXECUTE sys.sp_cdc_enable_db;
GO
           

檢視是否啟用CDC

SELECT  is_cdc_enabled,CASE WHE is_cdc_enabled=0 THEN 'CDC功能禁用'ELSE 'CDC功能啟用'END [描述]
FROM    sys.databases
WHERE   [name]='Incremental_DB'
           

建立成功後，會在測試庫自動添加CDC使用者和架構

二、對目标庫資料表顯式啟用CDC

USE Incremental_DB

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo'--架構名稱
  , @source_name = N'Department'--表名稱
  , @role_name = 'cdc_Admin'--會自動生成自定義 'cdc_Admin' 角色 如果不想控制通路角色，則@role_name必須顯式設定為null
  , @capture_instance=NULL

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo'--架構名稱
  , @source_name = N'Person'--表名稱
  , @role_name = 'cdc_Admin'--會自動生成自定義 'cdc_Admin' 角色 如果不想控制通路角色，則@role_name必須顯式設定為null
  , @capture_instance=NULL

           

語句執行成功後在系統表中生成倆張變化表

新增表.png

資料庫可程式設計性中增加倆個函數

新增函數.png

同時SQLServer 代理中新增倆個作業

• capture(捕獲作業)
• clean(清理作業)

  

  新增作業.png

如果出現如下錯誤，請參考上一篇文章CDC注意事項第三點

驗證資料表是否啟用CDC

USE Incremental_DB
--檢視是否已啟用：
SELECT  name ,
        is_tracked_by_cdc ,
        CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
             ELSE 'CDC功能啟用'
        END 描述
FROM    sys.tables
WHERE   OBJECT_ID IN(OBJECT_ID('Person'),OBJECT_ID('Department'))
           

四、禁用資料庫或資料表CDC功能

禁用資料表CDC

--禁用人員表CDC功能
EXECUTE sys.sp_cdc_disable_table 
    @source_schema = N'dbo', 
    @source_name = N'Person',
    @capture_instance = 'dbo_Person'
           

禁用後系統表中原來的dbo_Person_CT表被删除，函數删除等

禁用後系統表截圖.png

禁用資料庫CDC

EXEC sys.sp_cdc_disable_db