推薦：揭秘SQL Server數(shù)據(jù)庫系統(tǒng)的編譯
你知道SQL Server這么龐大的企業(yè)級數(shù)據(jù)庫服務(wù)器產(chǎn)品是如何build出來的嗎? 這有些相關(guān)的數(shù)據(jù)： 每個build 的大小在300GB左右。 每個完整的build需要幾十臺高端的服務(wù)器運行2.5天。 每個完整的build由幾千個job、10000多個參數(shù)組成。 我們每天同時做20個左右的

介紹:SQL Server 2008變更數(shù)據(jù)捕獲

SQL Server 2008的CDC函數(shù)讀取激活了CDC的每個表所關(guān)聯(lián)的事務(wù)日志來記錄系統(tǒng)表中的變更。它將這些文件寫到同一個數(shù)據(jù)庫的系統(tǒng)表中，這些系統(tǒng)表是可以通過直接查詢或系統(tǒng)函數(shù)來訪問的。

讓我們看些示例代碼并看看每一步有什么改變。

使用示例代碼來跟蹤可變部分

首先，我們從一個全新的數(shù)據(jù)庫開始。要跟蹤這些變更，打開對象瀏覽器并展開這些文件夾：

· Databases

· SQLServer Agent | Jobs

· (數(shù)據(jù)庫創(chuàng)建之后)Security | Roles | Database Roles

· (數(shù)據(jù)庫創(chuàng)建之后)Programmability | Stored Procedures | System Stored Procedures

· (數(shù)據(jù)庫創(chuàng)建之后)Programmability | Functions | Table-valued Functions

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE TestCdc

這為我們創(chuàng)建了新數(shù)據(jù)庫，但是沒有新作業(yè)。展開Tables文件夾和System Tables文件夾�，F(xiàn)在是空的。

在數(shù)據(jù)庫TestCdc上激活變更數(shù)據(jù)捕獲

USE TestCdc

-- Turn on Change Data Capture at database level

EXEC sys.sp_cdc_enable_db

這個步驟需要一些時間。現(xiàn)在刷新System Tables文件夾�，F(xiàn)在它下面有6個系統(tǒng)表：

· cdc.captured_columns

· cdc.change_tables

· cdc.ddl_history

· cdc.index_columns

· cdc.lsn_time_mapping

· dbo.systranschemas

注意，其中的5個是“cdc” schema 部分的。

在sys.all_objects表中還有四個新的看起來很奇怪的“SQL inline table-valued function”登錄：

· fn_cdc_get_all_changes_◦...◦

· fn_cdc_get_all_changes_...

· fn_cdc_get_net_changes_◦...◦

· fn_cdc_get_net_changes_...

(我使用了非比例字體，用“◦”來標記空格，使得很清楚地看到在這些名稱中有嵌入的空格。)

下面是會返回這些函數(shù)的查詢：

SELECT [name] FROM sys.all_objects WHERE [name] LIKE '%...%'

還有更多的新存儲過程

· sp_batchinsert_lsn_time_mapping

· sp_ins_dummy_lsn_time_mapping

· sp_ins_instance_enabling_lsn_time_mapping

· sp_ins_lsn_time_mapping

現(xiàn)在還沒有新的作業(yè)或角色。

創(chuàng)建新的Schema和用戶表

CREATE SCHEMA MySchema

CREATE TABLE MySchema.Person (

PersonID int IDENTITY PRIMARY KEY,

FirstName varchar(32) NOT NULL,

LastName varchar(32) NOT NULL,

UpdateCt int NOT NULL DEFAULT 0

)

上面的代碼創(chuàng)建了一個新的用戶表，但是這個表還沒有激活CDC。

這個步驟不影響我們所跟蹤的函數(shù)/存儲過程/角色列表。

為新的用戶表激活CDC

EXEC sys.sp_cdc_enable_table

@source_schema = N'MySchema',

@source_name = N'Person',

