Eine einfachere Methode zur Implementierung der triggerbasierten Änderungsdatenerfassung in SQL Server

Angenommen, Sie haben eine SQL mit Kundeninformationen, die mit einem Data Warehouse verbunden ist. Ihre Analysten verwenden diese Datenbank, um personalisierte Marketingkampagnen für Ihre Kunden zu erstellen. Neue Kundendaten kommen regelmäßig herein und alte Kundendaten ändern sich häufig. Um diese Änderungen zu berücksichtigen, müssen Sie Ihr Data Warehouse regelmäßig aktualisieren, was zu Latenzproblemen führen und die Analyse verlangsamen kann. Das Ergebnis? Sie verlassen sich bei Ihren entscheidenden Entscheidungen auf veraltete Daten.

Um sicherzustellen, dass Sie immer zuverlässige Entscheidungen treffen, können Sie Change Data Capture (CDC) verwenden – eine einfachere Möglichkeit, Daten an einem Zielort zu aktualisieren. CDC erkennt Änderungen in den Daten der Quelltabelle und aktualisiert Ihr Zielziel entsprechend. Es stellt sicher, dass Ihre Daten niemals veraltet sind und Ihre Entscheidungen immer auf zuverlässigen, aktuellen Informationen basieren. Es gibt verschiedene Arten der Änderungsdatenerfassung, z. B. protokollbasiert, datenmodifiziert, diff und triggerbasiert.

Dieser Blog gibt einen Überblick darüber, wie CDC beim Datenmanagement nützlich ist, und diskutiert die Trigger-basierte Änderungsdatenerfassung in SQL Server.

CDC und Datenverwaltung

CDC ist ein Game Changer im Datenmanagement. Mit CDC müssen Sie nur Änderungen an Ihrem Zielort aktualisieren. Dadurch können Sie ETL-Prozesse schnell ausführen.

Es kann eine zentrale Rolle bei der Notfallplanung spielen. Stellen Sie sich vor, Ihr Rechenzentrum hat einige Daten verloren und im Backup fehlen einige Dateien. Mit CDC können Sie Ihre primären Daten und Sicherungsquellen synchron halten und sicherstellen, dass Ihre Notfallwiederherstellungspläne narrensicher sind.

CDC beschleunigt Business Intelligence (BI), indem Daten in Echtzeit zwischen Datenbanken verschoben werden. Es ist auch in der Gesundheitsbranche hilfreich, wo Datengenauigkeit und Aktualität für eine optimale Patientenversorgung entscheidend sind. Wenn eine Datenbank mit einem Dashboard verbunden ist, kann CDC sicherstellen, dass alle Daten, die von medizinischen Geräten gestreamt werden, rechtzeitig im Data Warehouse aktualisiert werden.

Ausfallzeiten sind eine der größten Herausforderungen bei der Datenmigration. Mit CDC können Sie Migrationen nahezu ohne Ausfallzeiten durchführen, da die Workloads nicht unterbrochen werden.

Nachdem Sie nun ein grundlegendes Verständnis von CDC haben, wollen wir uns mit den Arten von CDC befassen.

Arten von CDC

CDC in SQL Server gibt es in drei Hauptformen: Triggerbasiert, Protokollbasiert und Abfragebasiert. Jeder Typ bietet einzigartige Vorteile, die auf unterschiedliche Datenumgebungen innerhalb der SQL Server-Landschaft zugeschnitten sind. Die Wahl des CDC-Typs in SQL Server hängt von den spezifischen Anforderungen und Einschränkungen Ihrer SQL Server-Datenumgebung ab. Um Organisationen dabei zu helfen, eine fundierte Entscheidung zu treffen, wird im folgenden Abschnitt auf diese CDC-Typen in SQL Server eingegangen und ihre Funktionen, Vorteile und idealen Anwendungsfälle hervorgehoben.

1. Triggerbasiertes CDC

