Blog AWS Indonesia

Backup database dari SQL Server ke Amazon S3

Dalam postingan blog ini, saya akan menjelaskan fungsionalitas baru di SQL Server 2022 yang memungkinkan Anda melakukan backup langsung ke Amazon Simple Storage Service (Amazon S3). Saya juga akan meninjau best practice untuk keamanan, biaya dan performance didalamnya.

Gambaran umum solusi

Fitur Backup to URL dirilis Bersama dengan SQL Server 2014 (dan SQL Server 2012 SP1 CU2), tetapi hingga rilis SQL Server 2022, fitur ini hanya berfungsi dengan Microsoft Azure Blob Storage. Dengan SQL Server 2022, sekarang fitur Backup to URL mendukung penyimpanan objek yang kompatibel dengan S3. Kompatibilitas S3 berarti solusi penyimpanan menggunakan Amazon S3 API untuk menyediakan interface yang sesuai dengan S3.

Implementasi fitur ini untuk SQL Server yang dideploy pada Amazon Elastic Cloud Compute (Amazon EC2), memerlukan penanganan persyaratan keamanan dan pemahaman parameter perintah BACKUP serta hubungannya dengan kemampuan dari Amazon S3. Di bagian berikut, saya juga akan melihat kasus penggunaan lain seputar disaster recovery, offsite backup dan biaya terkait, dan juga kemampuan performance saat menggunakan fitur Backup to URL.

Catatan, fitur Backup to URL ke penyimpanan objek yang S3-compatible hanya didukung pada SQL Server 2022, versi sebelum ini tidak mendukung.

Panduan

Panduan ini akan membantu Anda melalui semua langkah yang diperlukan untuk menerapkan dan menguji fitur Backup to URL SQL Server 2022 yang di-deploy pada Amazon EC2. Anda akan mempelajari cara menyiapkan instance SQL Server untuk menguji fitur, provision Amazon S3 bucket yang diperlukan, membuat IAM policy yang menyediakan akses ke bucket ini, dan membuat IAM user untuk akses ke AWS secara programmatic. Terakhir, saya akan menjelaskan bagaimana Anda dapat menerapkan backup SQL Server ke Amazon S3 bucket dan restore, termasuk parameter yang terkait dengan fitur Backup to URL.

Menjalankan SQL Server Instance

Jalankan Amazon EC2 instance dengan memilih Amazon Machine Image (AMI) yang telah dikonfigurasi sebelumnya dengan Windows Server 2019 dan SQL Server 2022 Developer Edition yang diinstal di region yang sama dengan Amazon S3 bucket Anda. Untuk contoh ini, saya menggunakan us-east-1. Anda dapat merujuk ke Membuat instance menggunakan launch instance wizard untuk menemukan panduan langkah demi langkah menjalankan SQL Server Instance.

Membuat dua Amazon S3 bucket

Panduan ini akan memerlukan dua Amazon S3 bucket, bucket sql-backup-2022 di region us-east-1 dan bucket lain, sql-backup-2022-ohio, di region us-east-2 dengan nama yang sama dengan yang asli, tetapi dengan akhiran “-ohio“, seperti yang ditunjukkan pada Gambar 1. Untuk instruksi terperinci tentang membuat Amazon S3 bucket, silakan lihat halaman Membuat sebuah bucket.

Amazon S3 bucket sql backup

Gambar 1. Amazon S3 bucket

Mengonfigurasi IAM policy

Untuk membuat policy AWS Identity and Access Management (IAM) yang akan mendefinisikan permission minimum secara spesifik yang diperlukan untuk berinteraksi dengan Amazon S3 bucket, silakan gunakan file JSON yang disajikan pada Gambar 2. File JSON ini memberikan permission s3:ListBucket untuk memungkinkan melihat daftar konten di dalam bucket dan s3:PutObject dan s3:GetObject untuk memungkinkan file backup ditulis dan dibaca dari bucket. Klausa Resource menentukan bucket tempat permission ini akan berlaku. Harap dicatat bahwa akhiran “*/*” yang digunakan dalam mendefinisikan sumber daya bucket menunjukkan bahwa itu mencakup setiap bucket dengan nama yang dimulai dengan sql-backups-2022 – nama yang Anda gunakan untuk bucket yang dibuat pada langkah sebelumnya. Jika Anda memilih nama yang berbeda untuk bucket Anda, perbarui file JSON yang sesuai dengan keinginan anda.

Untuk membuat policy IAM sql-backups-2022-policy, ikuti instruksi dalam dokumen Membuat IAM Policy dengan file JSON yang ditunjukkan pada Gambar 2.

<code class="lang-json">{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::sql-backups-2022*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::sql-backups-2022*/*"
            ]
        }
    ]
} </code>

