• Artykuł

Microsoft SQL Server 2008

SQL Server 2008 – mechanizm Change Data Capture Udostępnij na: Facebook

Autor: Damian Widera

Opublikowano: 6 listopada 2007

Zawartość strony
Wstęp   Wstęp
Jak działa CDC   Jak działa CDC
Szczegółowe informacje o konfigurcji mechnizmu CDC   Szczegółowe informacje o konfigurcji mechnizmu CDC
Mechanizm Change Data Capture od środka   Mechanizm Change Data Capture od środka
Praca z mechanizmem CDC   Praca z mechanizmem CDC
Przykład   Przykład
Podsumowanie   Podsumowanie

Wstęp

Jedną z nowości zaprezentowanych podczas oficjalnego pokazu SQL Server 2008 na konferencji TechEd w Orlando, w czerwcu tego roku, jest mechanizm Change Data Capture (CDC), pozwalający na śledzenie zmian w tablicach wywołanych operacjami DML, takimi jak INSERT, UPDATE lub DELETE. Informacja o tym mechanizmie została zaprezentowana na łamach polskich stron TechNet w lipcu 2007 w artykule Marcina Guzowskiego Co nowego w silniku bazodanowym SQL Server 2008 June CTP. Niniejszy artykuł szczegółowo przybliża administratorom baz danych zagadnienia związane z działaniem, konfiguracją oraz możliwościami mechanizmu CDC.

 Do początku strony Do początku strony

Jak działa CDC

Mechanizm Change Data Capture działa podobnie do replikacji transakcyjnej – cykliczne zadanie (job) pobiera informacje z dziennika transakcji i umieszcza je w specjalnej tablicy (bądź tablicach). Domyślnie, po instalacji instancji SQL Server 2008, mechanizm CDC jest wyłączony i dla każdej bazy danych musi zostać włączony przez członka serwerowej roli sysadmin, a następnie użytkownik będący w roli db_owner może wskazać, w których tablicach będą obserwowane zmiany.

W momencie, w którym pierwsza z tablic zostaje udostępniona dla mechanizmu CDC, uruchamiany jest proces przechwytujący, który rozpoczyna obserwowanie dziennika transakcji bazy danych, ponieważ zmiana w tablicy źródłowej powoduje zapis informacji do dziennika transakcji bazy danych. Mechanizm CDC odczytuje te zmiany i zapisuje je w specjalnej tablicy (bądź tablicach), która jest lustrzanym odbiciem (mirror) obserwowanej tablicy. Oprócz zapisywania zmian danych mechanizm CDC zapisuje także metadane każdej transakcji, które pozwalają zidentyfikować np. jaka akcja spowodowała zmiany w tablicy źródłowej. Dla każdej tablicy udostępnionej dla mechanizmu CDC można utworzyć maksymalnie 2 tablice przechowujące zmiany.

Rys. 1. Działanie mechanizmu CDC.

 Do początku strony Do początku strony

Szczegółowe informacje o konfigurcji mechnizmu CDC

Konfigurowanie mechanizmu CDC składa się z co najmniej 2 kroków, o których wspomniałem powyżej:

  1. udostępnienie przez członka serwerowej roli sysadmin bazy danych dla mechanizmu CDC
  2. udostępnienie przez członka bazodanowej roli db_owner określonych tablic dla mechanizmu CDC

Konfiguracja obydwu kroków jest bardzo prosta i sprowadza się do uruchomienia odpowiedniej procedury. Najpierw wybrana baza danych musi zostać udostępniona dla mechanizmu CDC:

use AdventureWorks

go

EXEC sys.sp_cdc_enable_db_change_data_capture

W celu sprawdzenia, które bazy danych zostały udostępnione dla mechanizmu CDC, należy wykonać poniższe zapytanie:

SELECT  [Name] FROM sys.databases WHERE is_cdc_enabled=1

W momencie, w którym baza danych zostaje udostępniona dla mechanizmu CDC, tworzony jest schemat cdc, użytkownik cdc, tablice z meta danymi oraz inne obiekty systemowe w ramach tej bazy danych. Schemat cdc będzie zawierał wszystkie tablice z metadanymi dla wszystkich tablic, które mechanizm CDC będzie obserwował oraz tablice zmian (służących jako repozytoria), które miały miejsce w tablicy źródłowej. Należy pamiętać, iż schemat cdc oraz użytkownik cdc są utworzone specjalnie dla mechanizmu przechwytywania zamian i takie obiekty nie mogą istnieć już w bazie danych dla innych celów. W takim przypadku należy istniejącym obiektom zmienić nazwy lub je usunąć.

