Merhaba,

Bu makalemizde SQL Server tarafında yapılan maniplasyonların (Insert, Update, Delete) geri planda otomatik olarak kayıt altına alınmasını anlatıyor olacağız.

Şimdi bir senaryo düşünelim. Bir ticari yazılımımız var. Bu yazılımı dışarıdan satınaldık ve özelliklerine müdahele edemiyoruz kaynağı bizde olmadığı için.

Sistem üzerinde önemli bir fatura hareketinin değiştirildiğini ya da çıkarıldığını düşünelim. Son dönemlerdeki ticari yazılımlar bunların kayıt altına alınmasına izin veriyor ancak vermeyenler de var. Bu anlamda bizim database bazında bu kayıtların loglanmasına ihtiyacımız söz konusu.

Bu işlerle biraz uğraşanlar için ilk akla gelen tabiki trigger yazılması. Doğru bu bir çözümdür ancak sıkıntıları vardır.

Bu sıkıntılar genel olarak şöyledir;

• Sizin yazdığınız trigger ticari programın kendisinin hata vermesine sebep olabilir ve kayıtların yapılmamasına sebep olabilir. Zira trigger lar transactionların bir parçasıdır ve trigger da gerçekleşen hata tüm transaction ı rollback  yapar.
• Özellikle mevzuat değişimi gereği sıklıkla versiyon geçişi söz konusudur ve bu versiyon geçişlerinde database düzenlemesi yapıldığı için büyük ihtimal trigger larınız silinir ve her seferinde yeniden oluşturacak scriptler oluşturmanız gerekecektir.
• Genel olarak Türkiye şartlarında dönem mali dönem bağımlı çalışmak tercih edildiği için her yıl başında fiziken yeni tablolar oluşturulmaktadır ve bunlar için de trigger lar yeniden yazılmalıdır.

Anlaşılacağı üzere trigger meselesi etkin bir çözümdür fakat biraz zahmetlidir.

Peki bizim yazımızın da konusu olan bu durum için bir çözüm yok mu? Birim fiyatı 5000 TL olan bir malzemenin satış faturasındaki fiyatını 50 TL olarak  değiştiren bir kişiyi tespit etmenin pratik bir yolu yok mudur?

Bu noktada imdadımıza SQL Server Change Data Capture (CDC) dediğimiz özellik yetişiyor. Bu arkadaş yetenekli bir arkadaş. SQL Server’da bildiğiniz üzere tüm manipülasyon işlemleri önce Log dosyasına sonra Data dosyasına yazılır.  Burada log dosyası diye bahsettiğim SQL server’ın sistem log dosyası değil database’in Log dosyasıdır (LDF).

İşte CDC sistem üzerinde Log dosyasını izler ve olan değişiklikleri hızlı bir şekilde kayıt altına alır.

Örnek olarak siz aşağıdaki gibi bir UPDATE cümlesi çalıştırdınız.

CUSTOMERS tablosunun 20 alandan oluştuğunu varsayalım oysa biz sadece bir alanı update ettik. Dolayısıyla SQL Server transaction log üzerinde sadece bir alanlık işlem hacmi söz konusu.

İşte Change Data Capture sadece bu bilgiyi okuyarak arka planda veriyi logluyor.

Siz  CDC yi configure ederken belli bir süreliğine dataları loglayıp belli bir tarihten öncesini sildirebiliyorsunuz. Burada yazacağınız bir script ile önce bu datalara herhangi bir warehouse ortamına alıp daha sonra sistemden temizleyebilirsiniz.

Öncelikle şunu başta belirtmek isterim ki bu özellik SQL Server 2008 den beri vardır ancak Enterprise edition üzerinde çalışır. Tabi test ortamları için developer edition da enterprise ın tüm özelliklerine sahiptir.

Şimdi bu CDC nasıl çalışıyor bir bakalım.

1. Önce bir tablo oluşturalım.

2.Database imizde CDC yi enable yapıyoruz.

3.Tablomuzda CDC yi enable yapalım.

CDC yi enable ettikten sonra system tables altında aşağıdaki tablolar oluşur.

• cdc.captured_columns : Adından da anlaşılacağı üzere değişikliklerin takip edileceği kritik alanları tutar. Bu tablo manuel olarak edit edilebilir durumda olup içeriği değiştirilebilir.
• cdc.change_tables :Hangi tabloların değişiminin takip edileceği bilgisini tutar.
• cdc.ddl_history :Şema bilgilerindeki değişiklikleri tutar.
• cdc.lsn_time_mapping: Asıl Tablo üzerinde yapılan her transaction işlemi bu tablo içerisinde tutulur ve içerisindeki lsn bilgisine göre hangi sırada yapıldığı bilgisi tutulur.

