05 Oktober 2011

Change Data Capture pada SQL Server 2008

Pendahuluan

Buat Anda yang menggeluti bidang data warehouse dan mungkin telah membaca artikel CDC saya sebelumnya, pastinya menyadari bahwa salah satu tantangan tersulit dari proyek data warehouse / data consolidation adalah pengambilan range perubahan data (delta) dari snapshot terakhir dari sumber data.

Kasus tersebut di atas sering diistilahkan dengan Change Data Capture (CDC). Dan kabar gembira untuk para komunitas SQL Server karena saat ini SQL Server versi 2008 (Enterprise Edition) sudah mendukung fitur tersebut secara langsung tanpa perlu adanya aplikasi third party. Ini menjadikan SQL Server 2008 pilihan yang sangat baik sebagai database transaksional (OLTP).

Pada artikel berikut kali ini saya coba sharing mengenai mekanisme kerja dan contoh cara konfigurasi CDC SQL Server 2008 dan penggunaannya sehingga bisa kita manfaatkan secara optimal.

Arsitektur dan Mekanisme Kerja

Beberapa hal mendasar yang perlu diketahui tentang fitur CDC ini adalah sebagai berikut :
  • Konfigurasi CDC adalah untuk pemantauan per tabel
  • Proses pemantauan perubahan data dilakukan oleh SQL Server Agent melalui mekanisme job. Dengan demikian SQL Server Agent harus aktif.
  • Berlaku untuk database recovery model simple, full ataupun bulk-logged. Ini pada awalnya membuat saya salah asumsi bahwa CDC tidak boleh dalam model simple.
  • Sebaiknya memiliki key atau kombinasi key yang unik. Hal ini berguna untuk mendapatkan "net changes".