Po udostępnieniu bazy danych dla mechanizmu CDC można wskazać, dla których tablic należy przechwytywać zmiany. Jak wspomniałem, dla każdej z tablic można wskazać maksymalnie 2 tablice, które będą przechowywały informacje o zmianach w tablicy źródłowej.

Procedura składowana sys.sp_cdc_enable_table_change_data_capture pozwala skonfigurować mechanizm CDC dla konkretnej tablicy. Jej ogólna postać jest następująca (za Books Online dla CTP4):

sys.sp_cdc_enable_table_change_data_capture 

    [ @source_schema = ] 'source_schema', 

    [ @source_name = ] 'source_name' ,

    [ @role_name = ] 'role_name'

    [,[ @capture_instance = ] 'capture_instance' ]

    [,[ @supports_net_changes = ] supports_net_changes ]

    [,[ @index_name = ] 'index_name' ]

    [,[ @captured_column_list = ] 'captured_column_list' ]

    [,[ @filegroup_name = ] 'filegroup_name' ]

Znaczenie parametrów jest następujące:

@source\_schema – określa nazwę schematu tabeli

@source\_name – określa nazwę tabeli

@role\_name – określa nazwę roli (na poziomie bazy danych), która to rola będzie kontrolowała dostęp do zmian danych. W przypadku, gdy rola o podanej nazwie nie istnieje, zostanie utworzona. Można zrezygnować z tworzenia tej roli i w takim przypadku należy jawnie podać wartość parametru @role\_name=NULL.

Członkowie ról sysadmin oraz db_owner mają pełny dostęp do danych przechowywanych w tablicach utworzonych przez mechanizm CDC, a innym użytkownikom należy nadać uprawnienia do wykonania komendy SELECT.

Uwaga – w przypadku, gdy specjalna rola bazodanowa została określona poprzez parametr @role\_name to każdy użytkownik nie będący członkiem ról sysadmin lub db_owner musi do tej roli należeć.

@capture\_instance – pozwala określić nazwę instancji tablicy zmian, ponieważ jak wspomniano powyżej – każda z tablic może mieć 2 tablice przechowujące zmiany w niej zachodzące.

@supports\_net\_changes - ustawienie wartości parametru na 1 spowoduje utworzenie funkcji, która będzie zwracała ostatnią zmianę dla rekordu w określonym przedziale czasowym. Funkcja jest tworzona dla każdej instancji, a jej nazwa ma formę cdc.fn_cdc_get_net_changes_<nazwa instancji>. Domyślnie tworzona jest jednak tablicowa funkcja cdc.fn_cdc_get_all_changes_<nazwa instancji>, która umożliwia dostęp do wszystkich zmian danych rekordów w określonym przedziale czasowym.

@index\_name – parametr określa nazwę indeksu tabeli źródłowej (musi to być albo klucz główny albo indeks unikalny). Parametr należy określić w przypadku, gdy wartość @supports\_net\_changes = 1. Należy również pamiętać o podaniu nazwy kolumny (bądź kolumn), na której indeks został założony, w parametrze @captured\_column\_list.

@captured\_column\_list – określa listę kolumn tablicy źródłowej, które powinny być zapisane w tablicy (bądź tablicach) zmian. Domyślnie wszystkie kolumny z tablicy źródłowej są odzwierciedlane przez mechanizm CDC w tablicach przechowujących zmiany.

@filegroup\_name – nazwa grupy plików, w której może zostać umieszczona tablica zmian. W przypadku, gdy parametr nie zostanie podany, to każda tablica przechowująca zmiany jest zapisana w domyślnej grupie plików. Uwaga - grupa plików musi istnieć w momencie wykonania procedury sys.sp_cdc_enable_table_change_data_capture. Ze względów wydajnościowych rekomenduje się utworzenie nowej grupy plików dla tablic przechowujących zmiany przechwycone przez mechanizm Capture Data Change.

Utworzenie instancji przechwytywania zmian w tablicy źródłowej powoduje utworzenie odpowiedniej tablicy zmian w schemacie cdc, której nazwa może być domyślna (w formacie cdc.NazwaSchematu_NazwaTablicy_CT) lub nazwana przez podanie parametru @capture\_instance (w formacie cdc.NazwaInstancji_CT).

W najprostszym przypadku wystarczy wskazać nazwę tablicy oraz schematu, w którym tablica się znajduje:

