Leitfaden zur Datensynchronisierung in Microsoft SQL Server

Der Austausch verwandter Informationen zwischen isolierten Systemen wird für Unternehmen immer wichtiger, da sie dadurch die Qualität und Verfügbarkeit von Daten verbessern können. Es gibt viele Situationen, in denen es nützlich ist, einen Datensatz zu haben, der auf mehr als einem Verzeichnisserver verfügbar und konsistent ist. Aus diesem Grund ist es wichtig, die allgemeinen Methoden zum Ausführen der SQL Server-Datensynchronisierung zu kennen.

Datenverfügbarkeit und -konsistenz können durch Datenreplikations- und Datensynchronisationsprozesse erreicht werden. Datenreplikation ist der Prozess der Erstellung einer oder mehrerer redundanter Kopien einer Datenbank zum Zwecke der Fehlertoleranz oder Verbesserung der Zugänglichkeit. Die Datensynchronisierung ist der Prozess des Herstellens von Datenkonsistenz zwischen zwei oder mehr Datenbanken und der anschließenden kontinuierlichen Aktualisierungen, um diese Konsistenz aufrechtzuerhalten.

In vielen Organisationen ist die Durchführung einer Datensynchronisierung über verschiedene Systeme sowohl wünschenswert als auch eine Herausforderung. Wir können viele Anwendungsfälle finden, in denen wir eine Datensynchronisierung durchführen müssen:

• Datenbankmigration
• Regelmäßige Synchronisation zwischen Informationssystemen
• Importieren von Daten aus einem Informationssystem in ein anderes
• Verschieben von Datensätzen zwischen verschiedenen Phasen oder Umgebungen
• Importieren von Daten aus einer Nicht-Datenbankquelle

Es gibt keinen eindeutigen Weg oder einstimmig vereinbarte Methode für die Datensynchronisierung. Diese Aufgabe ist von Fall zu Fall unterschiedlich und selbst auf den ersten Blick einfache Datensynchronisationen können aufgrund der Komplexität der Datenstrukturen kompliziert sein. In realen Szenarien besteht die Datensynchronisierung aus vielen komplexen Aufgaben, deren Ausführung viel Zeit in Anspruch nehmen kann. Wenn eine neue Anforderung entsteht, müssen Datenbankspezialisten normalerweise den gesamten Synchronisationsprozess neu implementieren. Da es neben der Replikation keine Standardmethoden dafür gibt, sind die Implementierungen der Datensynchronisation selten optimal. Dies führt zu schwieriger Wartung und höheren Kosten. Die Implementierung und Wartung der Datensynchronisierung ist ein so zeitaufwändiger Prozess, dass es für sich genommen schon eine Vollzeitbeschäftigung sein kann.

Wir können die Architektur für Datensynchronisierungsaufgaben manuell implementieren, möglicherweise unter Verwendung von Microsoft Sync Framework, oder wir können von bereits erstellten Lösungen innerhalb von Tools zur Verwaltung von Microsoft SQL Server profitieren. Wir werden versuchen, die gebräuchlichsten Methoden und Tools zu beschreiben, die verwendet werden können, um die Datensynchronisierung auf Microsoft SQL Server-Datenbanken zu lösen, und versuchen, einige Empfehlungen zu geben.

Basierend auf der Struktur von Quelle und Ziel (z. B. Datenbanken, Tabellen) können wir Anwendungsfälle unterscheiden, wenn Strukturen ähnlich oder unterschiedlich sind.

Quelle und Ziel haben sehr ähnliche Strukturen

Dies ist sehr oft der Fall, wenn wir Daten in verschiedenen Phasen des Softwareentwicklungslebenszyklus verwenden. Beispielsweise ist die Datenstruktur in den Test- und Produktionsumgebungen sehr ähnlich. Die gemeinsame Anforderung besteht darin, Daten zwischen der Test- und Produktionsdatenbank zu vergleichen und Daten aus der Produktion in die Testdatenbank zu importieren.

Quelle und Ziel haben unterschiedliche Strukturen

Wenn die Strukturen unterschiedlich sind, ist die Synchronisation komplizierter. Dies ist auch eine häufiger wiederkehrende Aufgabe. Ein häufiger Fall ist das Importieren von einer Datenbank in eine andere. Der häufigste Fall ist, wenn eine Software Daten aus einer anderen Software importieren muss, die von einem anderen Unternehmen verwaltet wird. Normalerweise müssen Importe automatisch nach Zeitplan ausgeführt werden.

