• Artykuł

Akademia SQL - Część 7: Replikacja Peer-to-Peer     Akademia SQL     Akademia SQL - Część 9: Mechanizm przechytywania zmian

Akademia SQL - Część 8: Mechanizm śledzenia zmian Udostępnij na: Facebook

Autor: Damian Widera

Opublikowano: 2 października 2008

Zawartość strony
Mechanizm śledzenia zmian – informacje podstawowe   Mechanizm śledzenia zmian – informacje podstawowe
Mechanizm śledzenia zmian – laboratorium   Mechanizm śledzenia zmian – laboratorium
Mechanizm śledzenia zmian – referencje   Mechanizm śledzenia zmian – referencje

Mechanizm śledzenia zmian – informacje podstawowe

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.

Typowe problemy, które są rozwiązane przez mechanizm śledzenia zmian (ang. change tracking), można przedstawić w następujący sposób:

  • Które wiersze zostały zmienione? Istotna jest informacja, że wiersze zostały zmienione. Z punktu widzenia aplikacji ważna jest wartość po ostatniej zmianie, nie jest istotne ile razy zmieniła się wartość w wierszu ani jakie były jego wartości po każdej zmianie,
  • Czy wiersz się zmienił? Zmiana wartości w wierszu musi zostać zapisana i być dostępna w ramach tej samej transakcji, która zmieniła dane w wierszu

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 z kolei jednoznacznie identyfikują każdy wiersz, który został zmieniony. Zmiany te są dostępne poprzez tzw. funkcje 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.

Mechanizm śledzenia zmian może być włączony w bazie danych z konsoli SSMS lub w wyniku wykonania instrukcji ALTERDATABASE.

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 wyświetlony w momencie, kiedy aplikacja kliencka spróbuje odczytać informacje zawarte w tablicy zmian.

Mechanizm śledzenia zmian może być włączony dla każdej tabeli przy użyciu konsoli SSMS lub w wyniku wykonania instrukcji ALTER TABLE.

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.

Dostęp do informacji zawartych w tablicy zmian jest możliwy 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 (a w zasadzie o ich kluczach głównych). Samo uprawnienie SELECT nie powinno być 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.

Poniżej zostały podsumowane zalety mechanizmu śledzenia zmian, które warto wziąć pod uwagę decydując się wprowadzenie rozwiązania opartego na własnym kodzie czy korzystając z mechanizmu oferowanego przez SQL Server 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.

 Do początku strony Do początku strony

Mechanizm śledzenia zmian – laboratorium

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

Zadanie 1

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

 

     2. W oknie eksploratora obiektów przejdź do sekcji Databases, wybierz bazę danych AdventureWorks2008.
     3. Zaznacz bazę danych AdventureWorks2008 i z menu kontekstowego wybierz opcję Properties.
     4. Przejdź na stronę Change Tracking i zaznacz opcje jak pokazaliśmy na rysunku poniżej:

Ustaw opcje:

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, AutoCleanup na True, co pozwoli na automatyczne czyszczenie tablicy zmian w tle.

      5. Te same operacje można wykonać z poziomu kodu T-SQL. W tym celu otwórz nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz poniższy fragment kodu, który uruchomi mechanizm śledzenia zmian dla bazy danych AdventureWorks2008:

ALTER DATABASE AdventureWorks2008

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

GO

     6. Po włączeniu mechanizmu śledzenia zmian w wybranej bazie danych należy włączyć go również w tabelach, gdzie będziemy śledzić zmiany w danych. Nie trzeba włączać tego mechanizmu we wszystkich tabelach.
     7. W konsoli SSMS w bazie danych AdventureWorks2008 znajdź tabelę Employee w schemacie HumanResources i z menu kontekstowego wybierz opcję Properties.
     8. Przejdź na stronę Change Tracking i skonfiguruj ją, jak pokazano na rysunku poniżej:

Ustaw opcje:

Change Tracking na True, 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.

      9. Te same operacje można wykonać z poziomu kodu T-SQL. W tym celu otwórz nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz poniższy fragment kodu, który uruchomi mechanizm śledzenia dla tablicy HumanResources.Employee w bazie danych AdventureWorks2008:

use AdventureWorks2008

GO

ALTER TABLE HumanResources.Employee

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON);

GO

Zadanie 2

1. Celem zadania nr 2 jest pokazanie, w jaki sposób można wykorzystać zalety mechanizmu śledzenia zmian. W tym celu uruchom konsolę SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

2. W oknie eksploratora obiektów przejdź do sekcji Databases, wybierz bazę danych AdventureWorks2008 i otwórz nowe okno zapytania (skrót klawiszowy CTRL+N).

3. Zmień dane w tablicy HumanResources.Employee tak, jak na poniższym fragmencie kodu:

UPDATE HumanResources.Employee

SET HireDate=CAST(GETDATE() AS DATE)

WHERE BusinessEntityID=1

4. Sprawdź, za pomocą funkcji systemowej CHANGETABLE, czy zmiany zostały zarejestrowane w tablicy zmian:

