Change Data Capture (CDC)

Yönettiğimiz veritabanlarında önemli (kritik) verilerin bulunuğu tablolarda SQL Server 2008 den önce Trigger vb. özellikler yardımı ile tablo üzerinde yapılan işlemleri log layabiliyorduk. Bu işlemelerin sisteme getirdiği bir yük de söz konusu idi. SQL Server 2008 ile hayatımıza giren Chage Data Captur (CDC) özelliği tablo üzerinde gerçekleşen hareketleri  SQL server tarafından izlenmesini ve kayıt altına alınmasını sağlamaktadır.

Bu özelliğin diğer özelliklere nazaran en önemli avantajı doğrudan Log altyapsını kullandığı için daha performaslı çalışmakta ve sisteme diğer süreçlere nazaran daha az yük bindirmektedir.

Yukarıda da bahsettiğim üzere bu yapı sadece SQL Server T-Log  ‘u kullanarak loglama yapmaktadır.  Peki bu nasıl oluyor yani çalışma mantığı özet olarak ; tablo üzerinde yapılan bir değişiklik ekleme,güncelleme ve silme gibi bir işlem olduğunda SQL Server bunu önce Log ‘a yazar . Log ‘a yazılan bu veriler eğer CDC yapımız aktif ise Transaction Log mekanizması  Log ‘a yazılan verileri CDC ‘ye giriş olarak gönderir ve CDC process ‘i ise log üzerinden değişiklikleri okuyup ilişkili tablolar ‘a iz kaydı oluşturur.

cdc

CDC özelliği ve Kullanım Adımları :

Değişikliklerin takibi tablo seviyesindedir fakat izlemek istediğiniz tablonun bulunduğu veritabanın da bu özelliği aktif etmeniz gerekmektedir.

Adım adım CDC örneğimizi yapalım .

Örnek veritabanı mızı ve test tablomuzu oluşturuyoruz.

CREATE DATABASE cdc_deneme;
GO

USE cdc_deneme;
GO
CREATE TABLE dbo.SQLTR_CDCM
    (
      Id INT IDENTITY(1, 1)
             PRIMARY KEY
             NOT NULL ,
      Name NVARCHAR(100) NOT NULL ,
      E_Mail NVARCHAR(100) NOT NULL
    );
GO

Daha sonra bu tabloyu izlemek istediğimizden tablomuzun bulunduğu veritabanında CDC ‘yi aşağıda ki script ile aktif hale getiriyoruz.

USE cdc_deneme;
GO

SET ANSI_NULL_DFLT_ON OFF;
EXEC sys.sp_cdc_enable_db;
    GO
SET ANSI_NULL_DFLT_OFF ON;

Tablomuz üzerinde CDC ‘yi Enable edelim. (  Bu özelliği aktifleştirmeden önce SQL Server Agent  Servisinin çalıştığından emin olmalısınız !!! )

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
    @source_name = N'SQLTR_CDCM', @role_name = NULL;
GO

 

Tablomuza kayıt girelim .

INSERT  INTO dbo.SQLTR_CDCM
VALUES  ( N'Yusuf KAHVECİ', N'[email protected]' ),
        ( N'Serkan Holosorlu', N'[email protected]' ),
        ( N'Serkan Bireller ACARMAZ', N'[email protected]' ),
        ( N'Neclet KırmızıTuna', N'[email protected]' ),
        ( N'Serkan Arasan', N'[email protected]' ),
        ( N'Tuncay Kalaycı', N'[email protected]' );

Tablo üzerinde UPDATE işlemi gerçekleştirelim.

UPDATE  dbo.SQLTR_CDCM
SET     E_Mail = E_Mail + 'aaa'
WHERE   Id = 1;

Tablo üzerinde bir de DELETE işlemi gerçekleştirelim.

DELETE  FROM dbo.SQLTR_CDCM
WHERE   Id = 3;

Yukarıda yaptığımız tüm işlemeleri özetleyecek olursak ; bir adet veritabanı oluşturduk ve içerisinde bir adet tablo CREATE ettik. Daha sonra bu veritabanı ve tablo üzerinde CDC özelliğini aktif hale getirdik. Tablo içerisine veri bastık ve bastığımız veriler üzerinde güncelleme ve silme işlemi yaptık. Şimdi ise yaptığımız bu işlemlerin sağlamasını yapacak olur isek ;

USE master; 
GO 
SELECT  [name] ,
        database_id ,
        is_cdc_enabled
FROM    sys.databases
WHERE   [name] = 'cdc_deneme';
GO

Yukarıda ki script sonucunda veritabanıız üzerinde CDC özelliğinin aktif olup olmadığını test ettik ve aşağıd aki ekran görüntüsünde is_cdc_enable 1 olarak set eildiği görünmektedir. Bu kolon CDC ‘nin aktif olup olmadığı bilgisini tutmaktadır.

cdc1