Triggerbasiertes CDC in SQL Server umfasst die Verwendung von Datenbanktriggern zum Erfassen von Änderungen. Ein Trigger ist eine gespeicherte Prozedur in einer Datenbank, die automatisch auf ein bestimmtes Ereignis reagiert, beispielsweise einen Einfüge-, Aktualisierungs- oder Löschvorgang. Wenn eines dieser Ereignisse eintritt, wird der Trigger ausgelöst und die Änderungen werden in einer separaten Tabelle, der sogenannten Änderungstabelle, aufgezeichnet. Obwohl diese Methode relativ einfach zu implementieren ist und in verschiedenen Datenbanktypen funktioniert, kann sie sich aufgrund des zusätzlichen Aufwands für die Verwaltung von Triggern auf die Leistung von SQL Server auswirken.

Triggerbasiertes CDC ist eine ausgezeichnete Wahl für SQL Server-Anwendungen, die sofortige Datenaktualisierungen erfordern, z. B. kundenorientierte Anwendungen, bei denen Echtzeitdaten für die Aufrechterhaltung einer guten Benutzererfahrung von entscheidender Bedeutung sind.

2. Protokollbasiertes CDC

Das protokollbasierte CDC in SQL Server erfasst Änderungen durch Lesen des Datenbanktransaktionsprotokolls. Das Transaktionsprotokoll ist eine Datei, die alle Transaktionen und Datenbankänderungen aufzeichnet, die durch jede Transaktion vorgenommen werden. Durch das Lesen dieses Protokolls kann Log-Based CDC erkennen, welche Daten sich wann geändert haben und in welcher Reihenfolge die Änderungen aufgetreten sind. Diese Methode ist im Allgemeinen effizienter und weniger aufdringlich als Trigger-basiertes CDC, da sie die Leistung von SQL Server nicht beeinträchtigt. Es erfordert jedoch ein tiefes Verständnis der internen Abläufe der Datenbank.

Log-Based CDC ist ideal für SQL Server-Umgebungen mit hohem Transaktionsvolumen, wie z. B. Finanzsysteme, in denen häufig Änderungen auftreten. Es eignet sich auch für Szenarien, in denen Änderungen in der genauen Reihenfolge ihres Auftretens erfasst werden müssen, wie z. B. Audit Trails oder Compliance-Berichte, bei denen die Reihenfolge der Ereignisse wichtig ist.

3. Abfragebasiertes CDC

Das abfragebasierte CDC in SQL Server erfasst Änderungen, indem es die Quelldatenbank regelmäßig abfragt und den aktuellen Datenstatus mit einem zuvor aufgezeichneten Status vergleicht. Diese Methode wird häufig verwendet, wenn weder Trigger-basiertes noch protokollbasiertes CDC implementiert werden kann, beispielsweise wenn die SQL Server-Quelldatenbank keine Trigger oder Transaktionsprotokolle unterstützt. Allerdings kann abfragebasiertes CDC ressourcenintensiv sein und möglicherweise nicht alle Änderungen erfassen, wenn zwischen Abfragen mehrere Änderungen auftreten.

Abfragebasiertes CDC eignet sich am besten für SQL Server-Datenbanken, die keine Trigger oder Transaktionsprotokolle unterstützen, oder für Szenarien, in denen Datenaktualisierungen in Echtzeit keine Priorität haben. Es ist auch in SQL Server-Umgebungen nützlich, in denen der Zugriff auf das Transaktionsprotokoll aufgrund von Sicherheits- oder technischen Einschränkungen eingeschränkt ist, beispielsweise bei cloudbasierten Datenbanken oder Datenbanken von Drittanbietern.

Heranzoomen auf Trigger-basierte CDC

Trigger-based CDC ist eine Methode, um dem Zielsystem zu signalisieren, dass es eine Änderung im Quellsystem gegeben hat. Wie der Name schon sagt, wird der Änderungsdatenerfassungsprozess basierend auf bestimmten Auslösern initiiert.

In einer SQL-Datenbank gibt es drei Auslöser: Einfügen, um einen neuen Eintrag anzuzeigen, Aktualisieren, um eine Änderung anzuzeigen, und Löschen, um einen Eintrag zu entfernen. Wann immer diese Befehle ausgeführt werden, werden sie in einer Schatten- oder Änderungstabelle aufgezeichnet, die eine detaillierte Aufzeichnung aller Änderungen enthält. Nach der Aufzeichnung werden diese Änderungen dann an das Ziel weitergegeben.

