SQL Server Change Data Capture ile Değişiklik Yapılan Kayıtların Loglanması

SQL Server Change Data Capture ile Değişiklik Yapılan Kayıtların Loglanması

İşletmelerde en büyük sorunlardan biri geçmişe yönelik silinen ya da değiştirilen kayıtların bilgilerinin tutulmasıdır.  SQL 2008 versiyonuna kadar bu işlemi yapabilmek için ya kullanılan paket programda bir değişiklik yaptırmak gerekiyordu ya da her tablonun altına trigger yazılması gerekiyordu. Bu yazılan triggerlar da değişen kayıtları algılayarak sizin istediğiniz formatta herhangi bir log tablosuna yazılmasını  amaçlamaktadır.

SQL Server 2008 ile birlikte ise CDC (Change Data Capture) isminde bir özellik geldi ki kendisi oldukça kullanışlı ve performanslı.  Performanslı diyorum çünkü bu arkadaş doğrudan log üzerinde çalışıyor.

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

UPDATE CUSTOMERS SET ACTIVE=1

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 (buradan sonra CDC diyeceğim) 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.

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

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

CREATE TABLE dbo.Customers(ID int Primary Key NOT NULL,Name varchar(100) NOT NULL,Address
) varchar(500) NOT NULL)

2.Database imizde CDC yi enable yapıyoruz.

EXEC sp_cdc_enable_db

3.Tablomuzda CDC yi enable yapalım.

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

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.

Ayrıca sistemde iki tane de otomatik job oluşur.

Bunlar,

cdc.TestDb_capture job:Logdaki bilgileri okuyarak değişen datayı loglar

cdc.TestDb_cleanup job:Belirlenen tarihten önceki tutulan datayı temizler.

4.Şimdi bir kayıt ekleyelim.

INSERT INTO CUSTOMERS (1,'MER','TRKYE')INSERT INTO CUSTOMERS (2,'AHMET','STANBUL')

5.UPDATE yapalım.

UPDATE CUSTOMERS SET ADDRESS='ANKARA' WHERE ID=1

6.DELETE Yapalım

DELETE FROM CUSTOMERS WHERE ID=1

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?

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.

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.

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şlemlerden Bir takım cdc function larını kullanıyoruz.

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

Buradan aşağıdaki sonucun döndüğünü görüyoruz.

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ı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'

Sonuç:

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

2.Sistemdeki insert, update ve delete leri loglayabiliyor.

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

4.Eğer kayıt loglargen bu kaydın kim tarafından hangi client bilgisayardan hangi user ile hangi ip den hangi bağlantı şekliyle, hangi client access uygulaması ile değiştirildiği gibi detay bilgileri de loglamak istiyor isek çok büyük transactionların olduğu tablolarda performans problemlerine yol açabilir. Çünkü her insert edilen kayıt eş zamanlı olarak başka bir tabloya yazılmakta ve transaction bilgileri de cdc.lsn_time_mapping tablosuna yazılmaktadır. Bu tarz durumlarda trigger gibi alternatif çözümler düşünülebilir.