Ghid de migrare Oracle la SQL Server și SQL Server la Oracle

„Dependența de furnizor” este un cuvânt înfricoșător pentru mulți directori de afaceri. Pe de altă parte, este deja înțeles pe scară largă în industrie că „independența completă a furnizorului” nu este realizabilă. Și este valabil mai ales în cazul bazelor de date.

Două dintre cele mai proliferate platforme RDBMS pentru întreprinderi sunt Oracle Database și Microsoft SQL Server (pentru concizie, pentru restul acestui articol, le voi numi „Oracle” și, respectiv, „SQL Server”). Sigur, IBM Db2 concurează cu Oracle pe platformele mainframe în continuă scădere – dar încă critice în multe domenii. Și alternativele open-source care progresează rapid, cum ar fi PostgreSQL, câștigă o poziție fermă în medii dinamice pe hardware-ul de marfă de nivel scăzut până la mediu și pe web.

Dar Oracle vs. SQL Server este alegerea cu care se confruntă mulți directori de afaceri atunci când organizațiile lor au nevoie de un nou RDBMS. Selecția finală se bazează pe mai mulți factori: costul licenței, expertiza internă disponibilă și experiența trecută, compatibilitatea cu mediile existente, relațiile cu partenerii, planurile de afaceri viitoare etc. Dar chiar și cu cea mai amănunțită evaluare inițială și cu luarea deciziilor cel mai bine educat, uneori factori schimbare și apoi platforma trebuie să se schimbe, de asemenea. Știu acest lucru pentru că, de-a lungul carierei mele, am implementat astfel de migrari de două ori, am pregătit evaluarea fezabilității tranziției o dată și lucrez la migrarea funcționalității pe mai multe platforme în acest moment.

Atât Oracle, cât și SQL Server sunt implementări RDBMS „de școală veche”, parțial compatibile cu ANSI. Lăsând deoparte extensiile procedurale – PL/SQL și Transact-SQL au sintaxe diferite, dar sunt în general ușor de tradus între ele – și viitorul mai nou orientat pe obiecte, codul SQL poate arăta înșelător de asemănător. Și aceasta este o capcană de miere periculoasă.

Două dintre cele mai critice puncte pentru orice proiect de migrare între Oracle și SQL Server (în ambele direcții) sunt tranzacțiile și, strâns legate, tabelele temporare , care sunt un instrument cheie în rezolvarea domeniului tranzacției. Vom acoperi, de asemenea, tranzacțiile imbricate - care există în domeniul de aplicare al unei alte tranzacții - deoarece sunt o parte cheie a implementării auditului de securitate a utilizatorilor în Oracle. Dar în SQL Server, auditarea securității utilizatorilor are nevoie de o abordare diferită din cauza comportamentului său COMMIT în acest context.

Înțelegerea structurii tranzacției: observarea Oracle vs. SQL Server de la zece mii de picioare

Tranzacțiile Oracle sunt implicite. Înseamnă că nu trebuie să începi o tranzacție — ești mereu într-o tranzacție. Și această tranzacție este deschisă până când emiteți o declarație de commit sau rollback. Da, puteți să începeți o tranzacție în mod explicit, să definiți puncte sigure de rollback și să setați tranzacții interne/imbricate; dar ceea ce este important este că nu ești niciodată „într-o tranzacție” și trebuie să emiti întotdeauna un commit sau rollback. De asemenea, rețineți că emiterea unei instrucțiuni de limbaj de definire a datelor (DDL) ( CREATE , ALTER , etc.; într-o tranzacție se poate face prin SQL dinamic) comite tranzacția în care a fost emisă.

Spre deosebire de Oracle, SQL Server are tranzacții explicite. Aceasta înseamnă că, dacă nu începeți în mod explicit o tranzacție, toate modificările dvs. vor fi comise „automat” - imediat ce declarația dvs. este procesată, deoarece fiecare instrucțiune DML ( INSERT , UPDATE , DELETE ) creează o tranzacție pe cont propriu și o comite, cu excepția cazului în care greșește. afară.

Acesta este rezultatul diferenței dintre implementările de stocare a datelor - cum sunt scrise datele într-o bază de date și cum le citește motorul bazei de date.

