Kritik verilerin bulunduğu veritabanı uygulamalarında en önemli ihtiyaçlardan biri de kullanıcının yaptığı değişikliklerin kayıt altına alınıyor olmasıdır. Bu amaçla ilgili tablolara ait bir log/tarihçe tablosu oluşturulup değişiklikler bu tabloya yazılır. Bunun için ya veritabanı içerisinde ilgili tablolar üzerinde trigger oluşturulur veya uygulama içerisinde değişiklikler uygulanmadan önce değişiklikler tarihçe / arşiv tablolarına aktarılır.
SQL Server 2008 ile sunulan Change Data Capture (CDC) özelliği tablo üzerinde gerçekleşen INSERT, DELETE, UPDATE faaliyetlerinin doğrudan SQL Server tarafından izlenmesini ve kayıt altına alınmasını sağlar. Bu özelliğin en önemli anvantajı doğrudan log altyapısını kullandığı için bizim oluşturacağımız algoritmalara göre daha performanslı olmasıdır. Ayrıca tüm satırı değil sadece değişikliğin yapıldığı kolonları alması da ergonomik bir yapı sağlamaktadır.
MSDN’den alınmış aşağıdaki illustration CDC’nin temel çalışma akışını göstermektedir.


Bu mekanizma kaynak olarak SQL Server transaction log’u kullanır. Tablo üzerinde ekleme, güncelleme ve silme işlemi gerçekleştiği zaman bu bilgiyi log’a yazılır. Transaction Log sistem, bu değişiklikleri CDC’ye giriş olarak gönderir. CDC prosesi de log üzerinden değişiklikleri okuyup ilişkili tablolar iz kaydı oluşturur.
Bir uygulama yapalım. Daha önceki örneklerimizde kullandığımız Ürün tablosunu düşünelim.

Öncelikle ilgili veritabanı için CDC özelliği aktifleştirelim.

EXEC sp_cdc_enable_db

Bu prosedür sonrası sys.databases tablosuna is_cdc_enabled kolonu eklenir. Böylece hangi DB’ler için CDC’nin açık olduğunu öğrenebiliriz.
Urun tablosu için CDC’yi devreye alalım.

EXEC sp_cdc_enable_table
	@source_schema = N'dbo',
	@source_name = N'Urun',
	@role_name = NULL,
	@filegroup_name = N'',
	@supports_net_changes = 1

Komutu çalıştırdığımızda aşağıdaki hata mesajı döndü.
Msg 22939, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 182
The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.
Tablo üzerinde UrunId alanını primary key olarak atayalım. Ardından sp_cdc_enable_table prosedürünü yeniden çalıştıralım. Prosedür sonrası aşağıdaki mesajlar dönecektir.
Job ‘cdc.AK_capture’ started successfully.
Job ‘cdc.AK_cleanup’ started successfully.
Böylece AK isimli veritabanı için capture işlemini yapacak iş emirleri oluşturulmuş oldu. İlk Job, değişiklikleri logdan okuyup ilgili verileri toplar. İkinci Job ise belli tarih öncesi kayıtları temizler.
Bu prosedür @captured_column_list seçmeli parametre de alır. TAblodaki tüm kolonları deği belli kolonları capture edilmesi istenirse ilgili kolonlar bu parametreye comma-separated list olarak girilir.

SELECT name, type, type_desc, is_tracked_by_cdc FROM sys.tables

sys.tables tablosunda hangi tabloları için CDC’nin devrede olduğunu okuyabiliriz. Bir diğer değişiklik sözkonusu veritabanın sistem tabloları altında cdc şemasına bağlı aşağıdaki tabloların oluşmasıdır.

• cdc.change_tables : CDC’nin takip altına aldığı tabloları içerir.
• cdc.captured_columns : Tablolarda capture edilecek kolonları içerir.
• cdc.ddl_history : ALTER TABLE komutuyla tablo yapısında yapılan değişiklikleri içerir.
• cdc.lsn_time_mapping : Transaction izleri bu tabloda tutulur böylece sırasıyla hangi işlemleri yapılacağı buradan öğrenilir.

Şimdi demo yapmaya geldik. Urun tablosunda aşağıdaki değişiklikleri yapalım.

--Tabloya yeni kayıt ekleyelim
INSERT Urun VALUES('U7',6,75)

--2 kaydı güncelleyelim
UPDATE Urun SET Fiyat=150, KategoriId=3 WHERE UrunId=5
UPDATE Urun SET Fiyat=100 WHERE UrunId=1

--5.kaydı silelim
DELETE Urun WHERE UrunId=5

--Tablo üzerinde şema değişikliği yapalım
ALTER TABLE Urun ALTER COLUMN UrunAdi VARCHAR(3)
ALTER TABLE Urun ADD KDV int

Tablodaki bu değişiklikleri okumak için cdc.dbo_Urun_CT tablosu veya aşağıdaki fonksiyonlar kullanılır. Tablo sonucu döndüren bu fonksiyonlar parametre olarak değişikliğin yapıldığı ilk ve son LSN değerlerini alır.

SELECT * FROM cdc.dbo_Urun_CT

Veya

DECLARE @begin_lsn binary(10), @end_lsn binary(10)
-- Urun değişikliğinin başladığı ilk LSN değeri
SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_Urun')
-- Urun değişikliğinin başladığı son LSN değeri
SELECT @end_lsn = sys.fn_cdc_get_max_lsn()

-- Bu 2 LSN Arasındaki değişiklikler
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Urun(@begin_lsn, @end_lsn, 'all');
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Urun(@begin_lsn, @end_lsn, 'all');

__$start_lsn log: başlangıç sequence number bilgisini belirtir.
__$seqval: İşlemin gerçekleşeceği sırayı belirtir.
__$operation: 1 = delete, 2 = insert, 3 = update (update öncesi değerler), 4 = update (update sonrası değerler)
Belli tarih aralığında değişiklikleri görmek için cdc.lsn_time_mapping tablosu sorgulanır.
Log kayıtlarını temizlemek için sp_cdc_cleanup_change_table prosedürü, tablo için CDC’yi devre dışı bırakmak için sys.sp_cdc_disable_table prosedürü, database seviyesinde CDC’yi kapatmak için de sys.sp_cdc_disable_db prosedürü kullanılır.

exec sys.sp_cdc_disable_table
  @source_schema = 'dbo',
  @source_name = 'Urun',
  @capture_instance = 'dbo_Urun' -- veya 'all'