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)
) varchar(500) NOT NULL)
2.Database imizde CDC yi enable yapıyoruz.
EXEC sp_cdc_enable_db3.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.
