Guida alla migrazione da Oracle a SQL Server e da SQL Server a Oracle

"Dipendenza dal fornitore" è una parola spaventosa per molti dirigenti aziendali. D'altra parte, è già ampiamente compreso nel settore che la completa "indipendenza del fornitore" non è realizzabile. Ed è particolarmente vero nel caso dei database.

Due delle piattaforme RDBMS aziendali più diffuse sono Oracle Database e Microsoft SQL Server (per brevità, per il resto di questo articolo, le chiamerò rispettivamente "Oracle" e "SQL Server"). Certo, IBM Db2 compete con Oracle su piattaforme mainframe sempre più ridotte, ma ancora critiche in molte aree. E le alternative open source in rapido progresso, come PostgreSQL, stanno guadagnando solide basi in ambienti dinamici su hardware di fascia medio-bassa e sul Web.

Ma Oracle vs. SQL Server è la scelta che molti dirigenti aziendali devono affrontare quando le loro organizzazioni hanno bisogno di un nuovo RDBMS. La selezione finale si basa su molteplici fattori: costo della licenza, competenza interna disponibile ed esperienza passata, compatibilità con gli ambienti esistenti, relazioni con i partner, piani aziendali futuri, ecc. Ma anche con la valutazione iniziale più approfondita e il processo decisionale più istruito, a volte fattori cambiare e quindi anche la piattaforma deve cambiare. Lo so perché nel corso della mia carriera ho implementato tali migrazioni due volte, preparato la valutazione della fattibilità della transizione una volta e sto lavorando sulla migrazione delle funzionalità multipiattaforma proprio in questo momento.

Sia Oracle che SQL Server sono implementazioni RDBMS "vecchia scuola", parzialmente conformi ad ANSI. Quando si lasciano da parte le estensioni procedurali (PL/SQL e Transact-SQL hanno sintassi diverse, ma sono generalmente semplici da tradurre tra) e futuri orientati agli oggetti più recenti, il codice SQL può sembrare ingannevolmente simile. E questa è una pericolosa trappola del miele.

Due dei punti più critici per qualsiasi progetto di migrazione tra Oracle e SQL Server (in entrambe le direzioni) sono le transazioni e, strettamente correlate, le tabelle temporanee , che sono uno strumento chiave per risolvere l'ambito delle transazioni. Tratteremo anche le transazioni nidificate, che esistono nell'ambito di un'altra transazione, perché sono una parte fondamentale dell'implementazione del controllo della sicurezza degli utenti in Oracle. Ma in SQL Server, il controllo della sicurezza degli utenti necessita di un approccio diverso a causa del suo comportamento COMMIT in quel contesto.

Comprensione della struttura delle transazioni: osservazione di Oracle e SQL Server da diecimila piedi

Le transazioni Oracle sono implicite. Significa che non è necessario avviare una transazione: sei sempre in una transazione. E questa transazione è aperta fino a quando non emetti una dichiarazione di commit o rollback. Sì, puoi avviare una transazione in modo esplicito, definire punti sicuri di rollback e impostare transazioni interne/nidificate; ma ciò che è importante è che non sei mai "non in una transazione" e devi sempre emettere un commit o un rollback. Si noti inoltre che l'emissione di un'istruzione DDL ( CREATE , ALTER , ecc.; in una transazione può essere eseguita tramite SQL dinamico) impegna la transazione in cui è stata emessa.

A differenza di Oracle, SQL Server ha transazioni esplicite. Ciò significa che, a meno che non si avvii esplicitamente una transazione, tutte le modifiche verranno confermate "automaticamente", immediatamente non appena la dichiarazione viene elaborata, poiché ogni istruzione DML ( INSERT , UPDATE , DELETE ) crea una transazione da sola e la esegue a meno che non commetta errori fuori.

Questo è il risultato della differenza nelle implementazioni di archiviazione dei dati: come i dati vengono scritti in un database e come li legge il motore di database.

In Oracle, le istruzioni DML modificano i record direttamente nel file di dati. La vecchia copia del record (o la sostituzione del record vuota, in caso di INSERT ) viene scritta nel file di rollback corrente e l'ora esatta della modifica viene contrassegnata sul record.