Da alle Trigger SQL-basiert sind, ist Trigger-basiertes CDC die bevorzugte Wahl für SQL Server.

Arbeiten mit Microsoft SQL Server

SQL Server ist eine relationale Datenbank, die im Backend verschiedener Anwendungen verwendet wird. Mit SQL Server können Benutzer Daten mühelos verwalten und speichern. Die Datenbank ist mit einer Vielzahl von Datenverwaltungs- und Analysefunktionen ausgestattet, die es Benutzern erleichtern, sie für BI- und maschinelle Lerninitiativen zu nutzen.

Es gibt verschiedene Gründe, warum Entwickler lieber mit Microsoft SQL Server arbeiten:

Ändern Sie die Datenerfassung in SQL Server

Microsoft bietet Change Data Capture in SQL Server, Azure SQL und Azure SQL Managed Instance an.

Die Änderungsdatenerfassung in SQL Server verwendet einen SQL Server-Agenten, um alle Einfüge-, Aktualisierungs- oder Löschaktivitäten in einer Tabelle aufzuzeichnen, und stellt sie dann in einem benutzerfreundlichen Format zur Verfügung.

Hier ist eine typische Datenbank in Microsoft SQL Server:

Alle Änderungen an dieser Tabelle werden von CDC in einer separaten Tabelle aufgezeichnet.

CDC erleichtert das Extrahieren von Daten aus SQL Server und das Laden in eine Datenbank oder ein Data Warehouse erheblich. Ein ETL/ELT-Tool extrahiert normalerweise Daten aus SQL Server und lädt sie inkrementell in ein Data Warehouse. Beim inkrementellen Laden laden Sie nur die letzten Daten statt der gesamten Datenbank, was Zeit spart und die Leistung verbessert. Eine Kombination aus ETL/ELT und CDC sorgt dafür, dass mit minimalen Ressourcen ein zuverlässiges Data Warehouse zur Verfügung steht.

Sie können CDC in Microsoft SQL Server mit den nativen Funktionen von Microsoft oder einem ETL-Tool eines Drittanbieters verwenden.

Option 1: Natives Aktivieren von Microsoft SQL Server Change Data Capture

Dazu müssen Sie bestimmte Voraussetzungen erfüllen CDC nativ in SQL Server aktivieren. Nur ein Benutzer mit der festen Serverrolle sysadmin oder ein db_owner kann CDC in einer Datenbank aktivieren. Da CDC in der Webversion nicht verfügbar ist, müssen Sie über SQL Server Developer, Enterprise oder Standard Edition verfügen.

Beim Aktivieren von CDC erstellt SQL Server ein CDC-Schema, Metadatentabellen und einen CDC-Benutzer. Das CDC-Schema enthält alle Metadatentabellen mit CDC. Nachdem Sie Quelltabellen für die Änderungsdatenerfassung aktiviert haben, dienen Änderungstabellen als Repository für Änderungsdaten.

Natives Aktivieren der Microsoft SQL Server-Änderungsdatenerfassung

Obwohl Sie die nativen CDC-Funktionen von SQL Server verwenden können, gibt es bestimmte Einschränkungen. Um SQL Server CDC verwenden zu können, muss der SQL Server Agent vorhanden sein. Außerdem erstreckt sich SQL Server CDC nicht auf Datenbanken, die nicht auf SQL Server-Instanzen gehostet werden. Wenn Sie Daten aus mehreren Quellen an ein Data Warehouse senden, muss der CDC-Prozess für jede Quelle separat gepflegt werden.

Sie können ein Drittanbieter-Tool wie z Astera Centerprise um diese Einschränkungen zu überwinden. Astera Centerprise ist eine einfachere Möglichkeit, die Kombination von CDC und ETL/ELT zu nutzen. Das codefreie Tool verfügt über eine leistungsstarke ETL/ELT-Engine, die große Datenmengen schnell verarbeitet. Kombiniert mit einer intuitiven Benutzeroberfläche und einer kurzen Lernkurve, Astera Centerprise macht die Implementierung von CDC in SQL Server mühelos.

