Akademia SQL - Część 9: Mechanizm przechytywania zmian

Autor: Damian Widera

Opublikowano: 7 października 2008

Zawartość strony

	Mechanizm przechytywania zmian - informacje podstawowe
	Mechanizm przechytywania zmian - laboratorium
	Mechanizm przechytywania zmian - referencje

Mechanizm przechytywania zmian - informacje podstawowe

Mechanizm przechytywania (ang. Change Data Capture) zmian pozwalaja przekazać aplikacjom klienckim informacje o zmianach w danych, przez co eliminuje konieczność oprogramowania własnych rozwizań w tym zakresie, np. opartych na wyzwalaczach. Mechanizm przechwytywania zmian nie wymaga wprowadzenia żadnych zmian schematów w istniejących tabelach, tak więc może być zastosowane w instniejących aplikacjach.

Mechanizm przechwytywania zmian pozwala na wyświetlenie informacji o historycznych zmianach w danych, tzn. każda zmiana zostaje zapisana w tabeli, którą można potem wykorzystać do wyświetlenia w aplikacji.

Mechanizm przechwytywania zmian działa podobnie do replikacji transakcyjnej. Zadanie usługi Agent pobiera informacje z dziennika transakcji i umieszcza je w specjalnej tablicy (bądź tablicach). Domyślnie, po instalacji serwera SQL 2008 mechanizm przechytywania zmian jest wyłączony. W momencie, w którym pierwsza z tablic zostaje udostępniona dla mechanizmu przechytywania zmian, uruchamiany jest proces przechwytujący, który rozpoczyna obserwowanie dziennika transakcji bazy danych, ponieważ zmiana w tablicy źródłowej powoduje bowiem zapis informacji do dziennika transakcji bazy danych. Mechanizm przechytywania zmian odczytuje te zmiany i zapisuje je w specjalnej tablicy (bądź tablicach), która jest lustrzanym odbiciem obserwowanej tablicy.

Oprócz zapisywania zmian danych mechanizm przechytywania zmian zapisuje także metadane każdej transakcji, które pozwalają zidentyfikować np. jaka akcja spowodowała zmiany w tablicy źródłowej.

Konfigurowanie mechanizmu przechwytywania zmian składa się z conajmniej 2 kroków:

1. Włączenie przez członka serwerowej roli sysadmin bazy danych dla mechanizmu przechytywania zmian. Do tego celu służy procedura składowana sys.sp_cdc_enable_db,
2. Określenie przez członka bazodanowej roli db_owner tablic dla mechanizmu przechytywania zmian.

Kiedy baza danych zostaje udostępniona dla mechanizmu przechwytywania zmian tworzony jest schemat cdc, użytkownik cdc, tablice z meta danymi oraz inne obiekty systemowe w ramach tej bazy danych. Schemat cdc zawiera wszystkie tablice z metadanymi oraz tablice zmian, które rejestrują zmiany w tablicach źródłowych. Po udostępnieniu bazy danych dla mechanizmu przechytywania zmian można wskazać, dla których tablic należy przechwytywać zmiany. Dla każdej tablicy źródłowej można wskazać maksymalnie 2 tablice, które będą przechowywały informacje o zmianach. Procedura składowana sys.sp_cdc_enable_table pozwala skonfigurować mechanizm przechwytywania zmian dla konkretnej tablicy.

Dane zawarte w tablicach przechowujących zmiany dostępne są poprzez systemowe procedury składowane oraz funkcje. Informacje o konfiguracji mechanizmu przechwytywania zmian 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 ona szczegółową informacje o każdej tablicy udostępnionej dla mechanizmu przechwytywania zmian. Z kolei procedura sys.sp_cdc_get_data_captured_columnswyś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_ zwracająca wszystkie zmiany z określonego przedziału.
2. cdc.fn_cdc_get_net_changes_ zwracająca tylko ostatnią zmianę dla każdego wiersza w określonym przedziale.

Obydwie funkcje akceptują taki sam zestaw parametrów:

1. @from_lsn,
2. @to_lsn,
3. <row_filter_option>.

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 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_ akceptuje parametr all update old, który pozwala na wyświetlenie wszystkich informacji, w tym także dokonywanych przed komendę UPDATE, czyli w tym przypadku wyświetlane są dwa wiersze. Jeden zawiera informacje sprzed zmiany a drugi po zmianie danych w wierszu.

Typowa aplikacja używająca mechanizmu przechytywania zmian 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:

1. poprzez podanie LSN,
2. poprzez wprowadzenie daty,

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

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 przechytywania zmian 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.

