Erkennen und Auflösen von Schemaabweichungen

Abgeschlossen

Ihr SQL-Datenbankprojekt gibt an, dass die Customers Tabelle 12 Spalten enthält. Produktion hat 13. Jemand hat letzten Donnerstag während eines Vorfalls eine `LoyaltyTier` Spalte direkt über SQL Server Management Studio (SSMS) hinzugefügt. Die nächste Bereitstellung wird diese Spalte unbemerkt entfernen, da das Projekt nicht weiß, dass es sie gibt. Diese Art von Situation wird als Schemaabweichung bezeichnet, und es ist eine der Möglichkeiten, wie CI/CD-Pipelines die Produktion ohne Warnung unterbrechen.

Grundlegendes zur Schemaabweichung

Schemaabweichung ist die Lücke zwischen dem, was Ihr Projekt definiert und was tatsächlich in der Livedatenbank vorhanden ist. Häufige Ursachen sind:

• Manuelle Änderungen: Jemand öffnet ein Abfragefenster und führt einen ALTER TABLE außerhalb des normalen Workflows aus.
• Notfall-Hotfixes: Ein Produktionsproblem wird um 2 Uhr gepatcht, und der Fix macht es nie wieder in das Projekt.
• Tools von Drittanbietern: Überwachen von Agents oder Object-Relational Mapping-Frameworks (ORM), die Objekte hinter den Kulissen erstellen oder ändern.

Die Gefahr ist nicht die Abweichung selbst. Das passiert als Nächstes. Ihre Pipeline stellt den .dacpac bereit, SqlPackage berechnet die Differenz, und alle Objekte, die im Projekt nicht bekannt sind, werden gelöscht. Es ist von kritischer Bedeutung, einen Drift zu erkennen, bevor diese Bereitstellung ausgeführt wird.

Erkennen von Drift mit Schemavergleich

Mithilfe von Schemavergleichstools können Sie zwei Datenbankdefinitionen nebeneinander speichern. Sie können eine beliebige Kombination aus einer Livedatenbank, einem SQL-Datenbankprojekt oder einer .dacpac Datei vergleichen. Wenn Sie eine Livedatenbank mit Ihrem Projekt vergleichen, werden alle Unterschiede angezeigt, gruppiert nach Aktionstyp (Create, Alter, Delete), wobei die Quell- und Zieldefinitionen für jedes Objekt angezeigt werden.

Starten Sie in Visual Studio oder Visual Studio Code den Schemaabgleich über das SQL Server-Menü oder die Ansicht "Datenbankprojekte". Richten Sie die Quelle auf die Live-Datenbank und das Ziel auf das Projekt aus. Das Vergleichsraster zeigt, was anders ist und was sich ändern würde, wenn Sie sie in die Ausrichtung gebracht haben.

Hinweis

Die Richtung ist umkehrbar. Vergleichen Sie datenbank-zu-Projekt, um Abweichung zu finden. Vergleichen Sie das Projekt mit der Datenbank, um eine Vorschau darauf zu sehen, was sich bei einer Bereitstellung ändern würde. Dasselbe Tool, entgegengesetzte Perspektive.

Schemavergleichsoptionen

Nicht jeder Unterschied ist wichtig. Sie können den Vergleich optimieren, um sich auf das Wesentliche zu konzentrieren:

• Leerzeichen ignorieren , um Formatierungsunterschiede zu überspringen.
• Ignorieren Sie die Spaltenreihenfolge , wenn die Spaltenposition für Ihre Anwendung irrelevant ist.
• Blockieren Sie den möglichen Datenverlust , um destruktive Vorgänge wie das Ablegen von Spalten zu kennzeichnen, die Daten enthalten.

Speichern Sie Ihre Vergleichseinstellungen als .scmp Datei, und übernehmen Sie ihn in Ihr Repository. Es speichert die Quell-, Ziel-, Vergleichsoptionen und ausgeschlossenen Objekttypen, wodurch Driftprüfungen im gesamten Team wiederholbar sind.

Importieren von Änderungen aus der Datenbank in das Projekt

Wenn Sie einen Drift entdecken, müssen Sie entscheiden: Hat die Live-Datenbank oder hat das Projekt die richtige Version? Wenn der Produktionshotfix korrekt war, verwenden Sie ihn in für das Projekt, damit Ihre zentrale Datenquelle korrekt bleibt.

Schemavergleich zur Anwendung von Änderungen verwenden

Wählen Sie in den Vergleichsergebnissen die spezifischen Unterschiede aus, die Sie importieren und anwenden möchten. Sie können in der grafischen Benutzeroberfläche in Visual Studio und Visual Studio Code eine granulare Auswahl treffen. Sie können beispielsweise den Hotfixindex akzeptieren, die temporäre Tabelle des Überwachungs-Agents jedoch ignorieren.

Automatisieren mit SqlPackage Extract

Verwenden Sie für CI/CD-Szenarien oder geplante Driftprüfungen SqlPackage Extract , um das Liveschema in Dateien abzurufen:

sqlpackage /Action:Extract /SourceConnectionString:"{connection string}" /TargetFile:MyDatabaseProject /p:ExtractTarget=SchemaObjectType

