Mechanizm śledzenia zmian w SQL Server 2008

Autor: Damian Widera

Opublikowano: 20 listopada 2008

Zawartość strony

	Działanie mechanizmu śledzenia zmian
	Praca z mechanizmem śledzenia zmian
	Podsumowanie

Serwer SQL 2008 wprowadził nowe rozwiązanie, nieobciążające silnika baz danych, które pozwala otrzymać informacje o zmianach danych. Logika aplikacji klienckich bardzo często oparta jest na informacji, że pewne istotne z punktu widzenia aplikacji dane zostały zmienione. Programiści baz danych, nie mając do dyspozycji nowej funkcjonalności, byli zmuszeni samodzielnie wykonać podobne, wymagające dużego nakładu pracy i wysiłku rozwiązanie np. w oparciu o wyzwalacze czy tabele robocze, które musiały być odpowiednio zarządzane. Dodatkowo, trudno było wykonać rozwiązanie na tyle uniwersalne, aby można w ten sposób obsługiwać różne aplikacje.

Działanie mechanizmu śledzenia zmian

Mechanizm śledzenia zmian, jak każda nowa funkcjonalność w serwerze SQL 2008, jest domyślnie wyłączony. Należy najpierw włączyć go na poziomie bazy danych a następnie we wszystkich tabelach, w których będą śledzone zmiany.

Włączenie mechanizmu śledzenia zmian na poziomie tabeli nie spowoduje konieczności zmiany jej definicji lub dodawania nowych elementów, np. wyzwalaczy.

Po włączeniu mechanizmu przechwytywania zmian każda operacja INSERT, UPDATE lub DELETE dotycząca tabel, w których ten mechanizm został włączony, spowoduje zachowanie informacji w tablicy zmian. Każda tabela posiadająca włączone śledzenia zmian posiada jedną tablicę zmian, w której przechowywane są wartości klucza głównego, które jednoznacznie identyfikują każdy wiersz, który został zmieniony. Zmiany te są dostępne poprzez tzw. funkcję zmian. W celu uzyskania informacji o zmienionych wierszach, aplikacja kliencka musi wykonać zapytanie do tablicy zmian, w której zapisane są klucze główne zmienionych wierszy, a następnie złączyć otrzymany wynik z tablicą źródłową. Tablica zmian pozwala również uzyskać informacje o kolumnach, które zostały zmienione w wyniku operacji UPDATE.

 Do początku strony

Praca z mechanizmem śledzenia zmian

Pracę z mechanizmem śledzenia zmian należy rozpocząć włączając odpowiednią opcję dla bazy danych i dla poszczególnych tabel.

Włączenie mechanizmu śledzenia zmian w bazie danych

Mechanizm śledzenia zmian może być włączony w bazie danych z konsoli SSMS lub w wyniku wykonania instrukcji ALTERDATABASE. Używając konsoli SSMS, aby włączyć mechanizm śledzenia zmian dla bazy danych AdventureWorks2008, należy w eksploratorze obiektów rozwinąć sekcję Databases, zaznaczyć bazę danych AdventureWorks2008 i z menu kontekstowego wybrać opcję Properties. Następnie w oknie własności bazy danych przejść na stronę Change Tracking i włączyć mechanizm śledzenia zmian ustawiając następujące opcje:

1. Change Tracking na True,
2. RetentionPeriod w kombinacji z RetentionPeriodUnits oznacza na liczbę jednostek czasu (minut, godzin, dni), przez które dane o zmianach będą przechowywane o w tablicach zmian. Domyślnie opcje te ustawione są tak, aby informacja była przechowywana przez 2 dni,
3. AutoCleanup na True, co pozwoli na automatyczne czyszczenie tablicy zmian w tle.

Sposób konfiguracji mechanizmu śledzenia zmian w bazie danych jest przedstawiony na rysunku 1 poniżej:

Rysunek 1: Konfiguracja mechanizmu śledzenia zmian w oknie własności bazy danych.

