Kiedy używać CDC do śledzenia historii?

26

SQL Server Change Data Capture to funkcja, która odczytuje dane historyczne z dzienników transakcji SQL Server i przechowuje je w specjalnej tabeli.

Dzięki zastosowaniu specjalnych funkcji wartości tabeli (TVF) umożliwia to użytkownikowi wysłanie zapytania do tych danych, co umożliwia albo uzyskanie wszystkich zmian w konkretnej tabeli, albo tylko zmian netto wynikających ze zmian w określonym czasie.

CDC ma pewne zalety

Można go skonfigurować tak, aby śledził tylko niektóre tabele lub kolumny.
Jest w stanie obsłużyć zmiany modelu do pewnego stopnia.
Nie wpływa to na wydajność tak mocno, jak wyzwalacze, ponieważ działa z dziennikami transakcji.
Można go łatwo włączyć / wyłączyć i nie wymaga dodatkowych kolumn w tabeli, które należy śledzić.

Ma również pewne wady:

Ilość danych historycznych może szybko stać się ogromna.
Nie możesz śledzić, kto dokonał zmian (przynajmniej nie w przypadku usuwania).
Nadrobienie danych historycznych zajmuje trochę czasu, ponieważ opiera się na dziennikach transakcji.
To zależy od agenta SQL Server. Jeśli agent nie działa lub ulega awarii, historia nie jest śledzona.

Dużo czytałem o CDC i chociaż wiem, jak go używać, wciąż nie jestem pewien, czy jest to właściwe narzędzie dla mnie.

Do jakich zadań / scenariuszy CDC jest właściwym narzędziem? (np. Zezwalanie użytkownikom na przywrócenie obiektu danych do określonego momentu w czasie? Audyt? Wyświetlanie pełnej historii danych?)
Kiedy raczej nie powinieneś używać CDC, ale skorzystać z niestandardowego rozwiązania opartego na wyzwalaczach?
Czy można używać CDC w operacyjnej bazie danych i korzystać z danych CDC w działającej aplikacji? (np. pokazanie go użytkownikowi końcowemu) Czy to wyraźnie niewłaściwe użycie tej funkcji?

Często słyszę, że CDC to narzędzie do inspekcji, ale czy nie po to służy SQL Server Audit ? Czy oba są różnymi narzędziami do tego samego zadania? A może CDC może być używany do innych celów?

Mój obecny scenariusz jest taki, że poproszono mnie o zbudowanie niezawodnej struktury danych, która ma być podstawą wielu przyszłych aplikacji. Dokładne wymagania są rozmyte, ale jednym z nich jest to, że powinien móc śledzić historię danych i przywracać starsze wpisy wraz ze wszystkimi powiązanymi danymi z innych tabel. W tej chwili oceniam CDC jako opcję, ale nie jestem pewien, czy jest to właściwy sposób, ponieważ tak naprawdę nie mogę znaleźć zalecanych przypadków użycia.

Chociaż doceniam porady dotyczące mojego konkretnego scenariusza, odpowiedzi powinny zawierać ogólne porady dotyczące tego, kiedy i kiedy nie należy używać funkcji przechwytywania danych.

sql-server change-data-capture data-versioning

— magnetyczny
źródło

1

Idealnie „ramy” nie podejmowałyby tego rodzaju decyzji; pozostawiono by to poszczególnym projektom. Ale ponieważ zostałeś o to poproszony, chciałbym przynajmniej zwrócić uwagę na to, kto daje ci te wymagania: istnieją różne sposoby osiągnięcia tego, a najlepszy wybór zależy w dużej mierze od dokładnego użycia i potrzeb. Zapytaj, czy mogą udzielić ci jakichkolwiek wyjaśnień, które mogą pomóc w podjęciu decyzji (np. Czy wydajność lub elastyczność są ważniejsze). Inną opcją do rozważenia jest opracowanie obu opcji w ramach „frameworka” i umożliwienie prawdziwym projektom wyboru, które z nich włączyć.

— jpmc26

@ jpmc26, struktura może być potrzebna, aby zatrzymać każdy projekt spędzający czas na podejmowaniu tego rodzaju pytań.

— Ian Ringrose