4.Şimdi bir kayıt ekleyelim.

5.UPDATE yapalım.

6.DELETE Yapalım

Görüldüğü gibi tablo üzerinde 2 insert,1 update ve 1 delete işlemi yaptık. Burada sistemde 4 satır kaydın logunun tutulması gerekiyor. Bakalım görebilecek miyiz?

Şimdi tablolarımıza bir bakalım.

CDC.captured_colums tablosu

CDC.ddl_history tablosu

CDC.index_column tablosu

CDC.lsn_time_mapping tablosu

Sistemde loglanan kayıtları ya doğrudan ya da tarih parametresi alan table valued function lar ile görebiliyoruz. Bu tablodaki kayıtlar ise log sequence number (lsn) ile tutuluyor. Bu fonksiyonlar da yine tablo bazlı olarak otomatik oluşuyor. Aşağıdaki resimde bu fonksiyonları görebilirsiniz.

Log kayıtlarını ulaşmak  istediğimizde  eğer tablonun tamamına ulaşmak istiyor isek

select

* from cdc.dbo_customers_CT şeklinde kullanıyoruz.

burada tablo formatı cdc.<schema>_<tablename>_CT şeklinde.

Bunun kullanımını sonucu aşağıdaki gibi.

table valued functionlar  ise aşağıdaki gibi kullanılıyor.

DECLARE @from_lsn binary(10), @to_lsn binary(10);

–minimum lsn numarasını buluyoruz.

SET @from_lsn = sys.fn_cdc_get_min_lsn(‘dbo_customers’);

–maximum lsn numarasını buluyoruz.

SET @to_lsn = sys.fn_cdc_get_max_lsn();

— CDC ile ilgili işlemlerde tablo bazlı oluşan cdc function larını kullanıyoruz.

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customers(@from_lsn, @to_lsn, ‘all’);

Görüldüğü üzere sistem 4 adet fazladan alan ve sistemde yapılan değişiklik üzerine loglanan kayıtları getirdi.

Burada

__$start_lsn log: sequence number bilgisini içeriyor. Buradan kayıt tarihine erişebiliyoruz.

__$seqval: Sequnce değeri yani işlemin hangi sırada gerçekleştiği bilgisine erişmek için bu alan kullanılıyorç

__$operation:2 Insert, 4 Update ve 1 Delete için kullanılıyor.

__$operation:1 Insert,Delete 0 Update

anlamına gelmektedir.

Burada kayıt zamanını elde etmek istediğimizde

sys.fn_cdc_map_lsn_to_time function ını kullanıyoruz.

select sys.fn_cdc_map_lsn_to_time(__$start_lsn) as KayitZamani,

* from cdc.dbo_customers_CT

Burada oluşan log kayıtlarını temizlemek için ise

sp_cdc_cleanup_change_table

komutunu kullanıyoruz.

Kullanımı aşağıdaki gibi.

— aşağıdaki kod  3 gün öncesine ait logları temizliyor.
declare @lsn binary(10);
set @lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,getdate()-3);
exec sys.sp_cdc_cleanup_change_table @capture_instance = ‘dbo_Customers’, @low_water_mark=@lsn

–CDC yi disable etmek için ise 
sp_cdc_disable_db,
sp_cdc_disable_table 
komutları kullanlr 

EXECUTE sp_cdc_disable_table@source_schema = N’dbo’,@source_name = N’Customers’,@capture_instance =N’dbo_Customers’

CDC çalıştırabilmek için SQL Server agent a ihtiyacımız söz konusu.  Sistem 2 adet job ı otomatik olarak oluşturmaktadır. Bunlardan birisi değişen datanın capture edilmesini sağlarken diğeri de logları temizlemektedir.

Sonuç:

• CDC gerçekten çok ihtiyaç duyulan ve çok kullanışlı bir araç.

• Sistemdeki insert, update ve delete leri loglayabiliyor.

• Eğer update cümlesinde kayıt değişmiyor ise gereksiz yer teşkil etmiyor.
• Örneğin: UPDATE CUSTOMERS SET NAME=NAME cümlesini çalıştırdığımızda herhangi bir loglama yapmıyor çünkü değişen bir şey yok.

• Sistemin çalışıyor olması için SQL Server Agent’ın mutlaka çalışması gerekir. Çünkü loğları okuyan bir job bu işleri yerine getirmektedir.

• Yazacağımız bir script ile istediğimiz tablolarda çalıştırıp istemediklerimizde çalıştırmayabiliriz. Hatta çok fazla kolon olan bir tabloda istediğimiz kolonlar için aktif hale getirirken istemediklerimizi es geçebiliriz.

Bir sonraki makalede görüşmek üzere.