Trying tobe SQL geek's

Apakah metode terbaik untuk mengimplemetasikan DRP (Disasters Recovery Plan) pada Microsoft SQL Server?

Artikel itu saya buat dikhususkan untuk pada Database Administrator SQL Server yang mempunyai database SQL Server dengan tingkat availability yang sangat tinggi.

Apa yang dimaksud dengan DRP (Disasters Recovery Plan)?
Disasters Recovery Plan, atau saya singkat DRP, adalah metode/rencana yang diterapkan/disusun oleh kita apabila terjadi hal-hal yang tidak diinginkan terjadi pada database atau mesin tempat menyimpan database yang kita punyai, sehingga menimbulkan rusak atau bahkan hilangnya data pada database kita. Hal-hal yang tidak diinginkan tersebut bisa bermacam-macam, misalnya data terhapus, mesin tempat menyimpan database kita rusak, kebakaran pada ruang tempat menyimpan mesin database kita, bencana alam dan lain sebagainya.

Beberapa hal yang harus diperhatikan dalam mengimplementasikan DRP
Sebelum kita membuat metode/rencana/langkah-langkah yang akan kita pakai dalam mengimplementasikan DRP ini, ada beberapa hal yang harus kita perhatikan berkaitan dengan implemetasi DRP ini. Hal-hal yang harus diperhatikan tersebut antara lain adalah :
Buat suatu list yang yang diurutkan berdasarkan tingkat availability dari semua database yang ada dalam mesin/server kita
Bedakan lokasi tempat menyimpan mesin/database/backup kita dengan lokasi tempat menyimpan mesin/database yang sedang running
Pilih metode yang terbaik untuk metode DRP ini, disesuaikan dengan semua resources yang ada pada kita, kalau memang kita tidak bisa mengeluarkan anggaran tambahan untuk implemetasi DRP ini.
Lakukan simulasi metode DRP yang telah kita tetapkan
Lakukan pengecekkan metode DRP secara berkala
Lakukan review terhadap metode DRP yang telah kita terapkan

Metode manakah yang terbaik?
Microsoft SQL Server menyediakan beberapa metode untuk mengimplementasikan DRP ini.

Data Transformation Services (DTS)
DTS bisa digunakan untuk meng-copy objek-objek database dan data antara database yang berbeda dalam server yang sama atau dengan server yang berbeda.
Keuntungan :
- Menyediakan wizard yang user friendly
- Menyediakan fasilitas untuk bisa membuat functionality secara programmatic
- Data bisa di transfer ke dalam berbagai macam format
Kerugian :
- Tidak bisa dijadikan warm-standby server
- Memakai resources server dan bandwidth yang besar, sehingga apabila database kita sudah berukuran besar, kemungkinan data tidak sinkron akan besar
Kesimpulan :
Sangat sulit untuk maintain warm-standby server dengan menggunakan DTS ini, karena kerugian-kerugian tersebut diatas. Untuk itu, gunakan DTS untuk men-transfer data daripada menjadikannya sebagai metode untuk DRP.

Bulk Copy (BCP)
BCP bisa dikatakan sebagai versi primitif dari DTS, karena bisa dijalankan dengan menggunakan command line.
Keuntungan :
- Efisien dan cepat dalam men-transfer data dengan ukuran yang besar
Kerugian :
- Sama kerugiannya DTS
- Tidak bisa digunakan untuk men-transfer objek database selain tables dan views
- User interface yang tidak bagus
- Tidak ada fungsi untuk scheduling
Kesimpulan :
Sangat sulit untuk maintain warm-standby server dengan menggunakan BCP ini, karena kerugian-kerugian tersebut diatas. Untuk itu, gunakan BCP untuk men-transfer data dengan cepat daripada menjadikannya sebagai metode untuk DRP.

