SQL Server’da Veri Değişimlerini Yakalamak

SQL Server’da kullanıcı veritabanlarına yapılan upsert ve delete işlemlerini yakalamayı sağlayan birçok mekanizma bulunmakta ve üstelik geliştiriciler kendi mekanizmalarını da oluşturabilmektedir. Bu işlemlerin izlenmesi gerek bazı uygulamaların geliştirilmesi, Data Warehouse ortamında işlem takibi gibi güvenlik amacıyla, gerekse SQL Server Instance’ları arasında veya SQL Server ve uygulamalar arasında en güncel verinin paylaşılması gibi birçok önemli iş sürecinin yürütülmesi ve yönetimi için önemlidir. Yapılan işlemler sonucunda tablolarda yer alan verilerin hangi tabloda, ne zaman ve neyin değiştiğini yakalayarak verinin geçmişteki ve değişime uğradıktan sonraki haline sahip olunmaktadır.

Bu değişiklikleri yakalamak için Trigger, Timestamp ve Stored Procedure kullanımı gibi çok değişik yöntemler göz önüne gelmiştir. SQL Server 2008 ile gelen Change Data Capture ve Change Tracking özellikleri ile veritabanı düzeyinde tablolara gerçekleştirilen tüm DML işlemlerinin Log’larının tutulması işlemi, Trigger’lar yerine artık bunlarla yapılmaya başlanmıştır. Değişiklikleri Log dosyasından okuması sebebiyle, değişiklikleri kısa sürede yakalayabilmesi gibi ayırt edici özelliklerle gelmiştir.

Change Data Capture yapısının Change Tracking yapısından farkı, verilerin değiştiği andan itibaren izlemeye başlama süreleri ile alakalıdır. CDC özelliğinde değişiklikler asenkron olarak yakalanmakta yani değişiklikler işlendikten sonra Transaction Log’dan okunmaktadır. Change Tracking’de ise değişiklikler senkron olarak yani DML işlemi yapıldığı anda yakalanmaktadır. Change Data Capture özelliği SQL Server Agent’a ihtiyaç duyarken Change Tracking özelliğinin çalışmak için herhangi bir şeye bağımlılığı yoktur. Diğer yöntemlere nazaran bu özelliklerin avantajları şu şekilde sıralanabilir:

• Geliştirme süresi kısalır. SQL Server 2019 (15.x) versiyonunda bu özellikler mevcuttur.
• Schema değişiklikleri yapmaya gerek yoktur. Delete gibi işlemleri izlemek için sütun eklemeniz, trigger veya herhangi bir tablo oluşturmanız gerekmez.
• “Gömülü Temizleme” mekanizması sayesinde yakalanan değişiklik kayıtları için temizleme işlemi arka planda otomatik olarak gerçekleştirilir. Tabloda depolanan verileri elle temizlemeye gerek yoktur.
• Değişiklik bilgilerini elde etmek için fonksiyonlar bulunmaktadır.
• DML işlemlerinin kaydı tutulurken sistem kaynakları az kullanılır. Senkron değişiklik izleme işlemi her zaman bir miktar ek yüke sahip olacaktır, fakat Trigger kullanımı gibi diğer türdeki yöntemlere nazaran kaynak kullanımı en aza indirilebilmektedir.
• Veri değişikliklerini izleme, verinin değiştiği transaction commit‘e dayanmaktadır. Değişikliklerin sırası transaction’ın commit süresine bağlıdır. Böylece uzun süren ve çakışan transaction’lar olduğunda güvenilir sonuçların elde edilmesi sağlanır. Timestamp kullanan diğer izleme yöntemleri, bu senaryoları ele alacak şekilde özel olarak tasarlanmalıdır.
• Bu yapıların yapılandırılması ve yönetimi için kullanabileceğiniz standart araçlar mevcuttur. SQL Server 2019 (15.x) versiyonunda standart DDL ifadeleri, SQL Server Management Studio, Catalog View’lar ve güvenlik izinleri ile bu işlemler gerçekleştirilebilir.

