Udostępnij za pośrednictwem

Śledzenie zmian danych (SQL Server)

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Program SQL Server udostępnia dwie funkcje śledzące zmiany danych w bazie danych: przechwytywanie zmian danych i śledzenie zmian . Te funkcje umożliwiają aplikacjom określanie zmian DML (operacji wstawiania, aktualizowania i usuwania), które zostały wprowadzone do tabel użytkowników w bazie danych. Przechwytywanie i śledzenie zmian można włączyć w tej samej bazie danych; nie są wymagane żadne specjalne uwagi. W przypadku wersji programu SQL Server, które obsługują przechwytywanie zmian danych i śledzenie zmian, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

Korzyści wynikające z używania funkcji przechwytywania danych zmian lub śledzenia zmian

Możliwość wykonywania zapytań o dane, które uległy zmianie w bazie danych, jest ważnym wymaganiem, aby niektóre aplikacje zostały wydajne. Zazwyczaj w celu określenia zmian danych deweloperzy aplikacji muszą zaimplementować niestandardową metodę śledzenia w swoich aplikacjach przy użyciu kombinacji wyzwalaczy, znacznika czasu kolumn i dodatkowych tabel. Tworzenie tych aplikacji zwykle wiąże się z dużą ilością pracy w celu zaimplementowania, prowadzi do aktualizacji schematu i często wiąże się z dużym obciążeniem.

Używanie funkcji przechwytywania zmian danych lub śledzenia zmian w aplikacjach w celu śledzenia zmian w bazie danych zamiast tworzenia rozwiązania niestandardowego ma następujące korzyści:

  • Czas programowania jest ograniczony. Ponieważ funkcje są dostępne w programie SQL Server, nie trzeba opracowywać rozwiązania niestandardowego.

  • Zmiany schematu nie są wymagane. Nie musisz dodawać kolumn, dodawać wyzwalaczy ani tworzyć tabeli bocznej, w której można śledzić usunięte wiersze ani przechowywać informacji śledzenia zmian, jeśli nie można dodać kolumn do tabel użytkowników.

  • Istnieje wbudowany mechanizm oczyszczania. Czyszczenie w celu śledzenia zmian odbywa się automatycznie w tle. Niestandardowe czyszczenie danych przechowywanych w tabeli bocznej nie jest wymagane.

  • Funkcje są udostępniane w celu uzyskania informacji o zmianach.

  • Operacje DML mają niski narzut. Synchroniczne śledzenie zmian zawsze będzie miało pewne obciążenie. Jednak użycie śledzenia zmian może pomóc zminimalizować obciążenie. Obciążenie często będzie mniejsze niż użycie rozwiązań alternatywnych, zwłaszcza rozwiązań, które wymagają użycia wyzwalaczy.

  • Śledzenie zmian jest oparte na zatwierdzonych transakcjach. Kolejność zmian jest oparta na czasie zatwierdzania transakcji. Pozwala to uzyskać wiarygodne wyniki w przypadku długotrwałych i nakładających się transakcji. Rozwiązania niestandardowe, które używają wartości znaczników czasu , muszą być zaprojektowane do obsługi takich scenariuszy.

  • Dostępne są standardowe narzędzia, których można użyć do konfigurowania i zarządzania nimi. Program SQL Server udostępnia standardowe instrukcje DDL, SQL Server Management Studio, widoki katalogu i uprawnienia zabezpieczeń.

Różnice funkcji między przechwytywaniem danych zmian i śledzeniem zmian

W poniższej tabeli wymieniono różnice funkcji między przechwytywaniem danych zmian a śledzeniem zmian. Mechanizm śledzenia w przechwytywaniu danych zmian obejmuje asynchroniczne przechwytywanie zmian z dziennika transakcji, dzięki czemu zmiany są dostępne po operacji DML. W przypadku śledzenia zmian mechanizm śledzenia obejmuje synchroniczne śledzenie zmian zgodnie z operacjami DML, dzięki czemu informacje o zmianie są natychmiast dostępne.