Replication
Replication atau replikasi men-transfer data dari satu database ke database lain, dan bisa juga men-transfer dari SQL Server ke semua jenis ODBC data sources.
Replikasi ada 3 jenis :
- Snapshot Replication : Transfer tables secara komplit dari satu database ke database lain (tidak ideal untuk table yang besar)
- Transactional Replication : hanya men-transfer perubahan yang terjadi dalam database sumber
- Merge Replication : merupakan replikasi 2 arah, baik digunakan untuk data yang terdistribusi.
Keuntungan Transactional Replication :
- Hanya men-transfer perubahan yang terjadi dalam database sumber
- Dengan rencana yang baik, perbedaan antara database sumber dan database tujuan bisa diatur waktunya
- Enterprise Manager menyediakan user interface yang bagus
Kerugian :
- Banyak hal yang bisa menyebabkab proses replikasi ini terjadi error, diantaranya dalah :
o Tingkat availability dari server-server yang lain (publisher, distributor, subscriber)
o Availability SQL Server Agent
o Security permission dalam folder Snapshot
o Bandwidth network
o Hubungan antara publisher, subscriber and distributor
o Tempat ruang kosong dalam harddisk distribution database
- Semua table harus mempunya primary key
- Memerlukan perhatian besar dari database administrator dan harus dimonitor secara kontinyu
- Tidak bisa men-transfer objek database, seperti UDF (User Define Function)
Kesimpulan :
Selama transactional replication bisa digunakan untuk men-transfer data antara server yang satu dengan yang lainnya, tetap memerlukan setup dan monitoring yang agak sedikit sulit. Sebagai pertimbangan gunakan transactional database ini untuk maintain database reporting dengan men-transfer dari OLTP database.

Clustering
Clustering merupakan metode terbaik untuk High Availability karena bisa otomatis memindahkan koneksi ke server lain bila server primer mengalami masalah. Dalam cluster aktif/pasif, server sekunder secara terus menerus momonitor server primer, dan akan segera mengambil alih apabila server primer mengalami masalah.
Keuntungan :
- High Availability
- Tidak ada campur tangan user, ketika melakukam recovery server.
Kerugian :
- Memerlukan SQL Server Enterprise Edition dalam tiap-tiap server, sehingga memerlukan cost untuk licensing yang lebih mahal.
- Memerlukan spesifikasi hardware yang compatible untuk proses clustering.
- Sulit dalam mebuat konfiguransinya
- Memerlukan un-clustering dan re-clustering ketika akan mengimplementasikan SQL Server Service Pack.
- Memerlukan 2 koneksi yang dihubungkan decara fisik.
Kesimpulan :
Clustering adalah merupakan salah satu cara yang terbaik untuk solusi High Availability pada Microsoft SQL Server, tetapi bukan merupakan metode pendekatan terbaik untuk DRP, kecuali kalau digabung dengan metode-metode yang lain, seperti backup/restore, disk mirroring, dan sebagainya.

Backup/Restore
Backup database menyediakan duplikat dari database yang sedang running, dan proses backup juga tidak hanya membuat duplikat dari suatu database tetapi termasuk dengan objek-objek yang ada dalam database tersebut.
Keuntungan :
- Membuat duplikat data dan objek dalam database dengan sama persis.
- Bisa mudah diimplementasukan dengan menggunakan wizard Database Maintenance Plan
- Hanya memerlukan sedikit resources untuk monitoring, karena hanya sekali di configure
Kerugian :
- Ketika melakukan proses restore, database harus dalam keadaan single user mode
Kesimpulan :
Dengan mengkombinasikan full database backup dan transaction log backup bisa dijadikan salah satu alternatif sebagai metode untuk proses DRP.

Log Shipping
Log shipping ada proses pemindahan/peng-copy-an transaction log files secara otomatis. Log Shipping memerlukan server primer dan server sekunder yang harus kita tetapkan.
Keuntungan :
- Log Shipping menyediakan kapabilitas untuk proses copy dan restore log files secara otomatis berdasarkan durasi tertentu dan secara terus menerus.
- Dengan Log Shiping ini perbedaan data diantara server primer dan sekunder bisa dikurangi.
Kerugian :
- Tidak tersedia pada Microsoft SQL Server 7.0

Kesimpulan :
Log Shipping boleh dikatakan sebagai metode DRP yang terbaik, karena resiko kehilangan data sangat sedikit dan sedikti pula waktu untuk downtime.


Dari semua uraian diatas kita bisa mengambil salah satu metode yang paling baik manakah yang bisa diimplemetasi sebagai metode untuk DRP.

Strategy backup pada Microsoft SQL Server

Untuk seorang database administrator tentunya pekerjaan mengenai backup database adalah sudah merupakan perkerjaan yang "wajib" dilakukan. Mengapa demikian? Karena kegiatan backup ini adalah sebenarnya kegiatan yang intangible (tidak dapat diukur dengan besarnya uang), tetapi bisa menimbulkan "bencana" yang sangat besar apabila kurang diperhatikan dengan baik. Akan tetapi setelah pekerjaan backup itu dilakukan pertanyaan selanjutnya adalah, apakah metode/strategi untuk backup ini sudah dilakukan dengan benar?

Seperti kita ketahui bahwa Microsoft SQL Server menyediakan beberapa tipe/metode untuk melakukan database backup ini, yaitu :
1. Full Backup
2. Differential Backup
3. Transaction Log Backup
4. Filegroup Backup

Apakah perbedaan dari masing-masing metode backup tersebut?

Full Backup
Metode backup ini akan membuat backup seluruh database

Differential Backup
Metode backup ini akan membuat backup perbedaan database dari terakhir kali dilakukan full backup

Transaction Log Backup
Motode backup ini akan membuat backup perbedaan database dari terakhir kali dilakukan transactional backup atau full backup

Filegroup Backup
Metode backup ini akan membuat backup filegroups dari database

Bagaimanakah menerapkan strategy backup dengan menggunakan metode-metode tersebut diatas? Berikut beberapa tip supaya data yang kita simpan aman dan resiko kehilangan data juga menjadi kecil, serta tentunya pemakaian media backup yang tidak terlalu berlebihan.
1. Gunakan full backup berdasarkan durasi waktu yang paling lama
2. Gunakan differential backup diantara full backup dan transactional backup
3. Gunakan transactional backup sesering mungkin

Dari uraian diatas berikut contoh penerapan yang bisa dilakukan :
1. Lakukan full backup tiap minggu
2. Lakukan differential backup tiap hari
3. Lakukan transactional backup tiap jam (lebih sering lebih bagus)

Dengan metode tersebut kita akan mudah melakukan proses restore apabila terjadi sesuatu dengan database kita.

May Day

Hari Kamis kemarin, tanggal 1 Mei 2008, karena bertepatan dengan hari libur seharian saya di rumah, menghabiskan waktu dengan nonton TV, ternyata isinya kebanyakan berita mengenai demo para pekerja yang memperingati hari buruh sedunia. Dalam demo itu ada beberapa aspirasi/tuntutan dari para buruh yang diantaranya adalah mengenai perbaikan upah dan penghentian sistem kerja kontrak dan outsoucing.

Saya sempat berpikir, sebetulnya yang disebut sebagai buruh itu siapa? Apakah seseorang yang bekerja di kantoran dengan menggunakan stelan jas dan berdasi bisa dikatakan sebagai buruh? Ataukah buruh itu merupakan sebutan untuk orang-orang yang bekerja di sebuah pabrik? 

Sebagai orang yang pernah juga mengalami kerja di pabrik :), tentunya, buat saya yang namanya buruh adalah sebetulnya kata lain untuk seorang karyawan yang bekerja di suatu perusahaan, sehingga seseorang yang juga bekerja dengan stelan jas dan berdasi kalau memang dia bekerja untuk sebuah perusahaan tentunya merupakan buruh juga. :) Yang menjadi pertanyaan sekarang adalah, apakah dari sisi kacamata yang lain sependapat dengan saya?

Faktor yang harus diperhatikan pada saat melakukan performance tuning

Ada beberapa hal yang harus diperhatikan apabila kita akan melakukan performance tuning terhadap suatu database, hal-hal tersebut antara lain:

 

1. Buat kesepakatan dengan user, sampai sejauh mana user akan menerima hasil dari proses tuning yang kita lakukan terhadap suatu database. Hal ini perlu untuk kita, sebagai executor, dalam pelaksaan proses tuning dalam database. Mengapa demikian? Apabila kita melaksanakan proses tuning pada database, tentunya harapannya adalah adanya peningkatan performance dari database tersebut. Untuk itulah maka perlu adanya satu batasan berupa kesepakatan dengan user database, jangan sampai suatu proses tuning database tidak di accept oleh user karena user merasa performance database yang telah kita lakukan tuning tidak significant perubahannya.
2. Identifikasi terlebih dahulu area-area mana saja pada database yang paling critical, apabila kita tidak segera melakukan tuning terhadap database tersebut, atau dengan kata lain prioritaskan proses tuning pada area-area yang dianggap paling critical.
3. Identifikasi bottleneck yang ada dalam sebuah atau beberapa query. Biasanya hanya karena sebuah query yang jelek menyebabkan performance menjadi turun secara keseluruhan.
4. Jika memungkinkan, review terlebih dahulu design dari database  yang akan kita tuning, apakah sudah bagus, sebelum kita melakukan tuning pada query-query terhadap database tersebut, karena apabila kita melakukan query tuning tetapi dari sisi design database  kurang bagus, maka tuning pada query akan tidak optimal, bahkan mungkin tidak ada impact sama sekali.
5. Pelajari strategi index yang sudah berjalan pada database yang akan kita tuning, dan lakukan improvement terlebih dulu pada area index ini.
6. Jika kita rasakan index yang ada sudah optimal, lakukan identifikasi fragmentation level dari index yang ada, dan pastikan index statistic selalu up-to-date.
7. Pelajari bagaimana cara kerja query optimizer, pelajari dan test beberapa bentuk tipe JOIN.
8. Hindari penggunaan sub-query (select in select).
9. Selalu gunakan UNION ALL daripada UNION, apabila memang ada operasi yang membutuhkan UNION
10. Evaluasi penggunaan trigger yang berdampak pada performance.
11. Hindari penggunaan SELECT ...... INTO sampai dengan kita yakin bahwa user yang terhubung ke database hanya kita sendiri atau proses yang kita lakukan hanya memerlukan waktu yang tidak lama. Jika sangat terpaksa gunakan INSERT .......SELECT.
12. Gunakan SET NOCOUNT ON dalam semua modular code kita (Stored Procedure), untuk mengurangi informasi yang diberikan server ke client dan untuk mengurangi beban network.
13. Jika memungkinkan, gantu semua query yang merupakan inline query menjadi stored procedure yang berparameter
14. Jika memungkinkan, gunakan temporary table untuk mengurangi jumlah record pada saat query. Jika temporary table tersebut di join dengan permanent table, buat index di dalam temporary table tersebut.
15. Optimalkan penggunaan loop, pindahkan semua proses yang tidak memerlukan pengulangan keluar loop.
16. Jangan gunakan cursor jika memang tidak sangat terpaksa, TSQL tidak dioptimalkan untuk memproses 1 record dalam satu waktu

Menghapus semua data dalam table pada sebuah database

Pernahkah anda merasa seperti wasting time untuk mengosongkan data sebuah database?Apalagi pada database tersebut banyak table yang di-reference dan ter-reference dengan table lainnya, tentunya anda harus menelusuri terlebih dahulu semua constraint yang ada dalam database tersebut, karena sebuah parent table tidak akan bisa dihapus datanya apabila data dalam parent table tersebut sudah di reference oleh child table-nya, dan constraint yang ada tidak memperbolehkan penghapusan secara cascade.

Dari kasus diatas bagaimana cara tercepat untuk melakukan penghapusan data pada sebuah database?

Kita lakukan dengan sedikit logic/algoritma sederhana dalam melakukan penghapusan ini.

Pertama, kita lakukan query terhadap system objects untuk me-list down semua user table yang ada pada database yang akan kita kosongkan datanya. Dalam Microsoft SQL Server 2005 ada system view yang fungsinya untuk menampilkan semua user table yang ada dalam sebuah database, kita gunakan view INFORMATION_SCHEMA.TABLES.

SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type = ‘BASE TABLE'

Query tersebut diatas akan menampilkan semua user table pada database aktif. Apabila kita jalankan query tersebut pada database AdventureWorks, maka SQL akan menampilkan list of user table yang ada pada database AdventureWorks.

Kedua, setelah kita dapatkan list of table tersebut, kita lakukan query untuk men-disable-kan constraint yang ada pada tiap-tiap table tadi. Tentunya supaya bisa terjadi proses looping/pengulangan untuk tiap-tiap table yang ada pada sebuah database tadi, maka kita harus tampung list of table tadi dalam sebuah cursor.

DECLARE cTable CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type = ‘BASE TABLE'

Script tersebut akan membuat sebuah cursor yang isinya adalah list of table dalam sebuah database aktif tadi.

Ketiga, kita harus lakukan sebuah loop dari cursor tadi untuk men-generate sebuat script query yang fungsinya untuk men-disable-kan constraint-constraint yang ada pada list of table tadi, kemudian script hasil generated tadi kita execute.

