Change Data Capture(CDC)

Change Data Capture(CDC), özelikle OLTP sistemlerden data warehouse tarzı sistemlere incremental veri aktarımlarında işimize yarayabilecek bir teknolojidir. Bir tabloya yapılan insert, update ve delete işlemlerini loglayarak, hangi kaydın ne zaman değiştiğini, ilk ve son halini bir değişiklik tablosunda tutar. Değişikliklerin tutulduğu bu tabloyu change table olarak ifade ederiz.

Daha sonra aktarmak istediğiniz hedef’e bu değişiklikleri yansıtır. Benzer işlemleri trigger kullanarak da yapabiliriz fakat CDC değişiklikleri log dosyasından aldığı için hem daha performanslı çalışır hem de işlemlerimizi ciddi anlamda kolaylaştırır.

Her zaman yaptığımızı gibi bir örnek üzerinden konuya devam edelim.

Öncelikle aşağıdaki script yardımıyla instance üzerinde CDC aktif edilmiş bir veritabanı varmı onu kontrol edelim.

USE master
GO
SELECT [name],is_cdc_enabled FROM sys.databases 

Daha sonra CDC’yi kullanmak istediğimiz veritabanında aşağıdaki script yardımıyla CDC’yi aktif hale getirelim.

USE [AdventureWorks2014Yeni]
GO
EXEC sys.sp_cdc_enable_db
GO

Yukarıdaki Script’i çalıştırdıktan sonra veritabanı altında cdc isminde bir şema ve bu şema içersinde bazı tablolar oluşacaktır. Bu tabloların listesi aşağıdaki gibidir. Tabloları CDC’nin aktif edildiği veritabanında sistem tablolarının altında görebilirsiniz.

CDC veritabanında tablo bazlı set edilebilir. Daha önce CDC’nin aktif edildiği tabloları listelemek için aşağıdaki script’i kullanabilirsiniz.

USE [AdventureWorks2014Yeni]
GO
SELECT [name] FROM sys.tables
where is_tracked_by_cdc=1

Aşağıdaki script yardımıyla da tablo bazında CDC’yi aktif hale getirebilirsiniz.

USE [AdventureWorks2014Yeni]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Address',
@role_name     = NULL

Eğer SQL Server Agent servisi açık değilse aşağıdaki gibi bir mesaj alırsınız. Bu hatayı almamak için SQL Server Agent servisini açmanız gerekir. Hatayı almanızın sebebi CDC’nin iki tane job oluşturması ve SQL Server Agent Servisi açık olmadığı için bu job’ların execute edilemeyecek olması. Servisi açtıktan sonra yukardaki script’i tekrar çalıştırmanıza gerek yok.

SQLServerAgent is not currently running so it cannot be notified of this action.

Script’i çalıştırdıktan sonra CDC’nin sistem tabloları altında cdc.dbo_Address_CT isminde yeni bir tablo oluşturduğunu göreceksiniz. Bu tabloda dbo.Address tablosundaki kolonların yanı sıra bakzı ekstra kolonlar olduğunu göreceksiniz.

CDC’nin doğru çalışıp çalışmadığını kontrol etmek için aşağıdaki gibi bir update işlemi yapalım.

USE [AdventureWorks2014Yeni]
GO
UPDATE [dbo].[Address]
   SET  [City] = 'Ankara'
WHERE City='Seattle' AND AddressID=23

Ve daha sonra aşağıdaki script yardımıyla değişikliğin değişiklikleri tutan tabloya yansıyıp yansımadığını kontrol edelim.

USE [AdventureWorks2014Yeni]
GO
SELECT *  FROM [cdc].[dbo_Address_CT]
GO

__$operation kolonunda göreceğiniz değerlerin anlamları aşağıdaki gibidir.

CDC’yi tablo bazından yukarda paylaştığım script ile aktif ettiğinizde tablodaki bütün kolonlarda yapılan değişiklikleri izler ve loglar. Eğer tablodaki belirli kolonlar için bu işlemi yapmak istiyorsanız script’in sonuna aşağıdaki gibi @captured_column_list parametresiyle ilgili kolonları eklemeniz gerekiyor.

USE [AdventureWorks2014Yeni]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Address',
@role_name     = NULL,
@captured_column_list = '[City],[PostalCode]'

CDC’nin aktif hale gelmesiyle veritabanı altında fn_cdc_get_all_changes_dbo_Address isminde bir fonksiyon oluştu. Bu fonksiyonu sorgulayarak da spesifik sonuçlar elde edebilirsiniz. Örnek kullanım;

USE AdventureWorks2014Yeni
GO
DECLARE @baslangic_zamani DATETIME, @bitis_zamani DATETIME, @ilk_lsn BINARY(10), @son_lsn BINARY(10);
--Son 1 Günlük değişiklikleri listelemek için başlangıç zamanını dün olarak set ediyoruz.
SELECT @baslangic_zamani = GETDATE()-1, @bitis_zamani = GETDATE();
--smallest greater than yerine smallest greater than or equal 'da yazabilirsiniz
SELECT @ilk_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @baslangic_zamani);
--largest less than or equal yerine largest less than 'de yazabilirsiniz.
SELECT @son_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @bitis_zamani);
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Address(@ilk_lsn,@son_lsn,'all')

Son olarak CDC’yi aşağıdaki gibi pasif hale getirebilirsiniz.

USE [AdventureWorks2014Yeni];
GO
EXECUTE sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name = N'Address',
    @capture_instance = N'dbo_Address';
GO
--Veritabanı bazında disable etmek istemiyorsanız aşağıdaki script'i çalıştırmayın
EXEC sys.sp_cdc_disable_db
GO

Capture Instance’a aşağıdaki stored procedure’ü çalıştırarak ulaşabilirsiniz.

sys.sp_cdc_help_change_data_capture

CDC’yi aktif ettikten sonra SSIS package’ları ile dataware house uygulamalarını besleyebilirsiniz. Aşağıda örnekleri bulabilirsiniz.

https://docs.microsoft.com/en-us/sql/integration-services/change-data-capture/change-data-capture-ssis