În Oracle, instrucțiunile DML modifică înregistrările direct în fișierul de date. Vechea copie a înregistrării (sau înlocuirea înregistrării goale, în cazul INSERT ) este scrisă în fișierul de rollback curent, iar ora exactă a modificării este marcată pe înregistrare.

Când este emisă o declarație SELECT , aceasta este procesată pe baza datelor care au fost modificate înainte de a fi emisă. Dacă orice înregistrări au fost modificate după emiterea SELECT , Oracle utilizează versiunea mai veche din fișierul rollback.

Acesta este modul în care Oracle a implementat consecvența citirii și citirea/scrierea fără blocare. Acesta este, de asemenea, motivul pentru care interogările de lungă durată pe baze de date tranzacționale foarte active ar întâlni uneori infama eroare ORA-01555, snapshot too old: rollback segment ... too small . (Aceasta înseamnă că fișierul rollback necesar interogării pentru o versiune mai veche a înregistrării a fost deja reutilizat.) Acesta este motivul pentru care răspunsul corect la întrebarea „Cât ar trebui să dureze tranzacția mea Oracle?” este „Atâta timp cât este nevoie și nu mai”.

Implementarea SQL Server este diferită: motorul bazei de date scrie și citește direct în/din fișiere de date numai. Fiecare instrucțiune SQL ( SELECT / INSERT / UPDATE / DELETE ) este o tranzacție, cu excepția cazului în care face parte dintr-o tranzacție explicită care grupează mai multe instrucțiuni, permițând derularea modificărilor.

Fiecare tranzacție blochează resursele de care are nevoie. Versiunile actuale ale Microsoft SQL Server sunt foarte optimizate pentru a bloca doar resursele necesare, dar ceea ce este necesar este definit de codul SQL, deci optimizarea interogărilor este esențială). Adică, spre deosebire de Oracle, tranzacțiile în SQL Server ar trebui să fie cât mai scurte posibil și de aceea comiterile automate sunt comportamentul implicit.

Și care construcție SQL din Oracle și SQL Server este afectată de diferența dintre implementările lor de tranzacții? Tabelele Temp.

Tabele temporare în Oracle și SQL Server

Când standardul ANSI SQL definește tabelele temporare locale și globale, nu precizează în mod explicit cum ar trebui implementate. Atât Oracle, cât și SQL Server implementează tabele temporare globale. SQL Server implementează și tabele temporare locale. Oracle 18c a implementat, de asemenea, tabele temporare locale „adevărate” (pe care le numesc „tabele temporare private”). Acest lucru face ca traducerea codului SQL Server în Oracle 18c să fie vizibil mai simplă decât este pentru versiunile mai vechi, completând adăugarea anterioară de către Oracle a câtorva conexe. caracteristici precum coloanele de identitate cu incrementare automată.

Dar dintr-o perspectivă pură de analiză funcțională, introducerea tabelelor temporare private poate fi o binecuvântare mixtă, deoarece face ca problemele de migrare SQL Server la Oracle să pară mai puțin decât sunt. Aceasta este o altă capcană pentru miere, deoarece poate introduce unele noi provocări proprii. De exemplu, validarea codului de proiectare nu se poate face pe tabele temporare private, astfel încât orice cod care le folosește va fi invariabil mai predispus la erori. Dacă ați folosit SQL dinamic, să o spunem astfel: tabelele temporare private sunt la fel de complexe de depanat, dar fără un caz de utilizare aparent unic. De aceea, Oracle a adăugat tabele temporare locale (private) numai în 18c și nu înainte.

Pe scurt, nu văd un caz de utilizare pentru tabelele temporare private în Oracle care să nu poată fi implementate folosind tabele temporare globale la fel sau mai bine. Deci, pentru orice conversie serioasă, trebuie să înțelegem diferența dintre tabelele globale temporare Oracle și SQL Server.

Tabele temporare globale în Oracle și SQL Server