Change Tracking

SQL Server Instance’ları arasında veya SQL Server ve uygulamalar arasında anlık veri senkronizasyonu sağlar. SQL Server üzerinde tabloda DML işlemi gerçekleştirildiği anda verideki değişiklik uygulamaya da anında yansıtılır. Daha çok geliştiriciler için çıkarılmış bir özelliktir.

Özelliğin etkinleştirildiği tabloda, herhangi bir DML işleminde tablo hemen izlenmeye başlanır ve hangi satırlarda veri değişikliğinin gerçekleştiği bilgisi edinilir. Geçmişe dönük herhangi bir kayıtlama gerçekleştirilmez. DML işlemlerinde minimum sistem yükü ile çalışacak şekilde tasarlanmıştır.

Change Tracking tarafından edinilen bütün bilgiler hafızadaki bir Rowstore içerisine geçici olarak depolanır. Daha sonra her bir checkpoint işleminde hafızadan diskteki belirli bir tabloya aktarılır ve rowstore içeriği tamamen silinir. Change Tracking aktif olan her bir tablo için Change Tracking fonksiyonları tarafından versiyonlama bilgisi elde etmek amacıyla, kullanılan disk içerisinde tutulan ekstra bir tablo oluşturulur. Burada Auto Clean devreye girer. Her Auto Clean çalıştığında Change Tracking özelliğinin aktif oduğu tablolar taranır ve retention periyoduna bağlı olarak diskte tutulan tablolar temizlenir. Auto Clean özelliği açılmadığında bir süre sonra disk dolmaya başlayacaktır. Ayrıca manuel clean işlemi aşağıdaki sp ile yapılabilir.

USE [database_name]
GO
sp_flush_CT_internal_table_on_demand [ @TableToClean= ] 'TableName';

Change Tracking Konfigurasyonu

Change Tracking ilk olarak veritabanı düzeyinde etkinleştirilmeli ardından istenilen tablolarda etkinleştirilmelidir. Aşağıdaki kod ile hangi veritabanında Change Tracking özelliğinin etkin olduğunu görebiliriz.

SELECT * FROM sys.change_tracking_databases;

Bu özelliği veritabanı düzeyinde aktif edebilmek için sysadmin rolüne sahip olunması gereklidir. İlk olarak aşağıdaki kod ile veritabanı düzeyinde özelliği etkinleştirelim:

ALTER DATABASE AdventureWorks2019
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

Aşağıdaki kod ile hangi tabloda Change Tracking özelliğinin aktif olduğunu görebiliriz:

USE [AdventureWorks2019]
GO
SELECT * FROM sys.change_tracking_tables;

Şimdi de istediğimiz bir tablo üzerinde Change Tracking özelliğini aktif hale getirelim. Aktif edilecek tablo üzerinde primary key bulunması zorunludur. Bu işlem için db_owner rolüne sahip olunması gereklidir.

ALTER TABLE Employee
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

Ek olarak, Change Tracking enable edildiğinde oluşturulan tablolar hakkında sys.internal_tables kataloğundan bilgi alınabilir.

Veri Değişikliklerini İzlemek

Change Tracking özelliği aktif hale getirildiğinde yakaladığı değişimleri öğrenmek için sistem fonksiyonları bulunmaktadır.