Ilustrasi mekanisme kerja CDC pada SQL Server 2008 - seperti ditunjukkan pada gambar - adalah sebagai berikut :
  • Database telah dikonfigurasi untuk mengaktifkan fitur CDC. Konfigurasi ini akan menghasilkan beberapa table system CDC.
  • Table sumber (A) diaktifkan untuk CDC. Operasi ini akan menghasilkan :
    • capture job pada SQL Server Agent.
    • table baru (A') yang merupakan table untuk menyimpan data historis / perubahan pada table sumber.
    • satu atau lebih function untuk mengakses table A' ini.
  • Setelah database dan table diaktifkan maka job juga akan secara aktif memantau setiap operasi DML : INSERT, UPDATE dan DELETE pada transaction log yang ditujukan ke table sumber.
  • Jika operasi DML tersebut di atas terpantau maka capture job pada SQL Server Agent akan memasukkan data dan jenis operasi perubahan pada table historis (A').
  • Untuk melakukan query terhadap range data historis yang terjadi maka kita mengakses function CDC yang sudah terdefinisikan untuk table A' tersebut.


Dari gambaran tersebut di atas, kita akan masuk ke rincian tahap-tahap penggunaan CDC pada bagian berikutnya dari artikel ini.

Informasi Status CDC pada Database

Konfigurasi pengaktifan database untuk CDC adalah langkah pertama yang harus dilakukan. Tetapi sebelumnya Anda tentu perlu mengetahui apakah database tersebut sudah diaktifkan atau belum untuk fitur ini. 

Untuk melakukan hal tersebut cobalah eksekusi query berikut di bawah ini. Jika hasil eksekusi dari kolom "is_cdc_enabled" bernilai 0, artinya database tersebut belum diaktifkan untuk fitur CDC. Sebaliknya, jika kolom tersebut bernilai 1 maka database tersebut sudah diaktifkan untuk fitur CDC.

Informasi Aktif Tidaknya Fitur CDC pada Database

select name, is_cdc_enabled from sys.databases 
where name = 'nama_database_anda'

Sebagai contoh, berikut adalah hasil eksekusi query tersebut terhadap database saya melalui SQL Server Management Studio.



Dan perhatikan struktur database tersebut di bawah ini - dimana belum ada tambahan system tables dan functions.


Pengaktifan CDC pada Database

Untuk mengaktifkan CDC pada database kita menggunakan stored procedure sys.sp_cdc_enable_db pada database aktif.

EXEC sys.sp_cdc_enable_db

Jika kita telah mengsekusi query tersebut dan sukses, cobalah sekarang periksa informasi status database kita - yang seharusnya sudah aktif dengan kolom "is_cdc_enabled" bernilai 1.


Dan sekarang coba perhatikan table-table system yang terbentuk dan juga adanya tambahan schema baru, yaitu cdc.



Jika kita lakukan query pada tabel-tabel yang baru terbentuk tersebut maka isinya sama sekali belum ada.


Pengaktifan Table Sumber (Source Table)

Setelah database aktif, maka sekarang saatnya untuk juga mengaktifkan CDC pada tabel yang akan dipantau. Untuk contoh berikut, table yang kita pilih adalah table "tr_penjualan" dari schema dbo. 

Stored procedure yang kita gunakan kali ini adalah sys.sp_cdc_enable_table. Berikut adalah contoh eksekusi stored procedured tersebut.

Aktifasi CDC pada level Table

EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name   = 'tr_penjualan',
@role_name     = 'role_untuk_cdc',
@capture_instance = 'tr_penjualan_historis',
@supports_net_changes = 0

Catatan

Perhatikan konfigurasi @support_net_changes diberikan nilai 0 karena kebetulan table yang digunakan tidak memiliki column key yang unik.


Jika eksekusi tersebut di atas sukses, maka perhatikan bahwa akan ada satu tabel historis (change table), role dan function terkait yang terbentuk. Ttable yang terbentuk memiliki akhiran "_CT" dan function-nya sendiri memiliki prefix "cdc.fn_cdc_get_all_changes_" dari nama instance yang kita tentukan sebelumnya (tr_penjualan_historis).


Nah, pada titik ini kembali coba lakukan 5 query berikut dan perhatikan hasilnya. Terlihat konfigurasi untuk pemantauan tabel "tr_penjualan_historis".

SELECT * FROM [PHI].[cdc].[captured_columns];
SELECT * FROM [PHI].[cdc].[change_tables];
SELECT * FROM [PHI].[cdc].[ddl_history];
SELECT * FROM [PHI].[cdc].[index_columns];
SELECT * FROM [PHI].[cdc].[lsn_time_mapping];
SELECT * FROM [PHI].[dbo].[systranschemas];


Lakukan juga "select..." query untuk table "cdc.tr_penjualan_historis_CT". Apakah Anda mendapatkan sejumlah row atau table tersebut masih kosong ?

DML dan Query terhadap Changed Data

Sekarang kita akan coba melakukan operasi update pada tr_penjualan_historis. Pada contoh berikut penulis merubah data "kode-kasir" dari nilai "039-127" ke "039-128".


Oke, sekarang kita akan lihat hasil capture dari CDC melalui query berikut dan perhatikan hasilnya.

CDC Function Query

declare @begin_lsn binary(10), @end_lsn binary(10)

set @begin_lsn = sys.fn_cdc_get_min_lsn('tr_penjualan_historis');

set @end_lsn = sys.fn_cdc_get_max_lsn();

-- Query menggunakan function cdc
select * from cdc.fn_cdc_get_all_changes_tr_penjualan_historis(@begin_lsn, @end_lsn, 'all');


Terlihat dari hasil eksekusi query bahwa ada operasi update ( _$operation = 4 )  terhadap tabel dengan semua nilai-nilai terakhir hasil update.

Dan juga perhatikan bahwa parameter untuk melakukan query adalah variable @begin_lsn dan @end_lsn sebagai id range data. LSN (log sequence number) adalah penanda row unik pada level terendah dari transaction log. Jika ada perubahan data maka row akan ditambahkan ke transaction log dengan ID LSN baru.

Untuk informasi lebih lanjut mengenai konsep LSN ini dapat dilihat pada link berikut : http://msdn.microsoft.com/en-us/library/ms190411.aspx.

Delete DML

Sekarang kita akan kembali mengulagi prosedur pada seksi sebelumnya dengan operasi delete. Sebagai contoh penulis akan menghapus satu row seperti ditunjukkan pada gambar berikut ini.


Jalankan kembali CDC Function Query di atas dan lihat hasilnya. Perhatikan terbentuk row baru dengan _$operation sekarang bernilai 1 (delete) disertai semua nilai field dari row yang dihapus pada source table.


Pembaca, sampai sejauh ini terllihat menarik bukan fitur ini ? Kita cukup menjalankan select query biasa ke function dengan parameter lsn sederhana. Cobalah lanjutkan contoh ini dengan operasi insert.

Field __$operation

Berikut adalah penjelasan arti nilai pada field __$operation :
1 = penghapusan / delete
2 = penambahan / insert
3 = perubahan / update (nilai lama / old values)
4 = perubahan / update (nilai baru / new values)


Query Berdasarkan Range Waktu

Contoh di atas pada prakteknya tidak begitu membantu. Query hanya menghasilkan semua perubahan dari awal mulai diaktifkannya CDC sampai perubahan terakhir. Sedangkan yang kita butuhkan sebenarnya adalah perubahan berdasarkan range waktu tertentu.
Apakah hal tersebut memungkinkan disini ? Tentu saja !

Berikut adalah contoh query dengan range waktu tgl 28 Maret 2010 jam 10:00 s/d jam 10:50. Perhatikan penggunaan function fn_cdc_map_time_to_lsn dan operator 'smallest greater than' / 'largest less than or equal' (lihat boks untuk keterangan penggunaan).

Query CDC Range Waktu

declare @begin_time datetime, @end_time datetime
declare @begin_lsn binary(10), @end_lsn binary(10)

SET @begin_time = '2010-03-28 10:00:00.000';
SET @end_time   = '2010-03-28 10:50:00.000';

SET @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);

SET @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

-- Query menggunakan function cdc
select * from cdc.fn_cdc_get_all_changes_tr_penjualan_historis(@begin_lsn, @end_lsn, 'all');


Hasil eksekusi dari query di atas.


'smallest greater than' dan 'largest less than or equal'

Pada saat penggunaan function fn_cdc_map_time_to_lsn sering sekali kita mengalami kesulitan memahami operator teks dari fungsi ini. Contoh dari artikel kita ini adalah 'smallest greater than' dan 'largest less than or equal'.

Berikut adalah ilustrasi yang sering penulis lakukan untuk membuat pemahaman terhadap operator tersebut. Andaikan saya memiliki data dengan tanggal 1 s/d 31 Januari 2010. Dan patokan tanggal saat ini adalah 10 Januari 2010 maka :
  1. Jika kita menggunakan operator 'smallest greater than' maka pertama kita perhatikan dulu bagian 'greater than' (lebih besar). Hasil komparasi terhadap titik referensi kita menghasilkan kumpulan tanggal berikut : 11 Januari 2010, 12 Januari 2010, 13 Januari 2010, dstnya. Nah, mana yang kita ambil ? Sekarang perhatikan bagian 'smallest' (terkecil) dari kumpulan data ini ada dimana ? Jawabannya : 11 Januari 2010 !
  2. Demikian juga untuk operator 'largest less than or equal', pertama kita perhatikan bagian 'less than or equal'. Bagian tersebut akan menghasilkan data berikut : 1 Januari 2010, 2 Januari 2010.... 10 Januari 2010. Sekarang perhatikan bagian 'largest' (terbesar)dari kumpulan data ini ada dimana ? Jawabannya : 10 Januari 2010 !

Penutup

Change Data Capture (CDC) pada SQL Server 2008 Enterprise Edition merupakan fitur penting pada rilis tersebut. CDC dapat kita gunakan untuk mengambil semua perubahan data pada suatu table baik melalui operasi insert (penambahan), update (koreksi) maupun delete (penghapusan). 

Dengan adanya fitur ini tentunya mengurangi overhead dari solusi sebelumnya seperti penggunaan trigger. Dan bagusnya, fitur ini secara native tersedia tanpa adanya tambahan instalasi komponen third party.

Sebagai praktisi data warehouse, tentunya ini akan sangat membantu sekali jika database transaksional adalah SQL Server 2008 Enterprise Edition.

Dan melalui artikel ini penulis mencoba membagi penggunaan CDC ini dengan  bahasan mulai dari mekanisme kerja, konfigurasi fitur sampai contoh penggunaannya sendiri.


Demikian... semoga bahasan dari artikel ini bisa berguna buat kita semua.

Masukan, saran dan komentar dapat dialamatkan ke info@phi-integration.com atau melalui forum komunitas kami di http://groups.google.com/group/indo-bi.
Source code atau query yang digunakan dalam artikel ini dapat di-download di : http://phi-integration.googlecode.com/svn/trunk/sql-server-2008-scripts/cdc/

Sumber Referensi

Diposting oleh adulyajed di 18.04

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)