📝 Dadurch werden Datenbankobjekte in Dateien extrahiert, die nach Schema und Objekttyp organisiert sind. Führen Sie git status für den Projektordner unter Git aus, nachdem die Extraktion genau angezeigt hat, welche Dateien geändert wurden. Das ist Ihr automatisierter Driftbericht.

Mit drei Befehlen erhalten Sie einen Driftzähler:

rm -rf MyDatabaseProject
sqlpackage /Action:Extract /SourceConnectionString:"{connection string}" /TargetFile:MyDatabaseProject /p:ExtractTarget=SchemaObjectType
git status --porcelain | wc -l

📝 Die Ausgabe ist die Anzahl der geänderten Dateien, sodass Sie eine automatisierte Driftzahl erhalten.

Überprüfen von Bereitstellungsänderungen vor der Anwendung

Sie haben die Aktionen Script und DeployReport in der vorherigen Einheit zum Thema Erstellen und Bereitstellen kennengelernt. In einem Drift-Detection-Kontext helfen Ihnen diese Befehle, zu überprüfen, was SqlPackage tun würde, bevor Sie sie für die Produktion ausführen.

Generieren eines Bereitstellungsskripts

Die Script Aktion erzeugt den genauen T-SQL-Code, der ausgeführt würde, ohne es tatsächlich auszuführen:

dotnet build MyProject.sqlproj
sqlpackage /Action:Script /SourceFile:bin/Debug/MyProject.dacpac /TargetConnectionString:"{connection string}" /DeployScriptPath:Deployment.sql

Überprüfen Sie Deployment.sql, um alle ALTER-, CREATE- und DROP-Aktionen anzuzeigen, die von der Bereitstellung ausgeführt werden würden. Speichern Sie das Skript als Pipelineartefakt zur Genehmigung, bevor Sie es für die Produktion ausführen.

Generieren eines Bereitstellungsberichts

Die DeployReport Aktion erzeugt eine XML-Zusammenfassung geplanter Vorgänge:

sqlpackage /Action:DeployReport /SourceFile:bin/Debug/MyProject.dacpac /TargetConnectionString:"{connection string}" /OutputPath:report.xml

Der Bericht listet Vorgänge wie Create, , Alter, Dropund TableDataMotion. Analysieren Sie den XML-Code in Ihrer Pipeline, um Warnungen für Ereignisse mit hohem Einfluss wie Datenbewegung, gruppierte Indexneuerstellungen oder Spaltenabbrüche auszulösen:

<DeploymentReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DeployReport/2012/02">
    <Alerts />
    <Operations>
        <Operation Name="Create">
            <Item Value="[dbo].[Products].[IX_Products_CategorySlug]" Type="SqlIndex" />
        </Operation>
        <Operation Name="Alter">
            <Item Value="[dbo].[Customers]" Type="SqlTable" />
        </Operation>
    </Operations>
</DeploymentReport>

Überprüfen der DACPAC-Konsistenz mit DacpacVerify

DacpacVerify vergleicht zwei .dacpac Dateien und meldet Unterschiede zwischen ihnen, einschließlich Skripts vor der Bereitstellung, Skripts nach der Bereitstellung, SQLCMD-Variablen, Datenbankverweise, Eigenschaften und Datenbankobjekte. Dieser Vergleich ist hilfreich beim Konvertieren von einem ursprünglichen SQL Server Data Tools (SSDT)-Projekt in sdk-format oder beim Überprüfen, dass eine Pipeline das erwartete Artefakt erzeugt.

Installieren Sie es als globales .NET-Tool, und führen Sie es für zwei .dacpac Dateien aus:

dotnet tool install -g microsoft.dacpacverify
dacpacverify before.dacpac after.dacpac

Wichtige Erkenntnisse

Schemaabweichung tritt auf, wenn die Livedatenbank vom SQL-Projekt abweicht, häufig durch Hotfixes, manuelle Änderungen oder direkte Produktionsbearbeitungen verursacht wird. Schemavergleichstools erkennen Unterschiede zwischen einer Datenbank und einem Projekt, sodass Sie auswählen können, welche Änderungen in das Projekt übertragen oder an die Datenbank übertragen werden sollen. Speichern Sie Schemavergleichseinstellungen in .scmp Dateien, sodass das Team jedes Mal konsistente Optionen verwendet. Automatisieren Sie die Drifterkennung, indem Sie SqlPackage /Action:Extract gemäß einem Zeitplan ausführen und das extrahierte Projekt mit dem Repository vergleichen. Mit SqlPackage /Action:DeployReport können Sie eine Vorschau aller geplanten CREATE, ALTER und DROP anzeigen, bevor Sie Änderungen vornehmen. Das Ziel besteht nicht darin, den Drift vollständig zu verhindern, sondern ihn frühzeitig zu erkennen und zu beseitigen, bevor die nächste Bereitstellung einen Hotfix in ein Rollback verwandelt. Als Nächstes erstellen Sie die CI/CD-Pipelines, die den gesamten Build-and-Deploy-Zyklus automatisieren.