Die verwendete Methode hängt von persönlichen Vorlieben und der Komplexität des zu lösenden Problems ab.

Unabhängig davon, wie ähnlich die Strukturen sind, können wir vier verschiedene Wege wählen, um die Datensynchronisation zu lösen:

• Synchronisation mit manuell erstellten SQL-Skripten
• Synchronisierung mit der Datenvergleichsmethode (nur verwendbar, wenn Quelle und Ziel ähnlich aufgebaut sind)
• Synchronisierung mit automatisch generierten SQL-Skripten – kommerzielles Produkt erforderlich

Quelle und Ziel sind gleich oder sehr ähnlich aufgebaut

Verwenden von manuell erstellten SQL-Skripten

Die einfachste und mühsamste Lösung besteht darin, SQL-Skripts für die Synchronisierung manuell zu schreiben.

Vorteile

• Kann mit kostenlosen und Open-Source-Tools (FOSS) ausgeführt werden.
• Wenn die Tabelle Indizes hat, ist sie sehr schnell.
• Das SQL-Skript kann in einer gespeicherten Prozedur gespeichert oder regelmäßig als Auftrag für SQL Server ausgeführt werden.
• Kann als automatischer Import verwendet werden, auch bei kontinuierlich geänderten Daten.

Nachteile

• Das Erstellen eines solchen SQL-Skripts ist ziemlich mühsam, da normalerweise drei Skripte für jede Tabelle benötigt werden: INSERT , UPDATE und DELETE .
• Sie können nur Daten synchronisieren, die über SQL-Abfragen verfügbar sind, sodass Sie keine Daten aus Quellen wie CSV- und XML-Dateien importieren können.
• Es ist schwer zu warten – wenn die Datenbankstruktur geändert wird, müssen zwei oder drei Skripte geändert werden ( INSERT , UPDATE und manchmal auch DELETE ).

Beispiel

Wir führen eine Synchronisierung zwischen der Tabelle Source mit den Spalten ID und Value und der Tabelle Target mit denselben Spalten durch.

Wenn Tabellen denselben Primärschlüssel haben und die Zieltabelle keinen Primärschlüssel mit automatischer Inkrementierung (Identität) hat, können Sie das folgende Synchronisationsskript ausführen.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Verwenden der Datenvergleichsmethode

Bei dieser Methode können wir ein Tool verwenden, um Quell- und Zieldaten zu vergleichen. Der Vergleichsprozess generiert SQL-Skripts, die die Unterschiede aus der Quelldatenbank in die Zieldatenbank übernehmen.

Es gibt eine Reihe von Programmen zum Datenabgleich und zur Synchronisation. Diese Programme verwenden meist den gleichen Ansatz. Der Benutzer wählt die Quell- und die Zieldatenbank aus, aber andere Alternativen könnten ein DB-Backup, ein Ordner mit SQL-Skripten oder sogar eine Verbindung zu einem Versionsverwaltungssystem sein.

Nachfolgend sind die beliebtesten Tools aufgeführt, die den Datenvergleichsansatz verwenden:

• dbForge Data Compare für SQL Server
• RedGate SQL-Datenvergleich
• Apex SQL-Daten Diff

Im ersten Schritt werden die Daten gelesen, oder es werden nur Prüfsummen größerer Daten von der Quelle und vom Ziel gelesen. Dann wird der Vergleichsprozess ausgeführt.

Diese Tools bieten auch zusätzliche Einstellungen für die Synchronisierung.

Wir müssen die folgenden Konfigurationsoptionen einrichten, die für die Datensynchronisierung erforderlich sind:

Synchronisierungsschlüssel

Standardmäßig wird der Primärschlüssel oder eine UNIQUE Einschränkung verwendet. Wenn kein Primärschlüssel vorhanden ist, können Sie eine Kombination von Spalten auswählen. Die Sync-Taste wird verwendet, um Zeilen der Quelle mit Zeilen des Ziels zu paaren.

Tischpaarung

Standardmäßig werden Tabellen nach Namen gepaart. Sie können dies ändern und sie nach Ihren eigenen Bedürfnissen koppeln. In der dbForge Data Compare-Software können Sie die SQL-Abfrage als Quelle oder Ziel auswählen.

