SQL Server’da Change Data Capture Özelliğini Aktif Etmek

Selam herkese. Bir önceki yazıda CDC ve Change Tracking hakkında ufak bir girizgahta bulunmuştum;

SQL Server’da Veri Değişikliklerini İzlemek: CDC ve Change Tracking Nedir?

Şimdi bu yazıyı teoriden pratiğe dökelim ve örnek bir uygulama yapalım.

Gereksinimler

Docker
SQL Server
AdventureWorks veri tabanı

Bu gereksinimler, bu yazı için olan gereksinimlerdir. Docker ve AdventureWorks veri tabanı tamamen benim çalışma ortamım için gerekli. Siz kendi veri tabanınızda bu çalışmaları gerçekleştirebilirsiniz.

Kurulum

Eğer daha önce Docker üzerinde bir MSSQL kurulumu yapmadıysanız aşağıdaki şekilde kurulumu gerçekleştirebilirsiniz.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=BenimP@r0lam1923" --env MSSQL_AGENT_ENABLED=True -p 1433:1433 --name localSqlServer --hostname localSqlServer -d mcr.microsoft.com/mssql/server:2022-latest

Bu kurulumda bir envorinment variable tanımladık dikkatini çekmediyse belirteyim. MSSQL_AGENT_ENABLED=True bu tanım ile birlikte SQL Server Agent’ını da aktif ediyoruz. Eğer aktif etmezseniz, CDC çalışmaz, değişiklikler algılanmaz.

Eğer daha önceden MSSQL kurduysanız ancak agent aktifleştirme işlemi yapmadıysanız docker’da MSSQL uygulamasının shell’ine girmelisiniz. Daha sonra aşağıdaki komutu çalıştırmanız gerekiyor;

/opt/mssql/bin/mssql-conf set sqlagent.enabled true

Daha sonranda container’ı durdurup baştan başlatmanız yeterlidir. Eğer systemctl bu sistemde mevcut ise aşağıdaki komutu vermeniz yeterlidir;

systemctl restart mssql-server.service

Evet hepsi bu kadar. Ben bu yazı için yukarıda da bahsettiğim gibi AdventureWorks veri tabanını kullanıyorum. Aşağıdaki linkten indirebilirsiniz;

https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak

Daha sonrasında bu restore etmeniz gerekiyor. Bu aşamayı SQL Server Management Studio ya da kullanıyorsanız DataGrip ile yapabilirsiniz.

Evet kurulum aşaması bu kadardı.

Change Data Capture Özelliğini Aktif Etmek

Bu özelliği aktif etmek için öncelikle CDC’yi veri tabanı seviyesinde aktifleştirmemiz gerekiyor. Bunu aşağıdaki SQL kodu ile yapıyoruz;

USE AdventureWorks2022
GO
EXEC sys.sp_cdc_enable_db
GO

Bu değişiklik ile cdc adında bir şema oluşuyor. Ancak henüz bir tablo seviyesinde ayar yapmadık bunu belirteyim. Oluşan cdc şeması aşağıdaki gibi görünüyor;

Burada değişiklik algılanacak kolonlar, tablolar, DDL geçmişi gibi bilgiler yer alıyor. Ancak dediğim gibi henüz tablo seviyesi bir ayar yapmadığımız için şu anda bu tabloların tamamı boş durumda.

Eğer veri tabanı seviyesinde CDC işlemini kapatmak isteseydik aşağıdaki komutu kullanacaktık;

USE AdventureWorks2022
GO
EXEC sys.sp_cdc_disable_db
GO

Her şey okey, önceki yazıda bahsettiğim rollere sahip olduğumuzu varsayarak o kısımlara değinmedim. Artık tablo seviyesinde aktif etme işlemine geçebiliriz. Aşağıdaki SQL kodu ile tablo ve schema seviyesinde aktifleştirme işlemini gerçekleştirebiliriz;

USE AdventureWorks2022
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name   = N'Department',
    @role_name     = NULL,
    @supports_net_changes = 1
GO

Yukarıdaki SQL kodu HumanResources şemasında yer alan Department tablosundaki değişiklikleri dinleyecek asenkron işlemi aktif ediyor.

source_schema — Şema bilgisi
source_name — Tablo bilgisi
role_name — Hangi rolde çalışılacağı bilgisini içerir. Eğer bu parametre NULL olarak ayarlanırsa veya belirtilmezse, varsayılan olarak CDC verilerine sadece sysadmin rolü ve db_owner veritabanı rolünün üyeleri erişebilir. Bu durum, CDC verilerinin güvenliğini sağlamak ve yalnızca yetkili kullanıcıların veya rollerin bu verilere erişimini kontrol etmek için önemlidir.
supports_net_changes — Sadece nihai değişiklik bilgisini içerir. Yani bir satırdaki bir bilgi birden fazla defa değişikliğe uğradıysa en son veriyi tutar.

Bir de yukarıda yer almayan bir değer daha var.

filegroup_name — cdc’nin hangi filegroup içerisinde yer alacağını belirtir. Varsayılan olarak cdc default file group altında yer alır.