Quando viene emessa un'istruzione SELECT , viene elaborata in base ai dati che sono stati modificati prima dell'emissione. Se alcuni record sono stati modificati dopo l'emissione di SELECT , Oracle utilizza la versione precedente del file di rollback.

Questo è il modo in cui Oracle ha implementato la coerenza di lettura e la lettura/scrittura non bloccante. È anche il motivo per cui le query di lunga durata su database transazionali molto attivi a volte si imbattevano nel famigerato errore ORA-01555, snapshot too old: rollback segment ... too small . (Ciò significa che il file di rollback necessario per la query per una versione precedente del record è già stato riutilizzato.) Ecco perché la risposta corretta alla domanda "Quanto dovrebbe essere lunga la mia transazione Oracle?" è "Per tutto il tempo necessario e non più".

L'implementazione di SQL Server è diversa: il motore di database scrive e legge direttamente solo in/da file di dati. Ogni istruzione SQL ( SELECT / INSERT / UPDATE / DELETE ) è una transazione a meno che non faccia parte di una transazione esplicita che raggruppa più istruzioni insieme, consentendo il rollback delle modifiche.

Ogni transazione blocca le risorse di cui ha bisogno. Le versioni attuali di Microsoft SQL Server sono altamente ottimizzate per bloccare solo le risorse necessarie, ma ciò che è necessario è definito dal codice SQL, quindi l'ottimizzazione delle query è fondamentale). Vale a dire, a differenza di Oracle, le transazioni in SQL Server dovrebbero essere il più brevi possibile, ed è per questo che i commit automatici sono il comportamento predefinito.

E quale costrutto SQL in Oracle e SQL Server è influenzato dalla differenza nelle loro implementazioni delle transazioni? Tavoli temporanei.

Tabelle temporanee in Oracle e SQL Server

Quando lo standard ANSI SQL definisce tabelle temporanee locali e globali, non indica in modo esplicito come dovrebbero essere implementate. Sia Oracle che SQL Server implementano tabelle temporanee globali. SQL Server implementa anche tabelle temporanee locali. Oracle 18c ha anche implementato "vere" tabelle temporanee locali (che chiamano "tabelle temporanee private"). Ciò rende la traduzione del codice di SQL Server in Oracle 18c visibilmente più semplice di quanto non lo sia per le versioni precedenti, completando la precedente aggiunta di Oracle di alcuni funzionalità come le colonne di identità con incremento automatico.

Ma dal punto di vista della pura analisi funzionale, l'introduzione di tabelle temporanee private può essere una benedizione mista in quanto fa sembrare i problemi di migrazione da SQL Server a Oracle meno di quanto non siano. Questa è un'altra trappola del miele, poiché potrebbe introdurre alcune nuove sfide. Ad esempio, la convalida del codice in fase di progettazione non può essere eseguita su tabelle temporanee private, quindi qualsiasi codice che le utilizza sarà invariabilmente più soggetto a errori. Se hai utilizzato SQL dinamico, mettiamola in questo modo: le tabelle temporanee private sono altrettanto complesse da eseguire il debug, ma senza l'apparente caso d'uso univoco. Ecco perché Oracle ha aggiunto tabelle temporanee locali (private) solo in 18c e non prima.

In breve, non vedo un caso d'uso per tabelle temporanee private in Oracle che non possono essere implementate utilizzando tabelle temporanee globali uguali o migliori. Quindi, per qualsiasi conversione seria, dobbiamo comprendere la differenza tra le tabelle temporanee globali di Oracle e SQL Server.

Tabelle temporanee globali in Oracle e SQL Server