EXEC sys.sp_cdc_enable_table_change_data_capture 

    @source_schema = N'Production'

  , @source_name = N'Product'

  , @role_name = NULL

W tym przypadku obserwowana będzie tablica Production w schemacie Product z bazy danych AdventureWorks. Utworzona została tablica Production_Product_CT w schemacie cdc. Znaczenie parametru @role\_name objaśniono powyżej.

W przypadku, gdy należy utworzyć drugą tablicę obserwującą zmiany w tablicy źródłowej, dodatkowo należy wskazać nazwę instancji przechwytywania:

EXEC sys.sp_cdc_enable_table_change_data_capture 

    @source_schema = N'Production'

  , @source_name = N'Product'

  , @role_name = NULL

  ,@capture_instance = 'Production_Product2'

W takim przypadku w schemacie cdc zostanie utworzona druga tablica :

Rys. 2. Tablice zmian w schemacie cdc dla źródłowej tablicy Production.Product.

Dla porządku należy tylko dodać, iż administrator baz danych może również sprawdzić, które tablice w kontekście wybranej bazy danych zostały udostępnione dla mechanizmu CDC wykonując poniższe zapytanie:

SELECT [Name] FROM sys.tables WHERE is_tracked_by_cdc=1

W przypadku, gdyby zaistniała potrzeba wyłączenia mechanizmu przechwytywania zmian dla określonej tablicy, należy użyć funkcji sys.sp_cdc_disable_table_change_data_capture, która umożliwia wyłączenie mechanizmu CDC dla jednej bądź wszystkich instancji tablic przechowujących zmiany:

