MSSQL Server CDC(Change Data Capture) Nedir

Bu makalemizde Change Data Capture konusunu ele almış olacağız. Bir tabloya yapılan insert,update ve delete işlemlerini loglayarak hangi kaydın ne zaman değiştiğini ilk ve son değişikliğini bir değişiklik tablosunda tutar. Bu tablolara da Change Table denir. Daha sonra aktarmak istediğimiz hedefte bu değişiklikleri yansıtır.

Benzer işlemleri triger kullanarakta yapabiliriz. Fakat CDC değişiklikleri log dosyasından aldığı için hem daha performanslı çalışır hem de işlemlerimiz ciddi anlamda kolaylaştırır. Triger ise tablonun kendisinden alır birden fazla tabloya yazar buda daha maliyetli ve performans açısından daha kötüdür. CDC tablolarda 3 günlük veri tutulur ondan önceki veriler yoktur silinir.

Aşağıdaki komut ile instance üzerinde CDC aktif edilmiş bir veritabanı var mı onun kontrolü sağlanır.

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

Sonuç 1 veya 0 olarak gelmektedir. 1 aktif olduğunu gösterir.

Hangi veritabanın da CDC özelliğini  kullanmak istiyorsak aşağıdaki komut ile  aktif hale getirilir.

use AdventureWorks2012
go
exec sp_cdc_enable_db
go

Not: Veritabanında CDC özelliğini aktif etmek istediğimizde aşağıdaki hata mesajıyla karşılaşabiliriz. KARŞILAŞABİLİRİZ.

Could not update the metadata that indicates database AdventureWorks2012 is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

Böyle bir sorunla karşılaşırsak ilgili database altında dbowner’ın sa yapılması gerekmektedir.

EXEC sp_changedbowner 'sa'

Kaldığımız yerden devam ettiğimizde CDC özelliğinin AdventureWorks2012 veritabanında aktif edildiğini görmekteyiz.

Aşağıdaki  komut ile hangi  database’lerde  cdc’nin açık olduğu görebiliriz.

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

Aktif ettiğimiz veritabanı altında Tables kısmında  bulunan System Tables altında CDC isminde bir şema ve gerekli tablolar oluşturduğunu görmekteyiz.

• cdc.captured_columns: Kolonların değişiklikleri listesini veren tablo
• cdc.change_tables: Tabloların değişiklikleri listesini veren tablo
• cdc.ddl_history: Bütün DDL işlemlerinin history’sini tutan tablo
• cdc.index_columns: Kayıt altına alınan kolonlara ait index’lerdeki değişikliği tutan tablo
• cdc.lsn_time_mapping: Transaction’ların yaptığı değişikliği ve zamanını tutan tablo

Yukardaki açıklamalardan sora CDC’yi  tablo bazında da aktif hala getirebiliriz

USE [AdventureWorks2012]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'Person',
@source_name   = N'Address',
@role_name     = NULL

Aktif edilen tablonunda System Tables altına geldiğini görmüş oluyoruz.

Şimdi CDC açık bir veritabanında hangi tablolarda aktif olduğunu görmek için aşağıdaki komut kullanılır. Bu işlemi başta cdc’nin  aktif olan hangi veritabanı olduğunu gördükten sonra Use kısmına ilgili veritabanımızı yazıyoruz. Ayrıca system tables altında tablo ve schema ismi görülmektedir.

USE
[AdventureWorks2012]
GO
SELECT [name] FROM sys.tables where is_tracked_by_cdc=1

Önemli bir not: Eğer sql server agent servisi açık değilse hata mesajı alırız Bu hatayı almamak için sql server agent servisini açmamız gerekir. Hata almamızın sebebi CDC’nin iki tane job oluşturması ve Agent servisi kapalı olduğu için jobların execute edilemeyecek olmasıdır. Sistem 2 adet job’ı otomatik olarak oluşturmaktadır. Bunlardan birisi değişen datanın capture edilmesini sağlarken diğeride logları temizlemektedir.

Veritabanı üzerinde herhangi bir update işlemi yaptığımda cdc tablomuzda kaydedildiği gözükür.

1 Delete işlemi, 2 İnsert işlemi, $operation kısmında 3 Update’den önceki hali, 4 Update’den sonraki hali ifade edilmektedir.

Aşağıdaki resimde herhangi bir silme işleminden sonra kaydın CDC History’e düştüğünü görmekteyiz.

CDC tablo bazında tüm kolonlarda yapılan değişikliği izler ve loglar. Eğer tabloda belirli kolonlar  için bu işlemi yapmak istersek CDC’yi tablo bazında aktif ettiğimiz de bu değişikliğide yapmamız gerekir.

USE [AdventureWorks2012]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'Person',
@source_name   = N'Address',
@role_name     = NULL,
@captured_column_list = '[City],[PostalCode]'