Una tabella temporanea globale Oracle è un oggetto dizionario dati permanente creato in modo esplicito in fase di progettazione da un'istruzione DDL. È "globale" solo in quanto è un oggetto a livello di database ed è accessibile da qualsiasi sessione di database con i permessi richiesti. Tuttavia, nonostante la sua struttura sia globale, tutti i dati in una tabella temporanea globale hanno come ambito solo la sessione in cui opera e non sono, in nessun caso, visibili al di fuori di questa sessione. In altre parole, altre sessioni possono avere i propri dati nella propria copia della stessa tabella temporanea globale. Quindi, in Oracle, una tabella temporanea globale contiene i dati locali della sessione, utilizzati principalmente in PL/SQL per la semplificazione del codice e l'ottimizzazione delle prestazioni.

In SQL Server, una tabella temporanea globale è un oggetto temporaneo creato in un blocco di codice Transact-SQL. Esiste finché la sessione di creazione è aperta ed è visibile, sia nella struttura che nei dati, ad altre sessioni nel database. Quindi, è un oggetto temporaneo globale per la condivisione dei dati tra le sessioni.

Una tabella temporanea locale in SQL Server è diversa da quelle globali in quanto è accessibile solo nella sessione che la crea. E l'utilizzo di tabelle temporanee locali in SQL Server è molto più diffuso (e, direi, più critico per le prestazioni del database) rispetto all'uso di tabelle temporanee globali.

Quindi, come vengono utilizzate le tabelle temporanee locali in SQL Server e come dovrebbero essere tradotte in Oracle?

L'uso critico (e corretto) delle tabelle temporanee locali in SQL Server consiste nell'abbreviare o rimuovere il blocco delle risorse della transazione, in particolare:

• Quando un insieme di record deve essere elaborato da un'aggregazione
• Quando un insieme di dati deve essere analizzato e modificato
• Quando lo stesso set di dati deve essere utilizzato più volte nello stesso ambito

In questi casi, è molto spesso una soluzione migliore selezionare questo set di record in una tabella temporanea locale per rimuovere il blocco dalla tabella di origine.

Vale la pena notare che le espressioni di tabella comuni (CTE, ovvero WITH <alias> AS (SELECT...) istruzioni) in SQL Server sono semplicemente "zucchero sintattico". Vengono convertiti in sottoquery inline prima dell'esecuzione di SQL. I CTE Oracle (con un /*+ materialize */ hint) sono ottimizzati per le prestazioni e creano una versione temporanea di una vista materializzata. Nel percorso di esecuzione di Oracle, i CTE accedono ai dati di origine solo una volta. Sulla base di questa differenza, SQL Server potrebbe funzionare meglio utilizzando tabelle temporanee locali anziché più riferimenti allo stesso CTE, come potrebbe essere fatto in una query Oracle.

