Migrasikan database Access ke SQL Server

Sebelum Anda memulai

Bagian berikut ini menyediakan latar belakang dan informasi lain untuk membantu Anda memulai.

Tentang database terpisah

Semua objek database Access dapat berada dalam satu file database, atau dapat disimpan dalam dua file database: database front-end dan database back-end. Ini disebut memisahkan database dan dirancang untuk memfasilitasi berbagi dalam lingkungan jaringan. File database back-end hanya boleh berisi tabel dan hubungan. File ujung depan hanya boleh berisi semua objek lainnya, termasuk formulir, laporan, kueri, makro, modul VBA, dan tabel tertaut ke database back-end. Saat Anda melakukan migrasi database Access, database tersebut mirip dengan database terpisah dalam SQL Server tersebut bertindak sebagai ujung belakang baru untuk data yang sekarang terletak di server.

Sebagai hasilnya, Anda masih bisa mempertahankan database Access ujung depan dengan tabel tertaut ke tabel SQL Server. Secara efektif, Anda dapat memperoleh manfaat pengembangan aplikasi cepat yang disediakan database Access, beserta skalabilitas SQL Server.

manfaat SQL Server

Masih perlu beberapa keyakinan untuk melakukan migrasi ke SQL Server? Berikut adalah beberapa manfaat tambahan untuk dipikirkan:

Lebih banyak pengguna serentak SQL Server dapat menangani lebih banyak pengguna serentak daripada Access dan meminimalkan persyaratan memori ketika lebih banyak pengguna ditambahkan.
Ketersediaan yang ditingkatkan Dengan SQL Server, Anda bisa mencadangkan secara dinamis, baik bertahap atau lengkap, database saat sedang digunakan. Karena itu, Anda tidak harus memaksa pengguna keluar dari database untuk mencadangkan data.
Kinerja tinggi dan skalabilitas Database SQL Server biasanya berkinerja lebih baik daripada database Access, terutama dengan database berukuran terabyte yang besar. Selain itu, SQL Server memproses kueri jauh lebih cepat dan efisien dengan memproses kueri secara paralel, menggunakan beberapa utas asli dalam satu proses untuk menangani permintaan pengguna.
Keamanan yang disempurnakan Menggunakan koneksi tepercaya, SQL Server terintegrasi dengan keamanan sistem Windows untuk menyediakan akses terpadu tunggal ke jaringan dan database, menggunakan yang terbaik dari kedua sistem keamanan. Ini membuatnya jauh lebih mudah untuk mengelola skema keamanan yang kompleks. SQL Server adalah penyimpanan ideal untuk informasi sensitif seperti nomor Jaminan Sosial, data kartu kredit, dan alamat yang bersifat rahasia.
Kemampuan pemulihan langsung Jika sistem operasi mengalami crash atau daya padam, SQL Server dapat secara otomatis memulihkan database ke status yang konsisten dalam hitungan menit dan tanpa intervensi administrator database.
Penggunaan VPN Access dan Virtual Private Networks (VPN) tidak akur. Namun dengan SQL Server, pengguna jarak jauh masih dapat menggunakan database ujung depan Access di desktop dan ujung belakang SQL Server yang terletak di belakang firewall VPN.
Server Azure SQL Selain manfaat SQL Server, menawarkan skalabilitas dinamis tanpa downtime, optimisasi cerdas, skalabilitas global dan ketersediaan, penghapusan biaya perangkat keras, dan pengurangan administrasi.

Pilih opsi Server Azure SQL terbaik

Jika Anda melakukan migrasi ke server Azure SQL, ada tiga opsi untuk dipilih, masing-masing dengan manfaat yang berbeda:

Database tunggal/kumpulan elastis Opsi ini memiliki kumpulan sumber dayanya sendiri yang dikelola melalui server SQL Database. Database tunggal seperti database yang dimuat dalam SQL Server. Anda juga bisa menambahkan kumpulan elastis, yang merupakan kumpulan database dengan sekumpulan sumber daya bersama yang dikelola melalui server SQL Database. Fitur SQL Server yang paling umum digunakan tersedia dengan cadangan, patch, dan pemulihan bawaan. Tetapi tidak ada jaminan waktu pemeliharaan dan migrasi yang tepat dari SQL Server mungkin sulit.
Instans terkelola Opsi ini adalah kumpulan database sistem dan pengguna dengan sekumpulan sumber daya bersama. Instans terkelola seperti contoh database SQL Server yang sangat kompatibilitas dengan SQL Server lokal. Instans terkelola memiliki cadangan bawaan, patching, pemulihan, dan mudah dimigrasikan dari SQL Server. Namun, ada sejumlah kecil fitur SQL Server yang tidak tersedia dan tidak ada jaminan waktu pemeliharaan yang tepat.
Azure Virtual Machine Opsi ini memungkinkan Anda menjalankan SQL Server di dalam mesin virtual di cloud Azure. Anda memiliki kontrol penuh atas mesin SQL Server dan jalur migrasi yang mudah. Namun, Anda perlu mengelola cadangan, patch, dan pemulihan.

Untuk informasi selengkapnya, lihat Memilih jalur migrasi database Anda ke Azure dan Apa itu Azure SQL?.

Langkah pertama

Ada beberapa masalah yang bisa Anda atasi di muka yang bisa membantu menyederhanakan proses migrasi sebelum Anda menjalankan SSMA:

Menambahkan indeks tabel dan kunci utama Pastikan setiap tabel Access memiliki indeks dan kunci utama. SQL Server mengharuskan semua tabel memiliki setidaknya satu indeks dan memerlukan tabel tertaut agar memiliki kunci utama jika tabel dapat diperbarui.
Memeriksa hubungan kunci primer/asing Pastikan hubungan ini didasarkan pada bidang dengan tipe dan ukuran data yang konsisten. SQL Server tidak mendukung kolom gabungan dengan tipe dan ukuran data yang berbeda dalam batasan kunci asing.
Menghapus kolom Lampiran SSMA tidak melakukan migrasi tabel yang berisi kolom Lampiran.

Sebelum Menjalankan SSMA, lakukan langkah-langkah pertama berikut.

Tutup database Access.
Pastikan bahwa pengguna saat ini yang tersambung ke database juga menutup database.
Jika database berada dalam format file .mdb, maka Hapus keamanan tingkat pengguna.
Cadangkan database Anda. Untuk informasi selengkapnya, lihat Memproteksi data Anda dengan proses pencadangan dan pemulihan.

Tips Pertimbangkan untuk menginstal edisi Microsoft SQL Server Express di desktop Anda yang mendukung hingga 10 GB dan merupakan cara gratis dan lebih mudah untuk menjalankan dan memeriksa migrasi Anda. Saat Anda tersambung, gunakan LocalDB sebagai instans database.

Tips Jika memungkinkan, gunakan versi Access mandiri. Jika Anda hanya dapat menggunakan Microsoft 365, gunakan mesin database Access 2010 untuk melakukan migrasi database Access saat menggunakan SSMA. Untuk informasi selengkapnya, lihat Microsoft Access Database Engine 2010 Redistributable.

Jalankan SSMA

Microsoft menyediakan Microsoft SQL Server Migration Assistant (SSMA) untuk mempermudah migrasi. SSMA terutama melakukan migrasi tabel dan memilih kueri tanpa parameter. Formulir, laporan, makro, dan modul VBA tidak dikonversi. Metadata Explorer SQL Server menampilkan objek database Access dan objek SQL Server yang memungkinkan Anda meninjau konten kedua database saat ini. Kedua koneksi ini disimpan dalam file migrasi Anda jika Anda memutuskan untuk mentransfer objek tambahan di masa mendatang.

Catatan Proses migrasi bisa memakan waktu tergantung pada ukuran objek database Anda dan jumlah data yang harus ditransfer.

Untuk melakukan migrasi database menggunakan SSMA, unduh dan instal perangkat lunak terlebih dahulu dengan mengklik ganda file MSI yang diunduh. Pastikan Anda menginstal versi 32 atau 64 bit yang sesuai untuk komputer Anda.
Setelah menginstal SSMA, buka SSMA di desktop Anda, sebaiknya dari komputer dengan file database Access.

Anda juga bisa membukanya di komputer yang memiliki akses ke database Access dari jaringan dalam folder bersama.
Ikuti instruksi awal di SSMA untuk menyediakan informasi dasar seperti lokasi SQL Server, database dan objek Access untuk melakukan migrasi, informasi koneksi, dan apakah Anda ingin membuat tabel tertaut.
Jika Anda melakukan migrasi ke SQL Server 2016 atau yang lebih baru dan ingin memperbarui tabel tertaut, tambahkan kolom rowversion dengan memilih Tinjau Alat > Pengaturan Proyek > Umum.

Bidang rowversion membantu menghindari konflik rekaman. Access menggunakan bidang rowversion ini dalam tabel tertaut SQL Server untuk menentukan kapan data terakhir diperbarui. Selain itu, jika Anda menambahkan bidang rowversion ke kueri, Access menggunakannya untuk memilih kembali baris setelah operasi pembaruan. Ini meningkatkan efisiensi dengan membantu menghindari kesalahan konflik penulisan dan skenario penghapusan rekaman yang bisa terjadi ketika Access mendeteksi hasil yang berbeda dari pengiriman asli, seperti mungkin terjadi dengan tipe data angka titik mengambang dan pemicu yang mengubah kolom. Namun, hindari penggunaan bidang rowversion dalam formulir, laporan, atau kode VBA. Untuk informasi selengkapnya, lihat rowversion.

Catatan Hindari rowversion yang membingungkan dengan stempel waktu. Meskipun stempel waktu kata kunci adalah sinonim untuk rowversion dalam SQL Server, Anda tidak dapat menggunakan rowversion sebagai cara untuk menandai waktu entri data.
Untuk mengatur tipe data yang tepat, pilih Tinjau Alat > Pengaturan Proyek > Ketik Pemetaan. Misalnya, jika Anda hanya menyimpan teks bahasa Inggris, Anda bisa menggunakan varchar daripada tipe data nvarchar .

Mengonversi objek

SSMA mengonversi objek Access menjadi objek SQL Server, tetapi tidak langsung menyalin objek. SSMA menyediakan daftar objek berikut ini untuk dimigrasikan sehingga Anda bisa memutuskan apakah Anda ingin memindahkannya ke database SQL Server:

Tabel dan Kolom
Pilih Kueri tanpa parameter.
Kunci utama dan asing
Indeks dan Nilai default
Periksa batasan (perbolehkan properti kolom panjang nol, aturan validasi kolom, validasi tabel)

Sebagai praktik terbaik, gunakan laporan penilaian SSMA, yang memperlihatkan hasil konversi, termasuk kesalahan, peringatan, pesan informasi, perkiraan waktu untuk melakukan migrasi, dan langkah-langkah koreksi kesalahan individual untuk dilakukan sebelum Anda benar-benar memindahkan objek.

Mengonversi objek database mengambil definisi objek dari metadata Access, mengonversinya menjadi sintaks Transact-SQL (T-SQL) yang setara, lalu memuat informasi ini ke dalam proyek. Anda kemudian dapat menampilkan objek Azure SQL Server atau SQL beserta propertinya menggunakan SQL Server atau SQL Azure Metadata Explorer.

Untuk mengonversi, memuat, dan melakukan migrasi objek ke SQL Server, Ikuti panduan ini.

Tips Setelah berhasil melakukan migrasi database Access, simpan file proyek untuk digunakan nanti, sehingga Anda dapat melakukan migrasi data lagi untuk pengujian atau migrasi akhir.

Menautkan tabel

Pertimbangkan untuk menginstal versi terbaru SQL Server driver OLE DB dan ODBC daripada menggunakan driver SQL Server asli yang dikirim dengan Windows. Driver yang lebih baru tidak hanya lebih cepat, tetapi juga mendukung fitur baru dalam Azure SQL yang tidak dilakukan driver sebelumnya. Anda bisa menginstal driver di setiap komputer tempat database yang dikonversi digunakan. Untuk informasi selengkapnya, lihat Microsoft OLE DB Driver 18 untuk SQL Server dan Microsoft ODBC Driver 17 untuk SQL Server.

Setelah melakukan migrasi tabel Access, Anda dapat menautkan ke tabel di SQL Server yang kini menghosting data Anda. Penautan langsung dari Access juga memberi Anda cara yang lebih sederhana untuk menampilkan data Anda daripada menggunakan alat manajemen SQL Server yang lebih kompleks. Anda bisa membuat kueri dan mengedit data yang ditautkan bergantung pada izin yang disiapkan oleh administrator database SQL Server Anda.

Catatan Jika Membuat DSN ODBC saat menautkan ke database SQL Server selama proses penautan, buat DSN yang sama di semua komputer yang menggunakan aplikasi baru atau gunakan string koneksi yang disimpan dalam file DSN secara terprogram.

Untuk informasi selengkapnya, lihat Menautkan ke atau mengimpor data dari Database Server Azure SQL dan Mengimpor atau menautkan ke data dalam database SQL Server.

Tips Jangan lupa untuk menggunakan Pengelola Tabel Tertaut di Access untuk merefresh dan menautkan ulang tabel dengan mudah. Untuk informasi selengkapnya, lihat Mengelola tabel tertaut.

Menguji dan merevisi

Bagian berikut ini menjelaskan masalah umum yang bisa Anda temui selama migrasi dan cara menanganinya.

Kueri

Hanya Pilih Kueri yang dikonversi; kueri lain tidak, termasuk Pilih Kueri yang mengambil parameter. Beberapa kueri mungkin tidak sepenuhnya dikonversi, dan SSMA melaporkan kesalahan kueri selama proses konversi. Anda dapat mengedit objek yang tidak dikonversi secara manual menggunakan sintaks T-SQL. Kesalahan sintaks mungkin juga memerlukan konversi fungsi dan tipe data khusus Access secara manual ke SQL Server. Untuk informasi selengkapnya, lihat Membandingkan Access SQL dengan SQL Server TSQL.

Tipe data

Access dan SQL Server memiliki tipe data yang serupa, tetapi ketahui potensi masalah berikut ini.

Angka Besar Tipe data Angka Besar menyimpan nilai numerik non moneter dan kompatibel dengan tipe data bigint SQL. Anda dapat menggunakan tipe data ini untuk menghitung angka yang besar secara efisien tetapi memerlukan format file database .accdb access 16 (16.0.7812 atau yang lebih baru) dan berkinerja lebih baik dengan Access versi 64-bit. Untuk informasi selengkapnya, lihat Menggunakan tipe data Angka Besar dan Pilih antara Office versi 64-bit atau 32-bit.

Ya/Tidak Secara default, kolom Access Ya/Tidak dikonversi menjadi bidang bit SQL Server. Untuk menghindari penguncian rekaman, Pastikan bidang bit diatur untuk tidak memperbolehkan nilai NULL. IN SSMA, You can select the bit column to set the Allow Nulls property to NO. Di TSQL, gunakan pernyataan CREATE TABLE atau ALTER TABLE .

Tanggal dan Waktu Ada beberapa pertimbangan tanggal dan waktu:

Jika tingkat kompatibilitas database adalah 130 (SQL Server 2016) atau yang lebih tinggi, dan tabel tertaut berisi satu atau beberapa kolom datetime atau datetime2, tabel mungkin mengembalikan #deleted pesan dalam hasil. Untuk informasi selengkapnya, lihat Tabel tertaut Access ke database SQL-Server mengembalikan #deleted.
Gunakan tipe data Tanggal/Waktu Access untuk memetakan ke tipe data datetime. Gunakan tipe data Tanggal/Waktu Akses diperpanjang untuk memetakan ke tipe data datetime2 yang memiliki rentang tanggal dan waktu yang lebih besar. Untuk informasi selengkapnya, lihat Menggunakan tipe data Tanggal/Waktu Diperpanjang.
Saat membuat kueri untuk tanggal dalam SQL Server, perhitungkan waktu serta tanggal. Misalnya:
- DateOrdered Between 1/1/19 and 31/1/19 may not include all orders.
- DateOrdered Between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM tidak menyertakan semua pesanan.

Lampiran Tipe data Lampiran menyimpan file dalam database Access. Dalam SQL Server, Anda memiliki beberapa opsi untuk dipertimbangkan. Anda bisa mengekstrak file dari database Access lalu mempertimbangkan menyimpan link ke file dalam database SQL Server Anda. Alternatifnya, Anda dapat menggunakan FILESTREAM, FileTable, atau Remote BLOB store (RBS) untuk menyimpan lampiran yang disimpan dalam database SQL Server.

Hyperlink Tabel Access memiliki kolom hyperlink yang tidak didukung SQL Server. Secara default, kolom ini akan dikonversi menjadi kolom nvarchar(max) di SQL Server, tapi Anda bisa mengkustomisasi pemetaan untuk memilih tipe data yang lebih kecil. Dalam solusi Access, Anda masih bisa menggunakan perilaku hyperlink dalam formulir dan laporan jika Anda mengatur properti Hyperlink agar kontrol benar.

Bidang multinilai Bidang multinilai Access dikonversi menjadi SQL Server sebagai bidang ntext yang berisi kumpulan nilai yang dipisahkan. Karena SQL Server tidak mendukung tipe data multinilai yang memperagakan hubungan banyak ke banyak, desain tambahan dan pekerjaan konversi mungkin diperlukan.

Untuk informasi selengkapnya tentang memetakan Access dan SQL Server tipe data, lihat Membandingkan tipe data.

Catatan Bidang multinilai tidak dikonversi.

Untuk informasi selengkapnya, lihat Tipe tanggal dan waktu, Tipe string dan biner, serta Tipe numerik.

Visual Basic

Meskipun VBA tidak didukung oleh SQL Server, perhatikan kemungkinan masalah berikut:

Fungsi VBA dalam Kueri Kueri Access mendukung fungsi VBA pada data dalam kolom kueri. Namun kueri Access yang menggunakan fungsi VBA tidak dapat dijalankan pada SQL Server, sehingga semua data yang diminta dikirimkan ke Microsoft Access untuk diproses. Biasanya, kueri ini harus dikonversi menjadi kueri kirim langsung.

Fungsi yang Ditentukan Pengguna dalam Kueri Kueri Microsoft Access mendukung penggunaan fungsi yang ditentukan dalam modul VBA untuk memproses data yang dikirimkan kepada mereka. Kueri bisa berupa kueri mandiri, pernyataan SQL di sumber rekaman formulir/laporan, sumber data kotak kombo dan kotak daftar pada formulir, laporan dan bidang tabel, dan ekspresi aturan default atau validasi. SQL Server tidak dapat menjalankan fungsi yang ditentukan pengguna ini. Anda mungkin perlu mendesain ulang fungsi ini secara manual dan mengonversinya menjadi prosedur yang disimpan di SQL Server.

Optimalkan kinerja

Sejauh ini, cara terpenting untuk mengoptimalkan kinerja dengan SQL Server ujung belakang yang baru adalah memutuskan kapan harus menggunakan kueri lokal atau jarak jauh. Saat melakukan migrasi data ke SQL Server, Anda juga berpindah dari server file ke model database klien-server komputasi. Ikuti panduan umum ini:

Jalankan kueri baca-saja kecil di klien untuk akses tercepat.
Jalankan kueri baca/tulis yang panjang di server untuk memanfaatkan daya pemrosesan yang lebih besar.
Minimalkan lalu lintas jaringan dengan filter dan agregasi untuk mentransfer data yang Anda perlukan saja.

Mengoptimalkan kinerja dalam model database server klien

Untuk informasi selengkapnya, lihat Membuat kueri kirim langsung.

Berikut ini adalah panduan tambahan yang disarankan.

Meletakkan logika di server Aplikasi Anda juga dapat menggunakan tampilan, fungsi yang ditentukan pengguna, prosedur yang disimpan, bidang terhitung, dan pemicu untuk memfokuskan dan berbagi logika aplikasi, aturan dan kebijakan bisnis, kueri kompleks, validasi data, dan kode integritas referensial di server, bukan pada klien. Tanyakan pada diri Anda sendiri, bisakah kueri atau tugas ini dilakukan di server dengan lebih baik dan lebih cepat? Terakhir, uji setiap kueri untuk memastikan kinerja optimal.

Menggunakan tampilan dalam formulir dan laporan Di Access, lakukan hal berikut:

Untuk formulir, gunakan tampilan SQL untuk formulir baca-saja dan tampilan terindeks SQL untuk formulir baca/tulis sebagai sumber rekaman.
Untuk laporan, gunakan tampilan SQL sebagai sumber rekaman. Namun, buat tampilan terpisah untuk setiap laporan, sehingga Anda bisa memperbarui laporan tertentu dengan lebih mudah, tanpa memengaruhi laporan lain.

Meminimalkan pemuatan data dalam formulir atau laporan Jangan tampilkan data hingga pengguna memintanya. Misalnya, biarkan properti recordsource tetap kosong, buat pengguna memilih filter pada formulir Anda, lalu mengisi properti recordsource dengan filter Anda. Atau, gunakan klausul DoCmd.OpenForm dan DoCmd.OpenReport untuk menampilkan data yang tepat yang diperlukan oleh pengguna. Pertimbangkan untuk menonaktifkan navigasi rekaman.

Hati-hati dengan kueri heterogen Hindari menjalankan kueri yang menggabungkan tabel Access lokal dan tabel SQL Server tertaut, terkadang disebut kueri hibrid. Tipe kueri ini masih memerlukan Access untuk mengunduh semua data SQL Server ke komputer lokal lalu menjalankan kueri, kueri tidak menjalankan kueri di SQL Server.

Kapan menggunakan tabel lokal Pertimbangkan untuk menggunakan tabel lokal untuk data yang jarang berubah, seperti daftar negara bagian atau provinsi di negara atau kawasan. Tabel statis sering digunakan untuk pemfilteran dan dapat berkinerja lebih baik di ujung depan Access.

Untuk informasi selengkapnya, lihat Konsultan Penyetelan Mesin Database, Menggunakan Penganalisis Kinerja untuk mengoptimalkan database Access, dan Mengoptimalkan Aplikasi Microsoft Office Access yang Ditautkan ke SQL Server.