śledzenie zmian w SQL Server to elastyczna i łatwa w użyciu technologia monitorowania tabel pod kątem wstawiania, aktualizacji i usuwania. W tym poście omówię wprowadzenie do śledzenia zmian w SQL Server i pokażę przykład, jak zacząć z nim.
śledzenie zmian w SQL Server
śledzenie zmian jest lekkim mechanizmem śledzenia, które wiersze zostały wstawione, zaktualizowane i usunięte w tabelach monitorowanych przez śledzenie zmian. Śledzenie zmian po raz pierwszy pojawił się w SQL Server 2008 i był w każdej wersji od tego czasu. Co więcej, śledzenie zmian jest dostępne w każdej edycji SQL Server, nawet w bezpłatnej edycji express.
w skrócie, oto jak to działa: W tabelach, które mają włączone śledzenie zmian, zmiana netto każdego wiersza jest śledzona wewnętrznie i jest dostępna za pomocą funkcji śledzenia zmian. Każda zmiana ma dołączony identyfikator wersji i będzie nowy identyfikator wersji dla każdego wiersza, który zostanie wstawiony, zaktualizowany lub usunięty. Wersja śledzenia zmian jest 8-bajtową liczbą całkowitą (BIGINT), która odzwierciedla najnowszy identyfikator zmiany w tej bazie danych. Ważne jest, aby pamiętać, że wersja śledzenia zmian nie jest specyficzna dla tabeli – operacja DML w dowolnej śledzonej tabeli w każdej bazie danych zwiększy numer wersji. Numer wersji zawsze będzie sekwencyjny, ale niekoniecznie będzie sąsiadujący w obrębie jednej tabeli, jeśli istnieje więcej niż jedna tabela włączona do śledzenia zmian.
śledzenie zmian jest włączone na poziomie bazy danych. Następnie każda tabela, która będzie monitorowana, musi być indywidualnie wpisana do śledzenia zmian. Każda tabela, która ma być monitorowana za pomocą śledzenia zmian, musi mieć klucz podstawowy, ponieważ jest to identyfikator na poziomie wiersza używany do raportowania operacji DML w ramach śledzenia zmian. Po włączeniu śledzenia zmian na poziomie tabeli można zdecydować się na śledzenie kolumn zmienionych w najnowszej aktualizacji, co zapewni lepszy wgląd w zmiany.
po skonfigurowaniu śledzenie zmian jest stosunkowo prostym procesem. Istnieje kilka funkcji-przede wszystkim CHANGE_TRACKING_CURRENT_VERSION() i CHANGETABLE (), które mogą być używane do sprawdzania znacznika bieżącej wersji w śledzeniu zmian i pobierania listy ostatnich zmian. Wkrótce zademonstruję obie te funkcje.
śledzenie zmian nie jest rejestrowaniem audytu
będę uważać, aby nie używać słów audyt lub rejestrowanie do opisania śledzenia zmian. Powiem jasno: nie jest to pełny mechanizm rejestrowania. Historia zmian nie jest w ogóle śledzona – śledzenie zmian tylko informuje o tym, że nastąpiła zmiana, ale nie zachowuje historii wersji. Rozważmy przypadek wiersza danych o ID 1234. Ten wiersz jest wstawiany, a następnie aktualizowany 5 razy, a następnie usuwany. Śledzenie zmian nie pokazuje historii wstawiania, aktualizacji i usuwania; raczej zgłosiłby tylko zmianę netto, że wiersz o ID 1234 został usunięty. Jeśli proces ładowania wymaga szczegółowej historii logowania dla każdej zmiany (a nie tylko delta wszystkich zmian), musisz użyć czegoś takiego jak przechwytywanie danych zmian.
Konfigurowanie śledzenia zmian w SQL Server
włączenie śledzenia zmian na poziomie tabeli jest procesem dwuetapowym. Po pierwsze, musi być włączona w bazie danych. Można to zrobić za pomocą interfejsu użytkownika we właściwościach bazy danych, na karcie śledzenie zmian.
jak pokazano, po włączeniu śledzenia zmian w bazie danych nie ma wiele do skonfigurowania. Wystarczy ustawić wartość śledzenia zmian na True, aby skonfigurować śledzenie zmian dla tej bazy danych. Opcjonalnie można również zmodyfikować wartość okresu przechowywania. Domyślną wartością jest 2 dni, które nadpisałem w tym przykładzie, aby zamiast tego użyć 14 dni. Podobnie jak w przypadku większości operacji UI, istnieje polecenie T-SQL, które robi to samo. Poniżej znajduje się polecenie skonfigurowania śledzenia zmian w tej bazie danych.
po tym kroku śledzenie zmian jest włączone, ale jeszcze niczego nie śledzi. Nadal musi być włączona dla każdej tabeli, która ma być śledzona. Interfejs Właściwości tabeli bardzo to ułatwia.
jak pokazano, wystarczy zmienić wartość śledzenia zmian na True, aby włączyć śledzenie zmian dla tej tabeli. W tym przykładzie zdecydowałem się również śledzić kolumny zmienione podczas aktualizacji (więcej na ten temat w nieco).
ostatni powyższy krok zostanie powtórzony dla każdej tabeli, która ma być śledzona w śledzeniu zmian. Po włączeniu śledzenia zmian wszelkie zmiany (wstawianie, aktualizacje lub usuwanie) do tej tabeli będą przechowywane w pamięci podręcznej śledzenia zmian.
Konfigurowanie śledzenia zmian
w powyższym przykładzie zamierzam wstawić, zaktualizować i usunąć niektóre dane, aby zademonstrować, jak uzyskać dostęp do danych śledzenia zmian wygenerowanych dla tych operacji DML. Dla odniesienia, oto struktura tabeli.
pokazałem wcześniej, jak włączyć śledzenie zmian dla pojedynczej tabeli za pomocą interfejsu użytkownika. Wolę używać T-SQL do tego zadania, ponieważ jest łatwiej powtarzalny. Włączenie śledzenia zmian dla tabeli, którą utworzyłem powyżej, można zrobić tak, jak pokazano tutaj:
przypomnij sobie, że wspomniałem wcześniej, że śledzenie zmian wykorzystuje identyfikator wersji do śledzenia bieżącej wersji śledzonych tabel. Ten identyfikator wersji jest naszym znacznikiem osi czasu do wykrywania zmian. Aby pobrać tę wartość, istnieje bardzo prosta funkcja: CHANGE_TRACKING_CURRENT_VERSION (). Jest on używany jak pokazano poniżej.
w moim systemie testowym wartość ta wynosi 470(ponieważ przeprowadziłem kilka testów przed tym pisaniem). To jest punkt wyjścia, a wszelkie zmiany wprowadzone od tego momentu spowodowałyby powstanie nowego numeru wersji. Zanotuję tę wartość, a teraz wprowadzę kilka zmian w tabeli opisanej powyżej. Wstawię kilka wierszy, aby pokazać, jak śledzenie zmian wyświetla wstawki.
po wstawieniu tych sześciu wierszy ponownie sprawdzam wartość CHANGE_TRACKING_CURRENT_VERSION() i stwierdzam, że wartość wynosi teraz 476. Został zwiększony o 6-jeden na wstawiony wiersz, czego się spodziewałem.
Korzystanie z funkcji śledzenia zmian
następnie użyjemy funkcji śledzenia zmian CHANGETABLE (), aby pokazać zmiany netto w tej tabeli.
żeby to rozbić:
- CHANGETABLE to funkcja systemowa o wartości tabeli, która zwróci listę zmian zapisanych w śledzeniu zmian
- zmiany wskazują, że szukam zmian, ponieważ określona wersja
- @ver jest zmienną, którą skonfigurowałem do przechowywania numeru wersji. CHANGETABLE zwróci wszystkie wyniki odzwierciedlające zmiany od tej wersji. Zauważ, że możesz użyć zmiennej tak jak ja, lub po prostu przekazać liczbę skalarną (używając dosłownego 470 tutaj osiągnęłoby to samo)
po uruchomieniu powyższego kodu otrzymuję następujący zestaw wyników.
to informuje mnie o wersji wstawiania i/lub aktualizacji, operacji (odpowiednio I, U lub D dla wstawiania, aktualizowania lub usuwania), masce kolumny dla operacji aktualizacji (więcej na ten moment) i głównym kluczu wiersza, na który ta zmiana ma wpływ. Ponieważ CHANGETABLE() zwraca tabelę, mogę łatwo dołączyć ten wynik z powrotem do oryginalnej tabeli, aby zobaczyć operację zmiany wraz z bieżącymi danymi w tej tabeli.
to będzie wyglądać trochę inaczej dla operacji aktualizacji. Następnie wykonam instrukcję aktualizacji, ale najpierw zwrócę uwagę na aktualną wersję śledzenia zmian (która nadal jest 476).
teraz instrukcja update, która zaktualizuje dwa wiersze w tabeli:
teraz, gdy uruchamiam kod CHANGETABLE() z góry, używając nowszej wersji śledzenia zmian (476) jako punktu wyjścia, otrzymuję inny wynik:
to są metadane dla wszystkich zmian od wersji 476, które zawierają tylko dwa wiersze zaktualizowane z powyższej instrukcji aktualizacji. Zauważ, że wersja creation jest null, ponieważ ta zmiana była aktualizacją, a nie wstawką. Ponadto, wartość SYS_CHANGE_COLUMNS jest teraz wypełniona, choć tak naprawdę nie pokazuje nam, co się zmieniło (jeszcze). To dobry moment, aby porozmawiać o funkcji śledzenia zmian CHANGE_TRACKING_IS_COLUMN_IN_MASK(). Ta funkcja sprawdzi, czy podana kolumna została zaktualizowana od najnowszej wersji. Jego składnia jest nieco dziwaczna, ale aby sprawdzić, czy nazwa Środkowa została zaktualizowana, zapytanie wyglądałoby tak:
szczerze mówiąc, Nie wiem, czy kiedykolwiek używałem funkcji CHANGE_TRACKING_IS_COLUMN_IN_MASK. To trochę uciążliwe, ponieważ musisz uruchomić to dla każdej kolumny, którą chcesz sprawdzić. Większość mojej pracy dotyczy hurtowni danych i natknąłem się na kilka przypadków, w których muszę dokładnie wiedzieć, które kolumny zostały zaktualizowane – chcę tylko wiedzieć, czy wiersz został zaktualizowany. Jednak w przypadku innych scenariuszy (zwłaszcza w OLTP) widzę potrzebę tego.
zademonstrowałem wstawki i aktualizacje. Przyjrzyjmy się, jak wyglądałoby usunięcie. Ponownie zanotuję aktualny numer wersji – 478 – do następnej operacji. Teraz usunę jeden wiersz danych:
po usunięciu jednego wiersza ponownie uruchomię CHANGETABLE (), aby zobaczyć, jakie raporty śledzenia zmian dla tej operacji.
znajduję jeden wiersz, który usunąłem w ostatniej operacji, z SYS_CHANGE_OPERATION ustawionym na D (Usuń):
teraz, pamiętaj, że Numer wersji robi różnicę tutaj! Numer wersji przekazany do CHANGETABLE () jest punktem wyjścia dla wszelkich zmian zwracanych przez tę funkcję. Poprzez to ćwiczenie sprawdzałem wyniki śledzenia zmian po każdej operacji DML. Mogę jednak ustawić początkowy numer wersji na dowolny prawidłowy numer wersji lub po prostu użyć NULL, aby uzyskać wszystkie dostępne wyniki śledzenia zmian dla tej tabeli. Aby zademonstrować, ustawię wartość z powrotem do wersji 470-punkt początkowy przed wszelkimi aktualizacjami-aby pokazać, jak wyglądałaby pełna historia. Kiedy ponownie uruchomię CHANGETABLE () używając naszej oryginalnej wersji śledzenia zmian, otrzymuję następujące informacje:
jest tu kilka przewidywalnych niuansów. Po pierwsze, wiersz pokazujący ID rekordu 1 (który był rekordem Phoebe Buffay, który usunąłem) pojawia się po prostu jako operacja usuwania, mimo że wiersz ten został wstawiony, a następnie usunięty od numeru wersji startowej. Pamiętaj, że to delta będzie wyświetlana-każda operacja przeciwko temu wierszowi nie jest zachowywana w śledzeniu zmian. Dla identyfikatorów numerowanych 2 i 4 – które były dwoma wierszami, które wstawiłem, a następnie zaktualizowałem-SYS_CHANGE_OPERATION pokazuje wstawienie, mimo że zaktualizowaliśmy oba rekordy po wstawieniu. Mówi się, że sys_change_version i sys_change_creation_version w tych wierszach nie pasują, co wskazuje, że ostatnią zmianą nie była wstawka.
wniosek
śledzenie zmian jest prostym i lekkim sposobem wykrywania zmian w SQL Server. Korzystanie ze śledzenia zmian umożliwia łatwą identyfikację nowych, zmienionych i usuniętych danych, eliminując potrzebę porównań z użyciem brutalnej siły. W następnym poście przyjrzę się temu z perspektywy ETL, integrując śledzenie zmian w procesie ładowania end-to-end.