A causa della differenza tra le implementazioni delle transazioni, le tabelle temporanee svolgono anche una funzione diversa. Di conseguenza, lo spostamento delle tabelle temporanee di SQL Server in Oracle "così com'è" (anche con l'implementazione di tabelle temporanee private in Oracle 18c) può essere non solo dannoso per le prestazioni, ma anche funzionalmente errato.

D'altra parte, quando si passa da Oracle a SQL Server, è necessario prestare attenzione alla lunghezza della transazione, all'ambito di visibilità delle tabelle temporanee globali e alle prestazioni dei blocchi CTE con l'hint "materializzato".

In entrambi i casi, non appena il codice migrato include tabelle temporanee, non dovremmo parlare di traduzione del codice, ma di re-implementazione del sistema.

Immettere le variabili della tabella

Gli sviluppatori probabilmente si chiederanno: che dire delle variabili di tabella? È necessario apportare modifiche o è possibile spostare le variabili di tabella "così com'è" nei nostri passaggi di migrazione da Oracle a SQL Server? Bene, questo dipende dal perché e da come vengono utilizzati nel codice.

Diamo un'occhiata a come possono essere utilizzate sia le tabelle temporanee che le variabili di tabella. Inizierò con Microsoft SQL Server.

L'implementazione delle variabili di tabella in Transact-SQL corrisponde in qualche modo alle tabelle temporanee ma aggiunge alcune funzionalità proprie. La differenza fondamentale è la capacità di passare variabili di tabella come parametri a funzioni e procedure memorizzate.

Questa è la teoria, ma le considerazioni sull'uso pratico sono un po' più coinvolte.

Incaricato per la prima volta di una seria ottimizzazione di Transact-SQL quando provenivo da un background Oracle profondamente radicato, mi aspettavo che fosse così: le variabili di tabella sono in memoria mentre le tabelle temporanee sono su disco. Ma ho scoperto che le versioni di Microsoft SQL Server fino al 2014 non memorizzavano le variabili di tabella in memoria. Pertanto, una scansione completa della tabella su una variabile temporanea è effettivamente una scansione completa della tabella sul disco. Per fortuna, SQL Server 2017 e versioni successive supportano l'ottimizzazione della memoria dichiarativa sia per le tabelle temporanee che per le variabili di tabella.

Quindi, qual è il caso d'uso per le variabili di tabella in Transact-SQL se tutto può essere fatto ugualmente o meglio usando tabelle temporanee? La proprietà chiave di una variabile di tabella che è una variabile e come tale non è influenzata dal rollback della transazione e può essere passata come parametro.

Le funzioni Transact-SQL sono molto restrittive: poiché il compito di una funzione è restituire un valore restituito singolare, non può avere effetti collaterali in base alla progettazione. Transact-SQL vede anche SELECT come un effetto collaterale, perché in SQL Server qualsiasi accesso a una tabella crea una transazione implicita e un blocco della transazione associato. Ciò significa che all'interno di una funzione, non possiamo accedere ai dati in una tabella temporanea esistente, né creare una tabella temporanea. Di conseguenza, se dobbiamo passare qualsiasi insieme di record in una funzione, dobbiamo utilizzare variabili di tabella.

Le considerazioni di Oracle sull'utilizzo di tabelle temporanee (globali) e variabili di raccolta (l'equivalente Oracle PL/SQL delle variabili di tabella Transact-SQL) sono diverse. Le variabili di raccolta Oracle sono in memoria, mentre le tabelle temporanee si trovano in tablespace temporanei. Le funzioni Oracle consentono l'accesso in sola lettura alle tabelle, permanenti o temporanee; un semplice SELECT in Oracle non pone mai un blocco sulle risorse.

In Oracle, la scelta di utilizzare le variabili di raccolta rispetto alle tabelle temporanee si basa sulla quantità prevista di dati, la durata per la quale questi dati devono essere conservati e la memoria rispetto all'allocazione e disponibilità del disco. Inoltre, le variabili di raccolta sono il modo standard per riportare un set di righe come output in un programma host.

Poiché la maggior parte degli elementi della sintassi SQL sembra molto simile tra SQL Server e Oracle, la conversione di blocchi di codice con variabili di tabella da SQL Server Transact-SQL in Oracle PL/SQL è un processo più semplice e sintatticamente più tollerante. Potrebbe superare un test di convalida di base, ma non sarà funzionalmente corretto a meno che non vengano eseguite le fasi di reimplementazione temporanea della tabella, come descritto sopra. D'altra parte, il codice spostato da Oracle a SQL Server comporta più passaggi di modifica solo per essere sintatticamente valido. Per essere anche funzionalmente corretto, dovrà affrontare i casi approfonditi di utilizzo di tabelle temporanee e CTE.

Transazioni interne ("Transazioni annidate")

In termini di sfide di migrazione da Oracle a SQL Server, la prossima area importante da considerare sono le transazioni nidificate.

Proprio come con le tabelle temporanee, se il codice Transact-SQL include qualsiasi transazione, nidificata o meno, o il codice Oracle include qualsiasi transazione nidificata, stiamo parlando non solo di una semplice migrazione del codice, ma di una reimplementazione funzionale.

Innanzitutto, diamo un'occhiata a come si comportano le transazioni nidificate Oracle e come tendiamo a usarle.

Transazioni annidate in Oracle

Le transazioni nidificate Oracle sono completamente atomiche e indipendenti dall'ambito esterno. Non vi è alcun uso effettivo per le transazioni nidificate in semplici query Oracle SQL interattive. Quando lavori con Oracle in modalità interattiva, esegui il commit manualmente delle modifiche quando vedi che sei arrivato a uno stato. Se hai apportato alcune modifiche che non puoi ancora confermare fino a quando non eseguirai l'ultimo passaggio, ad esempio incerto per te, di cui potrebbe essere necessario annullare il rollback, ma desideri preservare il lavoro che è già stato svolto da te, creerai un punto sicuro a cui tornare senza eseguire il commit o il rollback dell'intera transazione.

