Przewodnik po migracji z Oracle do SQL Server i SQL Server do Oracle

„Zależność od dostawcy” to przerażające słowo dla wielu dyrektorów firm. Z drugiej strony, w branży jest już powszechnie rozumiane, że całkowita „niezależność od dostawcy” jest nieosiągalna. Dotyczy to zwłaszcza baz danych.

Dwie z najbardziej rozpowszechnionych platform RDBMS dla przedsiębiorstw to Oracle Database i Microsoft SQL Server (dla zwięzłości, w dalszej części artykułu będę je nazywał odpowiednio „Oracle” i „SQL Server”). Oczywiście, IBM Db2 konkuruje z Oracle w zakresie coraz kurczących się — ale wciąż krytycznych w wielu obszarach — platform mainframe. Szybko rozwijające się alternatywy typu open source, takie jak PostgreSQL, zyskują mocną pozycję w dynamicznych środowiskach na sprzęcie nisko- i średniopoziomowym oraz w Internecie.

Jednak Oracle kontra SQL Server to wybór, przed którym staje wielu menedżerów biznesowych, gdy ich organizacje potrzebują nowego systemu zarządzania bazą danych (RDBMS). Ostateczny wybór opiera się na wielu czynnikach: koszt licencji, dostępna wewnętrzna wiedza i doświadczenie z przeszłości, zgodność z istniejącymi środowiskami, relacje partnerskie, przyszłe plany biznesowe itp. Ale nawet przy najbardziej szczegółowej ocenie z góry i najlepiej wykształconym procesie podejmowania decyzji, czasami czynniki zmienić, a potem platforma też musi się zmienić. Wiem o tym, bo w swojej karierze dwukrotnie realizowałem takie migracje, raz przygotowałem ocenę możliwości przejścia, a obecnie pracuję nad migracją funkcjonalności międzyplatformowych.

Zarówno Oracle, jak i SQL Server są „oldschoolowymi”, częściowo zgodnymi z ANSI implementacjami RDBMS. Pomijając rozszerzenia proceduralne — PL/SQL i Transact-SQL mają różne składnie, ale generalnie można je łatwo przetłumaczyć — oraz nowsze, zorientowane obiektowo przyszłościowe rozwiązania, kod SQL może wyglądać łudząco podobnie. A to jest niebezpieczna pułapka na miód.

Dwa z najbardziej krytycznych punktów każdego projektu migracji między Oracle i SQL Server (w każdym kierunku) to transakcje i ściśle powiązane tabele tymczasowe , które są kluczowym narzędziem w określaniu zakresu transakcji. Omówimy również transakcje zagnieżdżone — te, które znajdują się w zakresie innej transakcji — ponieważ są kluczowym elementem wdrażania audytu bezpieczeństwa użytkowników w Oracle. Ale w SQL Server, inspekcja bezpieczeństwa użytkowników wymaga innego podejścia ze względu na zachowanie COMMIT w tym kontekście.

Zrozumienie struktury transakcji: obserwowanie Oracle kontra SQL Server z dziesięciu tysięcy stóp

Transakcje Oracle są niejawne. Oznacza to, że nie musisz rozpoczynać transakcji — zawsze jesteś w transakcji. Ta transakcja jest otwarta do momentu wydania oświadczenia o zatwierdzeniu lub wycofaniu. Tak, możesz jawnie rozpocząć transakcję, zdefiniować bezpieczne punkty wycofania i ustawić transakcje wewnętrzne/zagnieżdżone; ale ważne jest to, że nigdy „nie jesteś w transakcji” i zawsze musisz wydać zatwierdzenie lub wycofanie. Należy również zauważyć, że wydanie instrukcji języka definicji danych (DDL) ( CREATE , ALTER itp.; w transakcji można to zrobić za pomocą dynamicznego SQL) zatwierdza transakcję, w której została wydana.