Synchronisierungsprozess

Nach der Bestätigung vergleicht das Tool Quell- und Zieldaten. Der gesamte Prozess besteht darin, alle Quell- und Zieldaten herunterzuladen und sie anhand festgelegter Kriterien zu vergleichen. Standardmäßig werden Werte aus gleichnamigen Tabellen und Spalten verglichen. Alle Tools unterstützen die Zuordnung von Spalten- und Tabellennamen. Außerdem besteht die Möglichkeit, IDENTITY (Autoinkrement) auszuschließen oder einige Transformationen durchzuführen, bevor Werte verglichen werden (Float-Typen runden, Groß-/Kleinschreibung ignorieren, NULL als leere Zeichenfolge behandeln usw.). Der Datendownload wurde optimiert. Bei großem Datenvolumen werden nur Prüfsummen heruntergeladen. Diese Optimierung ist in den meisten Fällen hilfreich, jedoch steigt der Zeitbedarf für die Durchführung von Operationen mit der Datenmenge.

Im nächsten Schritt gibt es ein SQL-Skript mit generierten Migrationen. Dieses Skript kann gespeichert oder direkt ausgeführt werden. Sicherheitshalber können wir sogar ein Datenbank-Backup erstellen, bevor wir dieses Skript ausführen. Das ApexSQL Data Diff-Tool kann ein ausführbares Programm erstellen, das das Skript auf einer ausgewählten Datenbank ausführt. Dieses Skript enthält Daten, die geändert werden müssen, nicht die Logik, wie sie geändert werden. Das bedeutet, dass das Skript nicht automatisch ausgeführt werden kann, um einen wiederkehrenden Import bereitzustellen. Das ist der größte Nachteil dieses Ansatzes.

Vorteile

• Fortgeschrittene SQL-Kenntnisse sind nicht erforderlich und können über die GUI durchgeführt werden.
• Sie haben die Möglichkeit, Unterschiede zwischen Datenbanken vor der Synchronisierung visuell zu überprüfen.

Nachteile

• Dies ist eine erweiterte Funktion kommerzieller Produkte.
• Bei der Übertragung enormer Datenmengen nimmt die Leistung ab.
• Das generierte SQL-Skript enthält nur Unterschiede und kann daher nicht für die automatische Synchronisierung zukünftiger Daten wiederverwendet werden.

Unten sehen Sie die typische Benutzeroberfläche dieser Tools.

Mit automatisch generiertem SQL synchronisieren

Diese Methode ist der Datenvergleichsmethode sehr ähnlich. Der einzige Unterschied zur vorherigen Methode besteht darin, dass kein Datenvergleich stattfindet und das generierte SQL-Skript keine Datenunterschiede, sondern eine Synchronisationslogik enthält. Das generierte Skript kann einfach in einer gespeicherten Prozedur gespeichert und regelmäßig ausgeführt werden (z. B. jede Nacht). Diese Methode ist nützlich für automatische Importe zwischen Datenbanken. Die Leistung dieser Methode ist viel besser als die Datenvergleichsmethode.

Die Synchronisierung durch automatisch generiertes SQL wird nur von SQL Database Studio bereitgestellt.

SQL Database Studio bietet eine ähnliche Schnittstelle wie die Datenvergleichsmethode. Wir müssen die Quelle und das Ziel (Datenbanken oder Tabellen) auswählen. Dann müssen wir Optionen einrichten (Synchronisierungsschlüssel, Paarung und Zuordnung). Es gibt eine grafische Abfrageerstellungsfunktion zum Einrichten aller Parameter.

Vorteile

• Fortgeschrittene SQL-Kenntnisse sind nicht erforderlich.
• Sie können alles ziemlich schnell in einer GUI einrichten.
• Das resultierende SQL-Skript kann in einer gespeicherten Prozedur gespeichert werden.
• Kann als automatischer Import verwendet werden - als Job für SQL Server.

Nachteile

• Dies ist eine erweiterte Funktion kommerzieller Produkte.
• Unterschiede können vor der Synchronisierung nicht manuell überprüft werden, da der gesamte Vorgang in einem Schritt ausgeführt wird.

Leistungsbenchmarks

Testfall