@IanRingrose Chodzi mi o to, że próba podjęcia takiej decyzji bez uwzględnienia konkretnych potrzeb projektu spowoduje na dłuższą metę więcej problemów niż rozwiązuje (a zatem będzie kosztowniejsze niż spędzanie tego czasu). Jest to decyzja, której nie można skutecznie podjąć w ogólnym przypadku. Należy wziąć pod uwagę specyfikę projektu . Korzystając z ogólnej decyzji, czas zostanie wykorzystany przy użyciu wybranego rozwiązania i dokonywaniu założeń wokół niego tylko po to, aby te założenia zostały naruszone, gdy okaże się, że nie było to właściwe rozwiązanie. Następnie system będzie musiał zostać przeprojektowany.

— jpmc26

1

@ jpmc26 Mógłbym faktycznie skorzystać z proponowanego przez Ciebie rozwiązania, na wypadek, gdyby znalazłem sposób, aby go rozwiązać: Opracowanie śledzenia historii opartego na wyzwalaczach i CDC, przełączalne i za wspólnym interfejsem. Aplikacje mogą następnie wybrać jedno lub drugie, w zależności od swoich wymagań, ale nie muszą się martwić o samodzielne wdrożenie. Oczywiście nadal chciałbym uzyskać dobrą odpowiedź na moje powyższe pytanie, ponieważ jeśli CDC i tak nie jest przeznaczone do tego rodzaju zadań (np. Ponieważ jest tylko przydatne do audytu), mogę zaoszczędzić sobie kłopotów i zawsze używać wyzwalaczy .

— magnatyczny

„Jeśli agent nie działa lub ulega awarii, historia nie jest śledzona” - ale jeśli zostanie zrestartowany, żadne zmiany nie zostaną utracone, prawda?

— Andy Joiner,

12

Po pierwsze,

Zmiana przechwytywania danych jest dostępna tylko w wersjach Enterprise, Developer i Evaluation SQL Server.

To może zdecydować, czy któryś z twoich klientów nie będzie miał wersji Enterprise, czy jeszcze nie wiesz, że będziesz używać wersji Enterprise. (Ponieważ specyfikacja obejmuje „wiele przyszłych aplikacji”, może to być dla Ciebie poważny problem)

W przeciwieństwie do wyzwalaczy, nie jest to czas rzeczywisty, jest to zarówno zaleta, jak i wada. Używanie wyzwalaczy zawsze spowalnia aktualizację.

Pracowałem na jednym systemie, kiedy korzystaliśmy z wyzwalaczy (generowanych przez CodeSmith), a także śledząc wszystkie zmiany w rekordach, połączyliśmy również zmiany z tabelą „historii”, która zawiera moduł aplikacji, która dokonała zmiany, oraz element interfejsu użytkownika, którego użytkownik dokonał zmiany.

Jednak najlepiej rozwiązać to na poziomie aplikacji, pisząc całą aktualizację do kolejki wiadomości, która jest następnie odtwarzana w celu utworzenia bazy danych w dowolnym momencie, zobacz Wzorce czasowe na blogu Martina Flowlera, aby uzyskać dobry przegląd opcji.

— Ian Ringrose
źródło

Link jest bardzo interesującą lekturą, dzięki za to. Mimo to rozwiązanie tego problemu na poziomie aplikacji nie jest możliwe w moim przypadku. Struktura, którą buduję, powinna wykonywać większość pracy, w tym śledzenie historii, dla opartych na niej aplikacji. Aplikacje pracują wtedy ze wspólnym interfejsem do przechowywania / pobierania danych, dzięki czemu nie muszą dbać o sposób przechowywania danych. Wiem, że to zadanie nie jest trywialne.

— magnaticzny

Ponadto nie rozważam obecnie wersji Enterprise ani nie decyduję w naszym przypadku. Wszystkie przyszłe aplikacje, o których mówię, najprawdopodobniej zostaną przez nas zbudowane i hostowane.

— magnatyczny

@atticae, Twój framework nie musi być ograniczony do bazy danych, może zawierać kod działający poza bazą danych.

— Ian Ringrose

Oczywiście nie ogranicza się to do bazy danych. (W tym przypadku nie nazwałbym tego ramą). Rozumiem, co masz na myśli przez „poziom aplikacji” i aktualnie w rzeczywistości używam odmiany wzorca właściwości tymczasowej, o której mówi twój link. Struktura, którą buduję, zapewnia ten interfejs aplikacjom, które z niej korzystają. Mimo to jest to część interfejsu i nic z tego nie odpowiada na moje pytania przedstawione powyżej.