Veritabanı üzerinde CDC ‘yi aktifleştirdikten sonra System Tables içerisinde cdc_ ön eki ile başlaan tabloların oluştuğunu göreceksiniz.

cdc2

 

cdc.change_tables CDC’nin aktif olduğu tabloları tutar.
cdc.captured_column Değişikliklerin yakalanacağı kolonların listesini tutar.
cdc.ddl_history Şema yapısındaki değişiklerin tarihçesini tutar.
cdc.lsn_time_mapping Tablo üzerinde yapılan transactional işlemlerin izlerini, lsn bilgisine göre de işlemin hangi sırada olduğunu tutar.

Tablo üzerinde yaptığımız işlemlerin izlerine bakacağımız zaman System Tables ‘ın altında yer alan cdc.dbo_SQLTR_CDCM_CT tablosundan hareketleri gözlemleyebiliriz.

SELECT  *
FROM    cdc.dbo_SQLTR_CDCM_CT;

 

cdc3

__$start_lsn log:  Başlangıç sequence number bilgisini belirtir.
__$seqval: İşlemin gerçekleşeceği sırayı belirtir.

Tablo üzerine yapılan INSERT işleminin izi ;

cdc4

Tablo üzerinde yapılan UPDATE işleminin izi ;

cdc5

Gördüğünüz üzere operation kolonunda 3 olan satır UPDATE işleminden önce ki hali 4 olan ise son güncel hali kolonların.

Tablo üzerinde DELETE işleminin izi ;

cdc6

Operation kolonunda 1 olanlar DELETE işlemini temsil etmekte.

Operation Kolonunda ki Değer ve Açıklamaları :

Operation  Açıklama
1 Satırın silinen kayda ait bilgileri içerdiğini
2 Satırın, eklenen kayda ait bilgileri içerdiğini
3 Satırın, güncelleme öncesi eski değerleri içerdiğini
4 Satırın, güncelleme sonrası kaydın yeni değerleri içerdiğini

Peki seçtiğimiz kolonlarda ki değişiklikleri nasıl yakalarız ?

Burada amaç tablo üzerinde ki tüm kolonları değilde  sadece belli kolonları etkileyen işlemleri nasıl log larız?  Örneğin örnek tablomuzda ID kolonunu etkileyen işlemlerin loglanmasını isteyebiliriz. Bu durumda tabloda CDC ‘yi aktifleştirmek için kullandığımız sp_cdc_enable_table prosedürüne captured_column_list parametresini eklememiz yeterlidir.

USE cdc_deneme;
GO
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
    @source_name = N'SQLTR_CDCM', @role_name = NULL,
    @captured_column_list = 'Id';
GO

Bu işlemden sonra tabloya tekrar kayıt girdiğimizde ;

INSERT  INTO dbo.SQLTR_CDCM
VALUES  ( N'Yusuf KAHVECİ', N'[email protected]' ),
        ( N'Serkan Holosorlu', N'[email protected]' ),
        ( N'Serkan Bireller ACARMAZ', N'[email protected]' ),
        ( N'Neclet KırmızıTuna', N'[email protected]' ),
        ( N'Serkan Arasan', N'[email protected]' ),
        ( N'Tuncay Kalaycı', N'[email protected]' );

Daha sonra tablonun izlerine CDC System tablosundan kontrol ettiğimizde ;

SELECT  *
FROM    cdc.dbo_SQLTR_CDCM_CT;

 

cdc7

Önceden log kayıtlarına baktığımızda tabloda ki tüm kolonları bize gösteriyordu şimdi ise sadece Id kolonuna ait verileri gösterdiğini görmektesiniz. Operation kolonunda ki kayıdın 2 olarak SET edilmesi bize Insert işleminin olduğu bilgisini vermekteydi.

Peki biz DB , Tablo ve Kolon üzerinde aktif edilmesini gerçekleştirdik . Şimdi ise Tablo ve DB üzerinde oluşturduğumuz CDC ‘yi nasıl pasif hale getirebilirz onu göreceğiz.

– Tablo üzerinde CDC ‘yi Pasif hale getirmek ;

USE cdc_deneme;
GO
EXECUTE sys.sp_cdc_disable_table @source_schema = N'dbo',
    @source_name = N'SQLTR_CDCM', @capture_instance = N'dbo_SQLTR_CDCM';
GO

Yukarıda ki script ile CDC ‘mizi tablo üzerinde pasif  hale getirdik.

– DB üzerinde CDC ‘yi Pasif hale getirmek ;

USE cdc_deneme; 
GO 
EXEC sys.sp_cdc_disable_db; 
GO

Yukarıda ki Script ile de DB üzerinde CDC ‘yi Disable hale getirebilirisiniz.

Konu hakkında ki sorularınızı yorum ve mail yolu ile sorabilirisiniz .

İyi Çalışmalar.

Leave a Reply

Your email address will not be published. Required fields are marked *