File database dan grup file

Berlaku untuk: SQL Server Azure SQL Managed Instance

Minimal, setiap database SQL Server memiliki dua file sistem operasi: file data dan file log. File data berisi data dan objek seperti tabel, indeks, prosedur tersimpan, dan tampilan. File log berisi informasi yang diperlukan untuk memulihkan semua transaksi dalam database. File data dapat dikelompokkan bersama dalam grup file untuk tujuan alokasi dan administrasi.

File database

Database SQL Server memiliki tiga jenis file, seperti yang diperlihatkan dalam tabel berikut ini.

File	Deskripsi
Primer	Berisi informasi startup untuk database dan menunjuk ke file lain dalam database. Setiap database memiliki satu file data utama. Ekstensi nama file yang direkomendasikan untuk file data utama adalah .mdf.
Sekunder	File data opsional yang ditentukan pengguna. Data dapat tersebar di beberapa disk dengan menempatkan setiap file pada drive disk yang berbeda. Ekstensi nama file yang direkomendasikan untuk file data sekunder adalah .ndf.
Log Transaksi	Log menyimpan informasi yang digunakan untuk memulihkan database. Setidaknya harus ada satu file log untuk setiap database. Ekstensi nama file yang direkomendasikan untuk log transaksi adalah .ldf.

Misalnya, database sederhana bernama Sales memiliki satu file utama yang berisi semua data dan objek dan file log yang berisi informasi log transaksi. Database yang lebih kompleks bernama Orders dapat dibuat yang menyertakan satu file utama dan lima file sekunder. Data dan objek dalam database tersebar di enam file, dan empat file log berisi informasi log transaksi.

Secara default, data dan log transaksi dimasukkan ke drive dan jalur yang sama untuk menangani sistem disk tunggal. Pilihan ini mungkin tidak optimal untuk lingkungan produksi. Kami menyarankan agar Anda menempatkan file data dan log pada disk terpisah.

Nama file logis dan fisik

File SQL Server memiliki dua jenis nama file:

• logical_file_name:logical_file_name adalah nama yang digunakan untuk merujuk ke file fisik di semua pernyataan Transact-SQL. Nama file logis harus mematuhi aturan untuk pengidentifikasi SQL Server dan harus unik di antara nama file logis dalam database.

• os_file_name:os_file_name adalah nama file fisik termasuk jalur direktori. Ini harus mengikuti aturan untuk nama file sistem operasi.

Untuk informasi selengkapnya tentang NAME argumen dan FILENAME , lihat MENGUBAH File DATABASE dan Opsi Grup File (Transact-SQL).

Tip

Data SQL Server dan file log dapat dimasukkan ke sistem file FAT atau NTFS. Pada sistem Windows, Microsoft merekomendasikan penggunaan sistem file NTFS karena aspek keamanan NTFS.

Peringatan

Grup file data baca/tulis dan file log tidak didukung pada sistem file terkompresi NTFS. Hanya database baca-saja dan grup file sekunder baca-saja yang diizinkan untuk dimasukkan ke sistem file terkompresi NTFS. Untuk penghematan ruang, sangat disarankan untuk menggunakan kompresi data alih-alih pemadatan sistem file.

Ketika beberapa instans SQL Server berjalan pada satu komputer, setiap instans menerima direktori default yang berbeda untuk menyimpan file untuk database yang dibuat dalam instans. Untuk informasi selengkapnya, lihat Lokasi File untuk Instans Default dan Bernama SQL Server.

Halaman file data

Halaman dalam file data SQL Server diberi nomor secara berurutan, dimulai dengan nol (0) untuk halaman pertama dalam file. Setiap file dalam database memiliki nomor ID file yang unik. Untuk mengidentifikasi halaman secara unik dalam database, ID file dan nomor halaman diperlukan. Contoh berikut menunjukkan nomor halaman dalam database yang memiliki file data utama 4-MB dan file data sekunder 1-MB.

Halaman header file adalah halaman pertama yang berisi informasi tentang atribut file. Beberapa halaman lain di awal file juga berisi informasi sistem, seperti peta alokasi. Salah satu halaman sistem yang disimpan dalam file data utama dan file log pertama adalah halaman boot database yang berisi informasi tentang atribut database.

Ukuran File

File SQL Server dapat tumbuh secara otomatis dari ukuran yang semula ditentukan. Saat menentukan file, Anda dapat menentukan kenaikan pertumbuhan tertentu. Setiap kali file diisi, file akan meningkatkan ukurannya dengan kenaikan pertumbuhan. Jika ada beberapa file dalam grup file, file tidak akan ditumbuhi secara otomatis hingga semua file penuh.

Untuk informasi selengkapnya tentang halaman dan jenis halaman, lihat Panduan Arsitektur Halaman dan Tingkat.

