Migrationshandbuch von Oracle zu SQL Server und von SQL Server zu Oracle

„Lieferantenabhängigkeit“ ist für viele Führungskräfte ein beängstigendes Wort. Andererseits ist in der Branche bereits weithin bekannt, dass eine vollständige „Vendor-Unabhängigkeit“ nicht erreichbar ist. Und das gilt besonders für Datenbanken.

Zwei der am weitesten verbreiteten RDBMS-Plattformen für Unternehmen sind Oracle Database und Microsoft SQL Server (der Kürze halber nenne ich sie für den Rest dieses Artikels „Oracle“ bzw. „SQL Server“). Sicher, IBM Db2 konkurriert mit Oracle auf immer kleiner werdenden – aber in vielen Bereichen immer noch kritischen – Mainframe-Plattformen. Und schnell fortschreitende Open-Source-Alternativen wie PostgreSQL gewinnen in dynamischen Umgebungen auf einfacher bis mittlerer Standardhardware und im Internet einen festen Platz.

Aber Oracle vs. SQL Server ist die Wahl, vor der viele Führungskräfte stehen, wenn ihre Organisationen ein neues RDBMS benötigen. Die endgültige Auswahl basiert auf mehreren Faktoren: Lizenzkosten, verfügbares internes Fachwissen und bisherige Erfahrungen, Kompatibilität mit bestehenden Umgebungen, Partnerbeziehungen, zukünftige Geschäftspläne usw. Aber selbst bei der gründlichsten Vorabbewertung und fundierten Entscheidungsfindung sind es manchmal Faktoren ändern und dann muss sich auch die Plattform ändern. Ich weiß das, weil ich im Laufe meiner Karriere solche Migrationen zweimal durchgeführt, einmal die Bewertung der Machbarkeit des Übergangs vorbereitet habe und gerade an der plattformübergreifenden Funktionsmigration arbeite.

Sowohl Oracle als auch SQL Server sind „alte“, teilweise ANSI-konforme RDBMS-Implementierungen. Abgesehen von prozeduralen Erweiterungen – PL/SQL und Transact-SQL haben unterschiedliche Syntaxen, sind aber im Allgemeinen einfach zu übersetzen – und neueren objektorientierten Futures, kann SQL-Code täuschend ähnlich aussehen. Und das ist eine gefährliche Honigfalle.

Zwei der kritischsten Punkte für jedes Migrationsprojekt zwischen Oracle und SQL Server (in beiden Richtungen) sind Transaktionen und, eng damit verbunden, temporäre Tabellen , die ein Schlüsselwerkzeug zum Auflösen des Transaktionsbereichs sind. Wir behandeln auch verschachtelte Transaktionen – die im Rahmen einer anderen Transaktion existieren –, da sie ein wichtiger Bestandteil der Implementierung von Benutzersicherheitsaudits in Oracle sind. Aber in SQL Server erfordert die Überwachung der Benutzersicherheit aufgrund des COMMIT Verhaltens in diesem Kontext einen anderen Ansatz.

Transaktionsstruktur verstehen: Oracle vs. SQL Server aus 10.000 Fuß beobachten

Oracle-Transaktionen sind implizit. Das bedeutet, dass Sie keine Transaktion starten müssen – Sie befinden sich immer in einer Transaktion. Und diese Transaktion ist offen, bis Sie eine Commit- oder Rollback-Anweisung ausgeben. Ja, Sie können eine Transaktion explizit starten, sichere Rollback-Punkte definieren und innere/verschachtelte Transaktionen festlegen; aber was wichtig ist, ist, dass Sie nie „nicht in einer Transaktion“ sind und immer ein Commit oder Rollback ausführen müssen. Beachten Sie auch, dass die Ausgabe einer DDL-Anweisung (Data Definition Language) ( CREATE , ALTER usw.; in einer Transaktion kann dies über dynamisches SQL erfolgen) die Transaktion festschreibt, in der sie ausgegeben wurde.

