SQL Server- Change Data Capture

Change Data Capture (CDC) , kaynak tablo üzerinde gerçekleşen INSERT, DELETE, UPDATE faaliyetlerinin SQL Server tarafından izlenmesini saglar. Hangi kaydın ne zaman değiştiğini, ilk ve son halini bir değişiklik tablosunda tutar.

Benzer işlemler trigger kullanarak da yapılabilir fakat CDC değişiklikleri log dosyasından aldığı için daha performanslı çalışır.

CDC Aktivasyonu

CDC aktivasyonu için sunucu rolü SYSADMIN olarak ayarlanmalı

USE BikeStores
GO

EXEC sp_changedbowner 'sa'

İlgili veritabanında CDC’yi aktifleştirme

EXEC sys.sp_cdc_enable_db
-- disable cdc 
-- EXEC sys.sp_cdc_disable_db

CDC aktive edilmiş veritabanı listesi:

SELECT [name],is_cdc_enabled 
FROM sys.databases 
WHERE is_cdc_enabled=1;

Kaynak tabloda CDC’yi aktifleştirme

Buradaki parametreler yazının devamında incelenecek.

EXEC sys.sp_cdc_enable_table
@source_schema=N'production'
,@source_name=N'stocks'
,@role_name=NULL
,@capture_instance=N'production_stocks' 
,@supports_net_changes=1
,@filegroup_name=N'PRIMARY';
GO

Script’i çalıştırdıktan sonra CDC’nin sistem tabloları altında cdc.production_stocks_CT isminde yeni bir tablo oluşur ve yapılan değişiklikler bu tabloda saklanır.

CDC aktive edilmiş tabloların listesi:

USE BikeStores
GO
SELECT name,is_tracked_by_cdc
FROM sys.tables
WHERE is_tracked_by_cdc=1
GO

CDC Parametreleri

• source_schema : Tablonun ait olduğu şema
• source_name : Kaynak tablo
• role_name: Değişiklik bilgilerine erişimi sınırlandırmak için kullanılır. NULL olarak ayarlanılırsa, sadece sysadmin ve db_owner rollerinin üyeleri yakalanan bilgilere erişebilir. Belirli bir role ayarlanırsa (gating role), yalnızca o rolün üyeleri change table’ı görebilir.
• capture_instance: Değişikliklerin kaydedileceği tablonun ismi [optional]
• supports_net_changes : [0,1] Bir kayıttakı değişikliklerin net değişiklik şeklinde gösterilmesi. (Kaynak tablonun primary key veya unique index’e sahip olması şartı vardır.)[optional]
• captured_column_list : Değişiklikleri takip edilecek kolon isimleri. Hepsini takip edecekseniz bu parametreyi eklemeyebilirsiniz.[optional]

@captured_column_list=N'name,id'

• filegroup_name: Change table’ın konumunu değiştirmek için kullanılır. [optional]

Use Case

Primary key’i olmayan bir tablo oluşturalım ve change table’ı inceleyelim.

USE BikeStores
GO
CREATE TABLE production.vip(
 product_name VARCHAR (255) NOT NULL,
 serial_num INT NOT NULL,

);

-- select * from production.vip;

INSERT INTO production.vip(product_name,serial_num) VALUES('golden brush',1)
INSERT INTO production.vip(product_name,serial_num) VALUES('silver soap',2)
INSERT INTO production.vip(product_name,serial_num) VALUES('dolphin case',3)

Ardından cdc’yi aktifleştirelim.

EXEC sys.sp_cdc_enable_table
@source_schema=N'production'
,@source_name=N'vip'
,@role_name=NULL
,@capture_instance=N'production_vip2' 
,@supports_net_changes=1
,@filegroup_name=N'PRIMARY';
GO

Burada karşımıza şöyle bir hata çıkar ⚠️

,@supports_net_changes=1 
Msg 22939, Level 16, State 1, Procedure sys.sp_cdc_enable_table_internal, Line 229 [Batch Start Line 11]
The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Unique index oluşturalım. — supports_net_changes’deki kuralımızı hatırlarsak (Kaynak tablonun primary key veya unique index’e sahip olması şartı vardır.) -

CREATE UNIQUE INDEX uidx_pid
ON production.vip (serial_num);

♲ CDC’yi aktifleştirme adımı (@index_name parametresini ekledik) :

EXEC sys.sp_cdc_enable_table
@source_schema=N'production'
,@source_name=N'vip'
,@role_name=NULL
,@capture_instance=N'production_vip2' 
,@supports_net_changes=1
,@index_name = N'uidx_pid'  
,@filegroup_name=N'PRIMARY';
GO

Bir sonraki başlığımızla bu örnek tabloda yapılan değişiklikler ve onun change table’a yansımasına bakalım.

DML Logları

‘vip’ tablosunda yaptığımız işlemler ve onlara ait logları aşağıda görebilirsiniz.

1 -> DELETE

2 -> INSERT

3 -> UPDATE (değişiklikten önce)

4 -> UPDATE (değişiklikten sonra)

SELECT * FROM cdc.production_vip2_CT;

—

Not-1: Yazıda geçen BikeStores database’ini kullanmak için -> source

Not-2: “SQLServerAgent is not currently running so it cannot be notified of this action.” seklinde bir hata mesaji alirsaniz SQL Server Agent’ini baslatarak sorunu cozebilirsiniz.

References

https://malapatidatabase.wordpress.com/2017/07/04/implementing-change-data-capture-cdc/

http://www.veritabani.gen.tr/2017/08/21/change-data-capturecdc/

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-ver16

https://www.striim.com/blog/a-guide-to-modern-database-replication/