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.
1 2 3 |
USE VeritabaniAdi GO EXEC sp_cdc_enable_db |
Komutu ile aktif edebiliriz.
Sıra geldi ilgili tablomuzda bu özelliği açmaya.
1 2 3 4 5 6 |
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.
1 2 3 4 |
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.
1 2 3 4 5 6 |
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.
Merhaba, İyi çalışmalar
Öncelikle Makaleniz benim adıma çok işime yaradı. Burada belirtmiş olduğumuz cdc tablolarımız tam olarak nerede olduğunu göremedim. Biraz araştırdım bu tablolarımıza İlgili database/ Tablo/SystemTables altına oluşturduğunu belirtmek istedim.
Merhaba,
bilgilendirici bir makale olmuş elinize sağlık.
bir kaç konu hakkında da bilgi verirseniz sevinirim.
cdc hangi tablolarda açılması önerilmez ? mesela log tabloları, aşırı derecede insert update delete işlemi olan ortamlarda açılsa ne gibi problemlere sebebiyet verir ? birde bu değişikliklerin yazıldığı tablo veri tabanı hangisi ? farklı bir veri tabanına yönlendirme gibi bir şansımız varmı ? cdc açtığımızda veri tabanı boyutunu ne ölçüde etkiler ?
Teşekkürler.
Hocam merhabalar.
Teşekkür ederim çok güzel sorular sormuşsunuz, bildiğim kadarıyla cevap vermeye çalışayım eksik ve yanlışlık olursa düzeltmenizi rica ediyorum.
Çok fazla insert update delete işlemleri olan tablolarda cdc önerilmez. Cdc yapılan değişiklikleri Log içerisinden yakalayan bir sistemdir ve tabloya çok fazla insert ve update gelmesi bu işlemlerin yakalanmasını güçleştirir ve performans kayıplarına sebebiyet verebilir, LDF boyutları artabilir, tabloya lock koyup koymayacağı konusunda emin değilim.
DB1 veri tabanında yapılan bir değişikliğin DB2 veri tabanında bir tabloya yansıtılması ise direkt olarak mümkün değil diye biliyorum, ancak düzenli olarak çalışan Joblar veya triger ile taşınabilir.
Bu konu ile alakalı bir tecrübeniz varsa bizim için de çok faydalı olur paylaşmanızı rica ederiz. Teşekkürler