Włączenie mechanizmu śledzenia zmian w bazie danych z poziomu kodu T-SQL wymaga wykonania poniższego fragmentu kodu:

ALTER DATABASE AdventureWorks2008

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

GO

Wartość zapisana w parametrze CHANGE_RETENTION powinna być dobrana tak, aby aplikacja kliencka bazująca na informacji zawartej w tablicy zmian otrzymała poszukiwany wynik.

Mechanizm śledzenia zmian może być włączony w bazach danych, w których poziom kompatybilności wynosi 90 lub więcej. Próba włączenia mechanizmu śledzenia zmian dla baz danych z poziomem kompatybilności mniejszym niż 90 nie spowoduje co prawda wyświetlenia komunikatu o błędzie, ale taki komunikat zostanie pokazany w momencie, kiedy aplikacja kliencka spróbuje odczytać informacje zawarte w tablicy zmian.

Włączenie mechanizmu śledzenia zmian w tabeli

Podobnie jak miało to miejsce przy konfigurowaniu mechanizmu śledzenia zmian dla bazy danych, mechanizm śledzenia zmian może być włączony dla każdej tabeli przy użyciu konsoli SSMS lub w wyniku wykonania instrukcji ALTER TABLE. Używając konsoli SSMS, aby włączyć mechanizm śledzenia zmian dla tabeli HumanResources.Employee z bazy danych AdventureWorks2008, należy w eksploratorze obiektów rozwinąć sekcję Databases, rozwinąć bazę danych AdventureWorks2008, zaznaczyć tabelę HumanResources.Employee i z menu kontekstowego wybrać opcję Properties. W oknie własności tabeli należy przejść na stronę Change Tracking i włączyć mechanizm śledzenia zmian konfigurując opcje:

1. Change Tracking na True,
2. TrackColumnsUpdated powinna być włączona na True tylko w sytuacji, kiedy ważna jest nie tylko informacja, że dany wiersz się zmienił, ale także która kolumna uległa zmianie. Tabela zmian zawiera wtedy dodatkową informację o zmianach w kolumnach. Domyślnie opcja ta ma wartość False, ponieważ włączenie jej wiąże się z dodatkowym nakładem związanym z koniecznością przechowania tej informacji.

Sposób konfiguracji mechanizmu śledzenia zmian dla wybranej tabeli przedstawiliśmy na rysunku 2.

Rysunek 2: Konfiguracja mechanizmu śledzenia zmian w tabeli HumanResources.Employee w bazie danych AdventureWorks2008.

Operacje opisane powyżej można również wykonać za pomocą instrukcji ALTERTABLE, jak na przykładzie poniżej:

use AdventureWorks2008

GO

ALTER TABLE HumanResources.Employee

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON);

GO

Wyłączenie mechanizmu śledzenia zmian

Mechanizm śledzenia zmian może zostać w każdym momencie wyłączony, ale należy pamiętać o odpowiedniej kolejności wykonywania tej operacji. Najpierw należy wyłączyć śledzenie zmian we wszystkich tablicach a następnie w bazie danych.

Na poniższym przykładzie zostało pokazane, jak wyłączyć mechanizm śledzenia zmian:

use AdventureWorks2008

GO

ALTER TABLE HumanResources.Employee

DISABLE CHANGE_TRACKING;

GO

ALTER DATABASE AdventureWorks2008

SET CHANGE_TRACKING = OFF;

GO

Aspekty bezpieczeństwa mechanizmu śledzenia zmian

Dostęp do informacji zawartych w tablicy zmian jest dostępny dla użytkowników posiadających uprawnienia SELECT co najmniej na kluczu głównym tabeli źródłowej. Drugim wymaganiem jest, aby użytkownik posiadał uprawnienia VIEWCHANGETRACKING. Wynika to z faktu, że w tablicy zmian przechowywane są także informacje np. o usuniętych wierszach. Samo uprawnienie SELECT nie jest więc wystarczające do uzyskania informacji o takich zmianach. Dodatkowo, tablica zmian może, w zależności od konfiguracji, przechowywać informacje o kolumnach, w których nastąpiły zmiany i z tego powodu uprawnienie SELECT również nie jest wystarczające.