Im Gegensatz zu Oracle verfügt SQL Server über explizite Transaktionen. Das bedeutet, dass alle Ihre Änderungen „automatisch“ festgeschrieben werden, es sei denn, Sie starten explizit eine Transaktion – sofort, wenn Ihre Anweisung verarbeitet wird, da jede DML-Anweisung ( INSERT , UPDATE , DELETE ) eine eigene Transaktion erstellt und sie festschreibt, sofern kein Fehler auftritt aus.

Dies ist das Ergebnis der unterschiedlichen Datenspeicherimplementierungen – wie Daten in eine Datenbank geschrieben werden und wie die Datenbank-Engine sie liest.

In Oracle ändern DML-Anweisungen Datensätze direkt in der Datendatei. Die alte Kopie des Datensatzes (oder die Ersetzung des leeren Datensatzes im Falle von INSERT ) wird in die aktuelle Rollback-Datei geschrieben, und der genaue Zeitpunkt der Änderung wird im Datensatz markiert.

Wenn eine SELECT -Anweisung ausgegeben wird, wird sie basierend auf den Daten verarbeitet, die vor ihrer Ausgabe geändert wurden. Wenn Datensätze geändert wurden, nachdem das SELECT ausgegeben wurde, verwendet Oracle die ältere Version aus der Rollback-Datei.

So hat Oracle Lesekonsistenz und nicht blockierendes Lesen/Schreiben implementiert. Das ist auch der Grund, warum lang andauernde Abfragen auf sehr aktive Transaktionsdatenbanken manchmal zu dem berüchtigten Fehler ORA-01555, snapshot too old: rollback segment ... too small führten. (Das bedeutet, dass die von der Abfrage benötigte Rollback-Datei für eine ältere Version des Datensatzes bereits wiederverwendet wurde.) Daher ist die richtige Antwort auf die Frage „Wie lange sollte meine Oracle-Transaktion dauern?“ ist „So lange wie nötig und nicht mehr“.

Die Implementierung von SQL Server ist anders: Die Datenbank-Engine schreibt und liest nur direkt in/aus Datendateien. Jede SQL-Anweisung ( SELECT / INSERT / UPDATE / DELETE ) ist eine Transaktion, es sei denn, sie ist Teil einer expliziten Transaktion, die mehrere Anweisungen zusammenfasst, sodass Änderungen rückgängig gemacht werden können.

Jede Transaktion sperrt die Ressourcen, die sie benötigt. Aktuelle Versionen von Microsoft SQL Server sind hochgradig darauf optimiert, nur die erforderlichen Ressourcen zu sperren, aber was benötigt wird, wird durch den SQL-Code definiert – daher ist die Optimierung Ihrer Abfragen von entscheidender Bedeutung). Das heißt, anders als in Oracle sollten Transaktionen in SQL Server so kurz wie möglich sein, und deshalb sind automatische Festschreibungen das Standardverhalten.

Und welches SQL-Konstrukt in Oracle und SQL Server ist von den unterschiedlichen Transaktionsimplementierungen betroffen? Temp-Tabellen.

Temporäre Tabellen in Oracle und SQL Server

Wenn der ANSI-SQL-Standard lokale und globale temporäre Tabellen definiert, gibt er nicht explizit an, wie sie implementiert werden sollten. Sowohl Oracle als auch SQL Server implementieren globale temporäre Tabellen. SQL Server implementiert auch lokale temporäre Tabellen. Oracle 18c implementierte auch „echte“ lokale temporäre Tabellen (die sie „private temporäre Tabellen“ nennen). Dies macht die Übersetzung von SQL Server-Code in Oracle 18c sichtbar einfacher als bei älteren Versionen – was Oracles früheres Hinzufügen einiger verwandter Tabellen abrundet Funktionen wie das automatische Inkrementieren von Identitätsspalten.