Oluşturduğumuz CDC’yi tablo bazlı kapatmak için kullanılacak komut.

USE [AdventureWorks2012]
GO
    EXEC sys.sp_cdc_disable_table
    @source_schema = N'Person',
    @source_name   = N'Address',
    @capture_instance = N'Person_Address'

GO

 Yukarıda bulunan @capture_instance = N’Person_Address’ kısmında aşağıdaki resimdeki gibi  tam ismi yazılmaz. Başındaki cdc ve _CT silinir.

Tablo bazında CDC’yi kapattığımızı aşağıdaki resimde görmekteyiz.

Veritabanı bazında CDC’yi kapatmak için başta açtığımız işlemin tam tersi yapılır.

USE [AdventureWorks2012]
GO
    EXEC sys.sp_cdc_disable_db
GO

Sistem tablolarının gittiğini görmüş olacağız.

Büyük tablolarımızda ve sistemlerimizde CDC özelliği kullanılmaması önerilir. Çok yoğun insert update delete işlemlerinde ciddi anlamda performans sorunları yaşarız.  SQL Server tüm değişiklikleri kendi içerisinde bir “SQL Server Transaction Log” tablosunda tutar. CDC ise, bu log’ları okuyarak anlamlı bir şekilde SQL’de belirtilen tabloya yazar ve değişiklikleri bizim için LOG tablosuna temiz olarak aktarır.

SQL Server 2008 den beri vardır ancak Enterprise edition üzerinde çalışır. Eğer Update cümlesinde kayıt değişmiyor ise gereksiz yer teşkil etmiyor. Triger’da ise yer teşkil ediyor.

Burada oluşan log kayıtlarını temizlemek için ise sp_cdc_cleanup_change_table  komutunu kullanıyoruz.

 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 = N'Person_Address',
       @low_water_mark=@lsn

Aşağıdaki sorgu yardımıyla veritabanında log backup alındıktan sonra neden truncate olmadığını görebiliriz.

select name, log_reuse_wait_desc from sys.databases

Sonuç olarak REPLICATION ifadesi  geliyorsa ve veritabanımızda herhangi bir replikasyon yoksa bu yapı CDC özelliğin aktif edildiğini göstermektedir. CDC özelliği aktifse veritabanımız log backup aldıktan sonra truncate olmayacaktır. Bu özelliğin pasif edilmesi gerekmektedir. Çalıştığım kurumda cdc özelliği aktif edildiği için log dosyasının dolduğunu görmüştüm. Bunun için CDC özelliğini veritabanı altında kapatmıştık. Veritabanı altında bu özelliğin kapatılması sistemimize herhangi bir zararı yoktur. CDC’yi disable ettikten sonra bir süre log dosyasını shrink edemeyeceksiniz. Bunun sebebi CHECKPOINT işleminin henüz gerçekleşmemesidir.

Yukarıdaki komutu tekrardan çalıştırdığımızda sonuç NOTHING geliyorsa veritabanımızın log dosyası artık shrink edilebilir hale gelecektir.

CDC log kayıtlarını işledikten sonra bile log’un truncate olmamasının ana nedeni, SQL Server’ın “her ihtimale karşı” güvenlik mekanizmasıdır. Bu yapıda veriyi tutmasına sebep olur.

Tekrar Faydalarına değinmek gerekirse:

• Update cümlesinde kayıt değişmiyor ise gereksiz yer teşkil etmiyor. Triger’da ise yer teşkil ediyor.
• CDC değişiklikleri log dosyasından aldığı için hem daha performanslı çalışır hem de işlemlerimiz ciddi anlamda kolaylaştırır. Triger ise tablonun kendisinden alır birden fazla tabloya yazar buda daha maliyetli ve performans açısından daha kötüdür.

Cdc özelliği açık olan bir veritabanını başka bir instance’a restore etmek için restore komutunun yanına aşağıdaki ifade eklenmektedir.

Restore Database CDCDB from disk = 'Y:\CDCDB.bak' with keep_cdc

Aynı instance altında farklı veritabanı ismi ile restore edilmesi bu özelliğin kapanmasına sebep olur.

Bu makalede CDC özelliğinin nasıl aktif pasif edildiğini ve gibi özelliklerinin olduğunu  ele almış olduk. Başka bir makalede görüşmek dileğiyle.

Yâ Sîn.(Ey Muhammed!) Hikmet dolu Kur’an’a andolsun ki sen elbette dosdoğru bir yol üzere (peygamber) gönderilenlerdensin.Kur’an, ataları uyarılmamış, bu yüzden de gaflet içinde olan bir kavmi uyarman için mutlak güç sahibi, çok merhametli Allah tarafından indirilmiştir. Yâsîn 1-6