Un tabel temporar global Oracle este un obiect dicționar de date permanent creat în mod explicit la momentul proiectării printr-o instrucțiune DDL. Este „global” doar deoarece este un obiect la nivel de bază de date și poate fi accesat de orice sesiune de bază de date care are permisiunile necesare. Cu toate acestea, în ciuda faptului că structura sa este globală, toate datele dintr-un tabel temporar global se limitează numai la sesiunea în care operează și nu sunt, în nicio circumstanță, vizibile în afara acestei sesiuni. Cu alte cuvinte, alte sesiuni pot avea propriile lor date în propria copie a aceluiași tabel temporar global. Așadar, în Oracle, un tabel temporar global conține date locale de sesiune - utilizate mai ales în PL/SQL pentru simplificarea codului și optimizarea performanței.

În SQL Server, un tabel temporar global este un obiect temporar creat într-un bloc de cod Transact-SQL. Există atâta timp cât sesiunea sa de creare este deschisă și este vizibilă – atât în ​​structură, cât și în date – altor sesiuni din baza de date. Deci, este un obiect temporar global pentru partajarea datelor între sesiuni.

Un tabel temporar local din SQL Server diferă de cel global prin faptul că este accesibil doar în sesiunea care îl creează. Și utilizarea tabelelor temporare locale în SQL Server este mult mai răspândită (și, aș spune, mai critică pentru performanța bazei de date) decât utilizarea tabelelor temporare globale.

Deci, cum sunt utilizate tabelele temporare locale în SQL Server și cum ar trebui să fie traduse în Oracle?

Utilizarea critică (și corectă) a tabelelor temporare locale în SQL Server este de a scurta sau elimina blocarea resurselor de tranzacție, în special:

• Când un set de înregistrări trebuie procesat printr-o anumită agregare
• Când setul de date trebuie analizat și modificat
• Când același set de date trebuie utilizat de mai multe ori în același domeniu

În aceste cazuri, este foarte adesea o soluție mai bună să selectați acest set de înregistrări într-un tabel temporar local pentru a elimina blocarea din tabelul sursă.

Este demn de remarcat faptul că expresiile de tabel obișnuite (CTE, adică instrucțiuni WITH <alias> AS (SELECT...) ) din SQL Server sunt doar „zahăr sintactic”. Ele sunt convertite în subinterogări inline înainte de executarea SQL. CTE-urile Oracle (cu un indiciu /*+ materialize */ ) sunt optimizate pentru performanță și creează o versiune temporară a unei vizualizări materializate. În calea de execuție a Oracle, CTE-urile accesează datele sursă o singură dată. Pe baza acestei diferențe, SQL Server poate funcționa mai bine folosind tabele temporare locale în loc de referințe multiple la același CTE, așa cum s-ar putea face într-o interogare Oracle.

Din cauza diferenței dintre implementările tranzacțiilor, tabelele temporare servesc, de asemenea, o funcție diferită. Ca rezultat, mutarea tabelelor temporare SQL Server la Oracle „așa cum este” (chiar și cu implementarea de către Oracle 18c a tabelelor temporare private) poate fi nu numai în detrimentul performanței, ci și funcțional greșit.

Pe de altă parte, atunci când treceți de la Oracle la SQL Server, trebuie acordată atenție lungimii tranzacției, sferei de vizibilitate a tabelelor temporare globale și performanței blocurilor CTE cu indiciu „materializat”.

În ambele cazuri, de îndată ce codul migrat include tabele temporare, ar trebui să vorbim nu despre traducerea codului, ci despre reimplementarea sistemului.

Introduceți variabilele tabelului

Dezvoltatorii se vor întreba probabil: cum rămâne cu variabilele de tabel? Trebuie să facem modificări sau putem muta variabilele de tabel „ca atare” în pașii noștri de migrare Oracle-la-SQL-Server? Ei bine, asta depinde de ce și cum sunt utilizate în cod.

Să vedem cum pot fi utilizate atât tabelele temporare, cât și variabilele de tabel. Voi începe cu Microsoft SQL Server.

Implementarea variabilelor de tabel în Transact-SQL se potrivește oarecum cu tabelele temporare, dar adaugă o anumită funcționalitate proprie. Diferența cheie este capacitatea de a trece variabilele tabelului ca parametri funcțiilor și procedurilor stocate.

Aceasta este teoria, dar considerentele practice de utilizare sunt puțin mai implicate.