Aber aus einer rein funktionalen Analyseperspektive kann die Einführung privater temporärer Tabellen ein zweischneidiger Segen sein, da sie die Migrationsprobleme von SQL Server zu Oracle weniger erscheinen lässt, als sie sind. Dies ist eine weitere Honigfalle, da sie einige neue Herausforderungen mit sich bringen kann. Beispielsweise kann die Codevalidierung zur Entwurfszeit nicht für private temporäre Tabellen durchgeführt werden, sodass jeder Code, der sie verwendet, unweigerlich fehleranfälliger ist. Wenn Sie dynamisches SQL verwendet haben, sagen wir es so: Private temporäre Tabellen sind genauso komplex zu debuggen, aber ohne den scheinbar einzigartigen Anwendungsfall. Daher fügte Oracle lokale (private) temporäre Tabellen erst in 18c hinzu und nicht vorher.

Kurz gesagt, ich sehe keinen Anwendungsfall für private temporäre Tabellen in Oracle, der nicht mit globalen temporären Tabellen gleich oder besser implementiert werden kann. Für jede ernsthafte Konvertierung müssen wir also den Unterschied zwischen globalen temporären Tabellen von Oracle und SQL Server verstehen.

Globale temporäre Tabellen in Oracle und SQL Server

Eine globale temporäre Oracle-Tabelle ist ein permanentes Data-Dictionary-Objekt, das explizit zur Entwurfszeit durch eine DDL-Anweisung erstellt wird. Es ist nur „global“, da es ein Objekt auf Datenbankebene ist und von jeder Datenbanksitzung mit den erforderlichen Berechtigungen darauf zugegriffen werden kann. Obwohl ihre Struktur global ist, sind alle Daten in einer globalen temporären Tabelle jedoch nur auf die Sitzung beschränkt, in der sie ausgeführt wird, und sind unter keinen Umständen außerhalb dieser Sitzung sichtbar. Mit anderen Worten, andere Sitzungen können ihre eigenen Daten in ihrer eigenen Kopie derselben globalen temporären Tabelle haben. In Oracle enthält also eine globale temporäre Tabelle sitzungslokale Daten, die hauptsächlich in PL/SQL zur Codevereinfachung und Leistungsoptimierung verwendet werden.

In SQL Server ist eine globale temporäre Tabelle ein temporäres Objekt, das in einem Transact-SQL-Codeblock erstellt wird. Sie existiert so lange, wie ihre erstellende Sitzung geöffnet ist, und sie ist – sowohl in der Struktur als auch in den Daten – für andere Sitzungen in der Datenbank sichtbar. Es handelt sich also um ein globales temporäres Objekt zum sitzungsübergreifenden Teilen von Daten.

Eine lokale temporäre Tabelle in SQL Server unterscheidet sich von globalen dadurch, dass sie nur in der Sitzung zugänglich ist, die sie erstellt. Und die Verwendung von lokalen temporären Tabellen in SQL Server ist viel weiter verbreitet (und ich würde sagen, kritischer für die Datenbankleistung) als die Verwendung von globalen temporären Tabellen.

Wie werden also lokale temporäre Tabellen in SQL Server verwendet und wie sollten sie in Oracle übersetzt werden?

Die kritische (und korrekte) Verwendung lokaler temporärer Tabellen in SQL Server besteht darin, Transaktionsressourcensperren zu verkürzen oder zu entfernen, insbesondere:

• Wenn eine Reihe von Datensätzen durch eine Aggregation verarbeitet werden muss
• Wenn Datensätze analysiert und geändert werden müssen
• Wenn derselbe Datensatz mehrmals im selben Bereich verwendet werden muss

In diesen Fällen ist es sehr oft eine bessere Lösung, diese Gruppe von Datensätzen in einer lokalen temporären Tabelle auszuwählen, um die Sperre von der Quelltabelle aufzuheben.

Es ist erwähnenswert, dass allgemeine Tabellenausdrücke (CTEs, dh WITH <alias> AS (SELECT...) Anweisungen) in SQL Server lediglich „syntaktischer Zucker“ sind. Sie werden vor der SQL-Ausführung in Inline-Unterabfragen umgewandelt. Oracle CTEs (mit einem Hinweis /*+ materialize */ ) sind leistungsoptimiert und erstellen eine temporäre Version einer materialisierten Ansicht. Im Ausführungspfad von Oracle greifen CTEs nur einmal auf die Quelldaten zu. Basierend auf diesem Unterschied kann SQL Server möglicherweise eine bessere Leistung erbringen, wenn lokale temporäre Tabellen anstelle mehrerer Verweise auf denselben CTE verwendet werden, wie dies in einer Oracle-Abfrage möglich wäre.

