Bagikan melalui

FILESTREAM (SQL Server)

  • Artikel

Berlaku untuk: SQL Server - Hanya Windows

FILESTREAM memungkinkan aplikasi berbasis SQL Server untuk menyimpan data yang tidak terstruktur, seperti dokumen dan gambar, pada sistem file. Aplikasi dapat menggunakan API streaming yang kaya dan performa sistem file dan pada saat yang sama mempertahankan konsistensi transaksional antara data yang tidak terstruktur dan data terstruktur yang sesuai.

FILESTREAM mengintegrasikan Mesin Database SQL Server dengan sistem file NTFS atau ReFS dengan menyimpan data objek besar biner (BLOB) varbinary(maks) sebagai file pada sistem file. Pernyataan T-SQL dapat menyisipkan, memperbarui, mengkueri, mencari, dan mencadangkan data ALIRAN FILE. Antarmuka sistem file Win32 menyediakan akses streaming ke data.

FILESTREAM menggunakan cache sistem NT untuk penembolokan data file. Penembolokan file dalam cache sistem membantu mengurangi dampak apa pun yang mungkin dimiliki data FILESTREAM pada performa Mesin Database. Kumpulan buffer SQL Server tidak digunakan; oleh karena itu, memori ini tersedia untuk pemrosesan kueri.

FILESTREAM tidak diaktifkan secara otomatis saat Anda menginstal atau meningkatkan SQL Server. Anda harus mengaktifkan FILESTREAM dengan menggunakan Pengelola Konfigurasi SQL Server dan SQL Server Management Studio. Untuk menggunakan FILESTREAM, Anda harus membuat atau mengubah database agar berisi tipe grup file khusus. Kemudian, buat atau ubah tabel sehingga berisi kolom varbinary(maks) dengan atribut FILESTREAM. Setelah menyelesaikan tugas-tugas ini, Anda dapat menggunakan Transact-SQL dan Win32 untuk mengelola data FILESTREAM.

Kapan Menggunakan FILESTREAM

Di SQL Server, BLOB dapat menjadi data varbinary(maks) standar yang menyimpan data dalam tabel, atau objek varbinary FILESTREAM (maks) yang menyimpan data dalam sistem file. Ukuran dan penggunaan data menentukan apakah Anda harus menggunakan penyimpanan database atau penyimpanan sistem file. Jika kondisi berikut ini benar, Anda harus mempertimbangkan untuk menggunakan FILESTREAM:

  • Objek yang disimpan rata-rata lebih besar dari 1 MB.
  • Akses baca cepat penting.
  • Anda mengembangkan aplikasi yang menggunakan tingkat menengah untuk logika aplikasi.

Untuk objek yang lebih kecil, menyimpan BLOB varbinary(maks) dalam database sering memberikan performa streaming yang lebih baik.

Penyimpanan FILESTREAM

Penyimpanan FILESTREAM diimplementasikan sebagai kolom varbinary(max) tempat data disimpan sebagai BLOB dalam sistem file. Ukuran BLOB hanya dibatasi oleh ukuran volume sistem file. Batasan varbinary(max) standar dari ukuran file 2 GB tidak berlaku untuk BLOB yang disimpan dalam sistem file.

Untuk menentukan bahwa kolom harus menyimpan data pada sistem file, tentukan atribut FILESTREAM pada kolom varbinary(max). Atribut ini menyebabkan Mesin Database menyimpan semua data untuk kolom tersebut pada sistem file, tetapi tidak dalam file database.

Data FILESTREAM harus disimpan dalam grup file FILESTREAM. Grup file FILESTREAM adalah grup file khusus yang berisi direktori sistem file alih-alih file itu sendiri. Direktori sistem file ini disebut kontainer data. Kontainer data adalah antarmuka antara penyimpanan Mesin Database dan penyimpanan sistem file.

Saat Anda menggunakan penyimpanan FILESTREAM, pertimbangkan hal berikut:

  • Saat tabel berisi kolom FILESTREAM, setiap baris harus memiliki ID baris unik non-null.
  • Beberapa kontainer data dapat ditambahkan ke grup file FILESTREAM.
  • Kontainer data FILESTREAM tidak dapat ditumpuk.
  • Saat Anda menggunakan pengklusteran failover, grup file FILESTREAM harus berada di sumber daya disk bersama.
  • Grup file FILESTREAM dapat berada pada volume terkompresi.

