Panduan Migrasi Oracle ke SQL Server dan SQL Server ke Oracle

"Ketergantungan vendor" adalah kata yang menakutkan bagi banyak eksekutif bisnis. Di sisi lain, sudah dipahami secara luas di industri bahwa "kemandirian vendor" sepenuhnya tidak dapat dicapai. Dan itu terutama benar dalam kasus database.

Dua dari platform RDBMS perusahaan yang paling berkembang biak adalah Oracle Database dan Microsoft SQL Server (untuk singkatnya, untuk sisa artikel ini, saya akan menyebutnya "Oracle" dan "SQL Server"). Tentu, IBM Db2 bersaing dengan Oracle pada platform mainframe yang terus menyusut—namun masih kritis di banyak bidang. Dan alternatif sumber terbuka yang berkembang pesat, seperti PostgreSQL, mendapatkan pijakan yang kokoh di lingkungan yang dinamis pada perangkat keras komoditas tingkat rendah hingga menengah dan di web.

Tetapi Oracle vs. SQL Server adalah pilihan yang dihadapi banyak eksekutif bisnis ketika organisasi mereka membutuhkan RDBMS baru. Pemilihan akhir didasarkan pada beberapa faktor: biaya lisensi, keahlian internal yang tersedia dan pengalaman masa lalu, kompatibilitas dengan lingkungan yang ada, hubungan mitra, rencana bisnis masa depan, dll. Tetapi bahkan dengan evaluasi awal yang paling menyeluruh dan pengambilan keputusan yang terdidik, terkadang faktor berubah dan kemudian platform juga perlu diubah. Saya tahu ini karena dalam perjalanan karir saya, saya telah menerapkan migrasi seperti itu dua kali, menyiapkan evaluasi kelayakan transisi sekali, dan saya sedang mengerjakan migrasi fungsionalitas lintas platform saat ini.

Baik Oracle dan SQL Server adalah implementasi RDBMS yang “jadul”, sebagian sesuai dengan ANSI. Saat mengesampingkan ekstensi prosedural—PL/SQL dan Transact-SQL memiliki sintaks yang berbeda, tetapi umumnya mudah untuk menerjemahkan antara—dan masa depan berorientasi objek yang lebih baru, kode SQL dapat terlihat mirip. Dan ini adalah perangkap madu yang berbahaya.

Dua poin paling penting untuk setiap proyek migrasi antara Oracle dan SQL Server (di kedua arah) adalah transaksi dan, yang terkait erat, tabel sementara , yang merupakan alat utama dalam menyelesaikan ruang lingkup transaksi. Kami juga akan membahas transaksi bersarang—yang ada dalam lingkup transaksi lain—karena transaksi tersebut merupakan bagian penting dari penerapan audit keamanan pengguna di Oracle. Tetapi di SQL Server, audit keamanan pengguna memerlukan pendekatan yang berbeda karena perilaku COMMIT dalam konteks itu.

Memahami Struktur Transaksi: Mengamati Oracle vs. SQL Server dari Sepuluh Ribu Kaki

Transaksi Oracle bersifat implisit. Artinya, Anda tidak perlu memulai transaksi—Anda selalu dalam transaksi. Dan transaksi ini terbuka sampai Anda mengeluarkan pernyataan komit atau rollback. Ya, Anda dapat memulai transaksi secara eksplisit, menentukan titik aman rollback, dan mengatur transaksi dalam/bersarang; tapi yang penting adalah Anda tidak pernah "tidak dalam transaksi" dan Anda harus selalu mengeluarkan komit atau rollback. Perhatikan juga bahwa mengeluarkan pernyataan bahasa definisi data (DDL) ( CREATE , ALTER , dll .; dalam transaksi itu dapat dilakukan melalui SQL dinamis) melakukan transaksi di mana ia dikeluarkan.

Tidak seperti Oracle, SQL Server memiliki transaksi eksplisit. Ini berarti bahwa kecuali Anda secara eksplisit memulai transaksi, semua perubahan Anda akan dilakukan "secara otomatis"—segera saat pernyataan Anda diproses, karena setiap pernyataan DML ( INSERT , UPDATE , DELETE ) membuat transaksi sendiri dan melakukan itu kecuali kesalahan keluar.

Ini adalah hasil dari perbedaan dalam implementasi penyimpanan data—bagaimana data ditulis ke database dan bagaimana mesin database membacanya.

Di Oracle, pernyataan DML mengubah catatan secara langsung dalam file data. Salinan catatan yang lama (atau penggantian catatan kosong, dalam kasus INSERT ) ditulis ke file rollback saat ini, dan waktu yang tepat dari perubahan tersebut ditandai pada catatan.

Ketika pernyataan SELECT dikeluarkan, itu diproses berdasarkan data yang telah dimodifikasi sebelum dikeluarkan. Jika ada catatan yang telah dimodifikasi setelah SELECT dikeluarkan, Oracle menggunakan versi yang lebih lama dari file rollback.

Beginilah cara Oracle mengimplementasikan konsistensi baca dan baca/tulis non-blocking. Itu juga mengapa kueri yang berjalan lama pada basis data transaksional yang sangat aktif terkadang mengalami kesalahan terkenal ORA-01555, snapshot too old: rollback segment ... too small . (Ini berarti bahwa file rollback yang diperlukan oleh kueri untuk versi catatan yang lebih lama telah digunakan kembali.) Inilah mengapa jawaban yang benar untuk pertanyaan “Berapa lama transaksi Oracle saya?” adalah "Selama dibutuhkan dan tidak lagi."

Implementasi SQL Server berbeda: Mesin database menulis dan membaca langsung ke/dari file data saja. Setiap pernyataan SQL ( SELECT / INSERT / UPDATE / DELETE ) adalah transaksi kecuali jika itu adalah bagian dari transaksi eksplisit yang mengelompokkan beberapa pernyataan bersama-sama, memungkinkan perubahan untuk dibatalkan.

Setiap transaksi mengunci sumber daya yang dibutuhkannya. Rilis Microsoft SQL Server saat ini sangat dioptimalkan dalam mengunci hanya sumber daya yang diperlukan, tetapi apa yang dibutuhkan ditentukan oleh kode SQL—jadi mengoptimalkan kueri Anda sangat penting). Artinya, tidak seperti di Oracle, transaksi di SQL Server harus sesingkat mungkin, dan inilah mengapa komit otomatis adalah perilaku default.

Dan konstruksi SQL mana di Oracle dan SQL Server yang terpengaruh oleh perbedaan dalam implementasi transaksinya? Tabel sementara.

Tabel Sementara di Oracle dan SQL Server

Ketika ANSI SQL Standard mendefinisikan tabel sementara lokal dan global, itu tidak secara eksplisit menyatakan bagaimana mereka harus diimplementasikan. Baik Oracle dan SQL Server mengimplementasikan tabel sementara global. SQL Server juga mengimplementasikan tabel sementara lokal. Oracle 18c juga menerapkan tabel sementara lokal "benar" (yang mereka sebut "tabel sementara pribadi.") Hal ini membuat terjemahan kode SQL Server ke Oracle 18c tampak lebih sederhana daripada untuk versi yang lebih lama — melengkapi penambahan Oracle sebelumnya dari beberapa yang terkait fitur seperti kolom identitas yang bertambah otomatis.

Tetapi dari perspektif analisis fungsional murni, pengenalan tabel sementara pribadi dapat menjadi berkah campuran karena membuat masalah migrasi SQL Server ke Oracle tampak kurang dari yang sebenarnya. Ini adalah jebakan madu lainnya, karena mungkin menimbulkan beberapa tantangan baru tersendiri. Misalnya, validasi kode waktu desain tidak dapat dilakukan pada tabel sementara pribadi, sehingga kode apa pun yang menggunakannya akan selalu lebih rawan kesalahan. Jika Anda telah menggunakan SQL dinamis, mari kita begini: Tabel sementara pribadi sama rumitnya untuk di-debug, tetapi tanpa kasus penggunaan yang unik. Karenanya mengapa Oracle menambahkan tabel sementara lokal (pribadi) hanya di 18c dan bukan sebelumnya.

Singkatnya, saya tidak melihat kasus penggunaan untuk tabel sementara pribadi di Oracle yang tidak dapat diimplementasikan menggunakan tabel sementara global yang sama atau lebih baik. Jadi untuk konversi yang serius, kita perlu memahami perbedaan antara tabel sementara global Oracle dan SQL Server.

Tabel Sementara Global di Oracle dan SQL Server

Tabel sementara global Oracle adalah objek kamus data permanen yang dibuat secara eksplisit pada waktu desain oleh pernyataan DDL. Ini "global" hanya karena itu adalah objek tingkat basis data dan dapat diakses oleh sesi basis data mana pun yang memiliki izin yang diperlukan. Namun, meskipun strukturnya bersifat global, semua data dalam tabel sementara global dicakup hanya untuk sesi yang beroperasi di dalamnya dan tidak, dalam keadaan apa pun, terlihat di luar sesi ini. Dengan kata lain, sesi lain dapat memiliki data mereka sendiri dalam salinan tabel sementara global yang sama. Jadi, di Oracle, tabel sementara global menyimpan data sesi-lokal—sebagian besar digunakan di PL/SQL untuk penyederhanaan kode dan optimalisasi kinerja.

Di SQL Server, tabel sementara global adalah objek sementara yang dibuat di blok kode Transact-SQL. Itu ada selama sesi pembuatannya terbuka, dan itu terlihat—baik dalam struktur maupun data—ke sesi lain dalam database. Jadi, ini adalah objek sementara global untuk berbagi data di seluruh sesi.

Tabel sementara lokal di SQL Server berbeda dari tabel global karena hanya dapat diakses di sesi yang membuatnya. Dan penggunaan tabel sementara lokal di SQL Server jauh lebih luas (dan, menurut saya, lebih penting untuk kinerja database) daripada penggunaan tabel sementara global.

Jadi, bagaimana tabel sementara lokal digunakan di SQL Server, dan bagaimana seharusnya diterjemahkan ke dalam Oracle?

Penggunaan tabel sementara lokal yang kritis (dan benar) di SQL Server adalah untuk mempersingkat atau menghapus kunci sumber daya transaksi, terutama:

• Ketika satu set catatan perlu diproses oleh beberapa agregasi
• Ketika kumpulan data perlu dianalisis dan dimodifikasi
• Ketika kumpulan data yang sama perlu digunakan beberapa kali dalam lingkup yang sama

Dalam kasus ini, seringkali merupakan solusi yang lebih baik untuk memilih kumpulan rekaman ini ke dalam tabel sementara lokal untuk menghapus kunci dari tabel sumber.

Perlu dicatat bahwa ekspresi tabel umum (CTE, yaitu WITH <alias> AS (SELECT...) ) di SQL Server hanyalah "gula sintaksis". Mereka diubah menjadi subkueri sebaris sebelum eksekusi SQL. Oracle CTE (dengan /*+ materialize */ hint) dioptimalkan untuk kinerja dan membuat versi sementara dari tampilan material. Di jalur eksekusi Oracle, CTE hanya mengakses data sumber satu kali. Berdasarkan perbedaan ini, SQL Server mungkin berkinerja lebih baik menggunakan tabel sementara lokal daripada beberapa referensi ke CTE yang sama, seperti yang dapat dilakukan dalam kueri Oracle.

Karena perbedaan antara implementasi transaksi, tabel sementara juga memiliki fungsi yang berbeda. Akibatnya, memindahkan tabel sementara SQL Server ke Oracle "apa adanya" (bahkan dengan implementasi tabel sementara pribadi Oracle 18c) tidak hanya merusak kinerja, tetapi juga salah secara fungsional.

Di sisi lain—saat berpindah dari Oracle ke SQL Server—perhatian harus diberikan pada panjang transaksi, cakupan visibilitas tabel sementara global, dan kinerja blok CTE dengan petunjuk “terwujud”.

Dalam kedua kasus, segera setelah kode yang dimigrasikan menyertakan tabel sementara, kita seharusnya tidak berbicara tentang terjemahan kode, tetapi tentang implementasi ulang sistem.

Masukkan Variabel Tabel

Pengembang mungkin akan bertanya-tanya: Bagaimana dengan variabel tabel? Apakah kita perlu membuat perubahan atau dapatkah kita memindahkan variabel tabel "apa adanya" dalam langkah migrasi Oracle-ke-SQL-Server kita? Yah, ini tergantung pada mengapa dan bagaimana mereka digunakan dalam kode.

Mari kita lihat bagaimana tabel sementara dan variabel tabel dapat digunakan. Saya akan mulai dengan Microsoft SQL Server.

Implementasi variabel tabel di Transact-SQL agak cocok dengan tabel sementara tetapi menambahkan beberapa fungsionalitasnya sendiri. Perbedaan utama adalah kemampuan untuk melewatkan variabel tabel sebagai parameter ke fungsi dan prosedur tersimpan.

Ini adalah teorinya, tetapi pertimbangan penggunaan praktis sedikit lebih terlibat.