• CHANGETABLE (CHANGES) : Bir tabloda belirli bir sürümden bu yana gerçekleşen tüm değişiklikler için izleme bilgilerini döndürür.
• CHANGETABLE (VERSION) : Bir tablodaki belirli bir satır için en son değişiklik bilgilerini döndürür.
• CHANGE_TRACKING_MIN_VALID_VERSION() : CHANGETABLE fonksiyonunu kullanırken, belirtilen tablodan değişiklik bilgilerini elde etmek için geçerli olan en düşük sürümü döndürür.
• CHANGE_TRACKING_CURRENT_VERSION : Son commit edilmiş transaction ile ilişkilendirilmiş bir sürüm alır. Değişiklikleri bir sonraki numaralandırmanızda, CHANGETABLE seçeneğini kullanarak bu sürümü kullanabilirsiniz.
• CHANGE_TRACKING_IS_COLUMN_IN_MASK : CHANGETABLE (CHANGES …) fonksiyonu tarafından döndürülen SYS_CHANGE_COLUMNS değerini değerlendirir.
• WITH CHANGE_TRACKING_CONTEXT : Veri değişikliğinin uygulamadan mı yoksa başka bir yerden mi yapıldığını belirlemek amacıyla komutu çalıştıran gönderen kimliği gibi değer oluşturmayı sağlar.

Disable Edilmesi

Veritabanı düzeyinde şu kodla devredışı bırakılabilir. Veri tabanı düzeyinde devredışı bırakmadan önce ilgili veritabanı içerisindeki özelliğin aktif olduğu bütün tablolarda devredışı bırakılmalıdır.

ALTER DATABASE AdventureWorks2019 SET CHANGE_TRACKING = OFF;

Tablo düzeyinde ise şu kodla devredışı bırakılabilir:

ALTER TABLE Employee DISABLE CHANGE_TRACKING;

Change Data Capture (CDC)

Change Tracking özelliğinde olduğu gibi CDC etkinleştirildiğinde DML işlemleri hemen takibe alınır ve değişiklikler izlenir. Sadece SQL Server 2016 öncesinde sadece Enterprise sürümünde etkinleştirilebilirken SQL Server 2016 ve sonrasında bütün sürümlerde etkinleştirilebilir. Change tabloları ayrı bir Filegroup ve veri tabanı üzerine kaydedilmesi Microsoft tarafından önerilmektedir.

SQL Server’da tablo üzerinde gerçekleşen her bir DML işlemi transaction log’a kaydedilir, yapılan değişiklikler buradan okunarak, takip edilen tablonun yapısal kopyası oluşturulur. Bu tablolar, orijinal tabloyla aynı sütunlara ve değişiklik ayrıntıları için fazladan sütunlara sahiptir. Bu sütunlar:

• __$start_lsn ve __$end_lsn sütunları: SQL Server tarafından değişiklik yapılan kayda atanan commit log sequence number (LSN) bilgisini tutarlar.
• __$seqval: Aynı transaction işlemi içerisinde gerçekleşen diğer işlemler arasındaki sırasını belirler.
• __$operation: Hangi DML işleminin yapıldığını saklar (delete=1, insert=2, update =3 (önce), update=4 (sonra))
• __$update_mask: Orijinal sütundaki her bir sütuna karşılık gelen bit mask. Hangi sütunda işlem yapıldığını belirlemeye yarar.

Değişikliklerin kayıtlandığı tablolarda:

• Her bir INSERT işlemi gerçekleştiğinde bu işlemle alakalı bir satır yazılır.
• Her bir DELETE işlemi gerçekleştiğinde bu işlemle alakalı bir satır yazılır.
• Her bir UPDATE işlemi gerçekleştiğinde bu işlemle alakalı iki satır yazılır. Birincisi satırın update edilmeden önceki hâli, ikincisi ilgili satırın update edildikten sonraki hâlidir.

Bu tabloyu kullanarak orijinal tabloda yapılan veri değişikliği ile alâkalı detaylı bilgi alınabildiği için aynı zamanda bir audit çözümüdür, ancak farklı filegroup’labildiği için süreç derinleşmektedir. Audit rapor yükü artacaktır. Sonuç olarak audit aracı olarak kullanıldığında bakımının yapılması ve iyi yönetilmesi gerektirir.

Ayrıca OLTP veritabanındaki her veri değişiminde T-SQL sorguları veya ETL metotlarıyla OLAP sistemlerine veri geçişi sağlanmaktadır.

CDC Konfigürasyonu