W przeciwieństwie do Oracle, SQL Server zawiera transakcje jawne. Oznacza to, że o ile jawnie nie rozpoczniesz transakcji, wszystkie Twoje zmiany zostaną zatwierdzone „automatycznie” — natychmiast po przetworzeniu wyciągu, ponieważ każda instrukcja DML ( INSERT , UPDATE , DELETE ) sama tworzy transakcję i zatwierdza ją, chyba że wystąpi błąd na zewnątrz.

Wynika to z różnicy w implementacjach przechowywania danych — w jaki sposób dane są zapisywane w bazie danych i jak odczytuje je silnik bazy danych.

W Oracle instrukcje DML zmieniają rekordy bezpośrednio w pliku danych. Stara kopia rekordu (lub pusta zamiana rekordu, w przypadku INSERT ) jest zapisywana w bieżącym pliku przywracania, a dokładny czas zmiany jest zaznaczany na rekordzie.

Po wydaniu instrukcja SELECT jest przetwarzana na podstawie danych, które zostały zmodyfikowane przed jej wydaniem. Jeśli jakiekolwiek rekordy zostały zmodyfikowane po wydaniu SELECT , Oracle użyje starszej wersji z pliku wycofania.

W ten sposób firma Oracle zaimplementowała spójność odczytu i nieblokujący odczyt/zapis. Jest to również powód, dla którego długo działające zapytania dotyczące bardzo aktywnych transakcyjnych baz danych czasami napotykają niesławny błąd ORA-01555, snapshot too old: rollback segment ... too small . (Oznacza to, że plik wycofania wymagany przez zapytanie dla starszej wersji rekordu został już ponownie wykorzystany.) Dlatego poprawna odpowiedź na pytanie „Jak długo powinna trwać moja transakcja Oracle?” to „Tak długo, jak trzeba, a nie dłużej”.

Implementacja SQL Server jest inna: silnik bazy danych zapisuje i odczytuje tylko bezpośrednio do/z plików danych. Każda instrukcja SQL ( SELECT / INSERT / UPDATE / DELETE ) jest transakcją, chyba że jest częścią jawnej transakcji grupującej wiele instrukcji, co umożliwia wycofanie zmian.

Każda transakcja blokuje potrzebne zasoby. Obecne wersje Microsoft SQL Server są wysoce zoptymalizowane pod kątem blokowania tylko potrzebnych zasobów, ale to, co jest potrzebne, jest zdefiniowane przez kod SQL — dlatego optymalizacja zapytań ma kluczowe znaczenie. To znaczy, w przeciwieństwie do Oracle, transakcje w SQL Server powinny być jak najkrótsze i dlatego automatyczne zatwierdzenia są zachowaniem domyślnym.

A na którą konstrukcję SQL w Oracle i SQL Server wpływa różnica w ich implementacjach transakcyjnych? Tabele temp.

Tabele tymczasowe w Oracle i SQL Server

Kiedy standard ANSI SQL definiuje lokalne i globalne tabele tymczasowe, nie określa wyraźnie, jak powinny być zaimplementowane. Zarówno Oracle, jak i SQL Server implementują globalne tabele tymczasowe. SQL Server implementuje również lokalne tabele tymczasowe. Oracle 18c zaimplementował również „prawdziwe” lokalne tabele tymczasowe (które nazywają „prywatnymi tabelami tymczasowymi”). To sprawia, że ​​tłumaczenie kodu SQL Server na Oracle 18c jest wyraźnie prostsze niż w przypadku starszych wersji — dopełniając wcześniejsze dodanie przez Oracle kilku powiązanych funkcje, takie jak automatyczne zwiększanie kolumn tożsamości.

Jednak z perspektywy czystej analizy funkcjonalnej wprowadzenie prywatnych tabel tymczasowych może być mieszanym błogosławieństwem, ponieważ sprawia, że ​​problemy z migracją SQL Server do Oracle wydają się mniejsze niż w rzeczywistości. To kolejna miodowa pułapka, ponieważ sama w sobie może wprowadzić nowe wyzwania. Na przykład walidacji kodu w czasie projektowania nie można przeprowadzić na prywatnych tabelach tymczasowych, więc każdy kod, który z nich korzysta, będzie niezmiennie bardziej podatny na błędy. Jeśli używałeś dynamicznego SQL, ujmijmy to w ten sposób: Prywatne tabele tymczasowe są równie skomplikowane do debugowania, ale bez widocznego unikalnego przypadku użycia. Dlatego Oracle dodało lokalne (prywatne) tabele tymczasowe tylko w 18c, a nie wcześniej.

Krótko mówiąc, nie widzę przypadku użycia prywatnych tabel tymczasowych w Oracle, które nie mogą być zaimplementowane przy użyciu globalnych tabel tymczasowych tak samo lub lepiej. Tak więc w przypadku każdej poważnej konwersji musimy zrozumieć różnicę między globalnymi tabelami tymczasowymi Oracle i SQL Server.

Globalne tabele tymczasowe w Oracle i SQL Server

Globalna tabela tymczasowa Oracle to trwały obiekt słownika danych utworzony jawnie w czasie projektowania za pomocą instrukcji DDL. Jest „globalny” tylko dlatego, że jest obiektem na poziomie bazy danych i można uzyskać do niego dostęp przez dowolną sesję bazy danych z wymaganymi uprawnieniami. Jednak pomimo swojej struktury globalnej, wszystkie dane w globalnej tabeli tymczasowej są ograniczone tylko do sesji, w ramach której działa, iw żadnym wypadku nie są widoczne poza tą sesją. Innymi słowy, inne sesje mogą mieć własne dane we własnej kopii tej samej globalnej tabeli tymczasowej. Tak więc w Oracle globalna tabela tymczasowa przechowuje dane lokalne sesji — używane głównie w PL/SQL do uproszczenia kodu i optymalizacji wydajności.

W SQL Server globalna tabela tymczasowa jest tymczasowym obiektem utworzonym w bloku kodu Transact-SQL. Istnieje tak długo, jak długo jego sesja tworzenia jest otwarta i jest widoczna — zarówno w strukturze, jak i danych — dla innych sesji w bazie danych. Jest to więc globalny obiekt tymczasowy do udostępniania danych między sesjami.

Lokalna tabela tymczasowa w SQL Server różni się od globalnej tym, że jest dostępna tylko w sesji, która ją tworzy. A użycie lokalnych tabel tymczasowych w SQL Server jest znacznie bardziej rozpowszechnione (i powiedziałbym, że ma większe znaczenie dla wydajności bazy danych) niż użycie globalnych tabel tymczasowych.

W jaki sposób lokalne tabele tymczasowe są używane w SQL Server i jak powinny być tłumaczone na Oracle?

Krytycznym (i prawidłowym) wykorzystaniem lokalnych tabel tymczasowych w SQL Server jest skrócenie lub usunięcie blokady zasobów transakcji, w szczególności:

• Gdy zbiór rekordów musi zostać przetworzony przez jakąś agregację
• Kiedy zestaw danych wymaga analizy i modyfikacji
• Gdy ten sam zestaw danych musi być używany wielokrotnie w tym samym zakresie

W takich przypadkach bardzo często lepszym rozwiązaniem jest wybranie tego zestawu rekordów do lokalnej tabeli tymczasowej w celu usunięcia blokady z tabeli źródłowej.

Warto zauważyć, że typowe wyrażenia tabelowe (CTE, tj. WITH <alias> AS (SELECT...) ) w SQL Server są jedynie „cukrem składniowym”. Są konwertowane na podzapytania wbudowane przed wykonaniem SQL. Oracle CTE (ze wskazówką /*+ materialize */ ) są zoptymalizowane pod kątem wydajności i tworzą tymczasową wersję zmaterializowanego widoku. Na ścieżce wykonania Oracle CTE uzyskują dostęp do danych źródłowych tylko raz. Bazując na tej różnicy, SQL Server może działać lepiej przy użyciu lokalnych tabel tymczasowych zamiast wielu odwołań do tego samego CTE, jak można by to zrobić w zapytaniu Oracle.

Ze względu na różnicę między implementacjami transakcji, tabele tymczasowe pełnią również inną funkcję. W rezultacie przenoszenie tymczasowych tabel SQL Server do Oracle „tak jak jest” (nawet przy implementacji prywatnych tabel tymczasowych w Oracle 18c) może być nie tylko szkodliwe dla wydajności, ale także niepoprawne funkcjonalnie.

Z drugiej strony – przy przechodzeniu z Oracle na SQL Server – należy zwrócić uwagę na długość transakcji, zakres widoczności globalnych tabel tymczasowych oraz wydajność bloków CTE z „zmaterializowaną” wskazówką.

W obu przypadkach, gdy tylko migrowany kod zawiera tabele tymczasowe, powinniśmy mówić nie o translacji kodu, ale o ponownej implementacji systemu.

Wprowadź zmienne tabeli

Deweloperzy prawdopodobnie będą się zastanawiać: A co ze zmiennymi tabelowymi? Czy musimy wprowadzić jakieś zmiany, czy też możemy przenieść zmienne tabeli „tak jak są” w naszych krokach migracji z Oracle do SQL Server? Cóż, to zależy od tego, dlaczego i jak są używane w kodzie.

Przyjrzyjmy się, jak można używać zarówno tabel tymczasowych, jak i zmiennych tabel. Zacznę od Microsoft SQL Server.

Implementacja zmiennych tabel w Transact-SQL w pewnym stopniu odpowiada tabelom tymczasowym, ale dodaje kilka własnych funkcji. Kluczową różnicą jest możliwość przekazywania zmiennych tabeli jako parametrów do funkcji i procedur składowanych.

To jest teoria, ale praktyczne rozważania dotyczące użycia są nieco bardziej skomplikowane.

Po raz pierwszy otrzymałem zadanie poważnej optymalizacji Transact-SQL, gdy wywodziłem się z głęboko zakorzenionych środowisk Oracle, spodziewałem się, że będzie tak: zmienne tabel znajdują się w pamięci, podczas gdy tabele tymczasowe są na dysku. Ale odkryłem, że wersje Microsoft SQL Server do 2014 roku nie przechowują zmiennych tabel w pamięci. Tak więc pełne skanowanie tabeli na zmiennej tymczasowej jest w rzeczywistości pełnym skanowaniem tabeli na dysku. Na szczęście SQL Server 2017 i nowsze wersje obsługują optymalizację pamięci deklaratywnej zarówno dla tabel tymczasowych, jak i zmiennych tabel.

Jaki jest więc przypadek użycia zmiennych tabel w Transact-SQL, jeśli wszystko można zrobić równie dobrze lub lepiej przy użyciu tabel tymczasowych? Właściwość klucza zmiennej tabeli, która jest zmienną i jako taka nie ma wpływu na wycofywanie transakcji i może być przekazana jako parametr.

Funkcje języka Transact-SQL są bardzo restrykcyjne: ponieważ zadaniem funkcji jest zwrócenie pojedynczej wartości zwracanej, z założenia nie może ona mieć skutków ubocznych . Transact-SQL postrzega nawet SELECT jako efekt uboczny, ponieważ w SQL Server każdy dostęp do tabeli tworzy niejawną transakcję i związaną z nią blokadę transakcji. Oznacza to, że wewnątrz funkcji nie możemy uzyskać dostępu do danych w istniejącej tabeli tymczasowej ani utworzyć tabeli tymczasowej. W rezultacie, jeśli potrzebujemy przekazać dowolny zestaw rekordów do funkcji, musimy użyć zmiennych tabelowych.