SELECT * FROM 

CHANGETABLE(CHANGES HumanResources.Employee,0) AS CT

5. Pierwszy parametr funkcji CHANGETABLE odnosi się do tabeli, w której śledzone są zmiany. Drugi parametr określa, który numer zmiany aplikacja kliencka juz posiada. Pozwala to pobierać tylko najnowsze zmiany od konkretnej, zapisanej wcześniej.

Aktualny numer zarejestrowanej zmiany odczytasz za pomocą funkcji CHANGE_TRACKING_CURRENT_VERSION:

CHANGE_TRACKING_CURRENT_VERSION:



SELECT CHANGE_TRACKING_CURRENT_VERSION();

GO

6. Zwróć uwagę, że tablica zmian przechowuje (oprócz kolumn systemowych), tylko kolumny klucza głównego tabeli źródłowej. W tym celu wykonaj jeszcze raz zapytanie z punktu 4.

SYS_CHANGE_VERSION   SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS    SYS_CHANGE_CONTEXT BusinessEntityID

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

17                   NULL                        U                    0x000000000A000000    NULL               1

11                   NULL                        U                    0x000000000D000000    NULL               278

15                   NULL                        U                    0x000000000C000000    NULL               283



(3 row(s) affected)

7. Wykonaj poniższe zapytanie, które pozwoli skorelować informacje zawarte w tablicy zmian z informacjami z tablicy źródłowej:

SELECT

    E.LoginID

    ,E.HireDate

    ,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, 0) AS CT

ON E.BusinessEntityID = CT.BusinessEntityID 

ORDER BY E.LoginID;

Przykładowy wynik może być następujący:

LoginID                      HireDate   Klucz glowny    Operacja    Mapa bitowa zmienionej kolumny

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

adventure-works\david8      2001-07-01      283          update         0x000000000C000000

adventure-works\garrett1    2001-07-01      278          update         0x000000000D000000

adventure-works\ken0        2008-08-26      1            update         0x000000000A000000



(3 row(s) affected)

8. Zwróć uwagę na rodzaj złączenia występujący w zapytaniu. RIGHT OUTER JOIN zapewni wyświetlenie wyniku nawet wtedy, gdy rekord został usunięty z tablicy źródłowej.

9. W kolumnie Mapa bitowa zmienionej kolumny zawarliśmy informację, która kolumna została zmieniona w wyniku wykonania operacji DML. Ta informacja będzie dostępna tylko wtedy, gdy mechanizm śledzenia zmian został uruchomiony z opcją TRACK_COLUMNS_UPDATED = ON dla na poziomie tablicy.

10. Uzyskanie nazwy kolumny na podstawie jej identyfikatora jest możliwe po wykonaniu następującej konwersji, wykorzystującej systemowa funkcję COLUMNPROPERTY:

DECLARE @HireDate INT = COLUMNPROPERTY(

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

11. Zmodyfikuj zapytanie z punktu 7 w podany poniżej sposób:

DECLARE @HireDate INT = COLUMNPROPERTY(

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





SELECT

    E.LoginID

    ,CASE

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

        ELSE NULL

        END AS CT_HireDate

    ,CHANGE_TRACKING_IS_COLUMN_IN_MASK(@HireDate, CT.SYS_CHANGE_COLUMNS) AS CT_HireDate_Changed

    , CT.SYS_CHANGE_COLUMNS

FROM HumanResources.Employee E JOIN

    CHANGETABLE(CHANGES HumanResources.Employee, 0) AS CT

ON E.BusinessEntityID = CT.BusinessEntityID 

ORDER BY E.LoginID;

12. Zwróć uwagę, na otrzymany przykładowy wynik tego zapytania (Twój może się różnić):

LoginID                    CT_HireDate      CT_HireDate_Changed     SYS_CHANGE_COLUMNS

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

adventure-works\david8      NULL                0                   0x000000000C000000

adventure-works\garrett1    NULL                0                   0x000000000D000000

adventure-works\ken0        2008-08-26          1                   0x000000000A000000



(3 row(s) affected)

W naszym ćwiczeniu zmieniliśmy w punkcie 3 wartość w kolumnie HireDate dla jednej osoby i dlatego możesz dla niej znaleźć wartość 1 w kolumnie CT_HireDate_Changed a dla pozostałych osób wartość ta wynosi 0, co oznacza, że innym osobom zmieniano wartości w innych kolumnach. Na podstawie wartości w kolumnie SYS_CHANGE_COLUMNS i sposobu pokazanego w punkcie 10 sprawdź, które to kolumny.

 Do początku strony Do początku strony

Mechanizm śledzenia zmian – referencje

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

[1] Co nowego w silniku bazodanowym SQL Server 2008 November CTP

[2] Wpis na blogu Sync Team firmy Microsoft (j. ang.)

[3] Database Change for SQL Server - webcast (j. ang.)

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

  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  

Akademia SQL - Część 7: Replikacja Peer-to-Peer     Akademia SQL     Akademia SQL - Część 9: Mechanizm przechytywania zmian