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 […]

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.

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
GO	
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.

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.

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.

 

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.

Benzer Yazılar

SQL SERVER SERViS RESTART HATASI

SQL Server 2 hafta önce

Bu haftaki yazımızda karşılan bir hata üzerindeki; logları ve çözümünü anlatacağım. Aşağıdaki GÖRSEL-1’de görüldüğü üzere SQL servisini restart ettiğimiz sırada bir hata ile karşılaşıyoruz. Servis running state’e geçemiyor. “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details” şeklinde bir uyarı veriyor. Hatanın çözümüne doğru ilerlerken farklı servis hesaplarıyla veya “Local System” hesabı ile restart etmeye çalıştığınızda servis ilginç bir şekilde ayağa kalkıyor. Ancak Always on sistem çalışıyorsanız farklı servis hesaplarını kullandığınızda always on size haberleşme izni vermiyor. Aynı hesabın şifresi ile ilgili sorunlar olduğu düşünüp hesabın şifresini de değiştirdiğiniz de yine sonuç alamıyorsunuz. Burdan yola çıkıldığında sıkıntı servis hesabında gibi görünüyor olabilir ancak çözüme geçildiğinde regedit üzerinde yapacağımız bir işlem ile sorunu çözüyoruz. Servis hesabının kaydının olduğu regedit kaydını siliyoruz. Regedit üzerindeki servis hesap bilgisi güncellendiğinde sorun çözülmüş olmakta. GÖRSEL-1  Servis restart edildiğinde SQL’in verdiği Error […]

SQL’DE İKİ NODE’UN RESOLVING DURUMA DÜŞMESİ VE ÇÖZÜMÜ

SQL Server 1 ay önce

Bu yazımızda failover olma işlemi esnasında karşılaşılan bir durumdan kısaca bahsedeceğim. Kısa bir yazı olacak ama önemli olduğunu düşünüyorum. Bazen failover olmak istediğinizde cluster secondary’e node’a failover olamaz, hem secondary hem de primary node’unuz resolving durumuna geçer. Bu durumla daha çok otomatik failover olma durumlarında karşılaşılır çünkü sistem failover’a aslında hazır değildir ancak cluster bunu bir şekilde bilemez. Failover olma gerçekleşemez bir anlamda sql cluster askıda kalır ve hiçbir sunucu da sql engine çalışmaya devam edemez. (GÖRSEL-1) GÖRSEL-1 GÖRSEL-1 üzerinde gördüğünüz üzere availability group resolving duruma düşer. Availability replica’lar üzerinde de gördüğünüz üzere primary ve secondary tüm node’lar resolving state’e düşer. Böyle bir durumunda iki farklı çözüm yolumuz var;   Çözüm: ikinci node’a sunucu restart’ı atmak. Bu noktada secondary sql node’a servis restart atmak işe yaramıyor. Zaten db’ler iki taraflı resolving modda. O sebeple ancak sunucu restart atıldığında cluster ayakta olan sunucuyu görüyor ve askıda kalma durumundan ilk başta primary […]

Query Store Nedir?

SQL Server 1 ay önce

Query Store ile birlikte execution planın seçimi ve bu sürecin performansa etkisini anlayabiliriz. SQL Server içerisinde bulunan Query Store özelliği, çalıştırılan sorguların execution planını ve bu sırada oluşan istatistiklerini otomatik olarak yakalar. Böylece query plan değişikliği ile oluşan problemleri de hızlı ve kolay şekilde fark edebiliriz. Elinizde bulunan bir sorguya ait query plan zamanla değişebilir. Bunun birçok sebebi vardır. Tablo yapısına yeni bir column eklenmesi Veri tipinin değiştirilmesi Sorgularda yeni parametrelerin eklenip çıkarılması Verilerde, schemalarda veya sorgu parametrelerindeki değişiklik Burada önemli olan ise bazen bu değişimler sorgunun yavaş çalışmasına neden olur. Query Store ile beraber bu yavaşlığın kök nedenine inmek daha kolay oldu. Ayrıca query store sayesinde ilgili sorguya ait read-write bilgileri ve cpu tüketimi bilgilerine de erişebilirsiniz. Query Store’u veritabanı seviyesinde aktif edebiliyoruz. Veritabanı üzerine sağ tıklayarak properties diyoruz ve Query Store sekmesine geliyoruz. Operation Mode alanından Read Write’ı seçiyoruz. Böylelikle Query Store gerekli bilgiyi toplayabilir ve size ilgili sonuçları […]

5 Yorum

  • Furkan KÜÇÜK 2 Mayıs 2023

    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.

  • […] bir maliyet oluşturduğu için bu yöntem tercih edilmektedir. CDC hakkında detaylı bilgi için Sql Server Change Data Capture Aktif Etme adlı makaleyi inceleyebilirsiniz. Sql Server 2022 ile birlikte Ledger özelliği ile hash yapıda […]

  • […] hakkında daha detaylı bilgi almak için CHANGE DATA CAPTURE NEDİR? adlı makaleyi okumanızı tavsiye […]

  • Ömer akkök 24 Ağustos 2023

    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.

  • Duran BÜYÜKÖZTÜRK 28 Ağustos 2023

    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

Yorum Yaz

Rastgele