@role_name = N'ChangeDataAccessRole',

@supports_net_changes = 1

這會為新的用戶表激活CDC。刷新上面所列的文件夾。我們現(xiàn)在有了

· 一個新的系統(tǒng)表(cdc.MySchema_Person_CT)

· 兩個新的作業(yè)(cdc.TestCdc_capture和cdc.TestCdc_cleanup)

· 一個新的數(shù)據(jù)庫角色(ChangeDataAccessRole)

· 兩個新的表值型函數(shù)

o fn_cdc_get_all_changes_MySchema_Person

o fn_cdc_get_net_changes_MySchema_Person

· 三個新的存儲過程：

o sp_batchinsert_389576426

o sp_insdel_389576426

o sp_upd_389576426

你可以獲得這些存儲過程(sp_helptext 'cdc.sp_batchinsert_389576426' 【你的數(shù)字可能不一樣】)的文本。很明顯這個存儲過程被生成了，而且是專用于這個用戶表的。

如果你為第二個表激活了CDC，那么你將獲得另一組的sp_batchinsert_n、sp_insdel_n和sp_upd_n存儲過程。

系統(tǒng)表cdc.MySchema_Person_CT

這個表具有以下字段：

· __$start_lsn

· __$end_lsn

· __$seqval

· __$operation

· __$update_mask

· PersonID

· FirstName

· LastName

· UpdateCt

作業(yè)cdc.TestCdc_capture

當你研究這個捕捉作業(yè)時，你會發(fā)現(xiàn)它是很有趣的。從sysjobs，我們可以看出這個作業(yè)是屬于sa的，是在“REPL-LogReader”分類中，并有一個“CDC Log Scan Job”的描述。

從sysjobsteps，我們看到它有兩個步驟：

讀過這些細節(jié)之后，接下來有兩個步驟，但是我們將保留第二個，直到我們插入了一些數(shù)據(jù)之后再執(zhí)行

sp_helptext N'sys.sp_MScdc_capture_job'

SELECT * FROM sys.dm_cdc_log_scan_sessions

存儲過程Psys.sp_MScdc_capture_job和sys.sp_cdc_scan

sys.sp_MScdc_capture_job是開始整個CDC過程的存儲過程。它首先進行一些安全檢查，然后調(diào)用sys.sp_cdc_scan，這將進行實質(zhì)的工作。sys.sp_cdc_scan執(zhí)行變更數(shù)據(jù)捕獲日志掃描操作。

默認情況下，sys.sp_cdc_scan在每個循環(huán)間進行五秒鐘的延遲。在每個常規(guī)的循環(huán)中，它調(diào)用sp_replcmds。(這是個復(fù)制存儲過程。使用默認(1)調(diào)用這個存儲過程會返回等待分配的下一個事務(wù)。在這個存儲過程中，默認@maxtrans是500。)

最后，它從sys.dm_cdc_log_scan_sessions讀取來準備調(diào)用sp_sqlagent_log_jobhistory，從而記錄作業(yè)歷史信息。

數(shù)據(jù)庫角色ChangeDataAccessRole

如果你打開新的ChangeDataAccessRole角色的屬性窗口，那么你會看到這個角色屬于“cdc”，并且具有對上面所列的兩個表值型函數(shù)的SELECT權(quán)限，除此之外沒有其它權(quán)限。

總結(jié)

研究這些細節(jié)使我們了解了Server 2008的CDC是怎樣執(zhí)行的。

分享：怎樣清除SQL Server日志
方法一 一般情況下，SQL數(shù)據(jù)庫的收縮并不能很大程度上減小數(shù)據(jù)庫大小，其主要作用是收縮日志大小，應(yīng)當定期進行此操作以免數(shù)據(jù)庫日志過大 1、設(shè)置數(shù)據(jù)庫模式為簡單模式：打開SQL企業(yè)管理器，在控制臺根目錄中依次點開Microsoft SQL Server--