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

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,

Określenie przez członka bazodanowej roli db_owner tablic dla mechanizmu przechytywania zmian.

cdc.fn_cdc_get_all_changes_<NazwaInstancji> zwracająca wszystkie zmiany z określonego przedziału.

cdc.fn_cdc_get_net_changes_<NazwaInstancji> zwracająca tylko ostatnią zmianę dla każdego wiersza w określonym przedziale.

@from_lsn,

@to_lsn,

<row_filter_option>.

poprzez podanie LSN,

poprzez wprowadzenie daty,

sys.fn_cdc_map_lsn_to_time, która zamienia numer LSN na datę,

sys.fn_cdc_map_time_to_lsn, która znajduje numer LSN dla podanej daty przy uwzględnieniu dodatkowych warunków.

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

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;

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)

Wykonaj kopię tablicy Production.Product:

SELECT * INTO Production.Product2
FROM Production.Product

Procedura składowana sys.sp_cdc_enable_table pozwala 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_<nazwa instancji_tabeli zmian>. Domyślnie tworzona jest tablicowa funkcja cdc.fn_cdc_get_all_changes_< nazwa instancji_tabeli zmian>, 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.

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

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)

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'

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'

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.

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;

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

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')
-----

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

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

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:

Zwróć uwagę, że:

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

DELETE FROM Production.Product2
WHERE ProductID<10

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

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