Datenmigration mit dem SQL-Server: Was hat sich bei dir geändert?

In Software- und Digitalisierungsprojekten kommt es oft vor, dass Daten aus einer SQL-Datenbank in das zukünftige Zielsystem importiert bzw. migriert werden müssen. Dabei steht man immer vor der Wahl: harte oder weiche Migration (Hard/Soft Migration). Welche Vor- und Nachteile diese bergen und welche technischen Möglichkeiten bestehen, zeige ich in diesem Blogbeitrag.

Hard Migration

Bei einer harten Migration ist auch oft die Rede vom sogenannten “Big Bang”. Das bedeutet, dass einmalig alle Daten vom Quellsystem in des Zielsystem überführt werden. Dadurch müssen Daten nur einmalig überführt werden und die Komplexität bleibt somit überschaubar, allerdings besteht hier ein sehr großes Risiko eines möglichen Ausfalls am Tag des Go-Lives. Auch muss der Durchsatz der APIs des Zielsystems berücksichtigt werden, um mit (sehr) großen Datenmengen umgehen zu können.

Vorteile:

• günstiger
• weniger Komplex
• geringerer Zeitaufwand

Nachteile:

• Sehr hohes Risiko, einen umfangreichen und teuren Ausfall zu erleiden (oft nur mit einer Downtime umsetzbar).

Soft Migration

Bei einer weichen Migration verlagert man das Risiko über die Zeit. Das bedeutet, dass nicht zu einem bestimmten Stichtag alle Daten vom Quell- in das Zielsystem überführt sein müssen. Somit können zu Beginn nur einige Entitäten für die Migration ausgewählt und im weiteren Projektverlauf (iterativ) weitere Entitäten zur Migration hinzugefügt werden. In einem möglichen Fehlerfall betrifft dieser immer nur einen Teilbereich des Systems und führt somit nicht zum Totalausfall.

Vorteile:

• geringeres Risiko für Gesamtsystem
• keine Ausfallzeit

Nachteile:

• hohe Komplexität
• hoher zeitlicher Aufwand
• teurer

Welche Möglichkeiten habe ich bei einem SQL-Server?

Im weiteren Verlauf möchte ich den Fokus auf die weiche Migration legen und zeigen, welche Möglichkeiten man in Bezug auf einen SQL-Server hat.

Manueller Abgleich

Der manuelle Abgleich erfordert ein sehr hohes Maß an Vorbereitung und Logik, um Änderungen im SQL-Server zu erfassen und festzustellen. Dabei gibt es jeweils zwei unterschiedliche Ansätze:

1. Kompletter Abgleich: Zum einen gibt es die Möglichkeit, durch eine Spiegelung der Datenbank einen kompletten Abgleich derselben zu erhalten. Man muss lediglich periodisch die Quelldatenbank abfragen und die Änderungen selbstständig ermitteln und in der eigenen Datenbank abspeichern. Je nach Kürze des Intervalls und der ermittelten Datenmenge kann dies die Grundlast auf dem SQL-Server erhöhen und evtl. produktive Anwendungen negativ beeinflussen.
2. Ereignisgesteuert: Zum anderen können Änderungen direkt in der Ausführung von gespeicherten Prozeduren (Stored Procedure) integriert und als Event an ein weiteres Ziel (z. B. Azure Service Bus) weitergeleitet werden.

Beide Ansätze haben allerdings einen Nachteil: Änderungen können aufgrund der eigenen Implementierung verloren gehen, indem diese durch einen Programmierfehler nicht mehr funktionieren oder einen Datenverlust durch den eigenen Abgleich hervorrufen. Falls sich das Schema der Quelldatenbank ändert, muss dies entsprechend berücksichtigt werden.

Boardmittel des SQL-Servers

Und was bietet der SQL-Server hier out of the box? Dankenswerterweise besitzt der SQL-Server hier Funktionalitäten, um Änderungen zu erfassen. Hierzu gehören Change Data Capture und Change Tracking.

Der zentrale Vorteil hierbei ist, dass der SQL-Server selbst sämtliche Funktionalität und Datenhaltung bereitstellt und man somit keine Abhängigkeiten hat. Außerdem wird der allgemeine overhead, der beim Abfragen der Änderungen entsteht, reduziert und produktive Anwendungen erhalten hier keine nennenswerte Beeinträchtigung.

Folgende Funktionalitäten haben Change Data Capture als auch Change Tracking gemeinsam:

• Änderungen (INSERT, UPDATE, DELETE) werden registriert.
• Erfassen, welche Spalte wurde geändert.
• Erfassen, welcher Änderungstyp durchgeführt wurde.

Change Data Capture erfasst zusätzlich noch Historie-Daten.

How to Change Tracking?

Nun möchten wir uns genau anschauen, wie man Change Tracking im SQL-Server aktiviert und wie es funktioniert.

Einrichtung von Change Tracking

Zunächst einmal müssen wir Change Tracking für die SQL-Datenbank aktivieren:

ALTER DATABASE [wf-sample]  
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

CHANGE_RETENTION legt dabei fest, wie lange Änderungen nachverfolgt und aufbewahrt werden. Nach dieser Periode werden sie automatisch gelöscht. AUTO_CLEANUP löscht die alten Change-Tracking-Informationen, wenn es aktiviert ist.

Das Change Tracking ist zwar jetzt in der Datenbank aktiviert, jedoch muss für jede einzelne Tabelle das Change Tracking explizit aktiviert werden.

Schauen wir uns nun die Tabelle an, welche zunächst erstellt wird:

USE [wf-sample]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee](
	[Id] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
	[Firstname] [nvarchar](50) NULL,
	[Lastname] [nvarchar](50) NULL,
	[Current Department] [nvarchar](50) NULL
) 
GO

Darin enthalten sind Informationen zu Mitarbeitenden und in welcher Abteilung sie gerade sind:

Nun aktivieren wir Change Tracking auch für diese Tabelle:

ALTER TABLE [dbo].[Employee]  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)  

Mit der Option TRACK_COLUMNS_UPDATED wird angegeben, dass auch erfasst wird, welche Spalte sich verändert hat.

Wie funktioniert Change Tracking?

Und jetzt wollen wir mal sehen, wie das Ganze funktioniert. Mit dem folgenden SQL-Statement erhält man alle Änderungen für die Employee-Tabelle.

Aktuell wird diese leer sein. Wenn wir nun Änderungen durchführen, sieht es schon anders aus:

UPDATE [dbo].[Employee]  
	SET [Current Department] = 'Marketing'
WHERE 
ID = 3

Und wenn wir nun die Änderungen abrufen:

declare @last_synchronization_version bigint;

SELECT
*
FROM
    CHANGETABLE(CHANGES Employee, @last_synchronization_version) AS CT;

Erhalten wir eine Übersicht der Änderungen:

In der Übersicht befinden sich einige Spalten, welche für das Change Tracking verwendet werden:

• SYS_CHANGE_VERSION ist eine fortlaufende und eindeutige Nummer über alle Tabellen hinweg.
• SYS_CHANGE_CREATION_VERSION ist die Versionsnummer, wann der Eintrag initial erstellt wurde.
• SYS_CHANGE_OPERATION hält fest, welche Operation (U = Update; I = Insert; D = Delete) auf diesem Datensatz durchgeführt wurde.
• SYS_CHANGE_COLUMNS beinhaltet Informationen (ID) zur geänderten Spalte.
• SYS_CHANGE_CONTEXT beinhaltet einen selbstgewählten Kontext, den man bei einer DML-Operation (Data Manipulation Language) hinzufügen kann.
• Id gibt die fachliche ID der Quelltabelle an.

Wenn man nun feststellen möchte, ob sich eine Spalte geändert hat, kann man dies mit folgendem SQL-Statement identifizieren:

-- Übersicht über geänderte Spalten
declare @last_synchronization_version bigint;
declare @DepartmentID int = COLUMNPROPERTY(OBJECT_ID('Employee'), 'Current Department', 'ColumnId');

SELECT 
	convert(VARCHAR(100), CT.SYS_CHANGE_CONTEXT) AS "Converted Context",
	CHANGE_TRACKING_IS_COLUMN_IN_MASK(@DepartmentID, CT.SYS_CHANGE_COLUMNS) AS "Department Changed?",
	*
FROM
	CHANGETABLE(CHANGES Employee, @last_synchronization_version) AS CT
WHERE 
	CHANGE_TRACKING_IS_COLUMN_IN_MASK(@DepartmentID, CT.SYS_CHANGE_COLUMNS) = 1

Hierzu wurde die Spalte “Department Changed?” hinzugefügt, um zu identifizieren, ob sich diese geändert hatte. Auf diese Weise können auch weitere Spalten hinzugefügt werden:

Wenn ich nun weitere Änderungen durchführe, erhalte ich auch weitere Einträge in der Change-Tracking-Tabelle.

Wenn ich allerdings die Change-Version bei der Abfrage ändere (in diesem Fall 2), erhalte ich lediglich die Änderung höher (exklusive) der angegeben Versionsnummer:

SELECT
*
FROM
    CHANGETABLE(CHANGES Employee, 2) AS CT;

Wenn man nun eine Anwendung entwickelt, kann diese Versionsnummer bei der Abfrage übergeben werden, um immer neue Änderungen abzurufen.

Fazit zum Change Tracking über den SQL-Server

Der SQL-Server bietet mit Change Data Capture und Change Tracking bereits umfangreiche und einfach zu verwendende Funktionalitäten, um Änderungen zu identifizieren und für weitere Systeme einfach zugänglich zu machen. Dabei muss man sich keine Gedanken über die Speicherung dieser Änderungsdaten machen. An dieser Stelle kann man nun ansetzen, um programmatisch z. B. über eine C#-Konsolenanwendung periodisch die Änderungen abzurufen.