Option 2: Implementieren der auslöserbasierten Änderungsdatenerfassung in SQL Server mit Astera Centerprise

Astera Centerprise vereinfacht den Prozess der Implementierung von CDC in SQL Server erheblich. Ziehen Sie die SQL Server-Datenbank einfach per Drag-and-Drop in den Datenfluss-Designer und wählen Sie im Dropdown-Menü die Option „Datenerfassung in Tabelle ändern aktivieren“.

Implementieren von Change Data Capture in SQL Server mit Astera Centerprise

Nachdem Sie CDC aktiviert haben, können Sie alle Änderungen in das Ziel Ihrer Wahl schreiben. Es gibt zwei Möglichkeiten: Sie können sie entweder in ein Dateiformatziel, eine Datenbank oder ein Data Warehouse schreiben.

Lesen von Datenbankänderungen aus Quelltabellen

Nachdem Sie CDC in den oben genannten Quelltabellen aktiviert haben, wählen Sie auslöserbasierte CDC aus dem Eigenschaftenbereich aus. Wenn Sie vorhandene Quelldaten in ein Ziel laden möchten, verwenden Sie die Option „Vollständiges Laden bei der ersten Ausführung durchführen“, die standardmäßig aktiviert ist. Deaktivieren Sie diese Option, wenn Sie nur die Änderungen laden möchten.

Lesen von Datenbankänderungen aus Quelltabellen

Schreiben von Datenbankänderungen in ein Dateiformat Ziel

Sie können Ihre Datenbankänderungen in jedem Dateiformat Ihrer Wahl schreiben. Astera Centerprise unterstützt verschiedene Dateiformate, einschließlich JSON, Excel, CSV und getrennt. Sie können das Ziel einfach per Drag-and-Drop in den Datenfluss-Designer ziehen und Daten von der Quelle zum Ziel zuordnen, ohne Code schreiben zu müssen.

Schreiben von Datenbankänderungen in Dateiformatziel mit Astera Centerprise

Schreiben von Datenbankänderungen in ein Data Warehouse oder eine Datenbank

Das Schreiben von Daten in ein Data Warehouse ist etwas anders, aber einfach. Sie aktualisieren entweder alte Datensätze oder fügen Ihrer Datenbank oder Ihrem Data Warehouse neue Datensätze hinzu. Für diese Reiseziele Centerprise bietet die Option für Upsert. Sie können die Upsert-Option aktivieren und den Primärschlüssel auswählen.

Wenn Sie im Ziel keinen Datensatz haben, wird er mit der Upsert-Option eingefügt. Wenn der Datensatz bereits ist, wird er aktualisiert.

Verwenden von Upsert in Astera Centerprise

Sobald Sie fertig sind, können Sie die Änderungen einfach in Ihrer Datenbank oder Ihrem Data Warehouse abbilden. Astera Centerprise verfügt über eine native Konnektivität zu gängigen Datenbanken und Data Warehouses wie MySQL, Snowflake, Amazon Redshift, PostgresSQL und mehr.

Schreiben von Datenbankänderungen in das Datenbank-/Data-Warehouse-Ziel mit Astera Centerprise

Automatisieren Sie die Trigger-basierte Änderungsdatenerfassung in SQL Server

Das Beste an der Implementierung von CDC mit Astera Centerprise ist, dass Sie die Automatisierungsfunktionen nutzen können. Mit Centerprise, können Sie ganz einfach einen Datenfluss von SQL Server zum Zielort einrichten und für die automatische Ausführung planen.

Centerprise verfügt über einen integrierten Jobplaner, den Sie basierend auf Ihren bevorzugten Einstellungen ausführen können. Es stehen verschiedene ereignis- und zeitbasierte Auslöser zur Auswahl. Sie können die Häufigkeit wöchentlich, monatlich, täglich oder sogar stündlich einstellen.