Zarządzanie mechanizmem śledzenia zmian

Silnik baz danych udostępnia dwa widoki katalogowe, które pozwalają na zarządzanie mechanizmem śledzenia zmian:

1. sys.change_tracking_databases,
2. sys.change_tracking_tables.

Widok sys.change_tracking_databases zapisuje po jednym wierszu dla każdej bazy danych, która ma włączony mechanizm śledzenia zmian, natomiast widok sys.change_tracking_tables zawiera po jednym wierszu dla każdej tablicy, dla której administrator włączył śledzenie zmian. Obydwa widoki przechowują informacje konfiguracyjne, które zostały wcześniej zapisane, czy to w oknie własności dla tabeli czy bazy danych lub za pomocą kodu T-SQL.

Użytkownik, który chciałby uzyskać informacje o bazie danych zawarte w widoku sys.change_tracking_databases musi być właścicielem tej bazy danych. Jeżeli nie spełnia tego kryterium, to musi posiadać uprawnienia ALTER ANY DATABASE lub VIEW ANY DATABASE na poziomie instancji serwera baz danych lub CREATE DATABASE w systemowej bazie danych master.

Pobieranie informacji o zmianach danych

Do pobierania informacji o zmianach w danych zarejestrowanych przez mechanizm śledzenia zmian służą tzw. funkcje zmian (and. change functions):

1. CHANGETABLE, która jest używana do pobrania informacji zapisanych w wewnętrznej tablicy zmian. Funkcja zwraca informacje zawierające klucze główne wierszy z tablicy źródłowej a także inne informacje, jak rodzaj wykonanej operacji DML na wierszu czy informacje o zmienionych kolumnach. Funkcja CHANGETABLE pobiera jako parametr numer wersji zmian ostatniej synchronizacji z tablicą zmian i zwraca informacje o wszystkich zmianach, które zaszły od czasu ostatniego pobrania danych,
2. CHANGE_TRACKING_CURRENT_VERSION jest używana do uzyskania aktualnej wersji zmian zapisany w tablicy zmian. Numer wersji odpowiada numerowi ostatnio zatwierdzonej transakcji dla danej tablicy,
3. CHANGE_TRACKING_MIN_VALID_VERSION pozwala uzyskać minimalny numer wersji zmian, który pozwoli uzyskać poprawne i spójne informacje z tablicy zmian. Aplikacja kliencka powinna sprawdzić, czy otrzymany, aktualny numer wersji nie jest mniejszy od zachowanego wcześniej, ponieważ oznacza to, że uzyskany wynik jest niepoprawny i należy ponownie zainicjalizować dane.

Inicjalizacja danych

Aplikacja kliencka przed pobraniem informacji z tablicy zmian po raz pierwszy musi otrzymać informacje o aktualnej wersji zmian oraz aktualne dane z tablicy źródłowej. Poniższy fragment kodu ilustruje sposób uzyskania tych informacji:

--aktualna wersja zmian uzyskana przy pomocy funkcji  CHANGE_TRACKING_CURRENT_VERSION

DECLARE @Wersja_Zmian INT = CHANGE_TRACKING_CURRENT_VERSION();

--aktualne dane z tablicy zrodlowej

SELECT

  E.BusinessEntityID as [Klucz główny]

    ,E.LoginID  as [Login]

    ,E.VacationHours as [Pozostałe dni wakacji]

    ,E.SickLeaveHours as [Pozotałe dni zwolnienia lekarskeigo]

FROM HumanResources.Employee E

WHERE E.JobTitle = 'Sales Representative'

ORDER BY E.LoginID;

Baza danych, która posiada włączony mechanizm śledzenia zmian przechowuje licznik wersji, który zwiększa się po każdej zatwierdzonej transakcji dla danej tablicy. Każda zmiana w tablicy źródłowej inkrementuje licznik zmian, a aktualną jego wartość można otrzymać wywołując funkcję CHANGE_TRACKING_CURRENT_VERSION.