— magnaticzny

Jeszcze raz dziękuję za odpowiedź. Jest to prawdopodobnie decydujący czynnik dla większości ludzi, więc myślę, że to dobra odpowiedź i prawdopodobnie pomoże przyszłym odwiedzającym nie zdecydować się na użycie CDC. Uważam jednak, że tak naprawdę nie odpowiada na większość moich pytań, więc będę musiał wynagrodzić stacylaray, który jako jedyny próbował odpowiedzieć na wszystkie moje pytania. (Chociaż miałem nadzieję na odpowiedź nieco bardziej

— złożoną

12

Oto bardzo dobrze napisana 9-częściowa seria, która omawia różne sposoby kontrolowania zmian danych programu SQL Server. Części 3, 4 i 5 koncentrują się na CDC. Warto przeczytać wszystkie artykuły, ponieważ to odpowie na twoje pytania, takie jak różne scenariusze, w których funkcje byłyby odpowiednie i narzutowe. http://solutioncenter.apexsql.com/tag/methods-for-auditing-sql-server

— brynn
źródło

1

Po przejrzeniu artykułu wciąż nie jestem mądrzejszy. Jak większość artykułów, szczegółowo opisano, jak korzystać z CDC i jak to się ma do śledzenia zmian. To jednak tak naprawdę nie odpowiada na moje powyższe pytania.

— magnetyczny

9

Do jakich zadań / scenariuszy CDC jest właściwym narzędziem? (np. Zezwalanie użytkownikom na przywrócenie obiektu danych do określonego momentu w czasie?

Może to zależy.

Audytować?

Tak.

Pokazuje pełną historię danych?)

Tak.

Kiedy raczej nie powinieneś używać CDC, ale skorzystać z niestandardowego rozwiązania opartego na wyzwalaczach?

Gdy dane w tabeli zmian nie spełniają twoich potrzeb.

Czy można używać CDC w operacyjnej bazie danych i korzystać z danych CDC w działającej aplikacji? (np. pokazując to użytkownikowi końcowemu)

Tak.

Czy jest to wyraźnie niewłaściwe użycie tej funkcji?

Nie, to nie jest niewłaściwe użycie tej funkcji.

Często słyszę, że CDC to narzędzie do inspekcji, ale czy nie do tego służy SQL Server Audit?

Tak.

Czy oba są różnymi narzędziami do tego samego zadania?

Nie.

A może CDC może być używany do innych celów?

CDC można wykorzystać do innych celów.

Istnieje śledzenie zmian i przechwytywanie zmian danych. Oba mają swoje korzenie w replikacji.

Śledzenie zmian zapewnia sposób na wprowadzenie zmian netto w tabeli. Przykładem może być ręczna synchronizacja urządzeń.

Z drugiej strony CDC śledzi każdą drobną zmianę, historię. Można użyć tej historii do aktualizacji hurtowni danych zamiast masowego kopiowania danych, lub można użyć tej historii jako samych danych i generować z nich raporty. Tabela zmian nie jest ukryta, nie ma też dziwnego schematu ani czegoś takiego. Możesz wykonać zapytanie i korzystać z danych w dowolny sposób. Pamiętaj tylko ... to nie jest czas rzeczywisty, jak powiedział Ian. Dane pochodzą z dziennika transakcji, więc zadbaj o to, jakbyś używał replikacji, kopii lustrzanej lub wysyłki dziennika. Zasadniczo będzie to szybsze niż wyzwalacze. Będziesz musiał użyć Snapshot Isolation, która ma narzut, i będziesz musiał pomyśleć o Disaster Recovery.

— stacylaray
źródło

2

Punkt korekty. W pewnym momencie zmiana przechwytywania danych była dostępna tylko w wersjach wymienionych powyżej. Jednak przechwytywanie danych zmian stało się dostępne w wersji standardowej od SP1 2016. Dlatego wiele artykułów napisanych przed SP1 2016 brzmi, jakby CDC było poza zasięgiem tych z nas, którzy korzystają z edycji Standard. Tak już nie jest. Dokument Microsoft opisujący dostępne CDC znajduje się w linku poniżej.

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017#DW

— Robert Sievers
źródło