Aufgrund des Unterschieds zwischen Transaktionsimplementierungen haben temporäre Tabellen auch eine andere Funktion. Daher kann das Verschieben von temporären SQL Server-Tabellen nach Oracle „wie besehen“ (selbst mit der Implementierung von privaten temporären Tabellen in Oracle 18c) nicht nur die Leistung beeinträchtigen, sondern auch funktional falsch sein.

Andererseits muss beim Wechsel von Oracle zu SQL Server auf die Transaktionslänge, den Sichtbarkeitsbereich globaler temporärer Tabellen und die Leistung von CTE-Blöcken mit dem „materialisierten“ Hinweis geachtet werden.

In beiden Fällen sollten wir, sobald der migrierte Code temporäre Tabellen enthält, nicht über die Codeübersetzung, sondern über die Neuimplementierung des Systems sprechen.

Geben Sie Tabellenvariablen ein

Entwickler werden sich wahrscheinlich fragen: Was ist mit Tabellenvariablen? Müssen wir Änderungen vornehmen oder können wir Tabellenvariablen „wie sie sind“ in unseren Migrationsschritten von Oracle zu SQL-Server verschieben? Nun, das hängt davon ab, warum und wie sie im Code verwendet werden.

Schauen wir uns an, wie sowohl temporäre Tabellen als auch Tabellenvariablen verwendet werden können. Ich beginne mit Microsoft SQL Server.

Die Implementierung von Tabellenvariablen in Transact-SQL stimmt in gewisser Weise mit temporären Tabellen überein, fügt jedoch einige eigene Funktionen hinzu. Der Hauptunterschied besteht in der Möglichkeit, Tabellenvariablen als Parameter an Funktionen und gespeicherte Prozeduren zu übergeben.

Dies ist die Theorie, aber Überlegungen zur praktischen Verwendung sind etwas komplizierter.

Zum ersten Mal mit ernsthafter Transact-SQL-Optimierung beauftragt, als ich aus einem tief verwurzelten Oracle-Hintergrund kam, erwartete ich es so: Tabellenvariablen sind im Speicher, während temporäre Tabellen auf der Festplatte sind. Aber ich habe festgestellt, dass Microsoft SQL Server-Versionen bis 2014 keine Tabellenvariablen im Speicher gespeichert haben. Somit ist ein Full Table Scan auf einer temporären Variablen tatsächlich ein Full Table Scan auf der Platte. Glücklicherweise unterstützen SQL Server 2017 und spätere Versionen die deklarative Speicheroptimierung sowohl für temporäre Tabellen als auch für Tabellenvariablen.

Was ist also der Anwendungsfall für Tabellenvariablen in Transact-SQL, wenn alles genauso gut oder besser mit temporären Tabellen gemacht werden kann? Die Schlüsseleigenschaft einer Tabellenvariablen, dass es sich um eine Variable handelt und als solche nicht vom Transaktions-Rollback betroffen ist und als Parameter übergeben werden kann.

Transact-SQL- Funktionen sind sehr restriktiv: Da die Aufgabe einer Funktion darin besteht, einen singulären Rückgabewert zurückzugeben, kann sie – per Design – keine Seiteneffekte haben . Transact-SQL betrachtet sogar SELECT als Nebeneffekt, da in SQL Server jeder Zugriff auf eine Tabelle eine implizite Transaktion und eine damit verbundene Transaktionssperre erzeugt. Das bedeutet, dass wir innerhalb einer Funktion weder auf Daten in einer vorhandenen temporären Tabelle zugreifen noch eine temporäre Tabelle erstellen können. Wenn wir also eine Reihe von Datensätzen an eine Funktion übergeben müssen, müssen wir Tabellenvariablen verwenden.