Pertama kali ditugaskan dengan optimasi Transact-SQL yang serius ketika saya berasal dari latar belakang Oracle yang mengakar, saya mengharapkannya seperti ini: variabel tabel ada di memori sementara tabel sementara ada di disk. Tetapi saya menemukan bahwa versi Microsoft SQL Server hingga 2014 tidak menyimpan variabel tabel di memori. Jadi pemindaian tabel penuh pada variabel sementara memang pemindaian tabel penuh pada disk. Untungnya, SQL Server 2017 dan versi yang lebih baru mendukung pengoptimalan memori deklaratif untuk tabel sementara dan variabel tabel.

Jadi, apa gunanya variabel tabel di Transact-SQL jika semuanya bisa dilakukan dengan baik atau lebih baik menggunakan tabel sementara? Properti kunci dari variabel tabel yang merupakan variabel dan dengan demikian tidak terpengaruh oleh rollback transaksi dan dapat diteruskan sebagai parameter.

Fungsi Transact-SQL sangat membatasi: karena tugas suatu fungsi adalah mengembalikan beberapa nilai pengembalian tunggal, fungsi itu—dengan desain —tidak dapat memiliki efek samping . Transact-SQL bahkan melihat SELECT sebagai efek samping, karena di SQL Server setiap akses ke tabel membuat transaksi implisit dan kunci transaksi terkait. Ini berarti bahwa di dalam suatu fungsi, kita tidak dapat mengakses data di tabel sementara yang ada, atau membuat tabel sementara. Akibatnya, jika kita perlu melewatkan kumpulan record apa pun ke dalam suatu fungsi, kita harus menggunakan variabel tabel.

Pertimbangan Oracle untuk menggunakan tabel sementara (global) dan variabel koleksi (setara dengan variabel tabel Transact-SQL Oracle PL/SQL) berbeda. Variabel koleksi Oracle ada di dalam memori, sedangkan tabel sementara terletak di ruang tabel sementara. Fungsi Oracle memungkinkan akses read-only ke tabel, permanen atau sementara; SELECT sederhana di Oracle tidak pernah mengunci sumber daya.

Di Oracle, pilihan untuk menggunakan variabel koleksi vs. tabel sementara didasarkan pada jumlah data yang diharapkan, durasi penyimpanan data ini, dan memori vs. alokasi dan ketersediaan disk. Juga, variabel koleksi adalah cara standar untuk mengambil set baris sebagai output kembali ke program host.

Karena sebagian besar elemen sintaks SQL terlihat sangat mirip antara SQL Server dan Oracle, konversi blok kode dengan variabel tabel dari SQL Server Transact-SQL menjadi Oracle PL/SQL adalah proses yang lebih sederhana dan lebih memaafkan secara sintaksis. Itu bisa lulus tes validasi dasar, tetapi tidak akan benar secara fungsional kecuali jika langkah-langkah implementasi ulang tabel sementara diambil, seperti yang diuraikan di atas. Di sisi lain, kode yang dipindahkan dari Oracle ke SQL Server melibatkan lebih banyak langkah modifikasi hanya agar valid secara sintaksis. Agar juga benar secara fungsional, itu perlu mengatasi kasus mendalam menggunakan tabel sementara dan CTE.

Transaksi Dalam (“Transaksi Bersarang”)

Dalam hal tantangan migrasi Oracle ke SQL Server, area utama berikutnya yang harus dilihat adalah transaksi bersarang.

Sama seperti tabel sementara, jika kode Transact-SQL menyertakan transaksi apa pun , bersarang atau tidak, atau kode Oracle menyertakan transaksi bersarang, yang kita bicarakan bukan hanya migrasi kode biasa, tetapi implementasi ulang fungsional.

Pertama, mari kita lihat bagaimana perilaku transaksi bersarang Oracle dan bagaimana kita cenderung menggunakannya.

Transaksi Bersarang di Oracle

Transaksi bersarang Oracle sepenuhnya bersifat atomik dan independen dari lingkup luar. Tidak ada penggunaan sebenarnya untuk transaksi bersarang dalam kueri Oracle SQL interaktif biasa. Saat Anda bekerja dengan Oracle dalam mode interaktif, Anda cukup melakukan perubahan secara manual saat Anda melihat bahwa Anda telah mencapai status. Jika Anda melakukan beberapa perubahan yang belum dapat Anda komit sampai Anda melakukan yang terakhir—misalnya, tidak pasti untuk Anda—langkah yang mungkin perlu dibatalkan, tetapi Anda ingin mempertahankan pekerjaan yang telah Anda lakukan, Anda akan membuat titik aman untuk memutar kembali ke sana tanpa melakukan atau memutar kembali transaksi penuh.