Manajemen Terintegrasi

Karena FILESTREAM diimplementasikan sebagai kolom varbinary(max) dan diintegrasikan langsung ke dalam Mesin Database, sebagian besar alat dan fungsi manajemen SQL Server berfungsi tanpa modifikasi untuk data FILESTREAM. Misalnya, Anda dapat menggunakan semua model pencadangan dan pemulihan dengan data FILESTREAM, dan data FILESTREAM dicadangkan dengan data terstruktur dalam database. Jika Anda tidak ingin mencadangkan data FILESTREAM dengan data relasional, Anda dapat menggunakan cadangan parsial untuk mengecualikan grup file FILESTREAM.

Keamanan Terintegrasi

Di SQL Server, data FILESTREAM diamankan sama seperti data lain yang diamankan: dengan memberikan izin di tingkat tabel atau kolom. Jika pengguna memiliki izin ke kolom FILESTREAM dalam tabel, pengguna dapat membuka file terkait.

Catatan

Enkripsi tidak didukung pada data FILESTREAM.

Hanya akun tempat akun layanan SQL Server berjalan diberikan izin ke kontainer FILESTREAM. Kami menyarankan agar tidak ada akun lain yang diberikan izin pada kontainer data.

Catatan

Login SQL tidak akan berfungsi dengan kontainer FILESTREAM. Hanya autentikasi NTFS atau ReFS yang akan berfungsi dengan kontainer FILESTREAM.

Mengakses Data BLOB dengan Akses Streaming Sistem File dan Transact-SQL

Setelah menyimpan data di kolom FILESTREAM, Anda dapat mengakses file dengan menggunakan transaksi Transact-SQL atau dengan menggunakan API Win32.

Akses Transact-SQL

Dengan menggunakan Transact-SQL, Anda dapat menyisipkan, memperbarui, dan menghapus data FILESTREAM:

  • Anda dapat menggunakan operasi sisipkan untuk mengisi bidang FILESTREAM dengan nilai null, nilai kosong, atau data sebaris yang relatif pendek. Namun, sejumlah besar data lebih efisien dialirkan ke dalam file yang menggunakan antarmuka Win32.
  • Saat memperbarui bidang FILESTREAM, Anda memodifikasi data BLOB yang mendasar dalam sistem file. Ketika bidang FILESTREAM diatur ke NULL, data BLOB yang terkait dengan bidang dihapus. Anda tidak dapat menggunakan pembaruan terpotong Transact-SQL, yang diimplementasikan sebagai UPDATE**.**Write(), untuk melakukan pembaruan parsial pada data.
  • Saat Anda menghapus baris atau menghapus atau memotong tabel yang berisi data FILESTREAM, Anda menghapus data BLOB yang mendasar dalam sistem file.

Akses Streaming Sistem File

Dukungan streaming Win32 berfungsi dalam konteks transaksi SQL Server. Dalam transaksi, Anda dapat menggunakan fungsi FILESTREAM untuk mendapatkan jalur sistem file UNC logis dari file. Anda kemudian menggunakan OPENSqlFilestream API untuk mendapatkan handel file. Handel ini kemudian dapat digunakan oleh antarmuka streaming file Win32, seperti ReadFile() dan WriteFile(), untuk mengakses dan memperbarui file melalui sistem file.

Karena operasi file bersifat transaksi, Anda tidak dapat menghapus atau mengganti nama file FILESTREAM melalui sistem file.

Peringatan

Kontainer FILESTREAM adalah folder yang dikelola oleh SQL Server. Jangan menambahkan atau menghapus file di folder FILESTREAM secara manual atau melalui aplikasi lain. Jika Anda melakukannya, ini akan mengakibatkan kesalahan pencadangan dan inkonsistensi. Untuk informasi selengkapnya, lihat MSSQLSERVER_3056, MSSQLSERVER_7908, dan MSSQLSERVER_7906.

Model pernyataan

Akses sistem file FILESTREAM memodelkan pernyataan Transact-SQL dengan menggunakan file terbuka dan ditutup. Pernyataan dimulai ketika handel file dibuka dan berakhir saat handel ditutup. Misalnya, ketika handel tulis ditutup, setiap pemicu AFTER yang mungkin terdaftar di tabel, menembak seolah-olah pernyataan UPDATE selesai.

Namespace penyimpanan

Di FILESTREAM, Mesin Database mengontrol namespace sistem file fisik BLOB. Fungsi intrinsik baru, PathName, menyediakan jalur UNC logis blob yang sesuai dengan setiap sel FILESTREAM dalam tabel. Aplikasi ini menggunakan jalur logis ini untuk mendapatkan handel Win32 dan beroperasi pada data BLOB dengan menggunakan antarmuka sistem file Win32 reguler. Fungsi mengembalikan NULL jika nilai kolom FILESTREAM adalah NULL.

Akses sistem file yang ditransaksikan

Fungsi intrinsik baru, GET_FILESTREAM_TRANSACTION_CONTEXT(), menyediakan token yang mewakili transaksi saat ini yang terkait dengan sesi. Transaksi harus dimulai dan belum dibatalkan atau dilakukan. Dengan mendapatkan token, aplikasi mengikat operasi streaming sistem file FILESTREAM dengan transaksi yang dimulai. Fungsi mengembalikan NULL jika tidak ada transaksi yang dimulai secara eksplisit.

Semua handel file harus ditutup sebelum transaksi dilakukan atau dibatalkan. Jika handel dibiarkan terbuka di luar cakupan transaksi, bacaan tambahan terhadap handel menyebabkan kegagalan; penulisan tambahan terhadap handel berhasil, tetapi data aktual tidak ditulis ke disk. Demikian pula, jika database atau instans Mesin Database dimatikan, semua handel terbuka tidak valid.

Durabilitas transaksi

Dengan FILESTREAM, setelah penerapan transaksi, Mesin Database memastikan durabilitas transaksi untuk data BLOB FILESTREAM yang dimodifikasi dari akses streaming sistem file.

Semantik isolasi

Semantik isolasi diatur oleh tingkat isolasi transaksi Mesin Database. Tingkat isolasi yang diterapkan baca didukung untuk Transact-SQL dan akses sistem file. Operasi baca yang dapat diulang, dapat diserialisasikan, dan tingkat isolasi rekam jepret didukung. Bacaan kotor tidak didukung.

Operasi buka akses sistem file tidak menunggu kunci apa pun. Sebagai gantinya, operasi terbuka segera gagal jika mereka tidak dapat mengakses data karena isolasi transaksi. Panggilan API streaming gagal dengan ERROR_SHARING_VIOLATION jika operasi terbuka tidak dapat dilanjutkan karena pelanggaran isolasi.

Untuk memungkinkan pembaruan parsial dibuat, aplikasi dapat mengeluarkan kontrol FS perangkat (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) untuk mengambil konten lama ke dalam file yang dibuka menangani referensi. Ini memicu salinan konten lama sisi server. Untuk performa aplikasi yang lebih baik dan untuk menghindari potensi waktu habis ketika Anda bekerja dengan file yang sangat besar, kami sarankan Anda menggunakan I/O asinkron.

Jika FSCTL dikeluarkan setelah handel ditulis, operasi tulis terakhir akan tetap ada, dan penulisan sebelumnya yang dibuat ke handel akan hilang.

API sistem file dan tingkat isolasi yang didukung

Ketika API sistem file tidak dapat membuka file karena pelanggaran isolasi, pengecualian ERROR_SHARING_VIOLATION dikembalikan. Pelanggaran isolasi ini terjadi ketika dua transaksi mencoba mengakses file yang sama. Hasil operasi akses tergantung pada mode file dibuka dan versi SQL Server tempat transaksi berjalan. Tabel berikut menguraikan kemungkinan hasil untuk dua transaksi yang mengakses file yang sama.

Transaksi 1 Transaksi 2 Hasil pada SQL Server 2008 (10.0.x) Hasil pada SQL Server 2008 R2 (10.50.x) dan versi yang lebih baru
Buka untuk dibaca. Buka untuk dibaca. Keduanya berhasil. Keduanya berhasil.
Buka untuk dibaca. Buka untuk menulis. Keduanya berhasil. Operasi tulis di bawah transaksi 2 tidak memengaruhi operasi baca yang dilakukan dalam transaksi 1. Keduanya berhasil. Operasi tulis di bawah transaksi 2 tidak memengaruhi operasi baca yang dilakukan dalam transaksi 1.
Buka untuk menulis. Buka untuk dibaca. Buka untuk transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION. Keduanya berhasil.
Buka untuk menulis. Buka untuk menulis. Buka untuk transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION. Buka untuk transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION.
Buka untuk dibaca. Buka untuk SELECT. Keduanya berhasil. Keduanya berhasil.
Buka untuk dibaca. Buka untuk UPDATE atau DELETE. Keduanya berhasil. Operasi tulis di bawah transaksi 2 tidak memengaruhi operasi baca yang dilakukan dalam transaksi 1. Keduanya berhasil. Operasi tulis di bawah transaksi 2 tidak memengaruhi operasi baca yang dilakukan dalam transaksi 1.
Buka untuk menulis. buka untuk SELECT. Transaksi 2 memblokir hingga transaksi 1 melakukan atau mengakhiri transaksi, atau waktu penguncian transaksi habis. Keduanya berhasil.
Buka untuk menulis. Buka untuk UPDATE atau DELETE. Transaksi 2 memblokir hingga transaksi 1 melakukan atau mengakhiri transaksi, atau waktu penguncian transaksi habis. Transaksi 2 memblokir hingga transaksi 1 melakukan atau mengakhiri transaksi, atau waktu penguncian transaksi habis.
Buka untuk SELECT. Buka untuk dibaca. Keduanya berhasil. Keduanya berhasil.
Buka untuk SELECT. Buka untuk menulis. Keduanya berhasil. Operasi tulis di bawah transaksi 2 tidak memengaruhi transaksi 1. Keduanya berhasil. Operasi tulis di bawah transaksi 2 tidak memengaruhi transaksi 1.
Buka untuk UPDATE atau DELETE. Buka untuk dibaca. Operasi terbuka pada transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION. Keduanya berhasil.
Buka untuk UPDATE atau DELETE. Buka untuk menulis. Operasi terbuka pada transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION. Operasi terbuka pada transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION.
Buka untuk SELECT dengan bacaan berulang. Buka untuk dibaca. Keduanya berhasil. Keduanya berhasil.
Buka untuk SELECT dengan bacaan berulang. Buka untuk menulis. Operasi terbuka pada transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION. Operasi terbuka pada transaksi 2 gagal dengan pengecualian ERROR_SHARING_VIOLATION.

Write-through dari klien jarak jauh

Akses sistem file jarak jauh ke data FILESTREAM diaktifkan melalui protokol Blok Pesan Server (SMB). Jika klien jarak jauh, tidak ada operasi tulis yang di-cache oleh sisi klien. Operasi tulis akan selalu dikirim ke server. Data dapat di-cache di sisi server. Sebaiknya aplikasi yang berjalan pada klien jarak jauh mengonsolidasikan operasi tulis kecil ke dalam operasi ukuran yang lebih besar. Tujuannya adalah untuk melakukan lebih sedikit penulisan.

Membuat tampilan yang dipetakan memori (I/O yang dipetakan memori) dengan menggunakan handel FILESTREAM tidak didukung. Jika pemetaan memori digunakan untuk data FILESTREAM, Mesin Database tidak dapat menjamin konsistensi dan durabilitas data atau integritas database.

Rekomendasi dan panduan untuk meningkatkan performa FILESTREAM