Quindi, dove vengono utilizzate le transazioni nidificate? Nel codice PL/SQL. Più precisamente nelle procedure autonome, quelle dichiarate con PRAGMA AUTONOMOUS_TRANSACTION . Significa che quando questo codice viene chiamato (come stored procedure denominata o in modo anonimo) la transazione viene salvata o annullata indipendentemente dalla transazione che ha chiamato questo codice.

L'obiettivo dell'utilizzo delle transazioni nidificate è di eseguire il commit o il rollback di un'unità di lavoro autonoma indipendentemente da ciò che accadrà al codice chiamante. Quando una transazione interna può essere impegnata o annullata, verrebbe utilizzata per verificare la disponibilità di (o prenotare) risorse condivise, ad esempio nell'implementazione di un sistema di prenotazione delle camere. L'uso principale per le transazioni interne di solo commit è il monitoraggio dell'attività, la traccia del codice e il controllo dell'accesso protetto (ovvero, a un utente non è stato consentito di apportare modifiche, ma ha tentato di farlo).

Le transazioni nidificate nel codice Transact-SQL di SQL Server sono completamente diverse.

Transazioni annidate in SQL Server

In Transact-SQL, il commit di una transazione interna dipende completamente dalla transazione più esterna. Se è stato eseguito il rollback di una transazione interna, viene semplicemente eseguito il rollback. Ma se una transazione interna è stata impegnata, non è ancora completamente impegnata, poiché può essere annullata se viene eseguito il rollback di qualsiasi livello della transazione con ambito esterno.

Quindi, qual è l'uso delle transazioni interne se i suoi commit possono essere annullati ripristinando la transazione esterna? La risposta è la stessa del caso d'uso per le tabelle temporanee locali: rilasciare il blocco sulle risorse. La differenza è che non si tratta di un rilascio di blocco globale, ma di un blocco nell'ambito della transazione esterna immediata (diretta "principale"). Viene utilizzato nel complesso codice Transact-SQL per rilasciare risorse interne per la transazione esterna. È uno strumento di ottimizzazione delle prestazioni e gestione delle risorse.

Poiché le transazioni interne/nidificate di Oracle e SQL Server hanno un comportamento diverso (forse anche opposto) e casi d'uso completamente diversi, la migrazione da una piattaforma all'altra richiede non solo una riscrittura, ma anche la riprogettazione completa di qualsiasi ambito che contenga blocchi di transazione annidati .

Altri fattori

Queste considerazioni sulla tabella temporanea e incentrate sulla transazione sono le uniche cose che devono essere affrontate in una migrazione da Oracle a SQL Server? Sebbene possano essere i più importanti, ce ne sono sicuramente altri, ognuno con le proprie stranezze che vale la pena coprire. Di seguito è riportato il resto di quelli che ho trovato essere gli argomenti più fraintesi:

1. Colonne di identità in SQL Server
2. Sequenze in Oracle
3. Sinonimi in Oracle
4. Indici filtrati
5. Coerenza di lettura (solo da Oracle a SQL Server)
6. Utilizzo di strumenti di migrazione

La parte successiva di questa serie continua esplorando questi, in particolare i primi tre.

Tabelle temporanee, variabili tabella/raccolta e transazioni nidificate: i 3 principali punti deboli della migrazione

Ho iniziato con tabelle temporanee, variabili/raccolte di tabelle e transazioni nidificate perché questi sono i punti di errore più comuni ed evidenti nei progetti di conversione. Qualsiasi sistema non banale in Oracle Database o Microsoft SQL Server ne utilizzerà senza dubbio alcuni e l'utilizzo di questi elementi è strettamente associato alla progettazione specifica del supporto delle transazioni da parte delle rispettive implementazioni RDBMS.

Continua a leggere nella parte 2!