Inițial, însărcinat cu optimizarea Transact-SQL serioasă, când veneam dintr-un fundal Oracle profund înrădăcinat, mă așteptam să fie așa: variabilele de tabel sunt în memorie, în timp ce tabelele temporare sunt pe disc. Dar am descoperit că versiunile Microsoft SQL Server până în 2014 nu au stocat variabile de tabel în memorie. Astfel, o scanare completă a tabelului pe o variabilă temporară este într-adevăr o scanare a tabelului complet pe disc. Din fericire, SQL Server 2017 și versiunile ulterioare acceptă optimizarea declarativă a memoriei atât pentru tabelele temporare, cât și pentru variabilele de tabel.

Deci, care este cazul de utilizare pentru variabilele de tabel în Transact-SQL dacă totul poate fi făcut la fel de bine sau mai bine folosind tabele temporare? Proprietatea cheie a unei variabile de tabel că este o variabilă și ca atare nu este afectată de rollback-ul tranzacției și poate fi transmisă ca parametru.

Funcțiile Transact-SQL sunt foarte restrictive: deoarece sarcina unei funcții este să returneze o valoare de returnare singulară, aceasta, prin proiectare , nu poate avea efecte secundare . Transact-SQL vede chiar și SELECT ca un efect secundar, deoarece în SQL Server orice acces la un tabel creează o tranzacție implicită și blocarea tranzacției asociată. Aceasta înseamnă că în interiorul unei funcții nu putem accesa datele dintr-un tabel temporar existent și nici nu putem crea un tabel temporar. Ca rezultat, dacă trebuie să trecem orice set de înregistrări într-o funcție, trebuie să folosim variabile de tabel.

Considerațiile Oracle pentru utilizarea tabelelor temporare (globale) și a variabilelor de colecție (echivalentul Oracle PL/SQL al variabilelor de tabel Transact-SQL) sunt diferite. Variabilele de colecție Oracle sunt în memorie, în timp ce tabelele temporare sunt situate în spații de tabele temporare. Funcțiile Oracle permit accesul numai în citire la tabele, permanent sau temporar; un simplu SELECT în Oracle nu blochează niciodată resursele.

În Oracle, alegerea utilizării variabilelor de colecție în comparație cu tabelele temporare se bazează pe cantitatea așteptată de date, pe durata pentru care aceste date trebuie păstrate și pe alocarea și disponibilitatea memoriei vs. disc. De asemenea, variabilele de colecție sunt modalitatea standard de a prelua un set de rânduri ca rezultat înapoi la un program gazdă.

Întrucât majoritatea elementelor de sintaxă SQL arată foarte asemănător între SQL Server și Oracle, conversia blocurilor de cod cu variabile de tabel din SQL Server Transact-SQL în Oracle PL/SQL este un proces mai simplu și mai iertător din punct de vedere sintactic. Ar putea trece un test de validare de bază, dar nu va fi corect din punct de vedere funcțional decât dacă sunt parcursi pașii temporari de reimplementare a tabelului, așa cum s-a subliniat mai sus. Pe de altă parte, codul mutat de la Oracle la SQL Server implică mai mulți pași de modificare doar pentru a fi valid sintactic. Pentru a fi, de asemenea, corect din punct de vedere funcțional, va trebui să abordeze cazurile în profunzime de utilizare a tabelelor temporare și a CTE-urilor.

Tranzacții interne („Tranzacții imbricate”)

În ceea ce privește provocările de migrare de la Oracle la SQL Server, următoarea zonă majoră de examinat sunt tranzacțiile imbricate.

La fel ca și în cazul tabelelor temporare, dacă codul Transact-SQL include orice tranzacție, imbricată sau nu, sau codul Oracle include orice tranzacție imbricată, vorbim nu doar de o migrare de cod simplu, ci de reimplementare funcțională.

Mai întâi, să vedem cum se comportă tranzacțiile imbricate Oracle și cum avem tendința de a le folosi.

Tranzacții imbricate în Oracle