sys.sp_cdc_disable_table_change_data_capture                         

            [ @source_schema = ] 'source_schema', 

      [ @source_name = ] 'source_name' ,

      [ ,[@capture_instance = ] 'capture_instance' | 'all'

Istnieje także możliwość wyłączenia mechanizmu CDC dla całej bazy danych i służy do tego funkcja sys.sp_cdc_disable_db_change_data_capture, która nie przyjmuje żadnych parametrów i musi być wywołana w kontekście bazy danych, dla której mechanizm CDC powinien zostać wyłączony:

use AdventureWorks

go

EXEC sys.sp_cdc_disable_db_change_data_capture

Pora bliżej przyjrzeć się mechanizmowi przechwytywania zmian.

 Do początku strony Do początku strony

Mechanizm Change Data Capture od środka

Dla każdej tablicy źródłowej udostępnionej dla mechanizmu CDC istnieje co najmniej jedna tablica przechowująca jej zmiany. Pierwsze pięć kolumn w każdej tablicy zmian to kolumny zawierające metadane zawierające dodatkowe informacje opisujące zmiany dokonane w tablicy źródłowej. Pozostałe kolumny odzwierciedlają kolumny obserwowanej tablicy, posiadają identyczne nazwy i zazwyczaj typ. Kolumny te przechowują dane jako rezultat dokonywania zmian instrukcjami DML w tablicy źródłowej. Każda operacja INSERT oraz DELETE skutkuje powstaniem jednego rekordu w tablicy przechowujące zmiany. Dla komendy INSERT wiersz będzie zawierał informacje po wykonaniu tej komendy. Analogicznie – tablica zmian zapisze informacje poprzedzające wykonanie komendy DELETE. Operacja UPDATE wymaga dwóch wpisów – jednego, który wstawi wartość kolumny przed dokonaniem zmian oraz drugiego będącego wynikiem zatwierdzenia zmian w tablicy źródłowej. Metadane zawarte w pierwszych 5 kolumnach tablicy przechowującej zmiany pozwalają zidentyfikować rodzaj operacji, jaka została wykonana. Pierwsza kolumna, __$start_lsn zawiera kolejny numer transakcji (log sequence name – LSN) identyfikujący zmiany. Numer LSN nie tylko identyfikuje zmiany, które zostały zatwierdzone w ramach jednej transakcji, ale jest w stanie także pokazać w jakiej kolejności odbyło się zatwierdzenie tych zmian. Kolejność zmian w ramach jednej transakcji zapisana jest w kolumnie __$seqval. Kolumna __$operation przechowuje informacje o rodzaju operacji dokonanej na tablicy źródłowej, wynikiem której jest pojawienie się rekordu w tablicy zmian:

  1. DELETE = 1
  2. INSERT = 2
  3. UPDATE (przed zapisem) = 3
  4. UPDATE (po zapisie) = 4

Kolumna __$update_mask jest maską bitową zmian przeprowadzonych w kolumnach tablicy źródłowej. Jeden bit maski odpowiada jednej kolumnie i dla operacji INSERT oraz DELETE wszystkie bity są ustawiane na wartość 1; z kolei dla operacji UPDATE tylko bity odpowiadające zmianom w konkretnych kolumnach ustawiane są na wartość 1.

Mechanizm CDC rozpoczyna wypełnianie tablic przechowujących zmiany zaraz po tym, jak tylko pierwsza tablica źródłowa zostaje udostępniona dla rejestrowania zmian. Mając wiedzę, w jaki sposób tablice zmian będą używane przez inne procesy, można umiejętnie zarządzać ilością informacji zapisaną w tablicach przechowujących zmiany. Domyślnie proces oczyszczania tych tablic usuwa dane starsze niż trzy dni. Czas ten jest konfigurowalny, ale należy racjonalnie zmieniać jego wartość, biorąc pod uwagę możliwość znacznego powiększenia się tablicy zmian. W momencie rozpoczęcia operacji oczyszczania tablicy zmian data graniczna, poniżej której wszystkie zmiany mają zostać usunięte, zamieniana jest na numer LSN (zawarty w kolumnie __$start_lsn). Wszystkie wiersze mające w kolumnie __$start_lsn wartość mniejszą, niż wyznaczony dla każdej tablicy zmian numer LSN, zostaną usunięte. Dodatkowo, w tablicy cdc.change_tables zostaje uaktualniona kolumna start_lsn zawierająca najmniejszy numer LSN dla każdej tablicy zmian. Istotnym jest zapamiętanie, iż każda tablica zmian może posiadać (i w praktyce tak będzie) inny numer LSN oznaczający początek przechwytywania zmian. Z kolei maksymalny numer LSN jest identyczny dla wszystkich tablic przechwytujących zmiany w ramach jednej bazy danych.

Z tego powodu, podczas pracy z mechanizmem CDC (opisanej w następnym rozdziale artykułu), należy posługiwać się funkcjami zwracającymi minimalną oraz maksymalną wartość numeru LSN dla danej instancji tablicy źródłowej.

 Do początku strony Do początku strony

Praca z mechanizmem CDC

Dane zawarte w tablicach przechowujących zmiany dostępne są poprzez procedury składowane oraz funkcje. Należy pamiętać o aspektach bezpieczeństwa, o których wspomniano powyżej, z których wynika, iż nadanie uprawnień SELECT dla użytkownika nie zawsze okaże się wystarczające i może zaistnieć potrzeba dodania użytkownika do specjalnej roli.

Informacje o konfiguracji mechanizmu CDC są dostępne za pomocą procedury sys.sp_cdc_help_change_data_capture. W przypadku, gdy procedura zostanie wywołana bez żadnych parametrów, zwróci szczegółową informację o każdej tablicy udostępnionej dla mechanizmu CDC (o ile użytkownik ma do niej dostęp). Z kolei procedura sys.sp_cdc_get_data_captured_columns wyświetla w kontekście tablicy przechowującej zmiany bardzo dokładne informacje na temat kolumn dla tej tablicy.

Do uzyskania informacji z tablic przechowujących zmiany służą dwie funkcje tabelaryczne:

  1. cdc.fn_cdc_get_all_changes_<NazwaInstancji> ( from_lsn , to_lsn , '<row_filter_option>' ) - zwracająca wszystkie zmiany z określonego przedziału.
  2. cdc.fn_cdc_get_net_changes_<NazwaInstancji> ( from_lsn , to_lsn , '<row_filter_option>' ) - zwracająca tylko ostatnią zmianę dla każdego wiersza w określonym przedziale.

Obydwie funkcje akceptują taki sam zestaw parametrów. Dwa pierwsze parametry określają przedział, z którego należy wyświetlić dane. Ostatni parametr przekazywany do obydwu funkcji pozwala odfiltrować część zmian i wyświetlić tylko te najbardziej nas interesujące.

W przypadku, gdy potrzebne są wszystkie informacje z określonego przedziału, należy użyć filtra ‘all’, który jest akceptowany przez obydwie funkcje. Otrzymany zestaw wierszy będzie zawierał wszystkie zmiany po ich zatwierdzeniu, a więc nie będzie zawierał informacji o danych sprzed wykonania instrukcji UPDATE. Funkcja fn_cdc_get_all_changes_<NazwaInstancji> akceptuje parametr ‘all update old’, który umożliwia wyświetlenie wszystkich informacji, w tym także dokonywanych przed komendę UPDATE (czyli wyświetlane są dwa wiersze - jeden zawiera informacje sprzed zmiany a drugi po zmianie). Dla wartości ‘all’ opcji ‘<row_filter_option> wartość w kolumnie __$update_mask będzie zawsze wynosić null.

Jeżeli zaistnieje potrzeba określenia, dla których kolumn zostały zmienione dane, to należy użyć opcji ‘all with mask’ dla funkcji cdc.fn_cdc_get_net_changes_<NazwaInstancji>. Analizując wartości z kolumny __$update_mask taką informację uzyskać. Dokładniej objaśniony przykład znajduje się w dalszej części artykułu:

--zmienne @from_lsn oraz @to_lsn określaja zakres zmian

DECLARE @from_lsn binary(10),@to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Pracownicy');

SET @to_lsn = sys.fn_cdc_get_max_lsn();



--funkcja sys.fn_cdc_get_column_ordinal zwróci numer kolumny, dla której należy sprawdzic, czy 

--byćy w niej zmiany

DECLARE @nr_kolumny bit;

SET @nr_kolumny = sys.fn_cdc_get_column_ordinal('dbo_Pracownicy', 'Status');

SELECT @nr_kolumny



--funkcja fn_cdc_is_bit_set sprawdza, czy zmieniono wartość w kolumnie bazując na zapisanej masce

SELECT * ,sys.fn_cdc_is_bit_set (@nr_kolumny, __$update_mask) as 'Zmieniono'

FROM  cdc.fn_cdc_get_net_changes_dbo_Pracownicy (@from_lsn, @to_lsn, 'all with mask')

Ostatnia opcja parametru <row filter option> funkcji cdc.fn_cdc_get_net_changes_<NazwaInstancji> ,‘all with merge’, pozwala na wyświetlenie tylko tych zmian, które były skutkiem wykonania instrukcji DELETE (w kolumnie __$operation będzie wartość 1) lub innej. Wtedy w kolumnie __$operation pojawi się wartość 5 co oznacza operację INSERT lub UPDATE.

Generalnie, funkcja zwracająca wszystkie zmiany jest szybsza niż funkcja zwracająca tylko ostatnie zmiany dla wierszy. W przypadku, gdy wiadomo, że zmiany dotyczą głównie operacji INSERT to wynikiem użycia obydwu funkcji (z filtrami ‘all’ lub ‘all with mask’) będzie ten sam zbiór danych. A więc użycie funkcji cdc.fn_cdc_get_all_changes_<NazwaInstancji> jest w tym przypadku uzasadnione względami wydajnościowymi.

Ponadto, jeżeli nie zdefiniowano klucza głównego lub unikalnego indeksu na tablicy źródłowej, to można użyć tylko funkcji cdc.fn_cdc_get_all_changes_<NazwaInstancji>.

Typowa aplikacja używająca mechanizmu CDC będzie wykonywała okresowe zapytania o zmiany danych wykonując kwerendę na jednej z dwóch omawianych powyżej funkcji. Zasadniczo istnieją dwie możliwości wykonywania zapytań na powyższych funkcjach:

  • poprzez podanie LSN
  • poprzez wprowadzenie daty

jako parametrów określających zakres poszukiwanych zmian.

W pierwszym przypadku aplikacja rozpoczyna poszukiwanie zmian od pierwszej zarejestrowanej zmiany, a następnie zapytuje tyko o zmiany zapisane od czasu ostatniej kwerendy. Pierwsze zapytanie mogłoby mieć następującą postać:

DECLARE @from_lsn binary(10),@to_lsn binary(10);

--wyznacz najmniejszy i najmniejszy zarejestrowany wpis do tablicy zmian

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Pracownicy');

SET @to_lsn = sys.fn_cdc_get_max_lsn();

-- wykonaj zapytanie

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all')

Z kolei każde następne zapytanie zwracałoby już tylko najnowsze zmiany:

DECLARE @from_lsn binary(10),@to_lsn binary(10);

--wyznacz najmniejszy wpis, ale wiekszy od poprzedniego maksimum. Funkcja 

--sys.fn_cdc_increment_lsn dodaje 1 do numeru ostatniego LSN



SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn); 