Setiap file juga dapat memiliki ukuran maksimum yang ditentukan. Jika ukuran maksimum tidak ditentukan, file dapat terus bertambah hingga menggunakan semua ruang yang tersedia pada disk. Fitur ini sangat berguna ketika SQL Server digunakan sebagai database yang disematkan dalam aplikasi di mana pengguna tidak memiliki akses yang nyaman ke administrator sistem. Pengguna dapat membiarkan file bertumbuh secara otomatis sesuai kebutuhan untuk mengurangi beban administratif pemantauan ruang kosong dalam database dan mengalokasikan ruang tambahan secara manual.

Untuk informasi selengkapnya tentang manajemen file log transaksi, lihat Mengelola ukuran file log transaksi.

File rekam jepret database

Bentuk file yang digunakan oleh rekam jepret database untuk menyimpan data copy-on-write tergantung pada apakah rekam jepret dibuat oleh pengguna atau digunakan secara internal:

• Rekam jepret database yang dibuat oleh pengguna menyimpan datanya dalam satu atau beberapa file jarang. Teknologi file jarang adalah fitur dari sistem file NTFS. Pada awalnya, file jarang tidak berisi data pengguna, dan ruang disk untuk data pengguna belum dialokasikan ke file jarang. Untuk informasi umum tentang penggunaan file jarang dalam rekam jepret database dan bagaimana rekam jepret database tumbuh, lihat Menampilkan Ukuran File Jarang rekam jepret Database.
• Rekam jepret database digunakan secara internal oleh perintah DBCC tertentu. Perintah ini mencakup DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, dan DBCC CHECKFILEGROUP. Rekam jepret database internal menggunakan aliran data alternatif jarang dari file database asli. Seperti file jarang, aliran data alternatif adalah fitur dari sistem file NTFS. Penggunaan aliran data alternatif yang jarang memungkinkan beberapa alokasi data dikaitkan dengan satu file atau folder tanpa memengaruhi ukuran file atau statistik volume.

Grup File

• Grup file utama berisi file data utama dan file sekunder apa pun yang tidak dimasukkan ke dalam grup file lainnya.
• Grup file yang ditentukan pengguna dapat dibuat untuk mengelompokkan file data bersama-sama untuk tujuan administratif, alokasi data, dan penempatan.

Misalnya: Data1.ndf, , Data2.ndfdan Data3.ndf, dapat dibuat pada tiga drive disk, masing-masing, dan ditetapkan ke grup fgroup1file . Tabel kemudian dapat dibuat secara khusus pada grup fgroup1file . Kueri untuk data dari tabel akan tersebar di tiga disk; itu akan meningkatkan performa. Peningkatan performa yang sama dapat dicapai dengan menggunakan satu file yang dibuat pada kumpulan garis RAID (array redundan disk independen). Namun, file dan grup file memungkinkan Anda dengan mudah menambahkan file baru ke disk baru.

Semua file data disimpan dalam grup file yang tercantum dalam tabel berikut.

Grup file	Deskripsi
Primer	Grup file yang berisi file utama. Semua tabel sistem adalah bagian dari grup file utama.
Data yang Dioptimalkan Memori	Grup file yang dioptimalkan memori didasarkan pada grup file aliran file
Aliran file
Ditentukan pengguna	Grup file apa pun yang dibuat oleh pengguna saat pengguna pertama kali membuat atau kemudian memodifikasi database.

Grup file default (utama)

Ketika objek dibuat dalam database tanpa menentukan grup file tempat objek tersebut berada, objek tersebut ditetapkan ke grup file default. Kapan saja, tepat satu grup file ditetapkan sebagai grup file default. File dalam grup file default harus cukup besar untuk menyimpan objek baru apa pun yang tidak dialokasikan ke grup file lain.

Grup file PRIMARY adalah grup file default kecuali diubah dengan menggunakan pernyataan ALTER DATABASE. Alokasi untuk objek sistem dan tabel tetap berada dalam grup file PRIMER, bukan grup file default baru.

Grup file data yang dioptimalkan memori

Untuk informasi selengkapnya tentang grup file yang dioptimalkan memori, lihat Grup File memori yang Dioptimalkan.

Grup file FILESTREAM

Untuk informasi selengkapnya tentang grup file FILESTREAM, lihat FILESTREAM dan Membuat Database yang Diaktifkan FILESTREAM.

Contoh file dan grup file

Contoh berikut membuat database pada instans SQL Server. Database memiliki file data utama, grup file yang ditentukan pengguna, dan file log. File data utama berada di grup file utama dan grup file yang ditentukan pengguna memiliki dua file data sekunder. Pernyataan ALTER DATABASE menjadikan grup file yang ditentukan pengguna sebagai default. Tabel kemudian dibuat menentukan grup file yang ditentukan pengguna. (Contoh ini menggunakan jalur c:\Program Files\Microsoft SQL Server\MSSQL.1 generik untuk menghindari menentukan versi SQL Server.)