Tranzacțiile imbricate Oracle sunt complet atomice și independente de domeniul exterior. Nu există nicio utilizare reală pentru tranzacțiile imbricate în interogări simple interactive Oracle SQL. Când lucrați cu Oracle în modul interactiv, doar comiteți manual modificările când vedeți că ați ajuns la o stare. Dacă ați făcut unele modificări pe care încă nu le puteți efectua până când nu veți face ultimul pas - să zicem, incert pentru dvs. - care poate fi necesar să fie anulat, dar doriți să păstrați munca care a fost deja făcută de dvs., veți crea un punct de siguranță pentru a reveni la acesta fără a efectua sau anula tranzacția completă.

Deci, unde sunt utilizate tranzacțiile imbricate? În cod PL/SQL. Mai precis în procedurile autonome — cele declarate cu PRAGMA AUTONOMOUS_TRANSACTION . Înseamnă că atunci când acest cod este apelat (ca procedură stocată denumită sau în mod anonim), tranzacția este confirmată sau anulată independent de tranzacția care a apelat acest cod.

Scopul utilizării tranzacțiilor imbricate este de a avea o unitate de lucru autonomă angajată sau anulată, indiferent de ceea ce se va întâmpla cu codul de apelare. Când o tranzacție internă poate fi angajată sau anulată, aceasta ar fi utilizată pentru a verifica disponibilitatea (sau rezerva) resurselor partajate, de exemplu, în implementarea unui sistem de rezervare a camerei. Utilizarea principală pentru tranzacțiile interne care implică doar angajare este monitorizarea activității, urmărirea codului și auditarea accesului securizat (adică, unui utilizator nu i s-a permis să facă modificări, dar a încercat să o facă.)

Tranzacțiile imbricate în codul Transact-SQL SQL Server sunt complet diferite.

Tranzacții imbricate în SQL Server

În Transact-SQL, dacă o tranzacție internă este efectuată depinde complet de tranzacția cea mai exterioară. Dacă o tranzacție internă a fost anulată, este doar anulată. Dar dacă o tranzacție internă a fost comisă, aceasta încă nu este complet angajată, deoarece poate fi anulată dacă orice nivel al tranzacției sale exterioare este anulat.

Deci, la ce folos tranzacțiile interioare dacă commit-urile sale pot fi anulate prin derularea înapoi a tranzacției sale exterioare? Răspunsul este același ca în cazul de utilizare pentru tabelele temporare locale: eliberarea blocării resurselor. Diferența este că nu este o eliberare globală de blocare, ci o blocare în domeniul de aplicare al tranzacției imediate externe („părinte”). Este folosit în codul complex Transact-SQL pentru a elibera resursele interioare pentru tranzacția exterioară. Este un instrument de optimizare a performanței și de gestionare a resurselor.

Deoarece tranzacțiile interioare/imbricate Oracle și SQL Server au un comportament diferit (poate chiar opus) și cazuri de utilizare complet diferite, migrarea de la o platformă la alta necesită nu doar o rescriere, ci și re-arhitectura completă a oricărui domeniu care conține blocuri de tranzacții imbricate. .

Alti factori

Sunt aceste considerente centrate pe tabele temporale și pe tranzacții singurele lucruri care trebuie abordate într-o migrare Oracle la SQL Server? Deși pot fi cele mai importante, cu siguranță există și altele, fiecare cu propriile sale ciudații care merită acoperite. Mai jos este restul subiectelor pe care le-am considerat a fi cele mai greșit înțelese:

1. Coloane de identitate în SQL Server
2. Secvențe în Oracle
3. Sinonime în Oracle
4. Indici filtrați
5. Consecvența citirii (numai Oracle către SQL Server)
6. Utilizarea instrumentelor de migrare

Următoarea parte a acestei serii continuă prin explorarea acestora, în special pe primele trei.

Tabele temporale, variabile de tabel/colecție și tranzacții imbricate: primele 3 puncte dificile de migrare

Am început cu tabele temporare, variabile/colecții de tabel și tranzacții imbricate, deoarece acestea sunt cele mai comune și mai evidente puncte de eșec în proiectele de conversie. Orice sistem non-trivial din Oracle Database sau Microsoft SQL Server va folosi fără îndoială unele dintre ele, iar utilizarea acestor elemente este foarte strâns cuplată cu designul specific al suportului pentru tranzacții de către implementările RDBMS respective.

Citiți mai departe în partea 2!