21 Ağu by NURULLAH ÇAKIR

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.captured_columns

CDC ile değişiklikleri kayıt altına alınan kolonların listesini veren tablo

cdc.change_tables

CDC ile değişiklikleri kayıt altına alınan tabloların listesini veren tablo

cdc.ddl_history

CDC aktif edildikten sonra yapılan bütün DDL işlemlerinin history’sini tutan tablo

cdc.index_columns

CDC ile değişiklikleri kayıt altına alınan kolonlara ait index’lerdeki değişikliği tutan tablo

cdc.lsn_time_mapping

Transaction’ların yaptığı değişikliği ve zamanını tutan tablo

 

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.

 

1

Delete İşlemi

2

Insert İşlemi

3

Update Önceki Hali

4

Update Sonraki Hali

 

 

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

Loading

4 Comments

  1. Hocam production ortamda enable edilmiş bir cdc yi disable etmenin, sessionlara veya db yi hang etmesine, veya sunucuya ulaşılamaması gibi bir sonuca sebep olabilir mi sizce ?

    1. cdc’yi disable ettiğinizde sadece cdc için yaratılan nesneler kaldırılıyor. production ortam’daki veri ile ilgili bir sıkıntı olmaz.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir