Buat rencana pemeliharaan SQL Server
Aktivitas umum yang dapat Anda jadwalkan untuk pemeliharaan SQL Server reguler meliputi:
- Database dan cadangan log transaksi
- Pemeriksaan konsistensi database
- Pemeliharaan indeks
- Pembaruan statistik
Sangat penting untuk memahami pentingnya pencadangan, serta pemeliharaan indeks dan statistik, untuk semua database Anda. Pemeriksaan konsistensi database, juga dikenal sebagai CHECKDB (menggunakan perintah DBCC CHECKDB), sama pentingnya karena ini adalah satu-satunya cara untuk memeriksa seluruh database untuk kerusakan. Bergantung pada ukuran database dan persyaratan waktu aktif Anda, Anda mungkin melakukan semua aktivitas ini setiap malam. Namun, dalam sistem produksi, operasi pemeliharaan sering tersebar selama seminggu, karena pemeliharaan indeks dan pemeriksaan konsistensi sangat intensif I/O dan biasanya dilakukan selama jam akhir pekan.
Banyak cadangan DBA yang mengejutkan database besar, melakukan satu pencadangan penuh seminggu dan menggunakan cadangan log diferensial dan transaksi untuk mengelola pemulihan ke titik waktu tertentu. SQL Server menawarkan cara bawaan untuk mengelola semua tugas ini menggunakan Rencana Pemeliharaan. Rencana Pemeliharaan membuat alur kerja tugas untuk mendukung database Anda dan dibuat sebagai paket Layanan Integrasi, memungkinkan Anda menjadwalkan aktivitas pemeliharaan Anda. Selain itu, banyak DBA menggunakan skrip sumber terbuka untuk pemeliharaan database untuk mendapatkan lebih banyak fleksibilitas dan kontrol atas aktivitas pemeliharaan.
Praktik terbaik untuk rencana pemeliharaan
Rencana pemeliharaan tidak hanya membantu Anda melakukan pemeliharaan database tetapi juga menawarkan opsi untuk memangkas data dari database msdb, yang berfungsi sebagai penyimpanan data untuk SQL Server Agent. Selain itu, rencana pemeliharaan memungkinkan Anda menentukan penghapusan cadangan database yang lebih lama dari disk. Menghapus file cadangan lama mengurangi ukuran volume cadangan Anda dan membantu mengelola ukuran database msdb.
Pastikan bahwa periode retensi cadangan Anda lebih panjang dari jendela pemeriksaan konsistensi Anda. Misalnya, jika Anda menjalankan pemeriksaan konsistensi setiap minggu, Anda harus mempertahankan riwayat pencadangan yang memadai untuk memulihkan dari potensi kerusakan yang terdeteksi selama pemeriksaan konsistensi. Perhatikan bahwa operasi pencadangan tidak mendeteksi kerusakan dalam database, sehingga dimungkinkan untuk mengalami kerusakan dalam file cadangan. Kegiatan rencana pemeliharaan dijadwalkan sebagai pekerjaan SQL Server Agent untuk eksekusi.
Membuat rencana pemeliharaan
Anda dapat membuat rencana pemeliharaan menggunakan SQL Server Management Studio, seperti yang ditunjukkan di bawah ini. Dalam contoh, beberapa tugas pemeliharaan digabungkan menjadi satu rencana pemeliharaan. Namun, praktik terbaiknya adalah membuat rencana pemeliharaan terpisah untuk setiap jenis tugas, dan bahkan mungkin untuk database tertentu di server Anda. Misalnya, Anda dapat membuat satu rencana pemeliharaan untuk mencadangkan database sistem dan yang lain untuk mencadangkan database pengguna. Selain itu, Anda dapat memiliki rencana pemeliharaan terpisah untuk menangani cadangan database pengguna yang sangat besar. Gambar di bawah ini dan contoh berikut menunjukkan cara membuat rencana pemeliharaan menggunakan Wizard Rencana Pemeliharaan.
Gambar menunjukkan layar pertama Wizard Rencana Pemeliharaan di SQL Server Management Studio (SSMS). Anda perlu menentukan nama untuk rencana pemeliharaan anda dan akun run-as. Sebagian besar tugas pemeliharaan akan berjalan sebagai akun layanan SQL Server Agent, tetapi untuk tujuan keamanan, beberapa tugas mungkin perlu dijalankan sebagai akun yang berbeda. Misalnya, jika Anda perlu mencadangkan ke berbagi file yang hanya dapat diakses oleh akun tertentu, Anda akan menggunakan pengguna proksi, yang merupakan komponen dari SQL Server Agent.
Apa itu akun proksi?
Akun proksi adalah akun dengan kredensial tersimpan yang dapat digunakan Agen SQL Server untuk menjalankan langkah-langkah pekerjaan tertentu sebagai pengguna yang ditunjuk. Informasi masuk untuk pengguna ini disimpan sebagai kredensial dalam instans SQL Server. Akun proksi biasanya digunakan ketika langkah pekerjaan tertentu memerlukan hak keamanan yang sangat terperinci.
Misalkan Anda memiliki pekerjaan SQL Server Agent yang perlu mencadangkan database ke berbagi file jaringan. Jika akun layanan SQL Server Agent tidak memiliki akses ke berbagi file, Anda dapat membuat akun proksi dengan izin yang diperlukan. Akun proksi ini kemudian dapat digunakan untuk menjalankan langkah pencadangan, memastikannya memiliki hak akses yang diperlukan.
Jadwal pekerjaan
Jadwal pekerjaan adalah bagian dari sistem pekerjaan dalam database sistem msdb. Pekerjaan dan jadwal SQL Server Agent memiliki hubungan banyak ke banyak, yang berarti setiap pekerjaan dapat memiliki beberapa jadwal, dan setiap jadwal dapat ditetapkan ke beberapa pekerjaan. Namun, Wizard Rencana Pemeliharaan tidak mengizinkan pembuatan jadwal independen. Sebaliknya, ini membuat jadwal tertentu untuk setiap rencana pemeliharaan.
Contoh berikut menunjukkan jadwal untuk eksekusi mingguan, tetapi Anda juga memiliki opsi untuk membuat jadwal dengan pengulangan per jam atau harian.
Langkah selanjutnya adalah memilih tugas pemeliharaan untuk ditambahkan ke paket. Contoh berikut menunjukkan operasi yang tersedia untuk dilakukan oleh rencana pemeliharaan Anda.
Periksa integritas database - Tugas ini menjalankan DBCC CHECKDB perintah untuk memvalidasi konsistensi logis dan fisik setiap halaman database. Anda harus melakukan tugas ini secara teratur dan menyelaraskannya dengan jendela retensi cadangan Anda. Pastikan Anda menyelesaikan pemeriksaan konsistensi sebelum membuang cadangan sebelumnya untuk mencegah terjadinya kerusakan.
Susutkan database - Tugas ini mengurangi ukuran database atau file log transaksi dengan memindahkan data ke ruang kosong di halaman. Setelah cukup ruang dibebaskan, ruang dapat dikembalikan ke sistem file. Disarankan untuk tidak menyertakan tindakan ini dalam pemeliharaan rutin karena menyebabkan fragmentasi indeks yang parah, merugikan performa database. Operasi ini juga sangat I/O dan intensif CPU, yang dapat berdampak signifikan pada performa sistem.
Mengatur ulang/Membangun ulang indeks - Tugas ini memeriksa tingkat fragmentasi dalam indeks database dan membangun kembali atau mengatur ulang indeks berdasarkan tingkat fragmentasi yang ditentukan pengguna. Membangun kembali indeks juga memperbarui statistiknya.
Statistik pembaruan - Tugas ini memperbarui statistik kolom dan indeks yang digunakan oleh SQL Server untuk membangun rencana eksekusi kueri. Statistik yang akurat sangat penting bagi pengoptimal kueri untuk membuat keputusan terbaik. Anda dapat memilih tabel dan indeks mana yang akan dipindai dan persentase atau jumlah baris yang akan dipindai. Laju pengambilan sampel default biasanya cukup, tetapi Anda mungkin memerlukan statistik yang lebih rinci untuk tabel tertentu.
Riwayat pembersihan - Tugas ini menghapus riwayat operasi pencadangan dan pemulihan dari msdb database, serta riwayat pekerjaan SQL Server Agent. Ini membantu mengelola ukuran database msdb.
Jalankan pekerjaan SQL Server Agent - Tugas ini menjalankan pekerjaan SQL Server Agent yang ditentukan pengguna.
Database Cadangan (Penuh/Diferensial/Log) - Tugas ini mencadangkan database pada instans SQL Server. Pencadangan penuh menangkap seluruh database dan berfungsi sebagai titik awal untuk pemulihan. Pencadangan diferensial menangkap halaman yang telah berubah sejak pencadangan penuh terakhir, menyediakan titik pemulihan inkremental. Pencadangan log transaksi mengambil halaman aktif di log transaksi Anda, memungkinkan Anda menentukan tujuan titik pemulihan Anda. Perhatikan bahwa pencadangan log transaksi tidak dapat dilakukan pada database dalam mode pemulihan SIMPLE.
Misalnya, Jika Anda mengambil cadangan penuh pada hari Minggu dan cadangan diferensial setiap malam, untuk memulihkan database Anda ke siang hari pada hari Kamis, Anda akan memulihkan cadangan penuh Hari Minggu, pencadangan diferensial Rabu, dan pencadangan log transaksi dari diferensial Rabu ke Kamis siang.
Tugas Pembersihan Pemeliharaan - Tugas ini menghapus file lama yang terkait dengan rencana pemeliharaan, termasuk laporan teks dan file cadangan. Ini hanya menghapus cadangan di folder yang ditentukan, sehingga subfolder apa pun harus dicantumkan secara eksplisit atau akan dilewati.
Setiap tugas dapat dicakup ke database pengguna, database sistem, atau pilihan database kustom, dan masing-masing memiliki opsi konfigurasi tertentu.
Setelah dibuat, perencanaan tersebut akan muncul sebagai pekerjaan di SQL Server Agent. Jika Anda menambahkan jadwal baik selama proses pembuatan atau setelahnya, pekerjaan itu akan dijalankan dan tugas pemeliharaan akan dilakukan.
Lingkungan multiserver
Dalam lingkungan multiserver , SQL Server Agent memungkinkan Anda menunjuk satu server sebagai server master yang dapat menjalankan pekerjaan di server lain, yang dikenal sebagai server target. Server master menyimpan salinan master pekerjaan dan mendistribusikannya ke server target. Server target secara berkala tersambung ke server master untuk memperbarui jadwal pekerjaan mereka. Penyiapan ini memungkinkan Anda menentukan pekerjaan sekali dan menyebarkannya di seluruh perusahaan Anda. Misalnya, Anda dapat mengonfigurasi tugas pemeliharaan database di server master dan mendorongnya ke sekelompok server target, memastikan penyebaran yang konsisten.