Learning Microsoft SQL Server and its backup mechanism

Catatan belajar Microsoft SQL Server

Dokumentasi ini adalah catatan belajar Microsoft SQL Server. Toko online di sini hanya kasus kecil agar kita punya data yang saling berhubungan untuk dicoba: produk, customer, order, dan detail order. Fokus utamanya bukan membangun aplikasi toko online, melainkan memahami cara kerja SQL Server dan bagaimana database dilindungi dari kehilangan data.

Urutannya bergerak dari dasar ke operasional: menyalakan SQL Server secara lokal, membaca data dengan T-SQL, memahami file database dan transaction log, memilih recovery model, membuat backup, lalu menguji restore.

1. Menyiapkan lab SQL Server

Docker Compose dipakai agar SQL Server mudah dibuat dan dihapus ulang selama belajar. Volume hanya menjaga data database tetap ada di luar lifecycle container; volume tersebut bukan pengganti backup.

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "Password123!"
    ports:
      - "1433:1433"
    volumes:
      - mssql_data:/var/opt/mssql

volumes:
  mssql_data:

Password pada contoh ini hanya untuk latihan lokal. Deployment nyata harus mengambil credential dari secret manager dan tidak menaruh password di file Compose yang masuk repository.

2. Kasus toko online sebagai bahan latihan T-SQL

Model riset menggunakan lima tabel inti. Categories mengelompokkan produk, Customers menyimpan pembeli, Orders menjadi transaksi, dan OrderItems menjadi detail produk pada setiap order. ERD ini bukan tujuan akhir dokumentasi; ia hanya menyediakan data latihan untuk mempraktikkan SELECT, JOIN, agregasi, subquery, dan LEFT JOIN di SQL Server.

Merender diagram...

Di OrderItems, UnitPrice adalah harga produk saat order dibuat, bukan harga produk saat ini. Dengan begitu, histori transaksi tidak berubah ketika harga katalog berubah.

3. Latihan query dengan T-SQL

Latihan bergerak dari filter sederhana ke agregasi dan subquery. Beberapa contoh yang paling mewakili:

Filter dan sorting

SELECT *
FROM Products
WHERE Price < 200000
ORDER BY Price ASC;

SELECT *
FROM Customers
WHERE City IN ('Jakarta', 'Bandung');

Menghubungkan entitas

SELECT p.ProductName, c.CategoryName
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID;

SELECT o.OrderID, o.OrderDate, c.FullName
FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID;

Detail sebuah order membutuhkan dua join. Subtotal dihitung dari kuantitas dikali harga historis pada item order.

SELECT
    p.ProductName AS nama,
    oi.Quantity AS quantity,
    oi.Quantity * oi.UnitPrice AS sub_total
FROM Orders o
JOIN OrderItems oi ON oi.OrderID = o.OrderID
JOIN Products p ON p.ProductID = oi.ProductID
WHERE o.OrderID = 1;

Agregasi untuk pertanyaan bisnis

SELECT c.CategoryName, COUNT(p.ProductID) AS jumlah
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryName;

SELECT o.Status, SUM(oi.Quantity * oi.UnitPrice) AS revenue
FROM Orders o
JOIN OrderItems oi ON oi.OrderID = o.OrderID
GROUP BY o.Status;

WHERE menyaring baris sebelum pengelompokan, sedangkan HAVING menyaring hasil agregasi. Contohnya, customer yang sudah membelanjakan lebih dari lima juta:

SELECT c.FullName, SUM(oi.Quantity * oi.UnitPrice) AS total_belanja
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON oi.OrderID = o.OrderID
WHERE o.Status IN ('SHIPPED', 'PAID')
GROUP BY c.CustomerID, c.FullName
HAVING SUM(oi.UnitPrice * oi.Quantity) > 5000000;

Subquery dan left join

Rata-rata harga dapat dipakai sebagai pembanding terhadap setiap produk:

SELECT *
FROM Products p
WHERE p.Price > (SELECT AVG(p2.Price) FROM Products p2);

Untuk menemukan customer yang belum pernah melakukan order, LEFT JOIN menjaga customer tetap ada di hasil meskipun sisi order kosong.

SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

Latihan lain dari dokumentasi mencakup produk dengan stok paling sedikit, kategori dengan lebih dari dua produk, produk terlaris, rata-rata nilai order, dan kota dengan revenue terbesar. Kasus bisnisnya boleh diganti, tetapi kemampuan T-SQL yang dilatih tetap sama: pahami grain data terlebih dahulu, baru tentukan JOIN, GROUP BY, dan metrik yang tepat.

4. Apa yang sebenarnya dibackup?

SQL Server menyimpan database dalam beberapa komponen utama:

• Data file (.mdf dan .ndf) menyimpan tabel, index, dan object database.
• Transaction log (.ldf) mencatat perubahan transaksi secara berurutan sebelum perubahan tersebut dianggap aman.
• Backup media (.bak atau .trn) adalah salinan untuk kebutuhan pemulihan, idealnya berada di storage yang berbeda dari database utama.

Ketika sebuah transaksi mengubah data, SQL Server menulis log terlebih dahulu. Mekanisme ini disebut write-ahead logging. Karena itu, transaction log bukan sekadar log aplikasi; ia adalah bagian penting dari konsistensi dan point-in-time recovery.

