>데이터 베이스 >MySQL 튜토리얼 > Using Change Data Capture (CDC) in SQL Server 2008

Using Change Data Capture (CDC) in SQL Server 2008

WBOY
WBOY원래의
2016-06-07 17:44:381333검색

Problem As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Change Data Capture. Can you give us a detailed explanation of how we go about using this one? Solution Change Data Capture

Problem
As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Change Data Capture.  Can you give us a detailed explanation of how we go about using this one?

Solution
Change Data Capture is a new feature in SQL Server 2008 that records insert, update and delete activity in SQL Server tables.  A good example of how this feature can be used is in performing periodic updates to a data warehouse.  The requirement for the extract, transform, and load (ETL) process is to update the data warehouse with any data that has changed in the source systems since the last time the ETL process was run.  Before CDC we might simply query a last updated DATETIME column in our source system tables to determine what rows have changed.  While this is simple and pretty effective, it is of no use in determining any rows that were physically deleted.  In addition we can't determine what was changed when; we can only access the current state of a row that has changed.  CDC provides a configurable solution that addresses these requirements and more.

In this tip we are going to gain an understanding of CDC by walking through a simple code sample to demonstrate how to:

  • Setup and configure CDC
  • Use CDC to extract rows that have been inserted, updated, or deleted via T-SQL queries
  • Before we start reviewing the sample T-SQL code, let's discuss how CDC works at a high level.  After performing some setup and configuration steps (which we will cover below), CDC will begin scanning the database transaction log for changes to certain tables that you specify, and will insert these changes into change tables.  These change tables are created during the setup and configuration process.  The setup and configuration process will also create table-valued functions which can be used to query for the changes.  You use the table-valued functions in lieu of querying the underlying change tables directly.  Based on this high level description, let's proceed to the demo.

    The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008.  Some of the function names and stored procedure names have changed from the earlier CTPs.

    Setup and Configuration

    CDC is a feature that must be enabled at the database level; it is disabled by default.  To enable CDC you must be a member of the sysadmin fixed server role.  You can enable CDC on any user database; you cannot enable it on system databases.  Execute the following T-SQL script in the database of your choice to enable CDC:

    declare @rc int exec @rc = sys.sp_cdc_enable_db select @rc -- new column added to sys.databases: is_cdc_enabled select name, is_cdc_enabled from sys.databases

    ,网站空间,香港服务器,美国服务器
    성명:
    본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.