Praca ze zmianą danych

Dotyczy:SQL ServerAzure SQL Managed Instance

Zmiany danych są udostępniane w celu zmiany odbiorców przechwytywania danych za pomocą funkcji wartości tabeli (TVFS). Wszystkie wywołania tych funkcji wymagają dwóch parametrów do określenia zakresu numerów sekwencji dziennika (LSN), które są brane pod uwagę podczas tworzenia zwracanego zestawu wyników. Zarówno górne, jak i dolne wartości LSN powiązane z interwałem są uznawane za uwzględnione w interwale.

Dostępnych jest kilka funkcji ułatwiających określenie odpowiednich wartości LSN do użycia podczas wykonywania zapytań dotyczących platformy TVF. Funkcja sys.fn_cdc_get_min_lsn zwraca najmniejszy identyfikator LSN powiązany z przedziałem prawidłowości wystąpienia przechwytywania. Interwał ważności to interwał czasu, dla którego dane zmiany są obecnie dostępne dla wystąpień przechwytywania. Funkcja sys.fn_cdc_get_max_lsn zwraca największą liczbę LSN w interwale ważności. Funkcje sys.fn_cdc_map_time_to_lsn i sys.fn_cdc_map_lsn_to_time są dostępne, aby ułatwić umieszczenie wartości LSN na konwencjonalnej osi czasu.

Ponieważ przechwytywanie zmian danych używa zamkniętych interwałów zapytań, czasami konieczne jest wygenerowanie następnej wartości LSN w sekwencji, aby upewnić się, że zmiany nie są zduplikowane w kolejnych oknach zapytań. Funkcje sys.fn_cdc_increment_lsn i sys.fn_cdc_decrement_lsn są przydatne, gdy wymagana jest przyrostowa korekta wartości LSN.

Weryfikowanie granic sieci LSN

Zalecamy zweryfikowanie granic LSN, które mają być używane w zapytaniu TVF przed ich użyciem. Punkty końcowe o wartości NULL lub punkty końcowe leżące poza okresem ważności instancji przechwytywania spowodują zwrócenie błędu przez funkcję TVF przechwytywania danych o zmianach.

Na przykład następujący błąd jest zwracany dla zapytania dla wszystkich zmian, gdy parametr używany do zdefiniowania interwału zapytania jest nieprawidłowy lub jest poza zakresem lub opcja filtru wierszy jest nieprawidłowa.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

Odpowiedni błąd zwrócony dla zapytania dotyczącego zmian netto jest następujący:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

Uwaga / Notatka

Uznaje się, że komunikat dla msg 313 jest mylący i nie przekazuje rzeczywistej przyczyny awarii. Ta niezręczna konstrukcja wynika z braku możliwości zgłoszenia jawnego błędu wewnątrz TVF. Niemniej jednak uznano, że lepiej zwrócić rozpoznawalny, choć niedokładny, błąd niż po prostu pusty wynik. Pusty zestaw wyników nie może odróżnić się od prawidłowego zapytania, które nie zwraca żadnych zmian.

W przypadku niepowodzenia autoryzacji podczas wysyłania zapytań o wszystkie zmiany będą zwracane błędy, jak pokazano poniżej:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

To samo dotyczy zapytań dotyczących zmian netto:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

W programie SQL Server Management Studio zobacz szablon Enumerate Net Changes Using TRY CATCH (Wyliczanie zmian netto przy użyciu funkcji TRY CATCH ), aby dowiedzieć się, jak przechwycić te znane błędy programu TVF i zwrócić bardziej znaczące informacje na temat błędu.

Wskazówka

Aby zlokalizować szablony przechwytywania zmian danych w programie SQL Server Management Studio, w menu Widok wybierz pozycję Eksplorator szablonów, rozwiń węzeł Szablony programu SQL Server, a następnie rozwiń folder Change Data Capture .

Funkcje zapytań