Zwei Datenbanken (A und B), die jeweils eine Tabelle mit 2.000.000 Zeilen enthalten. Die Tabellen befinden sich in zwei verschiedenen Datenbanken auf demselben SQL Server. Dieser Test deckt zwei Extremfälle ab: 1) Die Quelltabelle enthält alle 2.000.000 Zeilen und die Zieltabelle ist leer. Die Synchronisation muss viele INSERTS . 2) Die Quell- und Zieltabellen enthalten 2.000.000 Zeilen. Der Unterschied liegt nur in einer Zeile. Die Synchronisation muss nur ein UPDATE bereitstellen.

RedGate Data Compare benötigt 3 Schritte:

• Vergleichen
• Skript generieren
• Führen Sie das Skript in der Zieldatenbank aus

ApexSQL Data Diff benötigt 2 Schritte:

• Vergleichen
• Skript generieren und Skript in einem Schritt ausführen

SQL Database Studio führt die gesamte Synchronisation in einem Schritt durch. Unten sind die Synchronisationszeiten in Sekunden angegeben. In der mit "Einzelschritten" bezeichneten Spalte sind die Dauern der oben aufgeführten Synchronisationsschritte aufgeführt.

Weniger ist besser.

Derselbe Test, aber die Datenbanken befinden sich auf verschiedenen SQL-Servern, die nicht über einen Verbindungsserver verbunden sind.

Weniger ist besser.

Zusammenfassung

Aus den Ergebnissen geht hervor, dass es RedGate und Apex egal ist, ob sich Datenbanken auf demselben SQL-Server befinden, da der Synchronisationsalgorithmus nicht von SQL Server abhängig ist. SQL Database Studio verwendet native Funktionen von SQL Server; Daher ist das Ergebnis besser, wenn sich Datenbanken auf demselben Server befinden.

Quelle und Ziel haben eine unterschiedliche Struktur

Es gibt auch Situationen, in denen eine große Tabelle mit vielen kleinen verwandten Tabellen synchronisiert werden muss.

Dieses Beispiel besteht aus einer breiten Tabelle SourceData , die in kleine Tabellen Continent , Country und City synchronisiert werden muss . Das Schema ist unten angegeben.

Daten in SourceData könnten wie im Bild unten aussehen.

Verwenden von manuell erstellten SQL-Skripten

Skript zur Synchronisierung der Kontinenttabelle

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Skript zur Synchronisierung der Stadttabelle

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

Dieses Skript ist komplizierter. Das liegt daran, dass Datensätze in den Tabellen Country und Continent gefunden werden müssen. Dieses Skript fügt fehlende Datensätze in City ein und füllt ContryId korrekt aus.

Die UPDATE und DELETE Skripte könnten bei Bedarf auch auf die gleiche Weise geschrieben werden.

Vorteile

• Sie benötigen keine kommerziellen Produkte.
• Das SQL-Skript kann in einer gespeicherten Prozedur gespeichert oder regelmäßig als Job für SQL Server ausgeführt werden.

Nachteile

• Das Erstellen eines solchen SQL-Skripts ist schwierig und kompliziert (für jede Tabelle sind normalerweise drei Skripts – INSERT , UPDATE und DELETE – erforderlich).
• Es ist sehr schwer zu pflegen.

Verwendung externer Tools