Uwagi Oracle dotyczące używania (globalnych) tabel tymczasowych i zmiennych kolekcji (odpowiednik zmiennych tabel Transact-SQL w Oracle PL/SQL) są inne. Zmienne kolekcji Oracle znajdują się w pamięci, podczas gdy tabele tymczasowe znajdują się w tymczasowych obszarach tabel. Funkcje Oracle umożliwiają dostęp tylko do odczytu do tabel, stały lub tymczasowy; prosty SELECT w Oracle nigdy nie blokuje zasobów.

W Oracle wybór między używaniem zmiennych kolekcji a tabelami tymczasowymi opiera się na oczekiwanej ilości danych, czasie, przez jaki dane muszą być zachowane, oraz pamięci a alokacji i dostępności dysku. Ponadto zmienne kolekcji są standardowym sposobem, aby ustawić wiersz jako dane wyjściowe z powrotem do programu hosta.

Ponieważ większość elementów składni SQL wygląda bardzo podobnie między SQL Server i Oracle, konwersja bloków kodu ze zmiennymi tabeli z SQL Server Transact-SQL do Oracle PL/SQL jest procesem prostszym i bardziej wyrozumiałym pod względem składniowym. Może przejść podstawowy test weryfikacyjny, ale nie będzie funkcjonalnie poprawny, chyba że zostaną podjęte kroki ponownej implementacji tabeli tymczasowej, jak opisano powyżej. Z drugiej strony kod przeniesiony z Oracle do SQL Server wymaga większej liczby kroków modyfikacji tylko po to, aby był poprawny składniowo. Aby była również poprawna funkcjonalnie, będzie musiała zająć się szczegółowymi przypadkami używania tabel tymczasowych i CTE.

Transakcje wewnętrzne („Transakcje zagnieżdżone”)

Jeśli chodzi o wyzwania związane z migracją z Oracle do SQL Server, kolejnym ważnym obszarem, któremu należy się przyjrzeć, są transakcje zagnieżdżone.

Podobnie jak w przypadku tabel tymczasowych, jeśli kod Transact-SQL zawiera jakąkolwiek transakcję, zagnieżdżoną lub nie, lub kod Oracle zawiera jakiekolwiek transakcje zagnieżdżone, mówimy nie tylko o migracji zwykłego kodu, ale o ponownej implementacji funkcjonalnej.

Najpierw spójrzmy, jak zachowują się zagnieżdżone transakcje Oracle i jak zwykle z nich korzystamy.

Transakcje zagnieżdżone w Oracle

Transakcje zagnieżdżone Oracle są całkowicie niepodzielne i niezależne od zakresu zewnętrznego. Nie ma rzeczywistego zastosowania transakcji zagnieżdżonych w zwykłych interaktywnych zapytaniach Oracle SQL. Kiedy pracujesz z Oracle w trybie interaktywnym, po prostu ręcznie zatwierdzasz zmiany, gdy widzisz, że osiągnąłeś stan. Jeśli wprowadziłeś jakieś zmiany, których nie możesz jeszcze zatwierdzić, dopóki nie wykonasz ostatniego — powiedzmy, niepewny — krok, który może wymagać cofnięcia, ale chcesz zachować pracę, która została już przez Ciebie wykonana, utworzysz bezpieczny punkt, aby wrócić do niego bez zatwierdzania lub wycofywania pełnej transakcji.

Więc gdzie są używane transakcje zagnieżdżone? W kodzie PL/SQL. Dokładniej w procedurach autonomicznych — tych zadeklarowanych za pomocą PRAGMA AUTONOMOUS_TRANSACTION . Oznacza to, że gdy ten kod jest wywoływany (jako nazwana procedura składowana lub anonimowo), transakcja jest zatwierdzana lub wycofywana niezależnie od transakcji, która wywołała ten kod.