SET @to_lsn = sys.fn_cdc_get_max_lsn();

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all')

Z punktu widzenia aplikacji pozyskiwanie danych o zmianach w tablicy źródłowej oparte na LSN nie zawsze jest wygodne. Bardziej intuicyjna wydaje się możliwość wskazania czasowego przedziału, wewnątrz którego znajdują się interesujące nas zmiany. Mechanizm CDC oferuje programistom i administratorom baz danych funkcje konwertujące datę na LSN i odwrotnie. Jawne podanie dat powoduje powstanie problemu, a mianowicie może zdarzyć się sytuacja, kiedy zmiany dla górnego zakresu dat wprowadzonego do funkcji nie będą jeszcze zatwierdzone i serwer baz danych zgłosi błąd. W takiej sytuacji należy zapewnić, ażeby kwerenda ‘zaczekała’, aż zatwierdzone zostaną zmiany dla górnego zakresu zapytania. Przykład – cytowany za Books Online – umożliwiający rozwiązanie problemu związanego z brakiem zatwierdzonych zmian po dacie wskazanej w parametrze @to\_time:

DECLARE @to_time datetime, @max_time datetime;

SET @to_time = '12:00:00 31-10-2007';

SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn);

RETRY:

SELECT @max_time = sys.fn_map_lsn_to_time (sys.fn_cdc_get_max_lsn());

    IF (@to_time > @max_time)

        BEGIN

            WAITFOR DELAY '00:10:00'

            GOTO RETRY

        END

SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @to_time);

SELECT * from cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn, @to_lsn, 'all');

Obydwie funkcje mapujące:

  1. sys.fn_cdc_map_lsn_to_time (lsn_value) – zamienia numer LSN na datę
  2. sys.fn_cdc_map_time_to_lsn ( '<relational_operator>' , tracking_time ) – znajduje numer LSN dla podanej daty przy uwzględnieniu dodatkowych warunków.

korzystają z systemowej tablicy cdc.lsn_time_mappings aby zamienić datę na numer LSN i odwrotnie. Bardzo ciekawa jest zwłaszcza druga z wymienionych powyżej, która dla wybranej opcji parametru <relational operator> próbuje odszukać odpowiedni numer LSN:

<relational_operator> ::=

{  largest less than

 | largest less than or equal

 | smallest greater than

 | smallest greater than or equal

}

Po przeanalizowaniu listy dostępnych możliwych opcji można zauważyć, iż zawsze zakładana jest sytuacja, w której dla podanej daty nie istnieje dokładne odwzorowanie na numer LSN.

Ostatnim aspektem pracy z mechanizmem CDC jest możliwość reagowania na zmiany struktury tabeli źródłowej wywołanej komendą DDL (Data Definition Language) - ALTER TABLE.

Po utworzeniu instancji tablicy przechowującej zmiany danych w tablicy źródłowej jej kształt jest już ustalony - zamknięty. Oznacza to, iż w przypadku, gdy z tablicy źródłowej czy to zostanie usunięta kolumna bądź zostanie dodana nowa, to istniejąca tablica zmian nie jest w żaden sposób modyfikowana. Pozwala to na uzyskanie zawsze tej samej struktury danych po wykonaniu zapytania na omawianych funkcjach, zwracających informacje o zmianach. Dla usuniętej kolumny w tablicy źródłowej tablica zmian będzie zawierała wartość NULL, natomiast nowa kolumna – dodana do tablicy źródłowej – nie będzie w tablicy zmian widoczna. Istnieje jeszcze jedna możliwa zmiana metadanych tablicy źródłowej, a dzieje się tak w przypadku zmian typu danych zawartych w istniejącej kolumnie. Tego typu zmiany są propagowane na tablice zmian w momencie, kiedy w dzienniku transakcji zostanie wykryte odpowiednie polecenie DDL. W sytuacji, kiedy zostanie usunięta tablica źródłowa poleceniem DROP TABLE, to tablice zmian, funkcje, wszystkie wpisy do tablic konfiguracyjnych zostają usunięte.

Z tego właśnie powodu dla każdej tablicy źródłowej mogą istnieć dwie tablice przechowujące zmiany danych, ponieważ jedna z nich będzie np. zawierała informacje sprzed wykonania komend DDL a druga może odzwierciedlać (należy ją utworzyć ręcznie, tak samo jak utworzono pierwszą tablicę, ale musi mieć inną nazwę) nową strukturę tablicy źródłowej. Każda utworzona tablica posiada swój zestaw funkcji do pobierania danych, a od momentu utworzenia drugiej tablicy dla obydwu tablic będą zapisywane zmiany z tym samym numerem LSN.

Pozwala to nie zakłócać działania aplikacji, które bazują na tablicy zmian – w sytuacji zmian w strukturze tablicy źródłowej aplikacja nie otrzymuje nowego zestawu danych, co mogłoby spowodować jej niepoprawne działanie.