Sie können auch eine E-Mail-Benachrichtigung einrichten, die Sie jedes Mal benachrichtigt, wenn ein Datenfluss ausgeführt wird.

CDC als Audit-Lösung

CDC in SQL Server dient als Audit-Lösung, indem es eine systematische und detaillierte Verfolgung aller an den Daten in den Datenbanken vorgenommenen Änderungen ermöglicht. So funktioniert es:

• Detaillierte Nachverfolgung: CDC in SQL Server erstellt Änderungstabellen, die die Spaltenstruktur der verfolgten Quelltabellen widerspiegeln. Diese Änderungstabellen speichern eine detaillierte Aufzeichnung aller Datenänderungen, einschließlich Einfügungen, Aktualisierungen und Löschungen. Da CDC einen Prüfpfad bereitstellt und eine gründliche Überprüfung und Untersuchung von Datenänderungen im Laufe der Zeit ermöglicht, ist dies für die Aufrechterhaltung der Datenintegrität von entscheidender Bedeutung.
• Rechenschaftspflicht: Neben der Verfolgung von Datenänderungen erfasst die Änderungsdatenerfassung in SQL Server auch wichtige Metadaten wie die Art der Änderung (Einfügen, Aktualisieren, Löschen), den Zeitpunkt der Änderung und die mit der Änderung verbundene spezifische Transaktion. Die Führung einer detaillierten Aufzeichnung fördert die Verantwortlichkeit, da die Benutzer wissen, dass ihre Aktionen in der Datenbank aufgezeichnet werden, was eine Kultur der Verantwortung und Transparenz fördert.
• Kundenbindung: CDC verwendet einen Prozess namens Log Reader Agent in SQL Server, um das Transaktionsprotokoll zu lesen und die Änderungstabellen zu füllen. Der Protokolllese-Agent ist ein Job, der kontinuierlich im Hintergrund ausgeführt wird, das Transaktionsprotokoll der Datenbank durchsucht und alle Änderungen in die Verteilungsdatenbank kopiert. Durch die aktive Verfolgung und Überwachung der im System vorgenommenen Änderungen können Unternehmen regulatorische Anforderungen wie die Datenschutz-Grundverordnung (DSGVO) einhalten.
• Echtzeitüberwachung: Die Änderungsdatenerfassung in SQL Server erfolgt nahezu in Echtzeit. Es verwendet Erfassungsinstanzen, die jeweils aus einer Änderungstabelle und Abfragefunktionen bestehen, um bestimmte Tabellen kontinuierlich auf Datenänderungen zu überwachen. Wenn Änderungen auftreten, zeichnen die Erfassungsinstanzen die Details in den Änderungstabellen auf. Durch die Verfolgung von Daten in Echtzeit können Unternehmen unbefugte oder unangemessene Änderungen schnell erkennen und darauf reagieren und so die Datensicherheit erhöhen.

Warum sollten Sie wählen Astera Centerprise?

Astera Centerprise nimmt die Komplexität der Datenverwaltung. Die codefreie Umgebung und die intuitive Benutzeroberfläche ermöglichen es Geschäftsanwendern, ihre datengesteuerten Initiativen in die Hand zu nehmen. Hier sind einige Funktionen, die machen Astera Centerprise eine ausgezeichnete Wahl für die Aktivierung von CDC:

• Astera Centerprise unterstützt verschiedene Konnektoren für gängige Datenbanken, Data Warehouses und Dateiformate.
• Mit der Astera Centerprise, können Sie CDC für alle relationalen Datenbanken auf einer Plattform verwalten, anstatt sie separat zu verwalten.
• Astera unterstützt integrierte Transformationen, mit denen Sie Ihre Daten bereinigen und bearbeiten können
• Sie können verwenden Astera Datenprofilerstellung und Qualitätsmerkmale zur Gewährleistung der Datengenauigkeit und -zuverlässigkeit.
• Sie können verwenden Astera Centerprise Automatisierungs- und Auftragsplanungsfunktionen zur Beschleunigung der Datenübertragung

Herunterladen Astera Centerprise noch heute und 14 Tage kostenlos testen.