Τι είναι το Change Data Capture (CDC) και πως ενεργοποιείται στον SQL Server
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Το Change Data Capture που είναι γνωστό ως CDC καταγράφει ότι DML (insert, update, delete) action πραγματοποιείται σε ένα πίνακα μιας βάσης δεδομένων. Οι αλλαγές αυτές καταγράφονται σε έναν αντίστοιχο πίνακα που δημιουργείται. Μέσα από συστημικά functions μπορούμε να βρούμε τις αλλαγές που έγιναν σε εγγραφές.
Που χρησιμοποιείται
Το CDC είναι πολύ χρήσιμο για ETL ( Extraction, Transformation and Load) διεργασίες όπου θέλουμε ένα σταδιακό φόρτωμα των δεδομένων σε ένα Data Warehouse.
Σε ένα Data Warehouse μας ενδιαφέρει να απεικονίζονται οι αλλαγές μέσα στον χρόνο με ένα διαφορετικό σχήμα, οπότε μια ολική αντιγραφή των δεδομένων και θα καθυστερούσε και δεν θα κάλυπτε τις ανάγκες της εφαρμογής.
Ωστόσο θα μπορούσε να χρησιμοποιηθεί και για ανάγκες auditing με βασικό όμως μειονέκτημα ότι δεν καταγράφει SELECT τα οποία μπορεί να πραγματοποιούνται.
Βήμα βήμα η ενεργοποίηση με παράδειγμα
Έχουμε έναν πίνακα με λίγες εγγραφές:
select * from customer;
Για να ενεργοποιήσουμε το CDC στην βάση δεδομένων:
USE db_test GO EXEC sp_changedbowner 'sqlowner' --Προαιρετικά GO EXEC sys.sp_cdc_enable_db; GO
Ενεργοποιούμε το CDC στον πίνακα που είδαμε πριν:
USE db_test GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'customer', @role_name = N'cdc_viewer', @filegroup_name = null, @supports_net_changes = 1 ; GO
Με το που το ενεργοποιήσουμε θα δούμε ότι έχουν δημιουργηθεί κάποιοι συστημικοί πίνακες (01) στη βάση, δύο καινούργια Jobs στον SQL Server Agent (02) και τέλος ένα ή δύο functions (03) (ανάλογα την παράμετρο που θα διαβάσετε πιο κάτω).
Στην παράμετρο @role_name ορίζουμε τον ρόλο * που θέλουμε να δίνουμε σε χρήστες ώστε να έχουν πρόσβαση στα δεδομένα (φυσικά πρέπει να έχουν και δικαιώμα SELECT στον source πίνακα). Στην περίπτωση που η παράμετρος αυτή δεν οριστεί τότα τα δεδομένα θα μπορεί να τα διαβάσει μόνο όποιος έχει τον ρόλο του sysadmin ή είναι db_owner στη βάση.
*Αν δεν υπάρχει ο ρόλος δημιουργείται αυτόματα.
Με την παράμετρο @supports_net_changes = 1 δηλώνουμε ότι θέλουμε να δημιουργηθεί και το function cdc.fn_cdc_get_net_changes. Αυτό μας επιτρέπει να δούμε τις εγγραφές που αλλάξανε σε ένα συγκεκριμένο χρονικό διάστημα (επιστρέφει μια μοναδική αλλαγή για την κάθε αλλαγή στο διάστημα αυτό).
Ας κάνουμε μερικά DML actions (insert, update, delete) στον πίνακα:
insert into db_test..customer values
('Marios','Georgopoulos','Male','209285915');
update customer set sex = 'Female' where id = 1;
delete from customer where id = 3;
Για να βρούμε τις αλλαγές
Για να δούμε τις αλλαγές που έχουν συμβεί συμβεί στον πίνακα η Microsoft προτείνει την χρήση των functions cdc.fn_cdc_get_all_changes και cdc.fn_cdc_get_net_changes που δημιουργούνται.
Με το cdc.fn_cdc_get_all_changes μπορούμε να δούμε όλες τις αλλαγές που έχουν συμβεί:
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_customer')
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM [cdc].[fn_cdc_get_all_changes_dbo_customer](@from_lsn, @to_lsn, N' all update old');
GO
Βλέπουμε τις αλλαγές που εκτελέσαμε πριν μαζί με κάποιες extra κολόνες με metadata. Με τη κολώνα $start_lsn μας αναφέρεται το commit LSN που πραγματοποίησε την συναλλαγή. Με την $seqval μπορούμε να βάλουμε στην σειρά αλλαγές που πραγματοποιήθηκαν στην ίδια συναλλαγή. Η $update_mask αναφέρει σε bit τις κολώνες που άλλαξαν σε περίπτωση update.
Στην $operation αναφέρετε στον τύπο της αλλαγής που πραγματοποιήθηκε. Πιο συγκεκριμένα:
- 1 = delete
- 2 = insert
- 3 = η εγγραφή πριν το update
- 4 = η εγγραφή μετά το update
Μπορούμε όμως να δούμε με την function cdc.fn_cdc_get_net_changes που αναφέραμε πριν τις αλλαγές που συνέβησαν μέσα συνέβησαν μέσα σε ένα συγκεκριμένο χρονικό διάστημα π.χ. από την προηγούμενη ημέρα έως κάποια συγκεκριμένη ώρα:
DECLARE @from_lsn binary(10), @to_lsn binary(10), @end_time datetime,@begin_time datetime;
SET @begin_time = GETDATE()-1
SET @end_time = '2020-10-25 15:10:00.000'
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
SELECT * FROM [cdc].[fn_cdc_get_net_changes_dbo_customer](@from_lsn, @to_lsn, N' all');
GO
Βλέπουμε ότι μέχρι αυτή την ώρα είχε πραγματοποιηθεί μόνο το πρώτο insert από τις αλλαγές που πραγματοποιήσαμε στον πίνακα.
Για απενεργοποίηση του CDC στον πίνακα
Μπορούμε να βρούμε σε ποιους πίνακες έχει ενεργοποιηθεί το CDC με το πεδίο is_tracked_by_cdc στο συστημικό view sys.tables:
select * from sys.tables where is_tracked_by_cdc = 1
Για να το απενεργοποιήσουμε αντίστοιχα με την ενεργοποίηση του:
USE db_test GO EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'customer', @capture_instance = N'dbo_customer' ; GO