Wszystkie opisane powyżej zmiany wykonane za pomocą komend DDL są monitorowane i zapisane w tablicy cdc.ddl_history. Dostęp do wszystkich zmian dla określonej tablicy zmian jest możliwy przez wywołanie procedury składowanej:

sys.sp_cdc_get_ddl_history [ @capture\_instance = ] 'capture_instance'

 Do początku strony Do początku strony

Przykład

Niniejszy przykład w sposób praktyczny podsumuje teoretyczną wiedzę zawartą w artykule. Teoria opisana powyżej była niezbędna dla zrozumienia reguł rządzących mechanizmem CDC, ale najlepiej zgromadzoną wiedzę ugruntuje poniższy kod.

use AdventureWorks

GO

--usuwam obiekty, o ile istnieją

IF OBJECT_ID('IDX_Pracownicy_ID') IS NOT NULL   

    DROP INDEX IDX_Pracownicy_ID ON dbo.Pracownicy

GO



IF OBJECT_ID('dbo.Pracownicy') IS NOT NULL  

    DROP TABLE dbo.Pracownicy

GO



--tworze tablice dla mechanizmu CDC

CREATE TABLE dbo.Pracownicy

(

    ID int identity(1,1) not null

    ,Imie varchar(50) not null

    ,Nazwisko varchar(50) not null

    ,[Status] char(1) not null DEFAULT('A')

    ,[Data rozpoczecia pracy] date null

    ,[Data zakonczenia pracy] date null

    ,[Raportuje do] smallint not null

)

GO



--tablica zostaje wypelniona przykladowymi danymi

INSERT INTO dbo.Pracownicy (Imie, Nazwisko,[Data rozpoczecia pracy],[Raportuje do]) VALUES 

('Jan','Kowalski','2005-01-01',0),

('Piotr','Nowak','2005-01-01',1),

('Krzysztof','Pawlak','2005-02-28',1),

('Dariusz','Zawartka','2005-03-01',2),

('Jerzy','Podolski','2005-02-15',2),

('Franciszek','Kawecki','2005-01-14',3),

('Dorota','Postrach','2007-03-01',1)

GO



--tworze unikalny indeks dla pola ID tablicy Pracownicy 

--dla klarownosci przykladu definicja indeksu jest poza tablica

CREATE UNIQUE INDEX IDX_Pracownicy_ID ON dbo.Pracownicy(ID)

GO



--uruchamiam mechanizm CDC dla bazy danych AdventureWorks 

--(prosze pamietac o uzytkowniku, ktory moze taka operacje przeprowadzic)

EXEC sys.sp_cdc_enable_db_change_data_capture

GO



--Sprawdzam, czy baza danych jest udostepniona dla CDC

SELECT [Name] FROM sys.databases where is_cdc_enabled=1

GO



--uruchamiam mechanizm CDC dla tablicy Pracownicy w bazie danych AdventureWorks 

--(prosze pamietac o uzytkowniku, ktory moze taka operacje przeprowadzic)

EXEC sys.sp_cdc_enable_table_change_data_capture 

    @source_schema = N'dbo' 

  , @source_name = N'Pracownicy'

  , @role_name = NULL

  , @supports_net_changes  = 1

  , @index_name = 'IDX_Pracownicy_ID'

GO



--sprawdzam, czy tablica zostala udostepniona dla CDC

SELECT [Name] FROM sys.tables

WHERE is_tracked_by_cdc=1

GO



--dokonuje kilku zmian w tablicy dbo.Pracownicy

UPDATE dbo.Pracownicy

SET Status='P'

GO

INSERT INTO dbo.Pracownicy (Imie, Nazwisko,[Data rozpoczecia pracy],[Raportuje do]) VALUES 

('Waclaw','Jezierski','2007-10-30',7),

('Justyna','Fabryka','2007-10-30',7)

GO

UPDATE dbo.Pracownicy

SET Status='N'

GO

DELETE FROM dbo.Pracownicy 

WHERE ID=3

GO

UPDATE dbo.Pracownicy

SET [Raportuje do]=7

WHERE [Raportuje do]=3

GO





--testuje rozne funkcje mechanizmu CDC

--deklaracja wartosci minimalnych i maksymalnych numerow LSN dla tablicy przechowujcej zmiany 

DECLARE @from_lsn binary(10),@to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Pracownicy');

SET @to_lsn = sys.fn_cdc_get_max_lsn();



--sprawdzam wyniki dzialania funkcji pobierajacej informacje o zmianach w tablicy