Die Überlegungen von Oracle zur Verwendung von (globalen) temporären Tabellen und Erfassungsvariablen (das Oracle PL/SQL-Äquivalent zu Transact-SQL-Tabellenvariablen) sind unterschiedlich. Oracle-Erfassungsvariablen befinden sich im Arbeitsspeicher, während sich temporäre Tabellen in temporären Tablespaces befinden. Oracle-Funktionen ermöglichen den schreibgeschützten Zugriff auf Tabellen, permanent oder temporär; Ein einfaches SELECT in Oracle legt niemals eine Sperre für Ressourcen fest.

In Oracle basiert die Wahl der Verwendung von Sammlungsvariablen gegenüber temporären Tabellen auf der erwarteten Datenmenge, der Dauer, für die diese Daten aufbewahrt werden müssen, und der Speicher- vs. Festplattenzuweisung und -verfügbarkeit. Außerdem sind Sammlungsvariablen die Standardmethode, um ein Rowset als Ausgabe an ein Hostprogramm zurückzugeben.

Da die meisten SQL-Syntaxelemente zwischen SQL Server und Oracle sehr ähnlich aussehen, ist die Konvertierung von Codeblöcken mit Tabellenvariablen von SQL Server Transact-SQL in Oracle PL/SQL ein einfacherer und syntaktisch verzeihender Prozess. Es könnte einen grundlegenden Validierungstest bestehen, ist aber funktional nicht korrekt, wenn nicht die Schritte zur temporären Neuimplementierung von Tabellen durchgeführt werden, wie oben beschrieben. Andererseits erfordert Code, der von Oracle nach SQL Server verschoben wurde, mehr Änderungsschritte, nur um syntaktisch gültig zu sein. Um auch funktional korrekt zu sein, müssen die ausführlichen Fälle der Verwendung von temporären Tabellen und CTEs behandelt werden.

Innere Transaktionen („verschachtelte Transaktionen“)

In Bezug auf die Migrationsherausforderungen von Oracle zu SQL Server sind verschachtelte Transaktionen der nächste wichtige Bereich, den es zu untersuchen gilt.

Wenn Transact-SQL-Code eine Transaktion enthält, ob verschachtelt oder nicht, oder Oracle-Code verschachtelte Transaktionen enthält, sprechen wir genau wie bei temporären Tabellen nicht nur von einer einfachen Codemigration, sondern von einer funktionalen Neuimplementierung.

Schauen wir uns zunächst an, wie sich verschachtelte Oracle-Transaktionen verhalten und wie wir sie tendenziell verwenden.

Verschachtelte Transaktionen in Oracle

Verschachtelte Oracle-Transaktionen sind vollständig atomar und unabhängig vom äußeren Geltungsbereich. Es gibt keine tatsächliche Verwendung für verschachtelte Transaktionen in einfachen interaktiven Oracle SQL-Abfragen. Wenn Sie mit Oracle im interaktiven Modus arbeiten, übergeben Sie Ihre Änderungen einfach manuell, wenn Sie sehen, dass Sie einen Zustand erreicht haben. Wenn Sie einige Änderungen vorgenommen haben, die Sie noch nicht festschreiben können, bis Sie den letzten – z. Sie erstellen einen sicheren Punkt, um dorthin zurückzukehren, ohne die vollständige Transaktion festzuschreiben oder rückgängig zu machen.

Wo werden also verschachtelte Transaktionen verwendet? Im PL/SQL-Code. Genauer gesagt in autonomen Prozeduren, die mit PRAGMA AUTONOMOUS_TRANSACTION . Dies bedeutet, dass beim Aufrufen dieses Codes (als benannte gespeicherte Prozedur oder anonym) die Transaktion unabhängig von der Transaktion, die diesen Code aufgerufen hat, festgeschrieben oder rückgängig gemacht wird.

