The Power of Knowladge

Change data capture, fitur SQL 2008 yang sangat berguna...

Kemarin buka buka lagi catatan waktu ikut TPrep Project Untuk  SQL Server 2008 di Redmond Tahun yang lalu, ini saya lakukan karena memang Hari Kamis kemarin (22/10/2009) berkesempatan untuk membawakan session microsoft di Acara Metro Data Solution Day di Hotel Shangrila Jakarta. Kebetulan materi yang akan di bawakan berkaitan dengan fitur - fitur SQL yang berhubungan dengan pengolahan data dalam ukuran besan sehingga judul besar dari session yang saya adalah SQL Data Warehouse Penta Byte Data Solutions.  

Saya coba share sedikit apa yang saya presentasikan di blog saya ini, khususnya fitur-fitur yang berhungan dengan pengelolaan data yang memiliki ukuran sangat besar dengan memanfaatkan fitur fitur terbaru dari MS SQL 2008.

Change data capture adalah topik pertama yang saya sampaikan disini, dimana fitur baru di SQL Server 2008 ini memudahkan kita untuk melakukan capturing terhadap perubahan yang terjadi bada sebuah table dalam sebuah database. Dengan CDC ini memungkin semua operasi yang dilakukan pada sebuah table bisa kita tracking historynya dari operasi yang dilakukan, dari mulai data pertama kali di masukkan, perubahan terhadap data di setiap kolom hingga data terakhir yang ada pada sebuah table

untuk membuat CDC ini, kita bisa lakukan di SQL Server 2008 dengan terlebih dahulu mengaktifkan fitur CDC pada sebuah database dengan perintah berikut ini

EXEC sys.sp_cdc_enable_db;

GO

Perintah ini akan mengaktifkan fitur CDC di database yang kita akan buatkan Change Data Capture-nya, ini bisa dilihat dari adanya file tambahan pada database kita di bagian system table seperti

• cdc.captured_columns
• cdc.change_tables
• cdc.ddl_history
• cdc.index_columns
• cdc.lsn_time_mapping

Dengan CDC ini memungkinkan kita untuk dapat melakukan pencatatan terhadap perubahan data yang terjadi tanpa harus membuat table auditlog sendiri maupun membuat mekanisme trigger untuk mencatat sejarah perubahan data baik oleh aplikasi maupun oleh DBA secara back end

Berikut contoh scipt untuk fitur change data capture di SQL Server 2008, silahkan mencoba

--set current database context
USE [AdventureWorks]

GO

--append new user column to the HumanResources.Employee Table
ALTER TABLE humanresources.employee
ADD [User] NVARCHAR(50)

GO

--disable any triggers
DISABLE Trigger ALL ON HumanResources.employee

GO

--enable CDC for AW
EXEC Sp_cdc_enable_db

GO

--enable CDC for HumanResources.Employee
EXEC Sp_cdc_enable_table
  humanresources ,
  employee ,
  NULL ,
  1 ,
  dbo

GO

--create sample UDF to return change data
CREATE FUNCTION [dbo].[Udf_employee]
               (@start_time DATETIME,
                @end_time   DATETIME)
RETURNS @Employee TABLE(employeeid       INT,
                        nationalidnumber NVARCHAR(15),
                        contactid        INT,
                        managerid        INT,
                        title            NVARCHAR(50),
                        birthdate        DATETIME,
                        maritalstatus    NCHAR(1),
                        gender           NCHAR(1),
                        hiredate         DATETIME,
                        salariedflag     [FLAG],
                        vacationhours    SMALLINT,
                        sickleavehours   SMALLINT,
                        currentflag      [FLAG],
                        rowguid          UNIQUEIDENTIFIER,
                        [user]           NVARCHAR(50),
                        cdc_operation    VARCHAR(1))
AS
  BEGIN
    --declare local variables to hold LSNs
    DECLARE  @from_lsn BINARY(10),
             @to_lsn   BINARY(10)
    
    --Map the time interval to a change data capture query range.
    IF (@start_time IS NULL)
      BEGIN
        SELECT @from_lsn = sys.Fn_cdc_get_min_lsn('HumanResources_Employee')
      END
    ELSE
      BEGIN
        SELECT @from_lsn = sys.Fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)
      END
    
    IF (@end_time IS NULL)
      BEGIN
        SELECT @to_lsn = sys.Fn_cdc_get_max_lsn()
      END
    ELSE
      BEGIN
        SELECT @to_lsn = sys.Fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)
      END
    
    --if same then exit
    IF (@from_lsn = sys.Fn_cdc_increment_lsn(@to_lsn))
      BEGIN
        RETURN
      END
    
    -- Query for change data
    INSERT INTO @Employee
    SELECT employeeid,
           nationalidnumber,
           contactid,
           managerid,
           title,
           birthdate,
           maritalstatus,
           gender,
           hiredate,
           salariedflag,
           vacationhours,
           sickleavehours,
           currentflag,
           rowguid,
           [user],
           CASE __$operation
             WHEN 1
             THEN 'D'
             WHEN 2
             THEN 'I'
             WHEN 4
             THEN 'U'
             ELSE NULL
           END AS cdc_operation
    FROM   cdc.Fn_cdc_get_net_changes_humanresources_employee(@from_lsn,@to_lsn,'all')
    
    RETURN
  END

GO

--sample DML statement
UPDATE humanresources.employee
SET    title = 'Marketing Director',
       [User] = system_user
WHERE  employeeid = 13

--get the net change of the sample DML including the User
SELECT *
FROM   [dbo].[Udf_employee](NULL,NULL)

Share this post: | | | |