Diese Art der Synchronisierung (breite Tabelle in viele verwandte Tabellen) kann nicht mit der Datenvergleichsmethode durchgeführt werden, da sie auf unterschiedliche Anwendungsfälle ausgerichtet ist. Da die Datenvergleichsmethode ein SQL-Skript mit einzufügenden Daten erzeugt, hat sie keine direkte Möglichkeit, Referenzen in verwandten Tabellen nachzuschlagen. Aus diesem Grund können Anwendungen, die diese Methode verwenden, nicht verwendet werden (dbForge Data Compare for SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

SQL Database Studio kann Ihnen jedoch dabei helfen, Synchronisierungsskripts automatisch zu erstellen. In der Abbildung unten gibt es ein Element namens Editor für die Datensynchronisierung in SQL Database Studio.

Der Editor sieht aus wie der bekannte Query Builder und funktioniert auf sehr ähnliche Weise. Jede Tabelle muss einen definierten Synchronisationsschlüssel haben, aber es gibt auch definierte Beziehungen zwischen Tabellen. Im Bild oben gibt es auch ein Mapping für die Synchronisation. In der Spaltenliste (unterer Teil des Bildes) befinden sich die Spalten der Tabelle City (bei anderen Tabellen ist es ähnlich).

Säulen

• Id — Diese Spalte wird nicht zugeordnet, da es sich um den (automatisch generierten) Primärschlüssel handelt.
• CountryId — Diese Spalte ist als Referenz für die Tabelle definiert.
• Name — Diese Spalte wird aus der Spalte City in der Quelltabelle (breite Tabelle) gefüllt.

Als Synchronisationsschlüssel werden die Spalten CountryId und Name gewählt. Der Synchronisationsschlüssel ist ein Satz von Spalten, die eine Zeile in der Quell- und Zieltabelle eindeutig identifizieren. Sie können die Primärschlüssel-ID nicht als Synchronisationsschlüssel verwenden, da diese nicht in der Id enthalten ist.

Nach der Synchronisation sehen die Tabellen so aus:

Im obigen Beispiel gab es eine breite Tabelle als Quelle. Es gibt auch ein häufiges Szenario, wenn Quelldaten in mehreren verknüpften Tabellen gespeichert werden. Relationen werden in SQL Database Studio nicht über Fremdschlüssel definiert, sondern über Spaltennamen. Auf diese Weise ist auch der Import aus CSV- oder Excel-Dateien möglich (die Datei wird in eine temporäre Tabelle geladen und die Synchronisation von dieser Tabelle aus durchgeführt). Es empfiehlt sich, eindeutige Spaltennamen zu verwenden. Wenn dies nicht möglich ist, können Sie Aliase für diese Spalten definieren.

Vorteile

• Einfach und schnell zu erstellen
• Pflegeleicht
• Kann in einer gespeicherten Prozedur gespeichert werden (die gespeicherte Prozedur wird mit Daten gespeichert, die zum späteren Öffnen der Synchronisierung in einem Editor erforderlich sind)

Nachteile

• Kommerzielle Lösung

Vergleich der Lösungen

Die Datensynchronisierung besteht aus einer Folge von INSERT -, UPDATE - oder DELETE -Befehlen. Es gibt mehrere Möglichkeiten, Sequenzen dieser Befehle zu erstellen. In diesem Artikel haben wir uns drei Optionen zum Erstellen von Synchronisierungs-SQL-Skripts angesehen. Die erste Möglichkeit besteht darin, alles manuell zu erstellen. Es ist machbar (aber zu zeitaufwändig), erfordert ein komplexes Verständnis von SQL und ist schwierig zu erstellen und zu warten. Die zweite Möglichkeit besteht darin, kommerzielle Tools zu verwenden. Wir haben uns folgende Tools angeschaut:

• dbForge Data Compare für SQL Server
• RedGate SQL-Datenvergleich
• Apex SQL-Daten Diff
• SQL-Datenbank-Studio

Die ersten drei Tools funktionieren sehr ähnlich. Sie vergleichen Daten, lassen den Benutzer Unterschiede analysieren und können ausgewählte Unterschiede synchronisieren (sogar automatisch oder von der Befehlszeile aus). Sie sind für diese Anwendungsszenarien von Vorteil:

• Datenbanken sind aufgrund verschiedener Fehler nicht synchron.
• Sie müssen die Replikation vermeiden, während Sie Daten zwischen Umgebungen übertragen.
• Datenvergleichsberichte in Excel oder HTML werden benötigt.

Jedes Tool wird aus dem einen oder anderen Grund geliebt: dbForge hat eine großartige Benutzeroberfläche und viele Optionen, ApexSQL ist leistungsfähiger als die anderen und RedGate ist das beliebteste.

Das vierte Tool, SQL Database Studio, funktioniert etwas anders. Es generiert SQL-Skripts, die Synchronisationslogik enthalten, keine Änderungen. Auch die Leistung ist großartig, da alle Arbeiten direkt auf dem Datenbankserver ausgeführt werden, sodass keine Datenübertragung zwischen dem Datenbankserver und dem Synchronisierungstool erforderlich ist. Dieses Tool ist für die folgenden Anwendungsfälle nützlich:

• Automatische Datenbankmigrationen, bei denen Datenbanken unterschiedliche Strukturen haben
• Importieren Sie in mehrere verwandte Tabellen
• Import aus externen Quellen XML, CSV, MS Excel