DECLARE @Query NVARCHAR(4000)

DECLARE @TableName SYSNAME

DECLARE cTable CURSOR FOR SELECT Table_Schema + ‘.' + Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type = 'BASE TABLE'

OPEN cTable

FETCH NEXT FROM cTable INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

        SET @Query = ‘ALTER TABLE ‘+@TableName+' NOCHECK CONSTRAINT ALL'

        EXEC sp_executesql @Query

        FETCH NEXT FROM cTable INTO @TableName

END

SET @Query = ‘ALTER TABLE ‘+@TableName+' NOCHECK CONSTRAINT ALL'

EXEC sp_executesql @Query

CLOSE cTable

Script diatas akan men-disable semua constraint yang ada pada tiap-tiap table yang akan kita kosongkan datanya.

Keempat, setelah kita men-disable-kan semua constraint, kita tinggal generate script untuk melakukan proses delete pada tiap-tiap table tadi dengan melakukan looping/pengulangan pada cursor yang tadi sudah kita buat.

OPEN cTable

FETCH NEXT FROM cTable INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

        SET @Query = ‘DELETE ‘+@TableName

        EXEC sp_executesql @Query

        FETCH NEXT FROM cTable INTO @TableName

END

SET @Query = ‘DELETE ‘+@TableName

EXEC sp_executesql @Query

CLOSE cTable

Script diatas akan menghapus semua user table tadi.

Kelima, setelah kita disable-kan constraint dan menghapus data yang ada pada tiap-tiap table, tentunya kita harus me-enable-kan kembali constraint yang ada pada tiap-tiap table tadi.

OPEN cTable

FETCH NEXT FROM cTable INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

        SET @Query = ‘ALTER TABLE ‘+@TableName + ‘ WITH CHECK CHECK CONSTRAINT ALL'

        EXEC sp_executesql @Query

        FETCH NEXT FROM cTable INTO @TableName

END

SET @Query = ‘ALTER TABLE ‘+@TableName + ‘ WITH CHECK CHECK CONSTRAINT ALL'

EXEC sp_executesql @Query

CLOSE cTable

DEALLOCATE cTable

Script diatas akan kembali me-enable-kan semua constraint yang ada pada tiap-tiap table.

Step by step diatas adalah step by step detailnya, sebetulnya proses looping/pengulangan bisa dilakukan hanya sekali saja, dimana proses generate scriptnya langsung ada 3, yaitu, untuk men-disable-kan constraint, men-delete data dan me-enable-kan kembali constraint.

Script lengkap dari artikel diatas (stored procedure) bisa didownload disini.

Kesimpulan, dengan sedikit bantuan system objects yang ada dalam database, kita bisa melakukan suatu trik tertentu yang bisa diaplikasikan pada database kita.

 

SQL Server Best Practice yang berhubungan dengan Database Files

1. Simpan file data dan log dalam disk yang terpisah.
2. Buat minimal 1 secondary filegroup di tiap-tiap database untuk memisahkan antara data system dan data aplikasi. Buat secondary filegroup tadi sebagai primary filegroup, sehingga setiap object yang di create tanpa menyebutkan filegroupnya akan disimpan dalam secondary filegroup tadi.
3. Buat beberapa file database untuk tiap-tiap filegroup database, jangan gunakan 1 file database untuk beberapa filegroup.
4. Jika memungkinkan pisahkan filegroup untuk data-data yang hanya memerlukan operasi read only dengan filegroup yang berisi data-data yang memerlukan operasi read-write. Dan tandai filegroup yang berisi data static menjadi READ-ONLY
5. Jika filegroup sudah tidak terpakai, lebih baik di remove, karena akan membantu mengurangi resiko pada proses backup dan restore.
6. Tempatkan tipe data TEXT atau IMAGE pada filegroups yang terpisah
7. Simpan file backup database dalam disk yang berbeda dengan disk tempat menyimpan database, apabila backup database tersebut disimpan dalam disk.
8. Jika ada table yang sangat besar, pertimbangkan untuk menggunakan table dan index partitioning.
9. Hindari penggunakan autogrowth pada database, usahakan selalu hitung capacity planning untuk setiap database yang kita buat. Jika sangat terpaksa harus gunakan autogrowth, pastikan tempat di disk available ketika database butuh growth dan tetapkan batasan maksimalnya.