Backup database membaca data dan metadata yang diperlukan untuk membentuk salinan konsisten. Backup log membaca record log yang belum masuk backup log sebelumnya. Restore kemudian menggunakan salinan tersebut untuk membangun kembali data file dan mengulang perubahan log secara berurutan.

5. Recovery model menentukan mekanisme backup

Query tidak dapat memulihkan data yang sudah hilang. Backup perlu menjawab dua target operasional:

• RPO (Recovery Point Objective): berapa banyak data yang boleh hilang, misalnya maksimal 15 menit.
• RTO (Recovery Time Objective): berapa cepat service harus kembali online, misalnya maksimal satu jam.

Recovery model menentukan bentuk backup yang tersedia:

Model	Transaction log	Backup yang didukung	Point-in-time restore
SIMPLE	Dipotong otomatis	Full, differential	Tidak
FULL	Disimpan sampai dibackup	Full, differential, log	Ya
BULK_LOGGED	Minimal saat bulk operation	Full, differential, log terbatas	Terbatas

Model yang sedang digunakan dapat diperiksa dengan:

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'riset';

6. Jenis backup dan backup chain

Jenis backup memiliki peran berbeda:

• Full backup menyalin seluruh database sebagai titik dasar pemulihan.
• Differential backup menyimpan perubahan sejak full backup terakhir. Differential baru tidak bergantung pada differential sebelumnya.
• Transaction log backup menyimpan record log sejak backup log sebelumnya. Jenis ini membutuhkan recovery model FULL atau BULK_LOGGED.
• Copy-only backup membuat salinan tambahan tanpa mengubah posisi backup chain utama.

Backup tidak berdiri sendiri. Full backup menjadi titik dasar, differential menunjuk ke full tersebut, dan log backup menyambung perubahan transaksi secara berurutan.

Merender diagram...

Urutan restore harus dihormati. Differential terbaru tetap berbasis full backup terakhir, bukan differential sebelumnya. Jika satu log backup di tengah rantai hilang, pemulihan point-in-time setelah titik tersebut tidak dapat dilanjutkan.

Secara sederhana, proses restore point-in-time adalah:

1. Restore full backup dengan NORECOVERY.
2. Restore differential terbaru dengan NORECOVERY.
3. Restore setiap log backup secara berurutan.
4. Restore log terakhir dengan STOPAT jika perlu kembali ke waktu sebelum kesalahan.
5. Jalankan WITH RECOVERY pada restore terakhir agar database online.

7. Membuat backup di SQL Server

-- Full backup: titik dasar rantai
BACKUP DATABASE riset
TO DISK = '/var/opt/mssql/backup/riset_FULL.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;

-- Differential: perubahan sejak full terakhir
BACKUP DATABASE riset
TO DISK = '/var/opt/mssql/backup/riset_DIFF_20260519.bak'
WITH DIFFERENTIAL, COMPRESSION, INIT;

-- Transaction log: perubahan sejak log backup sebelumnya
BACKUP LOG riset
TO DISK = '/var/opt/mssql/backup/riset_LOG_1.trn'
WITH COMPRESSION, INIT;

-- Copy-only: backup ad hoc tanpa mengganggu rantai backup utama
BACKUP DATABASE riset
TO DISK = '/var/opt/mssql/backup/riset_ADHOC.bak'
WITH COPY_ONLY, COMPRESSION, INIT;

COPY_ONLY berguna sebelum deployment atau migrasi ketika kita ingin snapshot tambahan tanpa mengubah posisi differential atau log chain.

8. Memantau dan menguji restore

Backup yang selesai tanpa error belum tentu pernah diuji restore. Metadata backup di msdb membantu memeriksa histori dan lokasi file fisik.

SELECT TOP 10
    database_name,
    CASE type
        WHEN 'D' THEN 'FULL'
        WHEN 'I' THEN 'DIFFERENTIAL'
        WHEN 'L' THEN 'LOG'
    END AS backup_type,
    backup_start_date,
    backup_finish_date,
    backup_size / 1024.0 / 1024.0 AS size_mb
FROM msdb.dbo.backupset
WHERE database_name = 'riset'
ORDER BY backup_start_date DESC;

Melihat file backup saja tidak cukup. Backup harus dipantau dari metadata SQL Server dan diuji dengan restore ke database atau environment terpisah.

Checklist minimal pembelajaran ini:

1. Tentukan RPO dan RTO sebelum memilih jadwal backup.
2. Pastikan recovery model sesuai kebutuhan point-in-time restore.
3. Simpan file backup di storage yang berbeda dari file database utama.
4. Monitor keberhasilan, ukuran, timestamp, dan physical path backup.
5. Lakukan restore drill secara berkala, bukan hanya mengecek bahwa file .bak ada.

Penutup

Kasus toko online hanya membantu membuat latihan terasa konkret. Inti R&D ini adalah memahami SQL Server sebagai database engine: bagaimana data disimpan, bagaimana transaksi dicatat di log, bagaimana recovery model memengaruhi backup, dan bagaimana backup chain dipakai saat restore.

Database yang siap dipakai bukan hanya database yang bisa menerima INSERT. Ia juga memiliki backup yang konsisten, chain yang dapat dilacak, dan prosedur restore yang pernah benar-benar dijalankan.