--zrodlowej dla roznych wartosci filtra  

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all')

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all update old ')



SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all')

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all with mask')

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all with merge')



-- pobieranie danych poprzez okreslenie zakresu zmian na podstawie numerow LSN

-- sprawdzenie, czy zmienily sie dane dla okreslonej kolumny

SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn); 

SET @to_lsn = sys.fn_cdc_get_max_lsn();



DECLARE @nr_kolumny bit;

SET @nr_kolumny = sys.fn_cdc_get_column_ordinal('dbo_Pracownicy', 'Status');

SELECT @nr_kolumny

 

SELECT * ,sys.fn_cdc_is_bit_set (@nr_kolumny, __$update_mask) AS 'Zmieniono'

FROM  cdc.fn_cdc_get_net_changes_dbo_Pracownicy (@from_lsn, @to_lsn, 'all with mask') 



-- pobieranie danych poprzez okreslenie zakresu zmian na podstawie dat

-- uwaga - przyklad nie zawiera obslugi bledow wynikajacych z nieistnienia

-- numeru LSN w tablicy cdc.lsn_time_mappings dla gornego zakresu  

SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2007-10-30 13:00:00');

SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', '2007-10-30 17:00:00');

SELECT * from cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn, @to_lsn, 'all');





--zmieniam strukturę tablicy Pracownicy

ALTER TABLE dbo.Pracownicy

ADD [Zarobek] INT NOT NULL DEFAULT (0)

WITH VALUES

GO



ALTER TABLE dbo.Pracownicy

DROP COLUMN [Data zakonczenia pracy]

GO



-- dodaję nową instancji tablicy przechwytujacej zmiany

-- po zmianach struktury tablicy zrodlowej

EXEC sys.sp_cdc_enable_table_change_data_capture 

    @source_schema = N'dbo' 

  , @source_name = N'Pracownicy'

  , @role_name = NULL

  , @supports_net_changes  = 1

  , @index_name = 'IDX_Pracownicy_ID'

  , @capture_instance = 'dbo_Pracownicy2'

GO



--zmiany w tablicy zrodlowej

UPDATE dbo.Pracownicy

SET Zarobek=1500

GO



INSERT INTO dbo.Pracownicy (Imie, Nazwisko,[Data rozpoczecia pracy],[Raportuje do],Zarobek) VALUES 

('Krystyna','Malinowska','2007-10-31',7,2500),

('Jan','Wojtek','2007-10-31',2,3000)

GO



DELETE FROM dbo.Pracownicy

WHERE ID=4

GO



--deklaracja wartosci minimalnych i maksymalnych numerow LSN dla pierwszej tablicy przechowujcej zmiany 

DECLARE @from_lsn binary(10),@to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Pracownicy');

SET @to_lsn = sys.fn_cdc_get_max_lsn();



--sprawdzam wyniki dzialania funkcji pobierajacej informacje o zmianach w tablicy

--zrodlowej dla roznych wartosci filtra  

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all')

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all update old ')



--sprawdzenie, co zostalo zapisane w drugiej tablicy przechowujacej zmiany

--najpierw wyznaczenie minimalnej wartosci LSN dla tej tablicy

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Pracownicy2');

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy2(@from_lsn,@to_lsn,'all')

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy2(@from_lsn,@to_lsn,'all update old ')

GO



--wylaczenie mechnizmu CDC dla tablicy dbo.Pracownicy

EXEC sys.sp_cdc_disable_db_change_data_capture

GO



--wylaczenie mechanizmu CDC dla calej bazy danych AdventureWorks

EXEC sys.sp_cdc_disable_db_change_data_capture

GO

 Do początku strony Do początku strony

Podsumowanie

Mechanizm przechwytywania zmian danych w tablicach (CDC) umożliwia administratorom baz danych obserwację zmian danych dokonanych instrukcjami DML w określonych tablicach oraz przechowuje także zmiany metadanych dla tych tablic. Jest to pierwsze, uniwersalne ujęcie tak istotnego problemu, z którym większość spotyka się praktycznie na co dzień. Do tej pory administrator bądź programista baz danych musiał ten problem rozwiązać w swoim zakresie przy niejednokrotnie dużym nakładzie pracy, rzadko przy tym osiągając odpowiedni uniwersalizm. Mając do dyspozycji gotowe, dobrze działające rozwiązanie systemowe należy po prostu z niego skorzystać.

  Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET)
Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”.

Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.
 Do początku strony Do początku strony

Microsoft SQL Server 2008