suherman

Change Data Capture (CDC)

CDC merupakan salahsatu fitur baru yang hadir dalam SQL Server 2008 keatas. Fitur ini dapat difungsikan untuk meng-capture perubahan yang terjadi pada table di SQL Server pada aktivitas Insert, Update dan Delete. tentunya hal ini akan sangat membantu seorang DBA saat melakukan suatu investigasi sebuah tabel, tidak perlu repot lagi dengan trigger, join query atau timestamp column untuk menangkap perubahan data.

Untuk dapat menggunakan fitur CDC. pertama kita harus meng-enabled-kan nya pada level database terlebih dahulu. berikut ini caranya. dalam contoh ini saya menggunakan database Adventureworks dan pastikan SqlServer Agent telah aktif.

use AdventureWorks

exec sys.sp_cdc_enable_db

lalu setelah code diatas telah di execute, kita cek hasil dengan query berikut

select is_cdc_enabled from sys.databases 

where name='Adventureworks'

Jika hasilnya 1, maka fitur CDC pada database adventureworks telah di enabled-kan.

Lalu kita berlanjut pada tahap selanjutnya yaitu meng-enabled-kan CDC pada level tabel dengan query sebagai berikut :

exec sys.sp_cdc_enable_table 

@source_schema='production',

@source_name='Culture',

@role_name='Culture_CDC'

Kemudian setelah code diatas telah di execute, kita bisa periksa apakah CDC telah di-enabled-kan dengan query berikut :

select is_tracked_by_cdc from sys.tables where name='culture'

jika hasilnya 1, maka fitur CDC pada tabel production.culture telah di enabled-kan.

Setelah kita mengaktifkan CDC pada level database dan table, akan ada perubahan pada System Tables di database Adventure works seperti pada gambar berikut :

mari kita coba beberapa transaksi terhdap table production.culture.

insert into Production.Culture

  (CultureID,Name,ModifiedDate)

  values

  ('id','Indonesia','2010-06-01 00:00:00.000')

  go

  update Production.Culture set

  Name='Ina' where CultureID='id'

  go

  delete from Production.Culture

  where CultureID='id'

  go

sekarang kita lihat bagaimana CDC merekam transaksi-transaksi diatas. saya menggunakan query terhadap table yang terbentuk di System Tables.

SELECT *  FROM cdc.production_Culture_CT

hasilnya adalah sebagai berikut :

pada field __$operation terdapat angka 1,2,3 dan 4. angka 1 mewakili aktivitas DELETE, angka 2 mewakili aktivitas INSERT, angka 3 mewakili UPDATE (before image) dan angka 4 mewakili aktivitas UPDATE (after image).

Jadi bila kita perhatikan pada row ke 2 dan 4. operation 3 lalu dilanjutkan ke operation 4. disitu tampak perubahan proses updatenya ada pada coloumn Name dimana ‘Indonesia’ berubah menjadi ‘Ina’.

Bila kita tidak memerlukan aktivitas CDC untuk table production.culture ini, dapat di disabled-kan dengan query berikut :

exec sys.sp_cdc_disable_table

  @source_schema='production',

  @source_name='culture',

  @capture_instance='production_culture'

  go

demikian, semoga bermanfaat.

HTTP Endpoints in SQL Server 2005/2008

ternyata di SQL Server 2005/2008 kita bisa membuat layanan data dalam media web services dan ajaibnya kita tidak perlu menginstall IIS dari Host SQL Server tersebut berdiri. bagaimana caranya ? mari kita coba sama-sama.

Pertama kita akan menyiapkan 2 buah method di web services yaitu method pertama menampilkan semua daftar product berikut informasi ROP (reorder point) nya.

Method kedua adalah menampilkan nama sebuah product bila kita mencarinya berdasarkan productID.

untuk kebutuhan method pertama kita tuangkan siapkan sebuah Store Procedure seperti berikut :

use AdventureWorks

go

create procedure dbo.sp_GetProductROP

as

select ProductID,Name,ProductNumber,

SafetyStockLevel,ReorderPoint from 

Production.Product

go

Kemudian untuk kebutuhan method kedua kita siapkan sebuah User Defined Function seperti berikut :

create function dbo.FnProduct(@productID int)

returns varchar(50)

as

begin

declare @Nama varchar(50);

select @Nama=name from Production.Product

where ProductID=@productID

 IF (@Nama IS NULL) 

        SET @Nama = 'TIDAK DITEMUKAN!'

    RETURN @Nama

end

go

Selanjutnya adalah kita membuat HTTP Endpoints nya sebagai berikut :

create endpoint GetProductROP

state=started

as HTTP

(

path=N'/ProductROP',

ports=(clear),

authentication=(NTLM, kerberos, integrated),

site=N'suherman-pc',

clear_port=80,

compression=disabled)

For SOAP

(

webmethod 'ProductROP'(

    name=N'Adventureworks.dbo.sp_getProductROP',

    schema=default,

    format=all_results),

webmethod 'CariNamaProduct'(

    name=N'Adventureworks.dbo.FnProduct',

    schema=default,

    format=all_results),    

Batches=disabled,

WSDL=Default,

Sessions=Disabled,

session_timeout=60,

database=N'Adventureworks',

namespace=N'http://adventureworks/ProductROP',

schema=standard,

character_set=XML

)

sebenarnya sampai langkah ini kita telah berhasil membuat layanan data dalam bentuk Web Services. akan saya buktikan di Visual Studio pada saat melakukan add Services Reference seperti dibawah ini

seperti pada gambar, web services dapat dipanggil menggunakan address http://suherman-pc/ProductROP?wsdl yang memunculkan 2 buah operation method.

Selamat mencoba n semoga bermanfaat.

Cell and Column-Level Encryption

kadangkala kita menyimpan sebuah data kedalam sebuah tabel di database yang sifat datanya sangat rahasia misalnya menyimpan data username dan password dari pengguna sebuah aplikasi.

dalam artikel ini saya mencoba berbagi cara mengenai melakukan enkripsi dengan menggunakan asymmetric key.

pertama kita siapkan sebuah tabel T_Pengguna yang berisi field Fusername dan Fpassword

kemudian langkah selanjutnya kita buat terlebih dahulu asymmetric key nya terlebih dahulu.

nah sampai sini kita bisa mulai ber operasi untuk melakukan enkripsi terhadap sebuah cell atau field. kita akan coba input 2 buah user :

nah sampai sini kita telah berhasil melakukan enkripsi untuk Password-password tadi yang dimasukkan kedalam tabel T_Pengguna.

Mungkin untuk kasus ini sebenarnya tidak perlu dilakukan proses dekripsi. Namun apabila suatu waktu diperlukan perlakuan deskripsi field FPassword dapat dilakukan dengan T-SQL berikut :

Ok, selamat mencoba !