Co to jest przechwytywanie danych zmian (CDC)?
Dotyczy:
programu SQL ServerAzure SQL Managed Instance
W tym artykule omówiono przechwytywanie zmian danych (CDC), które rejestruje działanie w bazie danych, gdy tabele i wiersze zostały zmodyfikowane.
W tym artykule wyjaśniono, jak usługa CDC współpracuje z programem SQL Server i usługą Azure SQL Managed Instance. Aby uzyskać informacje o usłudze Azure SQL Database, zobacz CDC with Azure SQL Database.
Przegląd
Przechwytywanie zmian danych wykorzystuje agenta programu SQL Server do rejestrowania wstawiania, aktualizacji i usuwania występujących w tabeli. Te zmiany danych są łatwo dostępne do wykorzystania w formacie relacyjnym. Dane kolumn i istotne metadane, potrzebne do zastosowania tych zmian w środowisku docelowym, są przechwytywane dla zmodyfikowanych wierszy i przechowywane w tabelach zmian, które odzwierciedlają strukturę kolumn źródłowych tabel śledzonych. Ponadto funkcje wyceniane w tabelach są dostępne w celu systematycznego dostępu do tych zmian danych przez użytkowników.
Dobrym przykładem użytkownika danych, na który ta technologia jest ukierunkowana, jest aplikacja do wyodrębniania, przekształcania i ładowania (ETL). Aplikacja ETL ładuje przyrostowo zmiany danych z tabel źródłowych programu SQL Server do magazynu danych lub składnic danych. Chociaż reprezentacja tabel źródłowych w magazynie danych musi odzwierciedlać zmiany w tabelach źródłowych, kompleksowa technologia, która odświeża replikę źródła, nie jest odpowiednia. Zamiast tego potrzebny jest niezawodny ustrukturyzowany strumień danych zmian, aby użytkownicy mogli zastosować je do różnych docelowych reprezentacji danych. Funkcja przechwytywania zmian danych programu SQL Server zapewnia tę technologię.
Przepływ danych
Na poniższej ilustracji przedstawiono główny przepływ danych na potrzeby przechwytywania danych zmian.
Źródłem danych zmian do przechwytywania danych zmian jest dziennik transakcji programu SQL Server. Gdy do śledzonych tabel źródłowych są stosowane operacje wstawiania, aktualizacji i usuwania, do dziennika są dodawane wpisy opisujące te zmiany. Log służy jako dane wejściowe do procesu przechwytywania. Następnie odczytuje dziennik i dodaje informacje o zmianach do powiązanej tabeli zmian dotyczącej śledzonej tabeli. Funkcje są udostępniane w celu wyliczenia zmian, które pojawiają się w tabelach zmian w określonym zakresie, zwracając informacje w postaci filtrowanego zestawu wyników. Filtrowany zestaw wyników jest zwykle używany przez proces aplikacji w celu zaktualizowania reprezentacji źródła w niektórych środowiskach zewnętrznych.
Instancja przechwytywania
Przed śledzeniem zmian w poszczególnych tabelach w bazie danych należy jawnie włączyć przechwytywanie zmian dla bazy danych. Odbywa się to przy użyciu procedury składowanej sys.sp_cdc_enable_db. Gdy baza danych jest włączona, tabele źródłowe można zidentyfikować jako śledzone tabele przy użyciu procedury przechowywanej sys.sp_cdc_enable_table. Gdy tabela jest włączona do przechwytywania zmian danych, tworzone jest skojarzone wystąpienie przechwytywania w celu obsługi rozpowszechniania danych zmian w tabeli źródłowej. Instancja przechwytywania składa się z tabeli zmian i maksymalnie dwóch funkcji zapytań. Metadane opisujące szczegóły konfiguracji wystąpienia przechwytywania są przechowywane w tabelach metadanych przechwytywania zmian cdc.change_tables, cdc.index_columnsi cdc.captured_columns. Tę informację można pobrać przy użyciu procedury składowanej sys.sp_cdc_help_change_data_capture.
Wszystkie obiekty skojarzone z wystąpieniem przechwytywania są tworzone w schemacie przechwytywania zmian w włączonej bazie danych. Wymagania dotyczące nazwy wystąpienia przechwytywania są takie, że musi to być prawidłowa nazwa obiektu oraz że powinna być unikatowa w wystąpieniach przechwytywania bazy danych. Domyślnie nazwa to <nazwa schematu_nazwa tabeli> tabeli źródłowej. Nazwa skojarzonej z nią tabeli zmian powstaje poprzez dołączenie _CT do nazwy wystąpienia przechwytywania. Funkcja używana do zapytań o wszystkie zmiany jest określana przez dodanie ciągu fn_cdc_get_all_changes_ na początku nazwy wystąpienia przechwytywania. Jeśli instancja przechwytywania jest skonfigurowana do obsługi zmian sieciowych, funkcja zapytania net_changes jest również tworzona i nazywana przez dodanie przedrostka fn_cdc_get_net_changes_ do nazwy instancji przechwytywania.
Ważny
Maksymalna liczba wystąpień przechwytywania, które mogą być współbieżnie skojarzone z pojedynczą tabelą źródłową, wynosi dwa.
Zmień tabelę
Pięć pierwszych kolumn tabeli zmian przechwytywania danych to kolumny metadanych. Zawierają one dodatkowe informacje istotne dla zarejestrowanej zmiany. Pozostałe kolumny odzwierciedlają zidentyfikowane kolumny z tabeli źródłowej pod względem nazwy i zazwyczaj typu. Te kolumny przechowują przechwycone dane kolumn zebrane z tabeli źródłowej.
Każda operacja wstawiania lub usuwania, która jest stosowana do tabeli źródłowej, jest wyświetlana jako pojedynczy wiersz w tabeli zmian. Kolumny danych wiersza, które są wynikiem operacji wstawiania, zawierają wartości kolumn po wstawieniu. Kolumny danych wiersza, które są wynikiem operacji usuwania, zawierają wartości kolumn przed usunięciem. Operacja aktualizacji wymaga wpisu z jednym wierszem w celu zidentyfikowania wartości kolumn przed aktualizacją oraz drugiego wpisu wiersza w celu zidentyfikowania wartości kolumn po aktualizacji.
Każdy wiersz w tabeli zmian zawiera również inne metadane umożliwiające interpretację działania zmiany. Kolumna __$start_lsn identyfikuje numer sekwencji dziennika zatwierdzeń (LSN), który został przypisany do zmiany. Numer LSN zatwierdzenia identyfikuje zarówno zmiany, które zostały zatwierdzone w ramach tej samej transakcji, jak i porządkuje te transakcje. Kolumna __$seqval może służyć do zamawiania większej liczby zmian występujących w tej samej transakcji. Kolumna __$operation rejestruje operację skojarzona ze zmianą: 1 = delete, 2 = insert, 3 = update (przed obrazem) i 4 = update (po obrazie). Kolumna __$update_mask jest zmienną maską bitową z jednym zdefiniowanym bitem dla każdej przechwyconej kolumny. W przypadku wstawiania i usuwania wpisów maska aktualizacji ma ustawione wszystkie bity. Jednak wiersze aktualizacji będą miały zestaw bitów odpowiadający zmienionym kolumnom.
Interwał ważności
Interwał ważności przechwytywania zmian danych dla bazy danych to czas, w którym dane zmiany są dostępne dla wystąpień przechwytywania. Interwał ważności rozpoczyna się od momentu utworzenia pierwszego wystąpienia zarejestrowania dla tabeli bazy danych i trwa do chwili obecnej.
Baza danych
Dane, które są deponowane w tabelach zmian, stają się niezarządzane, jeśli dane nie są okresowo i systematycznie czyszczone. Proces oczyszczania danych przechwytywania zmian jest odpowiedzialny za wymuszanie polityki oczyszczania opartej na retencji. Najpierw przenosi niski punkt końcowy interwału ważności, aby spełnić ograniczenie czasu. Następnie usuwa wygasłe wpisy tabeli zmian. Domyślnie są zachowywane trzy dni danych.
Na zaawansowanym poziomie, gdy proces przechwytywania zatwierdza każdą nową partię danych zmian, nowe wpisy są dodawane do cdc.lsn_time_mapping dla każdej transakcji, która ma wpisy w tabeli zmian. W tabeli mapowania są zachowywane zarówno numer sekwencji dzienników zatwierdzeń (LSN), jak i czas zatwierdzania transakcji (odpowiednio kolumny start_lsn i tran_end_time). Maksymalna wartość LSN znaleziona w cdc.lsn_time_mapping reprezentuje górną granicę okna ważności bazy danych. Odpowiedni czas zatwierdzenia jest używany jako podstawa, z której czyszczenie oparte na zasadach przechowywania oblicza nowy dolny próg dla danych.
Ponieważ proces przechwytywania wyodrębnia dane zmiany z dziennika transakcji, istnieje wbudowane opóźnienie między czasem zatwierdzenia zmiany w tabeli źródłowej a czasem pojawienia się zmiany w skojarzonej tabeli zmian. Chociaż to opóźnienie jest zwykle małe, należy pamiętać, że dane zmiany nie są dostępne, dopóki proces przechwytywania nie przetworzył powiązanych wpisów dziennika.
Instancja przechwytywania
Chociaż okres ważności bazy danych i okres ważności pojedynczego wystąpienia przechwytywania często się pokrywają, nie zawsze jest to prawdziwe. Interwał ważności sesji przechwytywania rozpoczyna się, gdy proces przechwytywania rozpoznaje tę sesję i zaczyna zapisywać skojarzone zmiany w tabeli zmian. W związku z tym, jeśli instancje przechwytywania są tworzone w różnym czasie, każda z nich będzie miała inny dolny punkt końcowy. Kolumna start_lsn zestawu wyników zwrócona przez sys.sp_cdc_help_change_data_capture pokazuje bieżący niski punkt końcowy dla każdego zdefiniowanego wystąpienia przechwytywania. Gdy proces czyszczenia usuwa zmiany w wpisach tabeli, dostosowuje wartości start_lsn dla wszystkich instancji przechwytywania, aby odzwierciedlić nowy dolny próg dostępnych danych zmiany. Tylko te wystąpienia przechwytywania, których wartości start_lsn są mniejsze od obecnego nowego niskiego znacznika wody, są dostosowywane. W miarę upływu czasu, jeśli nie są tworzone żadne nowe wystąpienia przechwytywania, interwały ważności dla wszystkich wystąpień będą zwykle pokrywać się z interwałem ważności bazy danych.
Interwał ważności jest ważny dla użytkowników danych zmiany, ponieważ interwał wyodrębniania żądania musi być w pełni objęty bieżącym interwałem ważności przechwytywania danych zmiany dla wystąpienia przechwytywania. Jeśli niski punkt końcowy interwału wyodrębniania znajduje się po lewej stronie niskiego punktu końcowego interwału ważności, mogą brakować danych dotyczących zmian z powodu agresywnego czyszczenia. Jeśli wysoki punkt końcowy interwału wyodrębniania znajduje się po prawej stronie wysokiego punktu końcowego interwału ważności, oznacza to, że proces przechwytywania danych nie został jeszcze przetworzony przez czas reprezentowany przez interwał wyodrębniania, a także mogą brakować danych dotyczących zmian.
Funkcja sys.fn_cdc_get_min_lsn służy do pobierania bieżącej minimalnej wartości LSN dla wystąpienia przechwytywania, podczas gdy sys.fn_cdc_get_max_lsn jest używana do pobierania bieżącej maksymalnej wartości LSN. Jeśli podczas wykonywania zapytań dotyczących zmian danych określony zakres LSN nie znajduje się w tych dwóch wartościach LSN, funkcja kwerend przechwytywania zmian danych nie powiedzie się.
Obsługa zmian w tabeli źródłowej
Śledzenie zmian kolumn w śledzonych tabelach źródłowych jest trudnym problemem dla odbiorców podrzędnych. Mimo że włączenie przechwytywania danych zmian w tabeli źródłowej nie zapobiega wystąpieniu takich zmian DDL, przechwytywanie zmian danych zmniejsza wpływ na konsumentów, zachowując dostarczone zestawy wyników zwracane za pośrednictwem interfejsu API, nawet gdy struktura kolumn podstawowej tabeli źródłowej zmienia się. Ta stała struktura kolumn jest również odzwierciedlona w bazowej tabeli zmian, do której uzyskują dostęp zdefiniowane funkcje zapytań.
Proces przechwytywania odpowiedzialny za wypełnianie tabeli zmian uwzględnia stałą tabelę zmian struktury kolumn, ignorując wszystkie nowe kolumny, które nie zostały zidentyfikowane do przechwytywania, gdy tabela źródłowa została włączona na potrzeby przechwytywania danych zmian. Jeśli śledzona kolumna zostanie porzucona, wartości null zostaną podane dla kolumny w kolejnych wpisach zmiany. Jeśli jednak istniejąca kolumna przejdzie zmianę w swoim typie danych, zmiana zostanie rozpropagowana do tabeli zmian, aby upewnić się, że mechanizm przechwytywania nie wprowadza utraty danych do śledzonych kolumn. Proces przechwytywania publikuje również wszelkie wykryte zmiany w strukturze kolumn śledzonych tabel w tabeli cdc.ddl_history. Użytkownicy, którzy chcą otrzymywać powiadomienia o zmianach, które mogą być konieczne w aplikacjach podrzędnych, powinni użyć procedury składowanej sys.sp_cdc_get_ddl_history.
Zazwyczaj bieżące wystąpienie przechwytywania zachowuje swój kształt, gdy zmiany DDL są stosowane do skojarzonej tabeli źródłowej. Istnieje jednak możliwość utworzenia drugiej instancji przechwytywania dla tabeli, która odzwierciedla nową strukturę kolumn. Ta opcja umożliwia proces przechwytywania zmian w tej samej tabeli źródłowej na dwie odrębne tabele zmian o dwóch różnych strukturach kolumn. W związku z tym jedna tabela zmian może nadal karmić bieżące programy operacyjne, a druga może napędzać środowisko deweloperskie, które próbuje uwzględnić nowe dane kolumn. Zezwolenie mechanizmowi przechwytywania na wypełnianie obu tabel zmian w tandemie oznacza, że przejście z jednej do drugiej można osiągnąć bez utraty danych zmian. Może się to zdarzyć za każdym razem, gdy nachodzą na siebie harmonogramy przechwytywania danych o zmianach. Gdy zostanie wykonane przejście, można usunąć przestarzałe wystąpienie przechwytywania.
Ważny
Maksymalna liczba wystąpień przechwytywania, które mogą być współbieżnie skojarzone z pojedynczą tabelą źródłową, wynosi dwa.
Relacja z agentem czytnika dzienników
Logika procesu przechwytywania zmian danych jest osadzona w procedurze składowanej sp_replcmds, wewnętrznej funkcji serwera utworzonej w ramach sqlservr.exe, a także używanej przez replikację transakcyjną do zbierania zmian z dziennika transakcji. W programie SQL Server i usłudze Azure SQL Managed Instance, gdy funkcja przechwytywania zmian w danych jest włączona tylko dla bazy danych, należy utworzyć zadanie agenta SQL Server do przechwytywania danych jako mechanizm do wywoływania sp_replcmds. Gdy replikacja jest również obecna, tylko czytnik dzienników transakcyjnych jest używany do zaspokojenia potrzeb związanych ze zmianą danych dla obu tych odbiorców. Ta strategia znacznie zmniejsza rywalizację o dzienniki, gdy zarówno replikacja, jak i przechwytywanie zmian danych są włączone dla tej samej bazy danych.
Przełączanie między tymi dwoma trybami operacyjnymi w celu przechwytywania danych zmian odbywa się automatycznie, gdy wystąpi zmiana stanu replikacji bazy danych z włączoną funkcją przechwytywania danych zmian.
Notatka
W programach SQL Server i Azure SQL Managed Instance oba wystąpienia logiki przechwytywania wymagają uruchomienia agenta programu SQL Server w celu wykonania procesu.
Głównym zadaniem procesu przechwytywania jest skanowanie dziennika i zapisywanie danych kolumn oraz informacji związanych z transakcjami w tabelach przechwytywania zmian danych. Aby zapewnić spójność transakcyjną we wszystkich tabelach przechwytywania danych o zmianach, proces przechwytywania otwiera i zamyka własną transakcję w każdym cyklu skanowania. Wykrywa, kiedy tabele są nowo włączone do przechwytywania danych zmian i automatycznie dołączają je do zestawu tabel, które są aktywnie monitorowane pod kątem wpisów zmian w dzienniku. Podobnie wyłączenie przechwytywania zmian danych również zostanie wykryte, co spowoduje usunięcie tabeli źródłowej z zestawu tabel aktywnie monitorowanych pod kątem danych zmian. Po zakończeniu przetwarzania sekcji dziennika proces przechwytywania sygnalizuje logikę obcinania dziennika serwera, która używa tych informacji do identyfikowania wpisów dziennika kwalifikujących się do obcinania.
Ważny
Jeśli baza danych jest włączona dla przechwytywania zmian, nawet jeśli tryb odzyskiwania jest ustawiony na tryb prostego odzyskiwania, punkt obcinania dziennika nie zostanie przesunięty do momentu zebrania wszystkich zmian oznaczonych do przechwytywania przez proces przechwytywania. Jeśli proces przechwytywania nie jest uruchomiony i występują dane do zebrania, wykonanie polecenia CHECKPOINT nie spowoduje skrócenia dziennika.
Proces przechwytywania jest również używany do utrzymywania historii zmian DDL dla śledzonych tabel. Instrukcje DDL skojarzone z przechwytywaniem danych zmiany tworzą wpisy w dzienniku transakcji bazy danych za każdym razem, gdy baza danych z włączoną funkcją przechwytywania zmian lub tabela zostanie porzucona lub kolumny tabeli obsługującej przechwytywanie danych zmiany są dodawane, modyfikowane lub porzucane. Te wpisy dziennika są przetwarzane przez proces przechwytywania, który następnie publikuje skojarzone zdarzenia DDL do tabeli cdc.ddl_history. Informacje o zdarzeniach DDL, które mają wpływ na śledzone tabele, można uzyskać przy użyciu procedury składowanej sys.sp_cdc_get_ddl_history.
Ostrzeżenie
- MaxCmdsInTran nie została zaprojektowana tak, aby zawsze była włączona. Istnieje w celu obejścia przypadków, w których ktoś przypadkowo wykonał dużą liczbę operacji DML w jednej transakcji (powodując opóźnienie w dystrybucji poleceń, dopóki cała transakcja nie znajduje się w bazie danych dystrybucji, blokady itp.). Jeśli rutynowo wchodzisz w tę sytuację, przejrzyj logikę aplikacji, aby znaleźć sposoby zmniejszenia rozmiaru transakcji.
- MaxCmdsInTran nie jest obsługiwana, jeśli dana baza danych publikacji ma włączoną zarówno usługę CDC, jak i replikację. Użycie MaxCmdsInTran w tej konfiguracji może prowadzić do utraty danych w tabelach zmian CDC. Może to również powodować błędy PK, jeśli parametr MaxCmdsInTran jest dodawany i usuwany podczas replikowania dużej transakcji.
Zadania agenta
Dwa zadania agenta programu SQL Server są zwykle skojarzone z włączoną bazą danych przechwytywania zmian: jedno, które jest używane do wypełniania tabel zmian bazy danych, i jedno, które jest odpowiedzialne za oczyszczanie tabeli zmian. Oba zadania składają się z jednego kroku, który uruchamia polecenie Transact-SQL. Polecenie Transact-SQL, które jest wywoływane, to zdefiniowana procedura składowana do przechwytywania danych o zmianach, która implementuje logikę zadania. Zadania są tworzone, gdy pierwsza tabela bazy danych jest włączona do obsługi przechwytywania zmian danych. Zadanie czyszczenia jest zawsze tworzone. Zadanie przechwytywania zostanie utworzone tylko wtedy, gdy nie ma zdefiniowanych publikacji transakcyjnych dla bazy danych. Zadanie przechwytywania jest również tworzone, gdy zarówno przechwytywanie zmian danych, jak i replikacja transakcyjna są włączone dla bazy danych, a zadanie czytnika dzienników transakcyjnych jest usuwane, ponieważ baza danych nie ma już zdefiniowanych publikacji.
Zadania przechwytywania i oczyszczania są tworzone przy użyciu domyślnych parametrów. Zadanie przechwytywania rozpoczyna się natychmiast. Jest on uruchamiany w sposób ciągły, przetwarza maksymalnie 1000 transakcji na cykl skanowania z opóźnieniem wynoszącym 5 sekund między cyklami. Zadanie oczyszczania jest uruchamiane codziennie o godzinie 2:00. Zachowuje wpisy tabeli zmiany przez 4320 minut lub 3 dni, usuwając maksymalnie 5000 wpisów pojedynczym poleceniem usuń.
Zadania agenta przechwytywania zmian są usuwane, gdy przechwytywanie zmian danych jest wyłączone dla bazy danych. Zadanie przechwytywania można również usunąć po dodaniu pierwszej publikacji do bazy danych, a zarówno przechwytywanie zmian danych, jak i replikacja transakcyjna są włączone.
Wewnętrznie zadania agenta przechwytywania danych są tworzone i usuwane przy użyciu odpowiednio procedur składowanych sys.sp_cdc_add_job i sys.sp_cdc_drop_job. Te procedury składowane są również udostępniane, aby pozwolić administratorom kontrolować tworzenie i usuwanie tych zadań.
Administrator nie ma jawnej kontroli nad domyślną konfiguracją zadań agenta przechwytywania danych zmian. Procedura składowana sys.sp_cdc_change_job jest udostępniana w celu umożliwienia modyfikacji domyślnych parametrów konfiguracji. Ponadto procedura składowana sys.sp_cdc_help_jobs umożliwia wyświetlanie bieżących parametrów konfiguracji. Zarówno zadanie przechwytywania, jak i zadanie oczyszczania wyodrębnia parametry konfiguracji z tabeli msdb.dbo.cdc_jobs podczas uruchamiania. Wszelkie zmiany wprowadzone w tych wartościach przy użyciu sys.sp_cdc_change_job nie zostaną zastosowane do momentu zatrzymania i ponownego uruchomienia zadania.
Udostępniono dwie inne procedury składowane, aby umożliwić uruchamianie i zatrzymywanie zadań agenta przechwytywania danych zmian: sys.sp_cdc_start_job i sys.sp_cdc_stop_job.
Notatka
Uruchamianie i zatrzymywanie zadania przechwytywania nie powoduje utraty danych zmiany. Uniemożliwia to tylko procesowi przechwytywania aktywne skanowanie dziennika w poszukiwaniu wpisów zmian, aby umieścić je w tabelach zmian. Rozsądną strategią zapobiegania dodawaniu obciążenia skanowania dzienników w okresach szczytowego zapotrzebowania jest zatrzymanie zadania przechwytywania i ponowne uruchomienie go po zmniejszeniu zapotrzebowania.
Oba zadania agenta programu SQL Server zostały zaprojektowane tak, aby były wystarczająco elastyczne i wystarczająco konfigurowalne, aby spełnić podstawowe potrzeby środowisk przechwytywania danych zmian. W obu przypadkach jednak podstawowe procedury składowane, które zapewniają podstawowe funkcje, zostały ujawnione, aby możliwe było dalsze dostosowanie.
Przechwytywanie zmian danych nie może działać prawidłowo, gdy usługa silnika bazy danych lub usługa SQL Server Agent jest uruchomiona na koncie usługi sieciowej. Może to spowodować błąd 22832.
Współdziałanie z innymi funkcjami
Przechwytywanie zmian danych ma pewne ograniczenia podczas pracy z innymi funkcjami programu SQL Server. Sprawdź interoperacyjność, aby dowiedzieć się więcej.
Znane problemy
W przypadku znanych problemów i błędów związanych z przechwytywaniem danych zmian przejrzyj Znane problemy z usługą CDC.