Sql Server Change Data Capture Aktif Etme

Change Data Capture Nedir?

Change data capture (CDC) bir tabloda yapılan tüm değişiklikleri (insert, update, delete) tutan bir mekanizmadır denilebilir. Özellikle eski tarihli ve yeterince iyi planlanmamış bir tablo, veri ile uğraşan herkesin kabusudur. Tabloda bir güncelleme tarihi veya insert tarihi olmaması, çalışmalarımda ne yazık ki sıklıkla karşılaştığım problem. Özellikle benim gibi 2000 yılında VB ile yazılan uygulamaların aktif olarak hala kullanıldığı bir yerde çalışıyorsanız hayat sizin için çok zor olabiliyor. 🙂

Dönelim konumuza, tabloda yer alan verileri başka bir takım hesaplamalar ve işlemler için bir başka sunucuya taşımak isteyebilirsiniz. Ancak taşıdıktan sonra güncelliği de sağlamanız gerekiyorsa, sürekli tüm tabloyu çekmek efektif bir yöntem olmayabilir. Bunun yerine sadece değişen verileri çekmek elbette ki herkesin tercihidir. Ancak tabloda bir güncelleme tarihi, insert tarihi ve auto incremental bir alan yoksa bu işlemi yapmamız oldukça zor. Eğer bu kolonları da oluşturamıyorsak (hidden column benzeri yöntemlerde mevcut) geriye en mantıklı seçenek cdc oluyor.

SQL Server 2008 ile hayatımıza giren CDC tabloya gelen insert, update ve delete işlemlerini 3 gün boyunca saklayan bir sistem tablosu oluşturuyor. Hatta update işlemleri için ilgili row’un update edilmeden önceki halini ve update edildikten sonraki halinide tutuyor.

CDC Özelliğinin Aktif Edilmesi

Cdc özelliğini ilgili veri tabanı üzerinden aktif etmemiz gerekiyor. Hatta öncelikle ilgili veri tabanı üzerinde cdc aktif mi ona bakalım.

1	SELECT name,is_cdc_enabled FROM SYS.databases

Yukarıda yer alan script ile mencutta yer alan veri tabanlarımızda cdc özelliğinin aktif olup olmadığını görebiliriz.

Eğer ilgili veri tabanımız üzerinde cdc aktif değil ise.

USE VeritabaniAdi

EXEC sp_cdc_enable_db

Komutu ile aktif edebiliriz.

Sıra geldi ilgili tablomuzda bu özelliği açmaya.

EXEC sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'TabloAdi',

@role_name = NULL,

@filegroup_name = N'',

@supports_net_changes = 1

Komutu ile tablomuzda bu özelliği aktif ederiz. Ancak şunu söylemem gerekiyor ki tablomuzda en azından Primery Key bir alanımız olması gerekmektedir.

Ayrıca yukarıda yer alan komut ile tablonun tüm kolonlarını içeren bir cdc oluşturmuş oluyoruz. Eğer tüm kolonlara ihtiyacımız yoksa @captured_column_list = ‘kolon1,kolon2,kolon3’ , parametresini de ekleyebiliriz.

Çalıştırılan bu komut sonrasında iki adet job oluşturulur.

1 2	Job ‘cdc.TabloAdi_capture’ started successfully. Job ‘cdc.TabloAdi_cleanup’ started successfully.

Bu joblardan capture olanı değişiklikleri yakalama işlemini üstlenecektir. Bu yakalama işini transaction log kısmından yapacaktır. Diğer job ise belirli bir tarihten öncesini cdc tablosundan silecektir.

1	SELECT name, is_tracked_by_cdc FROM sys.tables

Çalıştırılan veri tabanında hangi tabloların cdc aktif olduğunu yukarıda yer alan kod ile görebiliriz.

Cdc etkinleştirilen bir tabloda tablonun dizaynı değişirse yeni bir kolon eklenir veya çıkarılırsa cdc çalışmamaya başlayacaktır. Bu gibi bir sorunda cdc yi pasif edip sonra tekrar aktif etmemiz gerekir. Pasif etmek için aşağıdaki kodu kullanabilirsiniz.

exec sys.sp_cdc_disable_table

@source_schema = 'dbo',

@source_name = 'TabloAdi',

@capture_instance = 'ALL'

Bunun haricinde eğer verileriniz sizin için çok önemli ise öncelikle önceki cdc tablosunda yer alan verileri yedekleyin.

1	Select * into dbo_TabloAdi_CT_Eski FROM dbo_TabloAdi_CT

Hatta sonrasında eski cdc verilerinizi yeni cdc tablonuza tekrar aktarabilirsiniz. Bu işlem genellikle sorunsuz gerçekleşse de metadatayı güncelleştiremediğine dair bir hata almışlığım oldu.

Error message

Msg 22832, Level 16, State 1, Procedure

sp_cdc_enable_table_internal, Line 639 [Batch Start Line 0]

Could not update the metadata that indicates table [<schema name>]. [<object name>] is enabled for Change Data Capture. The failure occurred when executing the command 'insert into [cdc].[captured_columns]'. The error returned was 213: 'Column name or number of supplied values does not match table definition.'. Use the action and error to determine the cause of the failure and resubmit the request.

Resolution

To resolve this issue, run sp_cdc_vupgrade after you attach a database on an instance of SQL Server 2016 or SQL Server 2017 on Windows that has Change Data Capture enabled.

Yukarıda yer alan hatayı aldım ve sp_cdc_vugrade komutu ile çözdüm. İlgili Microsoft makalesine tıklayarak ulaşabilirsiniz.

Çok aratılan ve kolaylıkla unutulan bir bilgi olduğu için ekleme gereği duyuyorum.

CDC tablolarında __$operation kolonu;

silinen veriler = 1 ile,
insert edilen veriler = 2 ile,
update edilen verilerin update edilmeden önceki hali = 3 ile,
update edilen verilerin update edildikten sonraki hali = 4 ile gösterilir.

Ayrıca __$start_lsn kolonu yapılan değişikliğin tarihini içerir. Datetime formatına dönüştürmek için sys.fn_cdc_map_lsn_to_time( __$start_lsn ) fonksiyonu kullanılabilir.