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

Log Dosyasının Truncate ya da Shrink Olmaması ve Diski Doldurması Durumu(CDC)

Normal şartlarda full recovery model veritabanınızda düzenli olarak log backup alıyorsanız, alınan her log backup’tan sonra sql server log dosyasını truncate eder ve bu şekilde log dosyasının büyümesi engellenmiş olur.

Eğer veritabanınızda CDC(Change Data Capture) enable edildiyse aldığınız log backuplar sonrasında log dosyası truncate olmayacak ve disklerinizi doldurmaya başlayacaktır.

Change Data Capture hakkında detaylı bilgi almak için “Change Data Capture(CDC)” isimli makaleyi okumak isteyebilirsiniz.

Normalde başka koşullarda da log dosyası backup aldıktan sonra truncate olmayabilir. Log dosyasının neden truncate olmadığını aşağıdaki sorgu yardımıyla öğrenebiliriz.

USE mydatabase
GO
select log_reuse_wait_desc from sys.databases where database_id=DB_ID()

Eğer sonuç olarak  “LOG_BACKUP” geliyorsa veritabanının log backup’ı alınmamış demektir. Log dosyasını shrink etmek için log bakcup almalısınız. Eğer full recovery model kullanıyorsanız düzenli olarak log backup almak için bir job oluşturmalısınız. “Veritabanı Recovery Modelleri” isimli makalemi okumak isteyebilirsiniz.

Eğer sonuç olarak “replication” geliyorsa ve veritabanı üzerinde herhangi bir replication yapılmamışsa bu veritabanınızda CDC’nin enable edildiği anlamına gelir.

Eğer sonuç olarak “ACTIVE_TRANSACTION” geliyorsa “Always ON’daki Primary Veritabanının Log Dosyasının ACTIVE_TRANSACTION Nedeniyle Dolması” isimli makaleyi okumalısınız.

CDC’yi disable etmek için aşağıdaki komutu kullanmalısınız.

USE mydatabase
GO
EXEC sys.sp_cdc_disable_db
GO

CDC’yi disable ettikten sonra bir süre log dosyasını shrink edemeyeceksiniz.

Bunun sebebi CHECKPOINT işleminin henüz gerçekleşmemesidir.

CHECKPOINT işlemi gerçekleştiğinde log_reuse_wait_desc kolonunda nothing yazacaktır ve log dosyası shrink edilebilir hale gelecektir.

CHECKPOINT işleminin detaylarını “Database Checkpoint Nedir” isimli makalemde bulabilirsiniz.

Eğer log dosyasında oluşan problemi önceden tespit edip gerekli önlemleri almak isterseniz “SQL Server Log Dosyası Problem Tespiti” isimli makaleyi okumanızı tavsiye ederim.