Funkcja Przechwytywanie zmian danych Śledzenie zmian
Śledzone zmiany
Zmiany DML Tak Tak
śledzone informacje
Dane historyczne Tak Nie
Czy kolumna została zmieniona Tak Tak
Typ DML Tak Tak

Przechwytywanie zmian danych

Przechwytywanie zmian danych zawiera historyczne informacje o zmianach dla tabeli użytkownika, przechwytując zarówno fakt, że wprowadzono zmiany DML, jak i rzeczywiste dane, które zostały zmienione. Zmiany są przechwytywane przy użyciu procesu asynchronicznego, który odczytuje dziennik transakcji i ma niewielki wpływ na system.

Jak pokazano na poniższej ilustracji, zmiany wprowadzone w tabelach użytkownika są przechwytywane w odpowiednich tabelach zmian. Te tabele zmian zapewniają historyczny widok zmian w czasie. Funkcja przechwytywania zmian danych zapewniana przez program SQL Server umożliwia łatwe i systematyczne używanie danych zmian.

Diagram przedstawiający koncepcję przechwytywania danych zmian.

Model zabezpieczeń

W tej sekcji opisano model zabezpieczeń przechwytywania zmian danych.

Konfiguracja i administracja

Aby włączyć lub wyłączyć przechwytywanie danych na potrzeby bazy danych, osoba wywołująca sys.sp_cdc_enable_db (Transact-SQL) lub sys.sp_cdc_disable_db (Transact-SQL) musi być członkiem stałej roli serwera sysadmin. Włączenie i wyłączenie przechwytywania zmian danych na poziomie tabeli wymaga, aby obiekt wywołujący sys.sp_cdc_enable_table (Transact-SQL) i sys.sp_cdc_disable_table (Transact-SQL) być członkiem roli administratora systemu lub członkiem roli db_owner bazy danych bazy danych.

Używanie procedur składowanych do obsługi administrowania zadaniami przechwytywania zmian danych jest ograniczone do członków serwera roli sysadmin i członków roli db_owner bazy danych.

Zmienianie zapytań dotyczących wyliczania i metadanych

Aby uzyskać dostęp do danych zmiany skojarzonych z wystąpieniem przechwytywania, użytkownik musi otrzymać dostęp SELECT do wszystkich przechwyconych kolumn skojarzonej tabeli źródłowej. Ponadto, jeśli podczas tworzenia instancji przechwytywania określono rolę kontrolującą, wywołujący musi być również członkiem tej roli, a schemat przechwytywania zmian danych (cdc) musi mieć uprawnienia SELECT do roli kontrolującej.

Inne funkcje ogólnego przechwytywania danych zmiany do uzyskiwania dostępu do metadanych będą dostępne dla wszystkich użytkowników bazy danych za pośrednictwem roli publicznej, chociaż dostęp do zwracanych metadanych będzie także zwykle ograniczany za pomocą uprawnień SELECT do bazowych tabel źródłowych oraz członkostwa w dowolnych zdefiniowanych rolach kontrolnych.

Operacje DDL w celu zmiany tabel źródłowych z włączoną obsługą przechwytywania danych

Gdy tabela jest włączona do przechwytywania zmian danych, operacje DDL mogą być stosowane tylko do tabeli przez członka stałej roli serwera sysadmin, członek roli bazy danych db_ownerlub członek roli bazy danych db_ddladmin. Użytkownicy, którzy mają jawne dotacje na wykonywanie operacji DDL w tabeli, otrzymają błąd 22914, jeśli spróbują wykonać te operacje.

Rozważania dotyczące typu danych w odniesieniu do przechwytywania zmian danych

Wszystkie podstawowe typy kolumn są obsługiwane przez przechwytywanie zmian danych. W poniższej tabeli wymieniono zachowanie i ograniczenia dotyczące kilku typów kolumn.

