peranan ms sql agent dalam menjaga performa - Faculty e
advertisement
PERANAN MS SQL AGENT DALAM MENJAGA PERFORMA BASIS DATA PADA MS SQL SERVER SECARA OTOMATIS Ibnu Gunawan1, Rudy Adipranata2 Fakultas Teknologi Industri, Jurusan Teknik Informatika Universitas Kristen Petra Jl. Siwalankerto 121 - 131, Surabaya email : ibnu@ petra.ac.id1, [email protected] Abstrak Dalam dunia basis data dikenal adanya 2 model basis data yaitu OLTP dan OLAP. OLTP singkatan dari OnLine Transactional Processing, merupakan fungsi basis data untuk menangani sejumlah besar transaksi yang dilakukan oleh sejumlah besar user secara simultan dengan cepat (real time). OLAP (OnLine Analytical Processing) adalah fungsi basis data untuk dapat melayani permintaan penggalian informasi yang kompleks secara cepat dan akurat sehingga dapat menghasilkan informasi yang digunakan untuk menganalisa kinerja sebuah organisasi. OLAP biasanya juga menggunakan basis data model OLTP sebagai tempat penggalian informasi. Sebagai model OLTP, maka basis data dituntut untuk mempunyai perangkat lunak dan perangkat keras yang optimal. Untuk dapat mengukur apakah sebuah sistem basis data mempunyai kemampuan yang optimal, terdapat performance baseline yang dapat digunakan. Salah satu sistem perangkat lunak basis data yang mendukung model OLTP dan OLAP adalah Microsoft SQL Server. Microsoft SQL Server mempunyai 4 fasilitas yang berhubungan dengan perannya sebagai perangkat lunak basis data OLTP dan OLAP yaitu MSSQLServer, MSSQLAgent, MSDistributedCoordinator serta MSSearch. Sayangnya yang diketahui secara umum baik fungsi maupun cara penggunaannya adalah MSSQLServer. Sedangkan ketiga fasilitas yang lain belum diketahui oleh banyak orang. MSSQL Agent adalah layanan yang diberikan oleh SQL Server untuk membantu bahkan bisa dikatakan menggantikan administrator basis data khususnya untuk tugas – tugas rutin seorang administrator. Jika sebuah sistem basis data bekerja selama 24 jam sehari dan 7 hari seminggu, maka administrator juga harus bertugas selama periode tersebut yang tentu saja tidak mungkin. Untuk itulah fasilitas MSSQLAgent diperlukan, dimana MSSQLAgent dapat diumpamakan sebagai administrator virtual di dalam SQL Server. MSSQLAgent ini dapat diatur sehingga sistem basis data dapat selalu memenuhi standar performance baseline. Dengan terpenuhinya performance baseline, maka dapat dikatakan sistem basis data tersebut bekerja secara optimal. Pada tulisan ini akan dibahas secara detail point-point yang termasuk ke dalam performance base line dan bagaimana cara menggunakan MSSQLAgent sehingga sistem selalu dapat memenuhi performance baseline tersebut.. Kata kunci: MSSQLAgent, otomasi, optimalisasi, basis data. 1. Pendahuluan Secara umum, penggunaan basis data pada sistem teknologi informasi merupakan hal yang penting karena tanpa adanya suatu sistem basis data, maka sistem teknologi informasi tidak akan bekerja dengan efisien. Sebuah sistem basis data membutuhkan perangkat keras serta perangkat lunak yang dapat dengan efisien memproses segala sesuatu yang berhubungan dengan penyimpanan data serta pengambilan kembali data yang telah disimpan tersebut. Pada sistem basis data dikenal adanya dua macam model yaitu OLTP (OnLine Transactional Processing) dan OLAP (OnLine Analytical Processing). Sebagai OLTP, maka sistem basis data harus mampu menangani transaksi yang dilalukan oleh sejumlah besar pengguna secara simultan dengan cepat (real time) dan efisien. Biasanya pada sistem OLTP ini data terorganisasi menjadi tabel yang saling berhubungan (relational table) untuk mengurangi informasi yang kembar (redundant) dan mempercepat proses. Sebagai contoh aplikasi yang memerlukan penggunaan sistem basis data OLTP ini adalah sistem perbankan, pemesanan tiket penerbangan dan lain-lain. Sedangkan sebagai OLAP, maka sistem basis data harus mampu menyediakan fasilitas untuk mengorganisasikan serta menggali informasi dari sejumlah besar data dengan struktur permintaan informasi yang kompleks sehingga dapat menghasilkan suatu informasi yang dapat digunakan untuk menganalisa kinerja organisasi secara cepat (real time) dan akurat. Sebagai contoh hasil OLAP ini adalah analisa tentang trend penjualan yang terjadi selama periode tertentu. Agar sebuah sistem basis data dapat dianggap sudah bekerja dengan optimal, diperlukan sebuah tolok ukur atau disebut dengan performance baseline, dimana performance baseline digunakan untuk membandingkan kinerja sistem saat ini dengan standar yang telah ditetapkan. Jika kinerja sistem kurang dari performance baseline yang ditetapkan maka kinerja sistem dikatakan tidak optimal sehingga perlu dilakukan sesuatu sehingga menjadi optimal kembali. Untuk menjaga agar sistem tetap berada dalam kondisi optimal, diperlukan administrator. Tugas utama administrator adalah menjaga sistem sehingga sistem tetap bekerja dengan optimal dan jika terjadi suatu masalah pada sistem, administrator bertugas untuk mengatasi masalah tersebut. Tugas administrator ini penting sekali karena meskipun sistem mempunyai perangkat keras serta perangkat lunak yang optimal, tanpa adanya administrator maka sistem tidak akan bekerja dengan optimal. Karena dari waktu ke waktu selama bekerja, sistem pasti akan mengalami penurunan unjuk dan jika terjadi suatu masalah, dapat terjadi kemungkinan sistem berhenti bekerja. 2. Microsoft SQL Server Microsoft SQL Server adalah sebuah perangkat lunak sistem basis data yang mendukung model OLTP serta OLAP. MS SQL Server ini berjalan pada sistem operasi Windows NT / 2000 / 2003 Server. MS SQL Server terdiri dari komponen client dan server yang berfungsi untuk menyimpan serta menggali data (query). SQL Server menggunakan arsitektur komunikasi yang terbagi menjadi layer-layer sehingga dapat dibangun aplikasi yang terpisah dari masalah protokol jaringan komputer. Hal ini memungkinkan untuk membangun sebuah aplikasi yang berjalan di lingkungan jaringan berbeda. Gambar 1. Arsitektur Microsoft SQL Server Microsoft SQL Server terdiri dari empat fasilitas (service) yaitu MSSQLServer, SQLServerAgent, Microsoft Distributed Transaction Coordinator (MS DTC) dan Microsoft Search. MSSQLServer berfungsi sebagai database engine dan merupakan fasilitas utama sistem basis data. SQLServerAgent bekerja sama dengan MSSQLServer untuk membuat dan mengatur alert, tugas (job) multiserver atau lokal serta operator. MS DTC mempunyai fasilitas untuk mengijinkan client mengikutsertakan data yang berasal dari beberapa sumber pada satu transaksi. Fasilitas terakhir adalah Microsoft Search yang merupakan engine guna pencarian data full-text. Dari keempat fasilitas Microsoft SQL Server, biasanya yang digunakan hanyalah MSSQLServer yang merupakan engine utama sistem basis data, sedangkan fasilitas lainnya seringkali tidak digunakan. Dari ketiga fasilitas selain MSSQLServer, MSSQLAgent merupakan fasilitas yang dapat membantu administrator untuk menjalankan tugas-tugas rutinnya secara otomatis. Seperti telah dibahas pada bagian Pendahuluan, sebuah sistem basis data pasti membutuhkan administrator yang harus selalu ada pada saat sistem bekerja. Hal ini tentu akan menyulitkan bagi perusahaan yang bekerja 24 jam sehari 7 hari seminggu untuk dapat mempunyai administrator yang selalu siap sedia setiap saat. Untuk itu diperlukan adanya suatu fasilitas untuk menggantikan administrator pada saat tidak berada di tempat. Guna menggantikan tugas administrator secara otomatis inilah digunakan MSSQLAgent. 3. Performance Baseline Agar sebuah sistem database dapat bekerja dengan optimal, maka dibutuhkan sebuah standar untuk mengukur kinerja sistem yang sering disebut dengan performance baseline. Performance baseline ini berupa suatu standar yang harus tetap dipenuhi selama sistem bekerja. Sistem terus dimonitor sehingga jika ternyata kinerja sistem atau status yang terukur berada di bawah standar performance baseline, maka perlu dilakukan sesuatu untuk mengembalikan kinerja sistem menjadi optimal kembali. Terdapat lima buah faktor yang digunakan untuk menentukan performance baseline, yaitu : workload, throughput, system resources, optimization, contention. Workload berhubungan dengan jumlah aktifitas yang terdapat pada sistem. Throughput berhubungan dengan jumlah query yang terjadi pada periode tertentu. System resources berhubungan dengan kapasitas perangkat keras yang dipunyai oleh sistem. Optimization berhubungan dengan desain basis data serta aplikasi. Dan terakhir contention berhubungan dengan kompetisi dari data yang berupa records. Gambar 2. Faktor Performance Baseline Sebelum dapat menentukan baseline performance, maka harus dilakukan monitor terhadap unjuk kerja server dan menuliskan tujuan kinerja yang hendak dicapai oleh sistem. Hal yang harus dilakukan contohnya adalah : mendefinisikan parameter pada server untuk sumber daya yang dipunyai server, beban pekerjaan yang dilakukan server serta unjuk kerja server, menentukan tujuan yang dikehendaki untuk throughput dan waktu respon sistem, mendokumentasikan operasi yang terjadi pada sistem serta melihat efek yang terjadi dari operasi tersebut, menganalisa transaksi yang terdapat pada setiap basis data, mengidentifikasikan masalah-masalah yang mempengaruhi unjuk kerja sistem. Setelah melakukan monitor pada sistem selama beberapa waktu tertentu, barulah dapat ditentukan performance baseline yang nantinya akan digunakan sebagai standar kinerja sistem untuk waktu selanjutnya. Untuk melakukan monitor terhadap kinerja sistem, terbagi menjadi tiga bagian yaitu system level monitoring, SQL Server specific monitoring serta specific query performance. Pada system level monitoring, pelaksanaan monitoring dilakukan pada perangkat keras, sistem operasi serta aplikasi yang dijalankan. Sedangkan pada SQL Server specific monitoring, pelaksanaan monitoring dilakukan pada aktivitas SQL Server serta konsistensi data. Microsoft SQL Server yang berjalan di sistem operasi Windows NT/2000/2003 Server mempunyai beberapa fasilitas untuk melakukan monitoring. Pada system level monitoring, terdapat Windows Event Viewer serta Windows Monitor yang dapat digunakan untuk memonitor unjuk kerja perangkat keras, sistem operasi serta aplikasi yang berjalan pada sistem operasi tersebut. Untuk SQL Server specific monitoring, dapat digunakan window Current-Activity yang terdapat pada SQL Enterprise Manager, perintah Transact-SQL dan system stored procedure serta SQL Profiler. Sedangkan untuk specific query performance, pelaksanaan monitor dilakukan untuk setiap query yang akan dianalisa, dan dilihat penggunaan index, waktu CPU untuk masing-masing query serta I/O aktual yang digunakan. Untuk melakukan monitoring ini dapat digunakan SQL Profiler, SQL Query Analyzer dan Index Tuning Wizard. Gambar 3. Fasilitas Monitoring Untuk masalah perangkat keras, SQL Server mempunyai fasilitas yang berhubungan dengan Windows System Monitor yaitu dengan menambahkan counter. Counter ini berupa suatu angka yang menyatakan unjuk kerja sistem basis data. Berikut ini disajikan tabel yang memuat counter beserta dengan deskripsi serta nilai yang seharusnya dicapai agar sistem bekerja dengan optimal. Tabel 1. Definisi Counter Guna Monitoring Sistem Jenis Counter Memory: Available bytes Deskripsi Nilai Seharusnya Memonitor jumlah byte tersedia yang Lebih besar dari 5000 Kb. Jika lebih kecil dapat digunakan untuk mengeksekusi berarti memori fisik harus ditambahkan. perintah. Memory: Pages/sec Memonitor jumlah page yang dibaca Tidak boleh lebih besar dari nol terus atau ditulis oleh harddisk untuk menerus. Jika terjadi lebih dari nol, maka mengatasi hard page faults. Windows menggunakan paging file untuk memenuhi permintaan memori. Process: Page faults/sec/SQL Memonitor page faults yang Seharusnya bernilai kecil. Jika bernilai Server instance disebabkan oleh Windows 2000 yang tinggi berarti terdapat proses paging dan mengecilkan ukuran proses yang trashing yang besar yang perlu diperiksa. sedang dikerjakan. Process: Working set / SQL Memonitor jumlah memori yang Lebih besar dari 5000 KB. Jika lebih kecil Server instance digunakan oleh proses yang berarti tidak ada memori yang dapat dilakukan SQL Server. digunakan oleh SQL Server. SQL Server: Buffer manager : Memonitor persentase pages yang Lebih besar dari 90 %, hal ini Buffer cache hit ratio terdapat pada buffer cache tanpa menunjukkan jumlah page yang membaca dari harddisk. ditemukan di cache. SQL Server: Buffer manager : Memonitor jumlah pages pada buffer Angka yang rendah menunjukkan proses Total pages cache termasuk database, pages I/O yang sering dilakukan pada disk yang bebas dan yang tercuri dari sehingga perlu menambah ukuran proses lain. memori. SQL Server: Memory manager: Memonitor jumlah total memori Jika bernilai tinggi terus menerus Total server memory dinamis yang digunakan server. dibanding dengan jumlah memori fisik yang tersedia, maka dibutuhkan memori tambahan. Processor: % Processor time Memonitor persentase waktu yang Lebih kecil dari 90 persen. Jika counter dibutuhkan oleh processor untuk menunjuk ke angka lebih tinggi, perlu memproses non-idle thread. melakukan pengurangan workload, melakukan efisiensi workload atau mengubah processor menjadi lebih baik / menambah jumlah processor pada sistem multiprocessor. System: Context switches/sec Memonitor jumlah perpindahan Pada komputer yang mempunyai lebih proses thread yang dilakukan dari satu processor, jika angka menunjuk processor dalam satu detik. ke 8000 dan counter Processor: % Processor time lebih dari 90%, maka harus diaktifkan SQL Server fiber mode scheduling. System: Processor queue length Memonitor jumlah thread yang Tidak boleh lebih dari dua terus menerus. sedang menunggu giliran processor Jika terjadi, perlu melakukan time. pengurangan workload, melakukan efisiensi workload atau mengubah processor menjadi lebih baik / menambah jumlah processor pada sistem multiprocessor. Processor: % Privileged time Memonitor persentase waktu yang Jika terjadi persentase yang signifikan dibutuhkan processor untuk dan counter harddisk juga tinggi, maka mengeksekusi perintah kernel sistem perlu meningkatkan unjuk kerja I/O operasi, seperti proses permintaan harddisk. I/O SQL Server. Processor: % User time PhysicalDisk: % Disk time Memonitor persentase waktu yang Persentase yang tinggi menyatakan dibutuhkan processor untuk bahwa terdapat proses atau aplikasi yang mengeksekusi proses user seperti sedang dieksekusi dan menghalangi pada SQL Server sendiri. operasi SQL Server. Memonitor persentase waktu yang Lebih kecil dari 90 %. dibutuhkan harddisk untuk melakukan baca / tulis PysicalDisk : Avg. disk queue Memonitor jumlah rata-rata antrian length permintaan baca / tulis PhysicalDisk: Disk reads / sec Memonitor rata-rata operasi Lebih kecil dari kapasitas subsystem pembacaan disk. harddisk. Memonitor rata-rata operasi Lebih kecil dari kapasitas subsystem penulisan disk. harddisk. PhysicalDisk: Disk writes / sec 4. Implementasi dan Pengujian MSSQL Agent Lebih kecil dari dua kali jumlah spindle. Guna Menjaga Baseline Performance Secara garis besar, tugas MSSQLAgent dapat dibagi menjadi dua yaitu menjalankan suatu tugas (job) pada waktu yang telah ditentukan sebelumnya serta mendeteksi adanya alert yang disebabkan oleh sesuatu kondisi pada SQL Server dan melakukan tugas sehubungan dengan alert yang terjadi serta menginformasikan kepada operator yang bertanggung jawab atas kondisi tersebut. Agar dapat bekerja, terlebih dahulu MSSQLAgent harus diaktifkan, karena secara umum pertama kali instalasi, MSSQLAgent dalam kondisi non-aktif. Untuk mengaktifkan serta menggunakan MSSQLAgent dapat dilakukan melalui SQL Enterprise Manager. Sebelum mendefiniskan tugas ataupun alert, sebaiknya dilakukan pengisian operator. Operator ini akan menerima pesan dari MSSQLAgent setelah selesai melakukan tugas pada waktu yang ditentukan atau setelah menerima alert tertentu. Pesan dapat diterima melalui email ataupun menggunakan pengiriman Net send jika operator berada pada komputer yang lain. Setelah membuat operator, barulah dilakukan pendefinisian job dengan cara melakukan klik kanan pada SQL Server Agent-New-Job. Gambar 4. Pendefinisian Job pada MSSQLAgent Pada pendefinisian job ini dapat ditentukan kapan sebuah job akan dijalankan yang dapat berupa sekali waktu ataupun berulang dengan periode tertentu. Macam job yang dapat dilakukan diantaranya adalah mengeksekusi perintah sistem, replikasi serta menjalankan Transact-SQL. Untuk mengimplementasikan penjagaan baseline performance, digunakan kemampuan MSSQLAgent yaitu dapat menerima alert yang berasal dari kondisi yang terjadi pada sistem. Agar memenuhi baseline performance, maka sistem harus selalu dimonitor sehingga unjuk kerja selalu terjaga dan jika ada kesalahan, harus segera diambil tindakan. Untuk inilah digunakan peran alert guna memonitor sistem dan memberi pesan jika terjadi sesuatu seperti terjadi kesalahan, disk penuh dan lain-lain. Setelah menerima pesan, MSSQLAgent dapat melakukan job dan/atau memberitahu operator. Job ini berisi definisi tugas yang dapat dilakukan jika terjadi alert tersebut. Untuk mendeteksi alert terlebih dahulu harus mendefinisikannya dengan melakukan klik kanan pada SQL Server Agent-New-Alert. Disini dapat dipilih alert apa saja yang ingin dideteksi atau dengan mendefinisikan nomor kesalahan yang mungkin terjadi pada tempat yang disediakan. Setelah memilih jenis alert, dapat ditentukan job apa yang seharusnya dilakukan serta siapa operator yang dihubungi. Dengan mendefinisikan sistem alert mengenai kemungkinan-kemungkinan terjadinya kesalahan atau terjadinya performance yang lebih rendah daripada baseline performance yang telah ditetapkan dan menentukan penanganan yang harus dilakukan, maka tugas administrator dapat tergantikan jika administrator tidak berada di tempat. Berikut ini adalah pengujian dari implementasi MSSQLAgent untuk mendeteksi adanya kekurangan stok pada sebuah sistem database perusahaan retail. Pertama kali didefinisikan terlebih dahulu mengenai message yang akan dimonitor. Untuk mendefinisikan message, digunakan menu Action-Manage SQL Server Messages. Kemudian dibuat New Message. Message ini mempunyai nomor kesalahan 50001. Gambar 5. Pendefinisian Message Guna Pengujian Alert Setelah mendefinisikan message, kemudian dibuat alert untuk mendeteksi message tersebut serta operator yang akan diberi informasi jika terjadi alert. Pada pendefinisian alert ini, dimasukkan nomor kesalahan message yang telah dibuat yaitu 50001 serta basis data yang akan dimonitor. Sedangkan operator dibuat untuk menerima informasi dengan menggunakan Net send. Untuk dapat menggunakan email, memerlukan Microsoft Exchange Server sebagai mail server. Gambar 6. Pembuatan Alert Guna Menerima Message Untuk membuat sistem menerima pesan kesalahan sehingga menimbulkan alert, dapat digunakan stored procedure atau trigger yang mendeteksi jumlah barang tersedia serta membandingkannya dengan minimum stok yang telah didefinisikan. Jika ternyata jumlah barang lebih kecil dari minimum stok, maka harus dibangkitkan error dengan perintah RAISERROR(no error, severity, state, parameter). Setelah pendefinisian, dilakukan pengujian dengan melakukan transaksi pada kondisi jumlah stok lebih kecil dari jumlah minimum, maka dalam waktu kurang dari 2 detik, akan muncul popup pemberitahuan dengan menggunakan Net send pada komputer yang telah didefinisikan sebagai administrator. Gambar 7. Hasil Pengujian Pengiriman Net Send 5. Kesimpulan Dalam sebuah sistem basis data haruslah ditentukan baseline performance yang digunakan sebagai tolok ukur untuk mengetahui kinerja sistem apakah sudah optimal. Dengan menggunakan MSSQLAgent dapat menggantikan administrator basis data MS SQL Server jika tidak berada di tempat. Hal ini dimungkinkan karena adanya fasilitas MSSQLAgent untuk mendeteksi kesalahan atau menurunnya unjuk kerja sistem dan mengambil tindakan jika hal tersebut terjadi secara otomatis. Dari pengujian, pesan yang diterima oleh operator setelah terjadi sesuatu alert hanya beberapa detik dengan menggunakan Net send. Kelemahan MSSQLAgent adalah tidak adanya kemampuan untuk mengirimkan pesan ke operator menggunakan mail server selain Microsoft Exchange Server. Daftar Pustaka [1] Byrne, Jeffry L., “Microsoft SQL server : What Database Administrations Need to Know”, Upper Saddle River, New Jersey, 1997. [2] Iseminger, David, “Microsoft SQL server 2000 reference library : Database creation, warehousing, and optimization”, Redmond, Washington, 2001. [3] Papa, John, “Microsoft SQL Server 7.0 Programming Unleashed”, Indianapolis, 1999.