Celem korzystania z transakcji zagnieżdżonych jest zatwierdzenie lub wycofanie samodzielnej jednostki pracy, niezależnie od tego, co stanie się z kodem wywołującym. Gdy transakcja wewnętrzna może zostać zatwierdzona lub wycofana, zostanie użyta do sprawdzenia dostępności (lub zarezerwowania) współdzielonych zasobów — np. przy wdrażaniu systemu rezerwacji sal. Podstawowym zastosowaniem transakcji wewnętrznych tylko do zatwierdzania jest monitorowanie aktywności, śledzenie kodu i kontrola bezpiecznego dostępu (tj. użytkownik nie mógł wprowadzać zmian, ale próbował to zrobić).

Transakcje zagnieżdżone w kodzie Transact-SQL w programie SQL Server są zupełnie inne.

Transakcje zagnieżdżone w SQL Server

W Transact-SQL, czy transakcja wewnętrzna jest zatwierdzona całkowicie, zależy od transakcji zewnętrznej. Jeśli transakcja wewnętrzna została wycofana, po prostu została wycofana. Ale jeśli transakcja wewnętrzna została zatwierdzona, nadal nie jest w pełni zatwierdzona, ponieważ można ją wycofać, jeśli jakikolwiek poziom jej transakcji o zakresie zewnętrznym zostanie wycofany.

Jaki jest więc pożytek z transakcji wewnętrznych, jeśli ich zatwierdzenia można cofnąć, wycofując transakcję zewnętrzną? Odpowiedź jest taka sama, jak w przypadku użycia lokalnych tabel tymczasowych: zwolnienie blokady zasobów. Różnica polega na tym, że nie jest to globalne zwolnienie blokady, ale blokada w zakresie bezpośredniej transakcji zewnętrznej (bezpośredniego „rodzica”). Jest używany w złożonym kodzie Transact-SQL, aby zwolnić wewnętrzne zasoby dla transakcji zewnętrznej. Jest to narzędzie do optymalizacji wydajności i zarządzania zasobami.

Ponieważ transakcje wewnętrzne/zagnieżdżone Oracle i SQL Server mają różne (być może nawet przeciwne) zachowanie i zupełnie różne przypadki użycia, migracja z jednej platformy na drugą wymaga nie tylko ponownego napisania, ale także całkowitego przeprojektowania dowolnego zakresu zawierającego zagnieżdżone bloki transakcji .

Inne czynniki

Czy te kwestie dotyczące tabel tymczasowych i transakcji są jedynymi kwestiami, które należy uwzględnić podczas migracji z Oracle do SQL Server? Chociaż mogą być najważniejsze, są zdecydowanie inne, z których każdy ma własne dziwactwa, które warto ukryć. Poniżej znajduje się pozostała część najbardziej niezrozumianych tematów:

1. Kolumny tożsamości w SQL Server
2. Sekwencje w Oracle
3. Synonimy w Oracle
4. Filtrowane indeksy
5. Spójność odczytu (tylko Oracle do SQL Server)
6. Korzystanie z narzędzi migracyjnych

Kolejna część tej serii to kontynuacja badania tych, zwłaszcza pierwszych trzech.

Tabele temp, zmienne tabeli/kolekcji i transakcje zagnieżdżone: trzy najważniejsze problemy związane z migracją

Zacząłem od tabel tymczasowych, zmiennych/kolekcji tabel i transakcji zagnieżdżonych, ponieważ są to najczęstsze i oczywiste punkty niepowodzenia w projektach konwersji. Każdy nietrywialny system w Oracle Database czy Microsoft SQL Server z pewnością będzie korzystał z niektórych z nich, a wykorzystanie tych elementów jest bardzo ściśle powiązane ze specyficzną konstrukcją obsługi transakcji przez poszczególne implementacje RDBMS.

Czytaj dalej w części 2!