Gambar 2. JSON bucket policy

Membuat IAM user

Sekarang Anda perlu membuat IAM user dan melampirkan policy IAM yang Anda buat di langkah sebelumnya. Gunakan instruksi yang didokumentasikan dalam Membuat IAM user untuk membuat pengguna bernama sql-backups-user dengan programmatic access. Assign sql-backups-2022-policy yang dibuat pada langkah sebelumnya kepada pengguna ini.

Saat membuat pengguna, perhatikan Access Key ID dan Secret Access key, yang akan digunakan untuk membuat kredensial SQL Server di langkah berikutnya.

Membuat kredensial SQL Server

Untuk memberi SQL Server permission yang diperlukan untuk membaca/menulis file langsung ke Amazon S3, Anda akan menggunakan Access Key ID dan Secret Access key dari langkah sebelumnya untuk membuat kredensial SQL Server. Informasi selengkapnya tentang kredensial SQL Server dapat ditemukan di Credentials.

  1. Hubungkan ke instance Amazon EC2 yang Anda buat sebelumnya, seperti yang didokumentasikan dalam Koneksi ke Windows instance.
  2. Jalankan SQL Server Management Studio (SSMS) dan sambungkan ke instance SQL Server 2022 Anda dengan login dengan privilege sysadmin di SQL Server.
  3. Buka query baru dan jalankan perintah T-SQL berikut untuk membuat kredensial:
CREATE CREDENTIAL [s3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups]
WITH IDENTITY = 'S3 Access Key'
      , SECRET   = 'AAAAAAAAAAAAAAAAAAAA:bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