Açıklamalar bu kadar. Şimdi bu SQL kodunu çalıştıralım. Ben aşağıdaki gibi bir çıktıyı DataGrip üzerinden aldım.

[2024-03-17 17:56:00] [S0001][22969] Update mask evaluation will be disabled in net_changes_function because the CLR configuration option is disabled.
[2024-03-17 17:56:00] [S0001][14243] Job 'cdc.AdventureWorks2022_capture' started successfully.
[2024-03-17 17:56:00] [S0001][14243] Job 'cdc.AdventureWorks2022_cleanup' started successfully.
[2024-03-17 17:56:00] completed in 6 s 274 ms

Yukarıda 2 adet job’ın tanımlı olduğunu görüyoruz. Bunlardan ilki veri değişikliklerini yakalarken, diğeri de otomatik olarak cdc tablosunda cleanup işlemi gerçekleştirir. Varsayılan cleanup ise 3 günlük bir süreye sahip. Yani retention süresi 3 gün. Şimdi cdc tablolarındaki değişikliklere bir bakalım.

captured_columns tablosunun değiştiğini, yani içerisine yeni veriler eklendiğini görmekteyiz.

change_tables da yine değişlik içermekte. Yani yeni bir satır eklendiğini görmekteyiz;

Tabii bunlar olmadan CDC’nin enabled olup olmadığını veri tabanı seviyesinde ayrıca şöyle bir sorgu ile görebiliriz;

SELECT name, is_cdc_enabled FROM sys.databases

Gördüğünüz gibi veri tabanı seviyesinde aktif olduğunu görebiliriz. Ayrıca CDC şemamıza içi boş olan yeni bir tablo daha katıldı. HumanResources_Department_CT adında bu tablo değişiklik kayıtlarının tutulacağı bilgiyi içerir. Dolu veri ile örneğini inceleyeceğiz.

Bu aşamadan sonra Department tablosunda ilk satırda bir değişiklik gerçekleştireceğim. Engineering olan Name kolonu değerini Engineering Department yapacağım. Ve CT yani Change Tracking tablosuna aşağıdaki 2 satır yansıdı.

Tabloya bakınca 2 farklı veri görüyorsunuz. Aslında neyin önce neyin sonra olduğunu anlayabilirsiniz. Fakat bunları daha kolay anlayabilmenin bir yolu daha var. 2 Satırlık verilerle bunları görmek daha kolay iken, binlerce satırda bu iş daha zor olabiliyor.

__$operation Kolonu Değerleri ve Açıklamaları

1 — Delete sorgularında bu değer kullanılır. Yani bir satırın silindiğini görebiliriz.
2 — Insert işleminde bu değer kullanılır.
3 — Before Update işlemi olarak geçer. Güncelleme işlemi yapılmadan önceki kaydın durumunu temsil eder. Bu, bir güncelleme işleminin “önceki” durumunu belirtir ve genellikle net değişikliklerin hesaplanmasında kullanılır.
4 — After Update işlemi olarak geçer. Güncelleme işleminden sonra kaydın durumunu temsil eder. Bu, bir güncelleme işleminin “sonraki” durumunu temsil eder ve güncellenmiş değerleri gösterir.

Tablo seviyesinde yapılan bu işlemi disable etmek istersek de aşağıdaki SQL kodunu kullanmamız gerekiyor;

USE AdventureWorks2022
GO
EXEC sys.sp_cdc_disable_table
    @source_schema = N'HumanResources',
    @source_name   = N'Department',
    @capture_instance   = N'HumanResources_Department',
GO

Genel olarak CDC işlemi temel olarak böyle. Tablo seviyesinde yapılan işlemler için ayrıca sadece kolon seviyesinde change tracking işlemi yapılabilir. Bu konuya şimdilik girmedim. Kaynaklar tarafında vereceğim linkten ulaşabilirsiniz.

Sonuç olarak CDC mükemmel bir özellik, ancak CDC tablolarında yer alan bu satırları böyle veri tabanından okumak çok zahmetli olabilir. Mesela şöyle bir süreç için zahmetli olabilir.

Satış yapıldığını varsayalım. Bu satışın verisi hem data warehouse’a aktarılacak hem de başka bir API’a bildirilecek. Bu gibi durumlarda bu tabloyu da ayrıca sürekli dinleyen bir çözüm üretmek gerekebilir.

Veri tabanındaki değişiklikleri CDC’yi kullanarak bir mesaj kuyruğuna stream etmek buradaki en iyi çözüm olabilir. Bunun için de Debezium kullanılabilir.

Debezium

Debezium aslında tam olarak bu iş için var. Örnek senaryomuz yukarıdaki senaryo olsun. Debezium, CDC tarafındaki verileri alsın ve belirttiğiniz bir Kafka kuyruğuna bassın. Siz de bu kuyruğu kullanarak data warehouse’a ve API endpointlerine yeni verileri aktarın. Bu gerçekten, custom solution implement etmekten daha da basit hale geliyor.

Hepsi bu kadar. Okuduğunuz için teşekkür ederim.