Istnieją dwie funkcje mapujące numer LSN na datę i na odwrót:

1. sys.fn_cdc_map_lsn_to_time, która zamienia numer LSN na datę,
2. sys.fn_cdc_map_time_to_lsn, która znajduje numer LSN dla podanej daty przy uwzględnieniu dodatkowych warunków.

Obydwie korzystają z systemowej tablicy cdc.lsn_time_mappings aby zamienić datę na numer LSN i odwrotnie.

Ostatnim aspektem pracy z mechanizmem przechwytywania zmian jest możliwość reagowania na zmiany struktury tabeli źródłowej wywołanej komendą ALTER TABLE.

Po utworzeniu instancji tablicy przechowującej zmiany danych w tablicy źródłowej jej kształt jest już ustalony i nie można go zmienić. 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ć 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 na niezakłócone działanie 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.

Mechanizm przechwytywania zmian dostępny jest w wersji Enterprise.

 Do początku strony

Mechanizm przechytywania zmian - laboratorium

Celem laboratorium będzie zapoznanie się z mechanizmem przechytywania zmian. W pierwszy zadaniu zostanie zaprezentowane, jak należy mechanizm włączyć na poziomie bazy danych oraz tabeli a następnie go wyłączyć. Drugie zadanie poświecone zostało pracy z mechanizmem przechytywania zmian.

Zadanie 1

1. Uruchom konsolę SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

2. Otwórz nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz poniższy pragment kodu, który uruchomi mechanizm przechytywania zmian w bazie danych AdventureWorks2008:

use AdventureWorks2008

GO

EXEC sys.sp_cdc_enable_db;

3. Wpisz poniższe zapytanie, aby sprawdzić, które bazy danych są włączone dla mechanizmu przechwytywania zmian:

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

---------------------------------

AdventureWorks2008

(1 row(s) affected)

4. Wykonaj kopię tablicy Production.Product:

SELECT * INTO Production.Product2

FROM Production.Product

5. Procedura składowana sys.sp_cdc_enable_tablepozwala skonfigurować mechanizm przechwytywania zmian dla konkretnej tablicy. Jej ogólna postać jest nastepująca:

sys.sp_cdc_enable_table 

    [ @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 procedury jest następujące:

a) @source_schema określa nazwę schematu tabeli,

b) @source_name określa nazwę tabeli,

c) @role_name określa nazwę roli, która będzie kontrolowała dostęp do zmian danych. W przypadku, gdy rola o podanej nazwie nie istnieje to 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 przechytywania zmian, a innym użytkownikom należy nadać uprawnienia do wykonania komendy SELECT.

d) @supports_net_changes, którego wartość ustawiona na 1 spowoduje utworzenie funkcji zwarcającej ostatnią zmianę dla rekordu w określonym przedziale czasowym. Funkcja jest tworzona dla każdej instancji tabeli zmian, a jej nazwa ma formę cdc.fn_cdc_get_net_changes_. Domyślnie tworzona jest tablicowa funkcja cdc.fn_cdc_get_all_changes_&lt; nazwa instancji_tabeli zmian&gt;, która umożliwia dostęp do wszystkich zmian danych rekordów w określonym przedziale czasowym.

e) @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.

f) @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 przechytywania zmian w tablicach przechowujących zmiany.

g) @filegroup_name przechowuje 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.

6. Wpisz do okna zapytania poniższy kod, który pozwoli na przechytywanie zmian w tablicy Product2 w schemacie Production:

EXEC sys.sp_cdc_enable_table 

    @source_schema = N'Production'

  , @source_name = N'Product2'

  , @role_name = NULL

7. Wykonaj poniższy fragment kodu abys sprawdzić, które tablice w bazie danych AdventureWorks2008 mają włączony mechanizm przechwytywania zmian:

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

-------------

Product2

(1 row(s) affected)

8. Mechanizm przechwytywania zmian może zostać wyłączony dla określonej tablicy przy pomocy funkcji sys.sp_cdc_disable_table, która umożliwia wyłączenie mechanizmu dla jednej bądź wszystkich instancji tablic przechowujących zmiany:

sys.sp_cdc_disable_table [ @source_schema = ] 'source_schema', 

      [ @source_name = ] 'source_name' ,

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

9. Po zakończonym zadaniu nr 2 wpisz do okna zapytania poniższy kod, który pozwoli na zakończenie przechwytywania zmian w tablicy Product2 w schemacie Production:

EXEC sys.sp_cdc_disable_table

    @source_schema = N'Production'

  , @source_name = N'Product2'

  , @capture_instance = 'all'