Das Ziel der Verwendung verschachtelter Transaktionen besteht darin, eine eigenständige Arbeitseinheit festzuschreiben oder rückgängig zu machen, unabhängig davon, was mit dem aufrufenden Code geschieht. Wenn eine innere Transaktion festgeschrieben oder rückgängig gemacht werden kann, wird sie verwendet, um die Verfügbarkeit gemeinsam genutzter Ressourcen zu prüfen (oder zu reservieren) – z. B. bei der Implementierung eines Raumreservierungssystems. Die primäre Verwendung für interne Commit-Transaktionen ist die Aktivitätsüberwachung, Codeverfolgung und die Überwachung des sicheren Zugriffs (d. h. ein Benutzer durfte keine Änderungen vornehmen, hat es aber versucht).

Geschachtelte Transaktionen in SQL Server Transact-SQL-Code sind völlig anders.

Verschachtelte Transaktionen in SQL Server

Ob eine innere Transaktion festgeschrieben wird, hängt in Transact-SQL vollständig von der äußersten Transaktion ab. Wenn eine innere Transaktion zurückgesetzt wurde, wird sie einfach zurückgesetzt. Aber wenn eine innere Transaktion festgeschrieben wurde, ist sie immer noch nicht vollständig festgeschrieben, da sie zurückgesetzt werden kann, wenn eine beliebige Ebene ihrer Transaktion im äußeren Bereich zurückgesetzt wird.

Was nützen also innere Transaktionen, wenn ihre Festschreibungen rückgängig gemacht werden können, indem ihre äußere Transaktion rückgängig gemacht wird? Die Antwort ist dieselbe wie im Anwendungsfall für lokale temporäre Tabellen: Freigeben der Sperre für Ressourcen. Der Unterschied besteht darin, dass es sich nicht um eine globale Sperrfreigabe handelt, sondern um eine Sperre im Rahmen der unmittelbar äußeren (direkten „Eltern“)-Transaktion. Es wird in komplexem Transact-SQL-Code verwendet, um innere Ressourcen für die äußere Transaktion freizugeben. Es ist ein Tool zur Leistungsoptimierung und Ressourcenverwaltung.

Da interne/verschachtelte Transaktionen von Oracle und SQL Server ein unterschiedliches (vielleicht sogar gegensätzliches) Verhalten und völlig unterschiedliche Anwendungsfälle aufweisen, erfordert die Migration von einer Plattform zu einer anderen nicht nur eine Neufassung, sondern auch die vollständige Neugestaltung aller Bereiche, die verschachtelte Transaktionsblöcke enthalten .

Andere Faktoren

Sind diese Überlegungen zu temporären Tabellen und Transaktionen die einzigen Dinge, die bei einer Migration von Oracle zu SQL Server angegangen werden müssen? Während sie vielleicht die wichtigsten sind, gibt es definitiv andere, jede mit ihren eigenen Macken, die es wert sind, behandelt zu werden. Nachfolgend finden Sie den Rest der Themen, die meiner Meinung nach am meisten missverstanden wurden:

1. Identitätsspalten in SQL Server
2. Sequenzen in Oracle
3. Synonyme in Oracle
4. Gefilterte Indizes
5. Lesekonsistenz (nur Oracle zu SQL Server)
6. Einsatz von Migrationstools

Der nächste Teil dieser Serie setzt sich mit der Erforschung dieser, insbesondere der ersten drei, fort.

Temporäre Tabellen, Tabellen-/Sammlungsvariablen und verschachtelte Transaktionen: Die 3 wichtigsten Probleme bei der Migration

Ich habe mit temporären Tabellen, Tabellenvariablen/Sammlungen und verschachtelten Transaktionen begonnen, da dies die häufigsten und offensichtlichsten Fehlerquellen in Konvertierungsprojekten sind. Jedes nicht-triviale System in Oracle Database oder Microsoft SQL Server wird zweifellos einige davon verwenden, und die Verwendung dieser Elemente ist sehr eng mit dem spezifischen Design der Transaktionsunterstützung durch die jeweiligen RDBMS-Implementierungen gekoppelt.

Lesen Sie weiter in Teil 2!