Aşağıdaki kod ile hangi veritabanında CDC’nin etkin olduğunu görebiliriz.

USE [master]
GO
SELECT name, is_cdc_enabled FROM sys.databases 

Sonuçta görüldüğü üzere tüm veritabanlarında devre dışı. Varsayılan olarak CDC devre dışıdır. Sistem veritabanlarında CDC özelliği kullanılamaz, sadece kullanıcı veritabanları üzerinde aktif edilebilmektedir. Ayrıca bu özelliği kurabilmek için de sysadmin rolüne sahip olunmalıdır. CDC özelliğini bir örnekte aktif hâle getirelim.

Aşağıdaki script ile AdventureWorks2019 veritabanı üzerinde CDC özelliğini aktif hale getiriyorum.

USE [AdventureWorks2019]
GO
EXEC sys.sp_cdc_enable_db
GO

Şimdi de istediğimiz bir tablo üzerinde CDC özelliğini aktif hale getirelim:

exec sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'ExTable' ,
@role_name = 'CDCRole',
@supports_net_changes = 1
select name, type, type_desc, is_tracked_by_cdc from sys.tables

Bu işlemlerden sonra ilgili veritabanında yeni Audit tabloları, Stored Procedure’lar ve fonksiyonlar oluşturulacaktır. Artık yapılan değişiklikleri tespit etmek amacıyla Transaction log taranacak ve edinilen bilgiler buradaki tablolara işlenecektir:

Oluşturulan tablolar şu şekilde olacaktır:

cdc.<capture_instance>_CT : İlişkili tablosundaki bir sütunda yapılan her değişiklik için bir satır döndürür.
cdc.captured_columns : Değişiklik yakalanan instance’da izlenen her sütun için bir satır döndürür.
cdc.change_tables : Veritabanındaki tabloda her değişiklik için bir satır döndürür.
cdc.ddl_history : Değişiklik verisi yakalaması için etkinleştirilmiş tablolarda yapılan her veri tanımı dili (DDL) değişikliği için bir satır döndürür.
cdc.lsn_time_mapping : Değişiklik tablosunda satır içeren her transaction için bir satır döndürür. Bu tablo, log sequence number (LSN) kesin değerleri ile işlemin gerçekleştirildiği zaman arasında eşleştirmek için kullanılır.
cdc.index_columns : Değişiklik yapılan tabloyla ilişkili her index sütunu için bir satır döndürür.
dbo.cdc_jobs (Transact-SQL) : Veri yakalama aracısı işleri için yapılandırma parametrelerini döndürür.

Sistem tablosu sys.tables içerisine is_tracked_by_cdc isminde CDC özelliğinin aktif olup olmadığını bildiren yeni bir sütun eklenecektir.

Yeni sistem tablolarının yanı sıra agent servisi ile çalışan yeni job’lar, sistem sp’leri ve fonksiyonlar da oluşturulacaktır. Oluşturulan job’ların çalışabilmesi için agent servisinin aktif hale getirilmesi gereklidir.

Veri Değişikliklerini İzlemek

Veri değişikliklerini izlemek için aslında change tablolarına DML türünü belirterek sorgu atılabilir.

select * from change_table where __$operation = 1

Fakat bu tablolara direk sorgu atılması Microsoft tarafından önerilen birşey değildir. Bunun için CDC.fn_cdc_get_all_changes() sistem fonksiyonu kullanılmalıdır. Bu fonksiyon ile @from_lsn, @to_lsn, @row_filter_option gibi parametreler belirtilerek istenilen sonuçlara sistematik bir yoldan ulaşılabilir.

Veritabanı Restore İşlemleri ve KEEP_CDC

CDC aktif bir veri tabanını yedekten dönerken birçok senaryo bulunmaktadır.

CDC aktif olan bir veritabanı yedeği veya log dosyası başka bir sunucuya restore edilirken CDC bu işlemi doğru bir şekilde yapabilmek için restore ederken KEEP_CDC seçeneği eklenmelidir. Eğer eklenmezse hata verecektir.