Typ kolumny Zmiany przechwycone w tabelach zmian Ograniczenia
Kolumny rzadkie Tak Nie obsługuje przechwytywania zmian podczas korzystania z zestawu kolumn.
Obliczone kolumny Nie Zmiany w obliczonych kolumnach nie są śledzone. Kolumna zostanie wyświetlona w tabeli zmian z odpowiednim typem, ale będzie miała wartość NULL.
XML Tak Zmiany poszczególnych elementów XML nie są śledzone.
Znacznik czasu Tak Typ danych w tabeli zmian jest konwertowany na dane binarne.
Typy danych obiektów BLOB Tak Poprzedni obraz kolumny BLOB jest przechowywany tylko wtedy, gdy sama kolumna zostanie zmieniona.

Integracja funkcji programu SQL Server

W tej sekcji opisano sposób interakcji następujących funkcji z przechwytywaniem danych zmian:

  • Dublowanie bazy danych
  • Replikacja transakcyjna
  • Przywracanie lub dołączanie bazy danych

Dublowanie bazy danych

Bazę danych włączoną do przechwytywania zmian można dublować. Aby upewnić się, że przechwytywanie i czyszczenie są wykonywane automatycznie na lustrze, wykonaj następujące kroki:

  1. Upewnij się, że program SQL Server Agent jest uruchomiony na serwerze lustrzanym.

  2. Utwórz zadanie przechwytywania i zadanie oczyszczania na serwerze lustrzanym po przełączeniu awaryjnym głównego serwera. Aby utworzyć zadania, użyj procedury składowanej sys.sp_cdc_add_job (Transact-SQL).

Aby uzyskać więcej informacji na temat dublowania baz danych, zobacz Database Mirroring (SQL Server).

Replikacja transakcyjna

Przechwytywanie zmian danych i replikacja transakcyjna mogą współistnieć w tej samej bazie danych, ale zapełnianie tabel zmian jest obsługiwane inaczej, gdy obie funkcje są włączone. Przechwytywanie zmian w danych i replikacja transakcyjna zawsze korzystają z tej samej procedury, sp_replcmds, aby odczytać zmiany z dziennika transakcji. Gdy funkcja przechwytywania zmian danych jest włączona samodzielnie, zadanie agenta programu SQL Server wywołuje sp_replcmds. Gdy obie funkcje są włączone w tej samej bazie danych, agent czytnika dzienników wywołuje sp_replcmds. Ten agent wypełnia zarówno tabele zmian, jak i tabele bazy danych distribution. Aby uzyskać więcej informacji, zobacz Agent czytnika dzienników replikacji.

Rozważmy scenariusz, w którym przechwytywanie zmian danych jest włączone w bazie danych AdventureWorks2022, a dwie tabele są włączone do przechwytywania. Aby wypełnić tabele zmian, zadanie przechwytywania wywołuje sp_replcmds. Baza danych jest włączona na potrzeby replikacji transakcyjnej, a publikacja jest tworzona. Teraz Agent czytający dziennik jest tworzony dla bazy danych, a zadanie przechwytywania danych jest usuwane. Agent Czytnika Dzienników nadal skanuje dziennik, zaczynając od ostatniego numeru sekwencji dziennika, który został zatwierdzony w tabeli zmian. Zapewnia to spójność danych w tabelach zmian. Jeśli replikacja transakcyjna jest wyłączona w tej bazie danych, agent czytnika dzienników zostanie usunięty i zadanie przechwytywania zostanie ponownie utworzone.

Notatka

Gdy agent czytnika dzienników jest używany zarówno do przechwytywania zmian danych, jak i replikacji transakcyjnej, zreplikowane zmiany są najpierw zapisywane w bazie danych distribution. Następnie przechwycone zmiany są zapisywane w tabelach zmian. Obie operacje są zatwierdzane razem. Jeśli występuje jakiekolwiek opóźnienie podczas zapisywania do bazy danych distribution, to odpowiednie opóźnienie wystąpi również, zanim zmiany pojawią się w tabelach zmian.

Przywróć lub dołącz bazę danych, w której włączono przechwytywanie zmian danych.