10. Po włączeniu mechanizmu przechwytywania zmian dla tablicy Production.Product zostaje utworzona systemowa tablica (w schemacie cdc) przechowująca zmiany. Serwer SQL 2008 utworzył również inne tablice w schemacie cdc, które przechowują metadane wymagane przez mechanizm przechwytywania zmian.

11. Istnieje także możliwość wyłączenia mechanizmu przechwytywania zmian dla bazy danych i służy do tego funkcja sys.sp_cdc_disable_db, która nie przyjmuje żadnych parametrów i musi być wywołana w kontekście bazy danych, dla której mechanizm włączono. Po zakończonym laboratorium wpisz w oknie zapytania poniższy kod:

use AdventureWorks2008

GO  

EXEC sys.sp_cdc_disable_db;

Zadanie 2

Celem zadania nr 2 będzie pokazanie, w jaki sposób można pracować z mechanizmem przechytywania zmian.

1. Uruchom konsolę SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

2. W tym kroku należy wstawić wiersz do tablicy Production.Product2 oraz sprawdzić, co zostało zapisane w systemowej tablicy cdc. Production_Product2_CT. Wykonaj poniższy fragment kodu, który najpierw wstawia wiersz:

INSERT INTO Production.Product2 (Name,ProductNumber,MakeFlag,FinishedGoodsFlag,SafetyStockLevel,ReorderPoint,StandardCost

,ListPrice,DaysToManufacture,SellStartDate,rowguid,ModifiedDate)

VALUES ('Normal Chain','AAA-01',0,1,50,2,25,10,50,'2008-06-01',NEWID(),GETDATE())

Poniższe zapytanie wyświetlio informacje, która została zapisana w tablicy zmian:

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

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

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



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

-----

3. Tablica zmian przechowuje nie tylko informacje o wstawionym rekordzie (dane), ale również inne informacje, które są niezbędne, np. rodzaj wykonanje operacji (w kolumnie __$operation) czy wskazanie, które kolumny zostały zmienione (w kolumnie __$update_mask).

4. Wykonaj poniższy fragment kodu, który zmieni dane w kolumnie ProductNumber dla jednego z produktów:

UPDATE Production.Product2

SET ProductNumber = 'BBB'

WHERE ProductID=2

5. Wykonaj sprawdzenie, co zostało zapisane w tablicy przechowującej zmiany:

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

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

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



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

Zwróć uwagę, że funkcja cdc.fn_cdc_get_all_changes_Production_Product2 jako trzeci parametr przyjmuje 'all update old', co pozwoli wyświetlić informacje o operacji UPDATE rozbitej na dwa wiersze. Pierwszy z tych wierszy (oznaczony kolorem czerwonym na rysunku poniżej) przedstawia stan wiersza przed dokonaniem operacji UPDATE, a drugi - w kolorze zielonym, zapisuje wiersz po wykonaniu tej operacji:

6. Zwróć uwagę, że:

• W kolumnie __$operation zapisana jest liczba 3 dla wiersza przed operacją UPDATE,
• W kolumnie __$operation zapisana jest liczba 4 dla wiersza po operacji UPDATE,
• W kolumnie __$update_mask znajduje się informacja, która z kolumn została zmieniona.

7. Wykonaj poniższy fragment kodu, który usunie kilka wierszy z tablicy Production.Product2:

DELETE FROM Production.Product2

WHERE ProductID<10

8. Za pomocą zapytania z punktu 6 sprawdź, jakie wiersze zostały usunięte. Przykładowy wynik został zaprezentowany poniżej:

9. Zwróć uwagę, że w kolumnie __$operationzostała zapisana liczba 1, co odpowiada operacji DELETE

 Do początku strony

Mechanizm przechytywania zmian - referencje

Dodatkowe informacje na temat mechanizmu przechwytywania zmian zdarzeń można znaleźć w Internecie:

[1] SQL Server 2008 - mechanizm Change Data Capture

[2] Webcast na portalu CHANNEL 9 na temat mechanizmu przechytywania zmian

[3] Microsoft SQL Server 2008 - Change Data Capture - PART I - artykuł Muthusamy Anantha Kumara

[4] Microsoft SQL Server 2008 - Change Data Capture - PART II - artykuł Muthusamy Anantha Kumara

[5] Projekt CDCHelper na stronach CodePlex

Jeżeli zainstalowany został serwer SQL wraz z wszystkimi wymaganymi komponentami, to dodatkowe informacje zawarte są w pliku pomocy pod hasłem Overview of Change Data Capture.

 

---

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

---