Jadi, di mana transaksi bersarang digunakan? Dalam kode PL/SQL. Lebih khusus lagi dalam prosedur otonom—yang dideklarasikan dengan PRAGMA AUTONOMOUS_TRANSACTION . Ini berarti bahwa ketika kode ini dipanggil (sebagai prosedur tersimpan bernama atau secara anonim) transaksi dilakukan atau dibatalkan secara independen dari transaksi yang memanggil kode ini.

Tujuan menggunakan transaksi bersarang adalah agar unit kerja mandiri berkomitmen atau dibatalkan terlepas dari apa yang akan terjadi pada kode panggilan. Ketika transaksi batin dapat dilakukan atau dibatalkan, itu akan digunakan untuk memeriksa ketersediaan (atau cadangan) sumber daya bersama—misalnya dalam menerapkan sistem reservasi kamar. Penggunaan utama untuk transaksi internal hanya-komit adalah pemantauan aktivitas, penelusuran kode, dan audit akses aman (yaitu, pengguna tidak diizinkan untuk membuat perubahan, tetapi berusaha melakukannya.)

Transaksi bersarang dalam kode SQL Server Transact-SQL benar-benar berbeda.

Transaksi Bersarang di SQL Server

Dalam Transact-SQL, apakah transaksi dalam dilakukan sepenuhnya tergantung pada transaksi terluar. Jika transaksi dalam telah dibatalkan, itu hanya dibatalkan. Tetapi jika transaksi dalam telah dilakukan, itu masih belum sepenuhnya dilakukan, karena dapat dibatalkan jika ada tingkat transaksi lingkup luarnya yang dibatalkan.

Jadi, apa gunanya transaksi dalam jika komitnya dapat dibatalkan dengan memutar kembali transaksi luarnya? Jawabannya sama seperti dalam kasus penggunaan untuk tabel sementara lokal: melepaskan kunci pada sumber daya. Perbedaannya adalah bahwa ini bukan pelepasan kunci global, tetapi kunci dalam lingkup transaksi luar langsung ("induk" langsung). Ini digunakan dalam kode Transact-SQL yang kompleks untuk melepaskan sumber daya dalam untuk transaksi luar. Ini adalah alat pengoptimalan kinerja dan manajemen sumber daya.

Karena Oracle dan transaksi dalam/bersarang Oracle dan SQL Server memiliki perilaku yang berbeda (bahkan mungkin berlawanan) dan kasus penggunaan yang sama sekali berbeda, migrasi dari satu platform ke platform lain tidak hanya memerlukan penulisan ulang, tetapi untuk sepenuhnya merancang ulang ruang lingkup apa pun yang berisi blok transaksi bersarang .

Faktor lain

Apakah pertimbangan temp-tabel dan berpusat pada transaksi ini satu-satunya hal yang perlu ditangani dalam migrasi Oracle ke SQL Server? Meskipun mereka mungkin yang paling penting, pasti ada yang lain, masing-masing dengan kebiasaan mereka sendiri yang layak untuk ditutupi. Di bawah ini adalah sisa dari apa yang saya temukan sebagai topik yang paling disalahpahami:

1. Kolom identitas di SQL Server
2. Urutan di Oracle
3. Sinonim di Oracle
4. Indeks yang difilter
5. Baca konsistensi (Oracle ke SQL Server saja)
6. Penggunaan alat migrasi

Bagian selanjutnya dari seri ini berlanjut dengan menjelajahi ini, terutama tiga yang pertama.

Tabel Temp, Variabel Tabel/Koleksi, dan Transaksi Bersarang: 3 Poin Nyeri Migrasi Teratas

Saya mulai dengan tabel sementara, variabel tabel/koleksi, dan transaksi bersarang karena ini adalah titik kegagalan yang paling umum dan jelas dalam proyek konversi. Setiap sistem non-sepele di Oracle Database atau Microsoft SQL Server pasti akan menggunakan beberapa dari mereka, dan menggunakan elemen ini sangat erat digabungkan dengan desain khusus dukungan transaksi oleh implementasi RDBMS masing-masing.

Baca di Bagian 2!