USE master;
GO
-- Create the database with the default data
-- filegroup, filestream filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
  ( NAME='MyDB_Primary',
    FILENAME=
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
  ( NAME = 'MyDB_FG_FS',
    FILENAME = 'c:\Data\filestream1')
LOG ON
  ( NAME='MyDB_log',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON MyDB_FG1;
GO

-- Create a table in the filestream filegroup
CREATE TABLE MyFSTable
(
  cola int PRIMARY KEY,
  colb VARBINARY(MAX) FILESTREAM NULL
)
GO

Ilustrasi berikut ini meringkas hasil contoh sebelumnya (kecuali untuk data Filestream).

Strategi isi file dan grup file

Grup file menggunakan strategi pengisian proporsional di semua file dalam setiap grup file. Karena data ditulis ke grup file, Mesin Database SQL Server menulis jumlah yang sebanding dengan ruang kosong dalam file ke setiap file dalam grup file, alih-alih menulis semua data ke file pertama hingga penuh. Kemudian menulis ke file berikutnya. Misalnya, jika file f1 memiliki 100 MB gratis dan file f2 memiliki 200 MB gratis, satu tingkat diberikan dari file f1, dua tingkat dari file f2, dan sebagainya. Dengan cara ini, kedua file menjadi penuh pada waktu yang hampir bersamaan, dan striping sederhana tercapai.

Misalnya, grup file terdiri dari tiga file, semuanya diatur untuk tumbuh secara otomatis. Ketika ruang di semua file dalam grup file habis, hanya file pertama yang diperluas. Ketika file pertama penuh dan tidak ada lagi data yang dapat ditulis ke grup file, file kedua diperluas. Ketika file kedua penuh dan tidak ada lagi data yang dapat ditulis ke grup file, file ketiga diperluas. Jika file ketiga menjadi penuh dan tidak ada lagi data yang dapat ditulis ke grup file, file pertama diperluas lagi, dan sebagainya.

Aturan untuk merancang file dan grup file

Aturan berikut berkaitan dengan file dan grup file:

• File atau grup file tidak dapat digunakan oleh lebih dari satu database. Misalnya, file sales.mdf dan sales.ndf, yang berisi data dan objek dari database penjualan, tidak dapat digunakan oleh database lain.
• File hanya dapat menjadi anggota dari satu grup file.
• File log transaksi tidak pernah menjadi bagian dari grup file apa pun.

Rekomendasi

Rekomendasi saat bekerja dengan file dan grup file:

• Sebagian besar database akan bekerja dengan baik dengan satu file data dan satu file log transaksi.
• Jika Anda menggunakan beberapa file data, buat grup file kedua untuk file tambahan dan jadikan grup file tersebut sebagai grup file default. Dengan cara ini, file utama hanya akan berisi tabel dan objek sistem.
• Untuk memaksimalkan performa, buat file atau grup file pada disk yang tersedia yang berbeda mungkin. Letakkan objek yang sangat bersaing untuk ruang dalam grup file yang berbeda.
• Gunakan grup file untuk mengaktifkan penempatan objek pada disk fisik tertentu.
• Letakkan tabel berbeda yang digunakan dalam kueri gabungan yang sama dalam grup file yang berbeda. Langkah ini akan meningkatkan performa, karena I/O disk paralel mencari data yang bergabung.
• Letakkan tabel yang sangat diakses dan indeks nonclustered yang termasuk dalam tabel tersebut pada grup file yang berbeda. Menggunakan grup file yang berbeda akan meningkatkan performa, karena I/O paralel jika file terletak di disk fisik yang berbeda.
• Jangan letakkan file log transaksi pada disk fisik yang sama yang memiliki file dan grup file lainnya.
• Jika Anda perlu memperluas volume atau partisi tempat file database berada menggunakan alat seperti Diskpart, Anda harus mencadangkan semua database sistem dan pengguna dan menghentikan layanan SQL Server terlebih dahulu. Selain itu, setelah volume disk berhasil diperluas, Anda harus mempertimbangkan menjalankan DBCC CHECKDB perintah untuk memastikan integritas fisik semua database yang berada di volume.

Untuk informasi selengkapnya tentang rekomendasi manajemen file log transaksi, lihat Mengelola ukuran file log transaksi.

Konten terkait

• BUAT DATABASE (SQL Server Transact-SQL)
• ALTER DATABASE File and Filegroup Options (Transact-SQL)
• Pencopotan dan Lampirkan Database (SQL Server)
• Arsitektur dan Panduan Manajemen Log Transaksi SQL Server
• Panduan Arsitektur Halaman dan Luas
• Mengelola ukuran file log transaksi