Veritabanını KEEP_CDC ile restore etmekle CDC jobları oluşturulmamaktadır. Veritabanını restore edildikten sonra Transaction Log’dan değişiklikleri okumak için kullanılan CDC Job ve Cleanup joblarını baştan oluşturmanız gerekmektedir. Bunun için sys.sp_cdc_add_job sp’sini kullanabilirsiniz. CDC job konfigürasyonları ile alâkalı blog yazısına şuradan göz atabilirsiniz.

NORECOVERY seçeneğiyle yedek restore ederken KEEP_CDC seçeneği kullanılamaz.

Veritabanı Düzeyinde Disable Edilmesi

Hem veritabanı düzeyinde hem tablo düzeyinde CDC disable edilebilir. Her bir tabloda disable etmeye gerek kalmadan sys.sp_cdc_disable_db sp’sini direk çalıştırarak veritabanı üzerindeki CDC aktif hale getirildiğinde oluşturulan tüm tablo, sp ve fonksiyonlar silinerek bu özellik disable edilebilir. Bu işlemi veritabanı düzeyinde yapabilmek için sysadmin rolüne sahip olunması gereklidir.

İlk olarak veritabanında etkin olup olmadığı tespit edilir. is_cdc_enabled değeri 1 olarak gözüküyorsa ilgili veritabanında CDC özelliği aktiftir.

USE master
GO
SELECT name, database_id, is_cdc_enabled 
FROM sys.databases   
GO

Ardından ilgili veritabanında disable sp’si çalıştırılır ve işlem tamamlanır.

GO
USE [database_name]
GO
EXEC sys.sp_cdc_disable_db                   
GO

Eğer CDC aktif halde olan bir veritabanı sunucudan silinirse, o veritabanı ile alakalı tüm CDC job, sp ve fonksiyonları da silinecektir.

Tablo Düzeyinde Disable Edilmesi

Bu işlem sys.sp_cdc_disable_table sp’si ile yapılmaktadır. Disable edildikten sonra ilgili tabloda değişiklikleri yakalamak için kullanılan tüm CDC fonksiyonları ve kayıtlı yapılan değişiklikler de silinecektir. Bu işlemin tablo düzeyinde yapılabilmesi için db_owner rolüne sahip olunması gereklidir.

İlk olarak tabldoa etkin olup olmadığı tespit edilir. Eğer sys.tables catalog view’da is_tracked_by_cdc değeri 1 olarak gözüküyorsa ilgili tabloda CDC özelliği aktiftir.

USE [database_name]
GO
SELECT name, is_tracked_by_cdc 
FROM sys.tables
GO

Ardından ilgili veritabanında disable sp’si çalıştırılır ve işlem tamamlanır.

USE [database_name]
GO
EXEC sys.sp_cdc_disable_table         
@source_schema = 'dbo' 
, @source_name = 'Employee' 
, @capture_instance = N'dbo_EmployeeE'
GO

İlgili tabloda CDC disable edildikten sonra, veritabanında başka CDC özelliğinin aktif olduğu herhangi bir tablo yoksa tüm CDC job, sp ve fonksiyonları da silinecektir.

Daha fazla bilgi için Change Data Capture (SSIS) overview makalesine ve Change Data Capture Yönetimi ve İzlenmesi makalesine göz atabilirsiniz.

CDC aktif veritabanı restore senaryolarına bu linkten ve bu linkten göz atabilirsiniz.

Change Data Capture ve Change Tracking Karşılaştırması

Özellik	Change Data Capture	Change Tracking
DML işlemleri	Evet	Evet
Historical Data	Evet	Hayır
Sütun değişimi	Evet	Evet
DML türü	Evet	Evet

Kaynaklar:
Configuring and Managing Change Tracking
Disable Change Data Capture (CDC)
RESTORE Statements – Arguments (Transact-SQL)
SQL Server Change Data Capture Tips
Change Data Capture for auditing SQL Server