Pobieranie zmian

Aplikacja uzyska informacje o zmienionych wierszach wywołując funkcję CHANGETABLE, do której musi podać numer aktualnie posiadanej wersji zmian. Na poniższym fragmencie kodu zostało pokazane, jak uzyskać informacje o zmianach w tabeli HumanResources.Employee wykorzystując uzyskaną w poprzednim punkcie informacje aktualnym numerze zmian zapisanym w zmiennej @Wersja\_Zmian INT.

--wykonujemy zmiany w tablicy HumanResources.Employee

UPDATE HumanResources.Employee 

SET VacationHours+=2

WHERE LoginID='adventure-works\david8';

GO

UPDATE HumanResources.Employee 

SET SickLeaveHours=0

WHERE LoginID='adventure-works\garrett1';

GO

--pobieramy informacje o zmianach

SELECT

    CT.BusinessEntityID as [Klucz glowny]

    ,CASE CT.SYS_CHANGE_OPERATION

        WHEN 'I' THEN 'insert'

        WHEN 'U' THEN 'update'

        WHEN 'D' THEN 'delete'

    END as [Operacja]

    , CT.SYS_CHANGE_COLUMNS as [Mapa bitowa zmienionej kolumny] 

FROM CHANGETABLE(CHANGES HumanResources.Employee, @Wersja_Zmian) CT;

GO

W zapytaniu powyżej zwrócone zostały klucze główne do wierszy, które zostały zmienione, ale w większości przypadków jest to informacja niewystarczająca, ponieważ aplikacja kliencka oczekuje takiego zestawu informacji, który będzie mógł zostać zaprezentowany użytkownikowi.

W tym celu należy informacje uzyskane z tabeli zmian powiązać z tabelą źródłową, jak pokazano poniżej:

--pobieramy aktualną wersję zmian

DECLARE @Aktualna_Wersja_Zmian INT = CHANGE_TRACKING_CURRENT_VERSION();

--pobieramy informacje wykorzystując POPRZEDNIO uzyskaną wersję zmian

SELECT

    E.LoginID

    ,E.VacationHours

    ,E.SickLeaveHours

  ,CT.BusinessEntityID as [Klucz glowny]

    ,CASE CT.SYS_CHANGE_OPERATION

        WHEN 'I' THEN 'insert'

        WHEN 'U' THEN 'update'

        WHEN 'D' THEN 'delete'

    END as [Operacja]

 ,CT.SYS_CHANGE_COLUMNS as [Mapa bitowa zmienionej kolumny]

FROM HumanResources.Employee E JOIN

    CHANGETABLE(CHANGES HumanResources.Employee, @Wersja_Zmian) AS CT

ON E.BusinessEntityID = CT.BusinessEntityID 

WHERE E.JobTitle = 'Sales Representative'

ORDER BY E.LoginID; 

--zamieniamy wersje zmian, bo w nastepnym zapytaniu należy skorzystać z aktualnej wersji

SET @Wersja_Zmian  = @Aktualna_Wersja_Zmian;

GO

Zwróć uwagę na następujące rzeczy:

1. Rodzaj złączenia występujący w zapytaniu, ponieważ RIGHT OUTER JOIN zapewni wyświetlenie wyniku nawet wtedy, gdy rekord został usunięty z tablicy źródłowej,
2. Konieczność pobrania aktualnego numeru wersji zmian, chociaż w zapytaniu wykorzystamy numer uzyskany poprzednio. Następne zapytanie musi jednak korzystać z najnowszego zapisanego w aplikacji numeru zmian, w przeciwnym wypadku uzyskasz ponownie te same dane.

Sprawdzenie ważności numeru zmian