CREATE CREDENTIAL [s3://sql-backups-2022-ohio.s3.us-east-2.amazonaws.com/backups]
WITH IDENTITY = 'S3 Access Key'
      , SECRET   = 'AAAAAAAAAAAAAAAAAAAA:bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';

Jika Anda membuat Amazon S3 bucket dengan nama yang berbeda dari yang digunakan dalam panduan ini, ganti nama sql-backups-2022 dalam perintah T-SQL di atas dengan nama yang Anda pilih.

Parameter SECRET terdiri dari Access Key ID dan Secret Access key Anda yang dipisahkan oleh titik dua. Harap dicatat bahwa jika salah satu nilai Access Key ID atau Secret Access key berisi titik dua, kunci akses perlu dihapus dan dibuat ulang, memastikan tidak ada karakter titik dua yang ada.

Backup database ke Amazon S3

Argumen yang digunakan dalam perintah T-SQL BACKUP DATABASE adalah:

  • TO URL = ‘xxx’

Perintah ini mengkonfigurasi path lengkap ke bucket dan nama file backup.

  • MIRROR TO URL = ‘xxx’

Fitur ini  hanya untuk enterprise yang memungkinkan kumpulan file backup kedua dibuat dalam bucket kedua.

  • WITH FORMAT

Perintah ini memungkinkan file yang ada di bucket Anda ditimpa. Tanpa itu, perintah akan gagal jika file sudah ada. AWS merekomendasikan penggunaan versioning di Amazon S3 untuk melindungi file agar tidak terhapus secara tidak sengaja. Menghapus argumen dan memberikan logika penanganan kesalahan untuk kegagalan juga bisa menjadi pendekatan yang lain.

  • COMPRESSION

Amazon S3 tidak memberikan kompresi apa pun, jadi mengaktifkan SQL Server Compression selama pembuatan file backup dan mengirim file sekecil mungkin ke Amazon S3 bucket bisa membuat penghematan biaya penyimpanan.

  • MAXTRANSFERSIZE = 20971520 (20mb)

Argumen MAXTRANSFERSIZE mendefinisikan ukuran file backup terbesar dalam byte. Ini terdiri dari 10.000 bagian satu file dapat dipecah. Setiap bagian ditentukan dalam ukuran dengan maksimal ukuran transfer. Membagi backup ke beberapa file hingga 64 URL terpisah memberikan ukuran total 12,2TB. Rumus untuk menghitung ukuran backup maksimum yang muncul adalah:

{ MaxTransferSize } * { # of Parts } * { # of URLs }

Menggunakan MAXTRANSFERSIZE = 20971520 dan membagi backup hingga 64 URL memberikan hasil berikut untuk ukuran backup maksimum:

{ 20971520 } * { 10000 } * { 64 } = 13.421.772.800.000 B =~ 12,21 TB

Perintah backup terakhir adalah di mana db1 adalah database dan /backups/db1.bak mendefinisikan struktur folder bernama “backups” untuk menampung file backup. Amazon S3 adalah struktur datar tetapi mendukung konsep folder untuk membantu dalam pengelompokan file secara logis. Detail selengkapnya tersedia dalam Organizing objects in the Amazon S3 console using folders.

Jika Anda membuat Amazon S3 bucket dengan nama yang berbeda dengan contoh yang disebutkan, maka ganti nama sql-backups-2022 pada perintah T-SQL di bawah ini dengan milik Anda sendiri.

BACKUP DATABASE db1
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Menjalankan perintah ini di SSMS akan menghasilkan output yang menunjukkan detail backup yang telah selesai.

Processed 536 pages for database 'db1', file 'db1' on file 1.
Processed 2 pages for database 'db1', file 'db1_log' on file 1.
BACKUP DATABASE successfully processed 538 pages in 0.903 seconds (4.650 MB/sec).
Completion time: 2022-09-08T13:55:48.1756888-05:00

Untuk meningkatkan performa, pecah backup ke beberapa file dengan merujuk beberapa URL. Ada 5 URL dalam contoh berikut, tetapi maksimal 64 URL yang dapat disediakan:

Jika Anda membuat Amazon S3 bucket dengan nama yang berbeda dengan contoh yang disebutkan, maka ganti nama sql-backups-2022 pada perintah T-SQL di bawah ini dengan milik Anda sendiri.

BACKUP DATABASE db1
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part1.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part2.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part3.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part4.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part5.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Anda juga dapat mengirim backup ke Amazon S3 bucket kedua dengan memanfaatkan argumen MIRROR TO URL:

BACKUP DATABASE db1
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part1.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part2.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part3.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part4.bak',
   URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-part5.bak'
MIRROR TO URL = 's3://sql-backups-2022-ohio.s3.us-east-2.amazonaws.com/backups/db1-part1.bak',
   URL = 's3://sql-backups-2022-ohio.s3.us-east-2.amazonaws.com/backups/db1-part2.bak',
   URL = 's3://sql-backups-2022-ohio.s3.us-east-2.amazonaws.com/backups/db1-part3.bak',
   URL = 's3://sql-backups-2022-ohio.s3.us-east-2.amazonaws.com/backups/db1-part4.bak',
   URL = 's3://sql-backups-2022-ohio.s3.us-east-2.amazonaws.com/backups/db1-part5.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Area yang perlu diperhatikan dalam argumen MIRROR TO URL:

  • Jumlah URL yang digunakan dalam argumen kedua harus cocok dengan jumlah URL yang digunakan dalam argumen pertama. Batas 64 URL bersifat kumulatif, artinya, dalam contoh di atas, Anda akan dibatasi hingga 32 URL di setiap argumen. Ini membagi dua ukuran backup maksimum yang dapat diambil.
  • Maximum tiga klausa MIRROR TO URL yang dapat digunakan sehingga mengurangi jumlah maksimum URL menjadi 16 dan ukuran backup total ~3TB. Pertimbangkan untuk menggunakan Amazon S3 object replication selain argumen MIRROR TO URL jika 12 TB penuh diperlukan.
  • Jika file ada di satu path URL tetapi tidak di path mirror URL, backup akan gagal terlepas dari argumen WITH FORMAT.
  • Ini adalah fitur enterprise.

Ada juga perubahan perilaku yang perlu diperhatikan di sini antara mencadangkan ke Microsoft Azure Blob Storage dan Amazon S3. Saat URL Anda menunjuk ke Azure Blob Storage, Anda dapat mencadangkan ke DISK dan URL dalam perintah yang sama, sedangkan saat mengarahkan URL Anda ke Amazon S3, Anda hanya dapat mencadangkan ke Amazon S3 di target utama dan mirror. Mencampur target backup akan menyebabkan kesalahan berikut:

Msg 3298, Level 16, State 5, Line 183
Backup/Restore to URL device error: Backup to S3 remote storage cannot be mixed with other backup device types. Ensure all Primary and Mirror Urls use S3 path.

Pilihan full, diferensial, dan transactional log tersedia dan dapat digunakan dengan argumen COPY_ONLY untuk memungkinkan backup di luar backup terjadwal:

BACKUP DATABASE db1
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.bak'
WITH COPY_ONLY, FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;   

BACKUP DATABASE db1
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1-diff.bak'
WITH COPY_ONLY, DIFFERENTIAL ,FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;    

BACKUP LOG [db1] 
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.log'
WITH COPY_ONLY, FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Restore database dari Amazon S3

Anda bisa merestore melalui URL baik untuk mengganti database terkini atau untuk restore sebagai database baru menggunakan pemformatan standar:

Jika Anda membuat Amazon S3 bucket dengan nama yang berbeda dengan contoh yang disebutkan, maka ganti nama sql-backups-2022 pada perintah T-SQL di bawah ini dengan milik Anda sendiri.

RESTORE DATABASE db1
FROM URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.bak'
WITH REPLACE;

RESTORE DATABASE [db1-copy] 
FROM URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.bak'
WITH FILE = 1,  
MOVE N'db1' TO N'C:\SQL2022\Data\db1-copy.mdf',  
MOVE N'db1_log' TO N'C:\SQL2022\Log\db1-copy_log.ldf

Pertimbangan biaya

Ada dua komponen biaya utama dari solusi ini: biaya storage dan biaya transfer data.

Amazon S3 Storage Tier

Tier Standard bukanlah merupakan pilihan yang paling cost effective untuk sebagian besar file backup SQL Server, karena pelanggan biasanya hanya akan mengaksesnya dalam upaya restore data. Ada beberapa opsi untuk tingkat penyimpanan alternatif berdasarkan periode retensi dan akses yang diharapkan. Detail setiap tingkatan didokumentasikan dalam Amazon S3 storage class, tetapi untuk rekomendasi umum di mana pola retensi dan akses dicampur atau tidak diketahui, AWS menyarankan agar Anda menggunakan kelas penyimpanan Amazon S3 Intelligent-Tiering.

Secara default, file yang ditulis ke bucket akan menggunakan tier Standar. Untuk mengonversi file backup dari tingkat Standar ke Intelligent tiering, Anda harus membuat aturan lifecycle, seperti yang didokumentasikan dalam Transisi objek menggunakan Amazon S3 lifecycle.

  1. Berikan nama aturan lifecycle seperti “Convert to Intelligent Tiering”
  2. Untuk cakupan aturan pilih Apply to all objects in the bucket dan check I acknowledge that this rule will apply to all objects in the bucket
  3. Tindakan aturan lifecycle adalah Move current versions of objects between storage classes dan Move noncurrent versions of objects between storage classes
  4. Transisi versi saat ini harus diatur ke Intelligent-Tiering dan zero hari setelah pembuatan objek
  5. Versi transisi noncurrent harus diatur ke Intelligent-Tiering dan zero hari setelah objek menjadi noncurrent

Gambar 3 menunjukkan cara mengatur opsi tiering.

Gambar 3. Konfigurasi aturan lifecycle

Biaya transfer data

Menulis data (ingress) ke Amazon S3 bucket Anda baik dari on-premise instance SQL Server atau instance SQL Server Amazon EC2 Anda di region yang sama dengan bucket Anda selalu gratis.

Membaca data (egress) dari Amazon S3 bucket Anda dikenakan biaya, seperti listing objek yang disimpan dalam bucket atau download file dari Amazon S3 ke drive lokal. Baca selengkapnya tentang harga di Amazon S3 pricing.

Ada juga biaya yang perlu dipertimbangkan saat menggunakan AWS Command Line Interface (AWS CLI) untuk listing objek dalam bucket Anda. Pendekatan yang lebih hemat biaya adalah menggunakan riwayat backup database Anda dalam database msdb untuk menentukan konten Amazon S3 dan perintah restore, yang ditunjukkan dalam contoh skrip TSQL berikut:

SELECT database_name, backup_finish_date, 'RESTORE DATABASE [' + database_name + '] FROM URL = ' + physical_device_name + ' WITH REPLACE;' AS tsql
FROM [msdb].[dbo].[backupmediafamily] bmf
INNER JOIN [msdb].[dbo].[backupset] bs ON bmf.media_set_id = bs.media_set_id
ORDER BY database_name, backup_finish_date DESC

Saat mempertimbangkan strategi replikasi untuk disaster recovery atau skenario lain, replikasi konten bucket ke region kedua menggunakan Cross-Region Repication menimbulkan biaya data keluar lintas region yang berbeda berdasarkan region sumber dan target. Menggunakan argumen MIRROR TO URL akan menghindari biaya keluar ini, tetapi konsekuensinya adalah ukuran maksimum backup lebih kecil, waktu backup yang berpotensi lebih lama, dan penggunaan koneksi internet Anda yang lebih tinggi.

Performa

Saat menguji jumlah maksimum URL (64) dalam perintah backup dengan MAXTRANSFERSIZE 20mb untuk satu backup database 144 GB di 64 file, throughput yang ditunjukkan pada Gambar 4 memuncak di sekitar ukuran EC2 16x pada sekitar 2403 MB/dtk dengan kapasitas jaringan EC2 sebesar 37,5 Gbps. Menambahkan kapasitas jaringan tambahan melalui instance Amazon EC2 yang lebih besar tidak meningkatkan performa karena faktor storage dari database sumber akan menjadi faktor pembatas pada akhirnya.

Membuat operasi backup database secara paralel memiliki kemampuan untuk memberikan kinerja serupa untuk setiap thread ke Amazon S3 bucket, tetapi Anda perlu menguji beban kerja Anda karena batas penyimpanan dan komputasi juga dapat menjadi hambatan. Jumlah file yang benar perlu ditentukan untuk setiap beban kerja tetapi harus cukup besar untuk mengakomodasi ukuran keseluruhan backup database yang sedang dibuat.

Gambar 4. Backup SQL Server ke Amazon S3

Membersihkan Resources

Setelah Anda selesai menggunakan resources di postingan blog ini, bersihkan sumber daya AWS untuk menghindari timbulnya biaya yang tidak diinginkan. Secara khusus, hapus elemen yang dibuat sebagai bagian dari panduan:

Kesimpulan

Postingan blog ini menyoroti kemampuan baru SQL Server 2022 dengan Amazon S3 dan pertimbangan keamanan, kinerja, dan biaya.

Dengan memanfaatkan policy IAM yang memberikan permission minimum yang diperlukan dan Amazon S3 Intelligent-Tiering untuk memberikan penggunaan otomatis tingkat penyimpanan Amazon S3 yang hemat biaya, Anda dapat memastikan strategi backup yang aman dan hemat biaya dengan fitur Backup to URL SQL Server 2022.

AWS dapat membantu Anda menilai bagaimana perusahaan Anda dapat memperoleh hasil maksimal dari cloud. Bergabunglah dengan jutaan pelanggan AWS yang mempercayai kami untuk memigrasikan dan memodernisasi aplikasi terpenting mereka di cloud. Untuk mempelajari selengkapnya tentang memodernisasi Windows Server atau SQL Server, kunjungi Windows di AWS. Hubungi kami untuk memulai perjalanan modernisasi Anda hari ini.

Artikel ini merupakan terjemahan dari artikel asli dengan judul “Backup SQL Server databases to Amazon S3” yang ditulis oleh Phil Ekins, Senior Solutions Architect di AWS.

TAGS: , ,
Indra Suryatama

Indra Suryatama

Indra Suryatama adalah Senior Solution Architect di AWS Indonesia. Memiliki pengalaman lebih dari 12 tahun di dunia IT Professional. mulai dari onpremise termasuk mainframe, sekarang Indra focus untuk membantu customer melalui digital transformasi menggunakan teknologi Amazon Web Services.