W zależności od charakterystyki śledzonej tabeli źródłowej oraz sposobu skonfigurowania jej instancji przechwytywania generowana jest jedna albo dwie funkcje TVF do wykonywania zapytań dotyczących danych o zmianach.

  • Funkcja cdc.fn_cdc_get_all_changes_<capture_instance> zwraca wszystkie zmiany, które wystąpiły dla określonego interwału. Ta funkcja jest zawsze generowana. Wpisy są zawsze zwracane w kolejności sortowania: najpierw według numeru LSN zatwierdzenia transakcji dla zmiany, a następnie według wartości określającej kolejność zmiany w ramach transakcji. W zależności od wybranej opcji filtru wierszy ostatni wiersz jest zwracany podczas aktualizacji (opcja filtru wierszy "wszystko") lub zarówno nowe, jak i stare wartości są zwracane w aktualizacji (opcja filtru wiersza "wszystkie aktualizuj stare".

  • Funkcja cdc.fn_cdc_get_net_changes_<capture_instance> jest generowana, gdy parametr @supports_net_changes ma wartość 1 podczas włączania tabeli źródłowej.

    Uwaga / Notatka

    Ta opcja jest obsługiwana tylko wtedy, gdy tabela źródłowa ma zdefiniowany klucz podstawowy lub jeśli parametr @index_name został użyty do zidentyfikowania unikatowego indeksu.

    Funkcja netchanges zwraca jedną zmianę dla zmodyfikowanego wiersza tabeli źródłowej. Jeśli w określonym interwale zarejestrowano więcej niż jedną zmianę dla wiersza, wartości kolumn będą odzwierciedlać ostateczną zawartość wiersza. Aby poprawnie zidentyfikować operację niezbędną do zaktualizowania środowiska docelowego, program TVF musi rozważyć zarówno początkową operację w wierszu w interwale, jak i ostateczną operację w wierszu. Gdy określono opcję filtrowania wierszy „all”, operacje zwracane przez zapytanie net changes będą operacjami wstawienia, usunięcia lub aktualizacji (nowe wartości). Ta opcja zawsze zwraca maskę aktualizacji jako null, ponieważ istnieje koszt związany z obliczaniem maski agregującej. Jeśli potrzebujesz maski zbiorczej, która odzwierciedla wszystkie zmiany dotyczące wiersza, użyj opcji „wszystko z maską”. Jeśli dalsze przetwarzanie nie wymaga rozróżniania operacji wstawiania i aktualizacji, użyj opcji „wszystko z użyciem scalania”. W takim przypadku wartość operacji będzie przyjmować tylko dwie wartości: 1 dla usunięcia i 5 dla operacji, która może być wstawiona lub aktualizacja. Ta opcja eliminuje dodatkowe przetwarzanie potrzebne do określenia, czy operacja pochodna powinna być wstawką, czy aktualizacją, i może poprawić wydajność zapytania, gdy to rozróżnienie nie jest konieczne.

Maska aktualizacji zwracana z funkcji zapytania jest kompaktową reprezentacją, która identyfikuje wszystkie kolumny, które uległy zmianie w wierszu danych zmiany. Zazwyczaj te informacje są wymagane tylko dla małego podzestawu przechwyconych kolumn. Funkcje są dostępne, aby pomóc w wyodrębnieniu informacji z maski w postaci, która jest bardziej bezpośrednio dostępna dla aplikacji. Funkcja sys.fn_cdc_get_column_ordinal zwraca położenie porządkowe nazwanej kolumny dla danego wystąpienia przechwytywania, podczas gdy funkcja sys.fn_cdc_is_bit_set zwraca parzystość bitu w podanej masce na podstawie porządkowego przekazanego w wywołaniu funkcji. Te dwie funkcje umożliwiają efektywne wyodrębnianie i zwracanie informacji z maski aktualizacji wraz z żądaniem zmiany danych. W programie SQL Server Management Studio zobacz szablon Enumerate Net Changes Using All With Mask (Wyliczanie zmian netto przy użyciu wszystkich z maską ), aby dowiedzieć się, jak te funkcje są używane.

Scenariusze funkcji zapytań

W poniższych sekcjach opisano typowe scenariusze wykonywania zapytań dotyczących danych mechanizmu Change Data Capture przy użyciu funkcji zapytań cdc.fn_cdc_get_all_changes_<capture_instance> i cdc.fn_cdc_get_net_changes_<capture_instance>.

Zapytanie dotyczące wszystkich zmian w przedziale ważności instancji przechwytywania

Najprostszym żądaniem dotyczącym danych o zmianach jest takie, które zwraca wszystkie aktualne dane o zmianach w okresie ważności instancji przechwytywania. Aby zrealizować to żądanie, najpierw określ dolną i górną granicę LSN przedziału ważności. Następnie użyj tych wartości, aby zidentyfikować parametry @from_lsn i @to_lsn przekazać je do funkcji cdc.fn_cdc_get_all_changes_<capture_instance> zapytania lub cdc.fn_cdc_get_net_changes_<capture_instance>. Użyj sys.fn_cdc_get_min_lsn funkcji, aby uzyskać dolną granicę i sys.fn_cdc_get_max_lsn , aby uzyskać górną granicę. W programie SQL Server Management Studio zobacz szablon Enumerate All Changes for the Valid Range, aby uzyskać przykładowy kod służący do wykonywania zapytań o wszystkie bieżące prawidłowe zmiany przy użyciu funkcji zapytania cdc.fn_cdc_get_all_changes_<capture_instance>. W programie SQL Server Management Studio zobacz szablon Enumerate Net Changes for the Valid Range (Wyliczanie zmian netto dla prawidłowego zakresu ), aby zapoznać się z podobnym przykładem użycia funkcji cdc.fn_cdc_get_net_changes_<capture_instance>.

Zapytanie o wszystkie nowe zmiany od ostatniego zestawu zmian

W przypadku typowych aplikacji odpytywanie o dane o zmianach będzie procesem ciągłym, polegającym na okresowym wysyłaniu żądań o wszystkie zmiany, które wystąpiły od czasu ostatniego żądania. W przypadku takich zapytań można użyć funkcji sys.fn_cdc_increment_lsn , aby uzyskać dolną granicę bieżącego zapytania z górnej granicy poprzedniego zapytania. Ta metoda gwarantuje, że żadne wiersze nie są powtarzane, ponieważ interwał zapytania jest zawsze traktowany jako zamknięty interwał, w którym oba punkty końcowe są uwzględniane w interwale. Następnie użyj funkcji sys.fn_cdc_get_max_lsn , aby uzyskać wysoki punkt końcowy dla nowego interwału żądań. W programie SQL Server Management Studio zobacz szablon Wylicz wszystkie zmiany od poprzedniego żądania, aby zapoznać się z przykładowym kodem służącym do systematycznego przesuwania okna zapytania w celu uzyskania wszystkich zmian od ostatniego żądania.

Zapytanie o wszystkie nowe zmiany do chwili obecnej

Typowym ograniczeniem, które jest umieszczane w zmianach zwracanych przez funkcję zapytania, jest uwzględnienie tylko zmian, które wystąpiły między poprzednim żądaniem do bieżącej daty i godziny. W przypadku tego zapytania zastosuj funkcję sys.fn_cdc_increment_lsn do wartości @from_lsn, która została użyta w poprzednim żądaniu, aby wyznaczyć dolną granicę. Ponieważ górna granica interwału czasu jest wyrażona jako określony punkt w czasie, musi zostać przekonwertowana na wartość LSN, zanim będzie mogła być używana przez funkcję zapytania. Przed przekonwertowanie wartości daty/godziny na odpowiadającą mu wartość LSN należy upewnić się, że proces przechwytywania przetworzył wszystkie zmiany zatwierdzone za pośrednictwem określonej górnej granicy. Jest to wymagane, aby upewnić się, że wszystkie zmiany spełniające kryteria zostały propagowane do tabeli zmian. Jednym ze sposobów jest utworzenie struktury pętli oczekiwania, która okresowo sprawdza, czy bieżąca maksymalna liczba zatwierdzeń lsn zarejestrowanych dla każdej tabeli zmian bazy danych przekracza żądany czas zakończenia interwału żądania.

Gdy pętla opóźnienia zweryfikuje, że proces przechwytywania danych przetworzył już wszystkie odpowiednie wpisy dziennika, użyj funkcji sys.fn_cdc_map_time_to_lsn, aby określić nowy górny punkt końcowy wyrażony jako wartość LSN. Aby upewnić się, że wszystkie wpisy zatwierdzone do określonego momentu zostały pobrane, wywołaj funkcję sys.fn_cdc_map_time_to_lsn i użyj opcji „największa wartość mniejsza lub równa”.

Uwaga / Notatka

W okresach bezczynności do tabeli cdc.lsn_time_mapping dodawany jest pozorny wpis, aby zaznaczyć, że proces przechwytywania danych przetworzył zmiany do określonego momentu zatwierdzenia. Zapobiega to pojawieniu się, że proces przechwytywania spadł, gdy po prostu nie ma ostatnich zmian w procesie.

Szablon Wylicz wszystkie zmiany do tej pory pokazuje, jak korzystać z poprzedniej strategii do wykonywania zapytań o dane dotyczące zmian.

Dodawanie czasu zatwierdzenia do zestawu wyników wszystkich zmian

Czas zatwierdzania każdej transakcji ze skojarzonym wpisem w tabeli zmian bazy danych jest dostępny w tabeli cdc.lsn_time_mapping. Łącząc wartość __$start_lsn zwróconą w żądaniu dotyczącym wszystkich zmian z wartością start_lsn wpisu tabeli cdc.lsn_time_mapping, można zwrócić tran_end_time wraz z danymi o zmianie, aby opatrzyć zmianę czasem zatwierdzenia transakcji w źródle danych. Szablon Dołącz czas zatwierdzenia do zestawu wyników wszystkich zmian pokazuje, jak wykonać to złączenie.

Dołączanie zmian danych przy użyciu innych danych z tej samej transakcji

Czasami przydatne jest połączenie danych o zmianach z innymi informacjami zebranymi na temat transakcji w chwili jej zatwierdzenia w systemie źródłowym. Kolumna tran_begin_lsn w tabeli cdc.lsn_time_mapping zawiera informacje potrzebne do wykonania takiego sprzężenia. Po zaktualizowaniu źródła należy zapisać wartość database_transaction_begin_lsn z widoku dynamicznego systemu sys.dm_tran_database_transactions wraz z innymi informacjami, które mają zostać połączone z danymi zmiany. Użyj funkcji fn_convertnumericlsntobinary , aby porównać database_transaction_begin_lsn wartości i tran_begin_lsn . Kod do utworzenia tej funkcji jest dostępny w szablonie Create Function fn_convertnumericlsntobinary. Szablon Zwróć wszystkie zmiany dla danego tran_begin_lsn pokazuje, jak wpływać na operację łączenia.

Zapytanie przy użyciu funkcji opakowujących DateTime

Typowy scenariusz aplikacji do wykonywania zapytań dotyczących danych zmiany polega na okresowym żądaniu danych zmiany przy użyciu okna przesuwanego ograniczonego wartościami daty/godziny. W przypadku tej klasy użytkowników przechwytywanie zmian danych udostępnia procedurę składowaną sys.sp_cdc_generate_wrapper_function, która generuje skrypty służące do tworzenia niestandardowych funkcji opakowujących dla funkcji zapytań mechanizmu przechwytywania zmian danych. Te niestandardowe opakowania umożliwiają wyrażenie przedziału zapytania jako pary daty i godziny.

Opcje wywołania procedury składowanej umożliwiają wygenerowanie opakowań dla wszystkich instancji przechwytywania, do których wywołujący ma dostęp, lub tylko dla określonej instancji przechwytywania. Obsługiwane opcje obejmują również możliwość określenia, czy wysoki punkt końcowy interwału przechwytywania powinien być otwarty lub zamknięty, które z dostępnych przechwyconych kolumn powinny być uwzględnione w zestawie wyników i które z dołączonych kolumn powinny mieć skojarzone flagi aktualizacji. Procedura zwraca zestaw wyników składający się z dwóch kolumn: wygenerowanej nazwy funkcji, którą można wyprowadzić z nazwy instancji przechwytywania, oraz instrukcji CREATE dla opakowującej procedury składowanej. Funkcja opakowująca zapytanie „all changes” jest zawsze generowana. Jeśli parametr @supports_net_changes został ustawiony podczas tworzenia instancji przechwytywania, generowana jest również funkcja opakowująca funkcję zmian netto.

Za wywołanie procedury składowanej służącej do generowania skryptów w celu wygenerowania instrukcji CREATE dla opakowujących procedur składowanych oraz za uruchomienie powstałych skryptów CREATE w celu utworzenia funkcji odpowiada projektant aplikacji. Nie występuje to automatycznie po utworzeniu wystąpienia przechwytywania.

Otoki daty/godziny są własnością użytkownika, a nie są tworzone w domyślnym schemacie obiektu wywołującego. Wygenerowana funkcja jest odpowiednia bez modyfikacji dla większości użytkowników. Jednak dalsze dostosowywanie można zawsze stosować do wygenerowanego skryptu przed utworzeniem funkcji.

Nazwa funkcji opakowującej zapytanie wszystkich zmian to fn_all_changes_, po którym następuje nazwa wystąpienia przechwytywania. Prefiks używany dla opakowania zmian netto to fn_net_changes_. Obie funkcje przyjmują trzy argumenty, podobnie jak odpowiadające im funkcje tabelaryczne Change Data Capture (CDC). Jednak przedział zapytania dla adapterów jest ograniczony przez dwie wartości typu datetime, a nie przez dwie wartości LSN. Parametr @row_filter_option dla obu zestawów funkcji jest taki sam.

Wygenerowane funkcje opakowujące obsługują następującą konwencję systematycznego przechodzenia przez oś czasu przechwytywania danych o zmianach: oczekuje się, że parametr @end_time poprzedniego interwału będzie używany jako parametr @start_time kolejnego interwału. Funkcja opakowująca odpowiada za mapowanie wartości typu datetime na wartości LSN oraz zapewnia, że przy przestrzeganiu tej konwencji żadne dane nie zostaną utracone ani powtórzone.

Wrappery można generować tak, aby obsługiwały albo domkniętą górną granicę, albo otwartą górną granicę dla określonego okna zapytania. Oznacza to, że element wywołujący może określić, czy wpisy, których czas zatwierdzenia transakcji jest równy górnej granicy przedziału ekstrakcji, mają zostać uwzględnione w tym przedziale. Domyślnie jest uwzględniana górna granica.

Podczas gdy wygenerowane funkcje TVF nie powiodą się, jeśli zostanie im przekazana wartość null dla wartości @from_lsn lub @to_lsn, funkcje opakowujące datetime używają wartości null, aby umożliwić zwracanie wszystkich aktualnych zmian. Oznacza to, że jeśli wartość null jest przekazywana jako dolny punkt końcowy okna zapytania do funkcji opakowującej datetime, to w bazowej instrukcji SELECT, stosowanej do funkcji TVF używanej w zapytaniu, jest używany dolny punkt końcowy przedziału ważności instancji przechwytywania. Podobnie, jeśli jako górna granica okna zapytania zostanie przekazana wartość null, przy wybieraniu z funkcji TVF zostanie użyta górna granica przedziału ważności instancji przechwytywania.

Zestaw wyników zwracany przez funkcję opakowującą zawiera wszystkie żądane kolumny, po których występuje kolumna operacji kodowana jako jeden lub dwa znaki w celu identyfikacji operacji powiązanej z danym wierszem. Jeśli zażądano flag aktualizacji, są one wyświetlane jako kolumny bitowe po kodzie operacji, w kolejności określonej w parametrze @update_flag_list . Aby uzyskać informacje na temat opcji wywoływania dostosowywania wygenerowanych otoek daty/godziny, zobacz sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Szablon Tworzenie funkcji opakowującej TVF z flagą aktualizacji pokazuje, jak dostosować wygenerowaną funkcję opakowującą, aby dołączyć do zestawu wyników zwracanego przez zapytanie o zmiany netto flagę aktualizacji dla określonej kolumny. Szablon Tworzenie wystąpień funkcji TVF otoki CDC dla schematu pokazuje, jak utworzyć otoki typu datetime dla funkcji TVF zapytań dla wszystkich instancji przechwytywania utworzonych dla tabel źródłowych w danym schemacie bazy danych.

Przykład użycia wrappera typu datetime do wykonywania zapytań o dane zmian można znaleźć w programie SQL Server Management Studio w szablonie Get Net Changes Using Wrapper With Update Flags. Ten szablon pokazuje, jak wyszukiwać zmiany wynikowe za pomocą funkcji opakowującej, gdy jest ona skonfigurowana do zwracania flag aktualizacji. Opcja filtru wierszy „wszystkie z maską” jest wymagana, aby bazowa funkcja zapytania zwróciła maskę aktualizacji o wartości innej niż null podczas operacji aktualizacji. Wartości null są przekazywane zarówno dla dolnej, jak i górnej granicy interwału daty/godziny, aby zasygnalizować funkcję w celu użycia niskiego punktu końcowego i wysokiego punktu końcowego interwału ważności dla wystąpienia przechwytywania podczas wykonywania bazowego zapytania opartego na sieci LSN. Zapytanie zwraca jeden wiersz dla każdej modyfikacji wiersza źródłowego, który wystąpił w prawidłowym zakresie dla wystąpienia przechwytywania.

Użyj funkcji opakowujących DateTime do przechodzenia między instancjami przechwytywania

Funkcja przechwytywania zmian danych obsługuje do dwóch instancji przechwytywania dla jednej śledzonej tabeli źródłowej. Głównym zastosowaniem tej funkcji jest dostosowanie przejścia między wieloma wystąpieniami przechwytywania, gdy język definicji danych (DDL) zmienia się w tabeli źródłowej, rozszerzając zestaw dostępnych kolumn do śledzenia. Podczas przechodzenia do nowej instancji przechwytywania jednym ze sposobów ochrony wyższych warstw aplikacji przed zmianami nazw bazowych funkcji zapytań jest zastosowanie funkcji opakowującej do opakowania bazowego wywołania. Następnie upewnij się, że nazwa funkcji opakowującej pozostaje taka sama. Gdy ma nastąpić przełączenie, można usunąć starą funkcję opakowującą i utworzyć nową o tej samej nazwie, która odwołuje się do nowych funkcji wykonujących zapytania. Jeśli najpierw zmodyfikujesz wygenerowany skrypt tak, aby utworzyć funkcję opakowującą o tej samej nazwie, możesz przełączyć się na nowe wystąpienie mechanizmu przechwytywania bez wpływu na wyższe warstwy aplikacji.

Treści powiązane

  • Last updated on