Program SQL Server używa następującej logiki, aby określić, czy przechwytywanie zmian danych pozostaje włączone po przywróceniu lub dołączeniu bazy danych:

  • Jeśli baza danych zostanie przywrócona na tym samym serwerze o tej samej nazwie bazy danych, przechwytywanie zmian danych pozostanie włączone.

  • Jeśli baza danych zostanie przywrócona na innym serwerze, domyślnie przechwytywanie zmian danych zostanie wyłączone i wszystkie powiązane metadane zostaną usunięte.

    Aby zachować przechwytywanie zmian danych, użyj opcji KEEP_CDC podczas przywracania bazy danych. Aby uzyskać więcej informacji na temat tej opcji, zobacz RESTORE.

  • Jeśli baza danych jest odłączona i dołączona do tego samego serwera lub innego serwera, funkcja przechwytywania zmian danych pozostaje włączona.

  • Jeśli baza danych jest dołączona lub przywrócona z opcją KEEP_CDC do dowolnej edycji innej niż Standardowa lub Enterprise, operacja jest blokowana, ponieważ przechwytywanie zmian danych wymaga wersji SQL Server Standard lub Enterprise. Zostanie wyświetlony komunikat o błędzie 932:

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

Za pomocą sys.sp_cdc_disable_db można usunąć przechwytywanie zmian danych z przywróconej lub dołączonej bazy danych.

Śledzenie zmian

Śledzenie zmian przechwytuje fakt, że wiersze w tabeli zostały zmienione, ale nie przechwytuje zmienionych danych. Dzięki temu aplikacje mogą określić wiersze, które zostały zmienione przy użyciu najnowszych danych wierszy uzyskiwanych bezpośrednio z tabel użytkownika. W związku z tym śledzenie zmian jest bardziej ograniczone w pytaniach historycznych, na które może odpowiedzieć w porównaniu do przechwytywania danych zmian. Jednak w przypadku tych aplikacji, które nie wymagają informacji historycznych, obciążenie związane z magazynem jest znacznie mniejsze z powodu braku przechwycenia zmienionych danych. Mechanizm śledzenia synchronicznego służy do śledzenia zmian. Zostało to zaprojektowane tak, aby zapewnić minimalne obciążenie operacji DML.

Na poniższej ilustracji przedstawiono scenariusz synchronizacji, który może przynieść korzyści przy użyciu śledzenia zmian. W scenariuszu aplikacja wymaga następujących informacji: wszystkie wiersze w tabeli, które zostały zmienione od czasu ostatniej synchronizacji tabeli i tylko bieżące dane wierszy. Ponieważ mechanizm synchroniczny służy do śledzenia zmian, aplikacja może przeprowadzać synchronizację dwukierunkową i niezawodnie wykrywać wszelkie konflikty, które mogły wystąpić.

Diagram przedstawiający koncepcję śledzenia zmian.

Usługi śledzenia zmian i synchronizacji dla ADO.NET

Usługi synchronizacji dla ADO.NET umożliwiają synchronizację między bazami danych, zapewniając intuicyjny i elastyczny interfejs API, który umożliwia tworzenie aplikacji przeznaczonych dla scenariuszy pracy w trybie offline i współpracy. Usługi synchronizacji dla ADO.NET udostępniają interfejs API do synchronizowania zmian, ale w rzeczywistości nie śledzi zmian w serwerze lub równorzędnej bazie danych. Można utworzyć niestandardowy system śledzenia zmian, ale zwykle wprowadza to znaczną złożoność i obciążenie związane z wydajnością. Aby śledzić zmiany w serwerze lub równorzędnej bazie danych, zalecamy użycie śledzenia zmian w programie SQL Server, ponieważ jest łatwe do skonfigurowania i zapewnia śledzenie wysokiej wydajności.

Aby uzyskać więcej informacji na temat śledzenia zmian i usług synchronizacji dla ADO.NET, skorzystaj z następujących linków:

Powiązana zawartość