Informacje o zmianach danych przechowywane są w tablicy zmian przez określony czas, który jest kontrolowany przez parameter CHANGE_RETENTION przekazany w poleceniu ALTER DATABASE. Aplikacja kliencka musi pobrać informacje o zmianach częściej, niż zadeklarowano to w parametrze CHANGE_RETENTION, w przeciwnym razie konieczne będzie ponowne zainicjalizowanie danych. Warto więc sprawdzić, czy numer wersji odpowiadający numerowi ostatniego pobierania danych jest jeszcze akceptowany. Zrobić to można wykorzystując funkcję CHANGE_TRACKING_MIN_VALID_VERSION, jak poniżej:

--sprawdzamy, czy możemy wykorzystać dane zawarte w tablicy zamian

IF (@Wersja_Zmian < 

    CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('HumanResources.Employee'))

BEGIN

-- aplikacja kliencka nie moze pobrać zmian

-- musimy ponownie wykonać inicjalizację danych

END;

GO

Śledzenie zmian w kolumnach

Mechanizm śledzenia zmian umożliwia uzyskanie informacji, w której kolumnie zostały zmienione dane podczas operacji UPDATE. Konfiguracja tej domyślnie wyłączonej opcji — TRACK_COLUMNS_UPDATED, odbywa się na poziomie każdej z tabel, na której włączony został mechanizm śledzenia zmian. Informacja ta jest zwracana jako wynik wywołania funkcji CHANGETABLE w kolumnie SYS_CHANGE_COLUMNS jako liczba całkowita, w której każdy bit oznacza identyfikator kolumny tabeli. Użytkownik musi mieć jednak możliwość uzyskania dokładnej informacji, która kolumna czy kolumny zostały zmienione.

Czytelnik powinien zwrócić uwagę na jeszcze jeden fakt, a mianowicie wartość NULL zostanie zwrócona jeżeli dane w kolumnie nie zostały zmienione, ale trzeba również pamiętać, że wartość w tej kolumnie mogła zostać zmieniona na NULL. Rozwiązaniem tego problemu jest odpowiednie wykorzystanie dwóch funkcji: COLUMNPROPERTY oraz CHANGE_TRACKING_IS_COLUMN_IN_MASK jak pokazano na poniższym przykładzie, w którym szukamy informacji, czy zmieniła się kolumna VacationHours w tabeli HumanResources.Employee.

DECLARE @VacationHoursID INT = COLUMNPROPERTY(

    OBJECT_ID('HumanResources.Employee'),'VacationHours', 'ColumnId')



SELECT

    E.LoginID

    ,E.VacationHours

    ,E.SickLeaveHours

    ,CASE

        WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(@VacationHoursID, CT.SYS_CHANGE_COLUMNS) = 1 THEN VacationHours

        ELSE NULL

        END AS CT_PozostaleWakacje

    ,CHANGE_TRACKING_IS_COLUMN_IN_MASK(@VacationHoursID, CT.SYS_CHANGE_COLUMNS) AS CT_PozostaleWakacje_Zmienione

    , CT.SYS_CHANGE_COLUMNS

FROM HumanResources.Employee E JOIN

    CHANGETABLE(CHANGES HumanResources.Employee, @Wersja_Zmian) AS CT

ON E.BusinessEntityID = CT.BusinessEntityID 

WHERE E.JobTitle = 'Sales Representative'

AND CT.SYS_CHANGE_OPERATION = 'U'

ORDER BY E.LoginID;

Funkcja CHANGE_TRACKING_IS_COLUMN_IN_MASK zwraca wartość 1 jeżeli kolumna o podanym identyfikatorze została zmieniona. Identyfikator kolumny został uzyskany z systemowej funkcji COLUMNPROPERTY wywołanej z parametrem ColumnID.

Wpływ mechnizmu śledzenia zmian na zachowanie silnika baz danych

Po włączeniu mechanizmu śledzenia zmian niektóre komendy języka T-SQL wykonują się inaczej, niż miało to miejsce przy wyłączonej funkcjonalności śledzenia zmian:

1. DROP TABLE usuwa wszystkie informacje odnoszące się do tablicy przeznaczonej do usunięcia,
2. ALTER TABLE DROP CONSTRAINT zgłosi błąd podczas usuwania klucza głównego tabeli. Należy najpierw wyłączyć mechanizm śledzenia zmian i usunąć klucz główny,
3. ALTER TABLE DROP COLUMN nie powiedzie się, jeżeli kolumna jest częścią klucza głównego tabeli. W innym przypadku kolumna może zostać usunięta, ale należy pamiętać, że może być ona nadal częścią mechanizmu śledzenia zmian, o ile opcja TRACK_COLUMNS_UPDATED była włączona,
4. ALTER TABLE ADD COLUMN spowoduje, że mechanizm śledzenia zmian będzie notował operacje UPDATE lub DELETE dla nowej kolumny, nie będzie jednak przechowana informacja o wstawianiu nowych danych do kolumny,
5. DROPINDEX lub ALTERINDEXDISABLE spowoduje błąd, jeżeli nie będzie można usunąć klucza głównego,
6. TRUNCATE TABLE może zostać wykonana na tabeli, która ma włączony mechanizm śledzenia zmian. Usuwane wiersze nie będą odzwierciedlone w tablicy zmian a numer wersji wierszy zostanie ustawiony na wartość minimalną. Z punktu widzenia użytkownika operacja TRUNCATETABLE jest podobna do wyłączenia i ponownego włączenia mechanizmu śledzenia zmian.

Mechanizm śledzenia zmian ma również wpływ na operacje DML, ponieważ zmiany w danych zapisywane są jako część operacji DML. Każdy wiersz zmieniony operacją DML powoduje zapisanie jednego wiersza w wewnętrznej tablicy zmian. Czas trwania tej operacji jest zależny od co najmniej trzech czynników:

1. Liczby kolumn klucza głównego tabeli,
2. Ilości danych, które zostały zmienione w tablicy,
3. Liczby wykonanych operacji DML w ramach transakcji.

Informacje o zmianach danych przechowywane są w dwóch wewnętrznych tablicach:

1. Tablicy zmian, do której zapisywany jest wiersz po każdej zmianie danych w określonej tablicy. W wierszu może być zawarta informacja ustawiona przez aplikację, która zmiany wprowadziła. Dodatkowo potrzebne są 4 bajty dla każdej kolumny, która jest identyfikowana przez mechanizm śledzenia zmian,
2. Tablicy transakcji, do której zapisywany jest wiersz po każdej zatwierdzonej transakcji odnoszącej się do tablicy z włączonym mechanizmem śledzenia zmian.

 Do początku strony

Podsumowanie

Poniżej zostały podsumowane zalety mechanizmu śledzenia zmian, które warto wziąć pod uwagę decydując się rozwiązanie oferowane przez serwer SQL 2008.

Mechanizm śledzenia zmian pozwala:

1. Zmniejszyć czas poświecony na implementację rozwiązania w aplikacji, ponieważ jest to integralna część silnika baz danych serwera SQL 2008,
2. Wykorzystać istniejące bazy danych, ponieważ nie są wymagane żadne zmiany w strukturach tabel,
3. Zmniejszyć konieczne czynności administracyjne do minimum, ponieważ procesy czyszczenia tablic zmian wykonywane są automatycznie zgodnie z ustaloną częstotliwością dla każdej tabeli,
4. Wykorzystać dostarczony model programowy oparty na tzw. funkcjach zmian do uzyskiwania informacji o zmianach,
5. Zminimalizować wpływ operacji DML, które powodują zapisywanie zmian. Wpływ ten jest znacznie mniejszy, niż ma to miejsce w przypadku stosowania rozwiązań opartych na własnym kodzie,
6. Uzyskiwać spójne informacje o zmianach danych, ponieważ kolejność zmian jest określona czasem zatwierdzenia transakcji, co z kolei ma wpływ np. przy długotrwałych transakcjach,
7. Skorzystać z narzędzi dostarczonych z konsolą SSMS, zintegrowanych z silnikiem baz danych, w tym z odpowiednich wyrażeń DML, widoków katalogowych oraz mechanizmów bezpieczeństwa.

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