Fitur SQL Server FILESTREAM memungkinkan Anda menyimpan data objek besar biner varbinary(maks) sebagai file dalam sistem file. Ketika Anda memiliki sejumlah besar baris dalam kontainer FILESTREAM, yang merupakan penyimpanan yang mendasar untuk kolom FILESTREAM dan FileTables, Anda dapat berakhir dengan volume sistem file yang berisi sejumlah besar file. Untuk mencapai performa terbaik saat memproses data terintegrasi dari database dan sistem file, penting untuk memastikan sistem file disetel secara optimal. Berikut ini adalah beberapa opsi penyetelan yang tersedia dari perspektif sistem file:

  • Pemeriksaan ketinggian untuk driver filter FILESTREAM SQL Server (misalnya, rsfx0100.sys). Evaluasi semua driver filter yang dimuat untuk tumpukan penyimpanan yang terkait dengan volume di mana fitur FILESTREAM menyimpan file dan memastikan bahwa driver rsfx terletak di bagian bawah tumpukan. Anda dapat menggunakan program kontrol FLTMC.EXE untuk menghitung driver filter untuk volume tertentu. Berikut adalah contoh output dari utilitas FLTMC: C:\Windows\System32>fltMC.exe filter

    Nama Filter Instans Num Ketinggian Menengah
    Sftredir 1 406000 0
    MpFilter 9 328000 0
    luafv 1 135000 0
    FileInfo 9 45000 0
    RsFx0103 1 41001.03 0

  • Periksa apakah server memiliki properti "waktu akses terakhir" yang dinonaktifkan untuk file. Atribut sistem file ini dipertahankan dalam registri: Nama Kunci: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Nama: NtfsDisableLastAccessUpdate
    Jenis: REG_DWORD
    Nilai: 1

  • Periksa apakah server menonaktifkan penamaan 8.3. Atribut sistem file ini dipertahankan dalam registri: Nama Kunci: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Nama: NtfsDisable8dot3NameCreation
    Jenis: REG_DWORD
    Nilai: 1

  • Periksa apakah kontainer direktori FILESTREAM tidak mengaktifkan enkripsi sistem file atau pemadatan sistem file, karena ini dapat memperkenalkan tingkat overhead saat mengakses file-file ini.

  • Dari prompt perintah yang ditingkatkan, jalankan instans fltmc dan pastikan tidak ada driver filter yang terpasang pada volume tempat Anda mencoba memulihkan.

  • Periksa apakah kontainer direktori FILESTREAM tidak memiliki lebih dari 300.000 file. Anda dapat menggunakan informasi dari sys.database_files tampilan katalog untuk mengetahui direktori mana dalam file penyimpanan FILESTREAM-related sistem file. Ini dapat dicegah dengan memiliki beberapa kontainer. (Lihat item poin berikutnya untuk informasi selengkapnya.)

  • Hanya dengan satu grup file FILESTREAM, semua file data dibuat di bawah folder yang sama. Pembuatan file dengan jumlah file yang sangat besar dapat dipengaruhi oleh indeks NTFS besar, yang juga dapat menjadi terfragmentasi.

    • Memiliki beberapa grup file umumnya harus membantu dengan ini (aplikasi menggunakan pemartisian atau memiliki beberapa tabel, masing-masing masuk ke grup filenya sendiri).

    • Dengan SQL Server 2012 (11.x) dan versi yang lebih baru, Anda dapat memiliki beberapa kontainer atau file di bawah grup file FILESTREAM, dan skema alokasi round-robin akan berlaku. Oleh karena itu jumlah file NTFS per direktori menjadi lebih kecil.

  • Pencadangan dan pemulihan dapat menjadi lebih cepat dengan beberapa kontainer FILESTREAM, jika beberapa volume menyimpan kontainer digunakan.

    SQL Server 2012 (11.x) mendukung beberapa kontainer per grup file dan dapat mempermudah prosesnya. Tidak ada skema partisi yang rumit yang mungkin diperlukan untuk mengelola sejumlah besar file.

  • Ketika ada sejumlah besar kontainer FILESTREAM dalam instans SQL, memulai database dengan banyak kontainer FILESTREAM mungkin membutuhkan waktu lama untuk mendaftarkannya di driver filter FILESTREAM. Menyebarkannya dalam beberapa volume yang berbeda membantu meningkatkan waktu mulai database.

  • MFT NTFS dapat menjadi terfragmentasi, dan itu dapat menyebabkan masalah performa. Ukuran cadangan MFT memang tergantung pada ukuran volume, sehingga Anda mungkin atau mungkin tidak mengalami hal ini.

    • Anda dapat memeriksa fragmentasi MFT dengan defrag /A /V C: (ubah C: ke nama volume aktual).

    • Anda dapat memesan lebih banyak ruang MFT dengan menggunakan perilaku fsutil yang ditetapkan mftzone 2.

    • File data FILESTREAM harus dikecualikan dari pemindaian perangkat lunak antivirus.

      Catatan

      Windows Server 2016 secara otomatis mengaktifkan Windows Defender. Pastikan Pertahanan Windows dikonfigurasi untuk mengecualikan file Filestream. Kegagalan untuk melakukan ini dapat mengakibatkan penurunan performa untuk operasi pencadangan dan pemulihan.

      Untuk informasi selengkapnya, lihat Mengonfigurasi dan memvalidasi pengecualian untuk pemindaian Antivirus Pertahanan Windows.

Konten terkait