{"id":15,"date":"2014-01-02T21:06:26","date_gmt":"2014-01-02T13:06:26","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=15"},"modified":"2014-01-02T21:06:26","modified_gmt":"2014-01-02T13:06:26","slug":"change-data-capture-cdc","status":"publish","type":"post","link":"https:\/\/suherman.asia\/w2\/change-data-capture-cdc.html","title":{"rendered":"Change Data Capture (CDC)"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

\n
\n
use<\/span> AdventureWorks<\/pre>\n
<\/p>\n
exec<\/span> sys.sp_cdc_enable_db<\/pre>\n
<\/div>\n<\/div>\n
lalu setelah code diatas telah di execute, kita cek hasil dengan query berikut<\/p>\n
\n
\n
select<\/span> is_cdc_enabled from<\/span> sys.databases <\/pre>\n
<\/p>\n
where<\/span> name='Adventureworks'<\/span><\/pre>\n
<\/div>\n<\/div>\n
Jika hasilnya 1, maka fitur CDC pada database adventureworks telah di enabled-kan.<\/p>\n
Lalu kita berlanjut pada tahap selanjutnya yaitu meng-enabled-kan CDC pada level tabel dengan query sebagai berikut :<\/p>\n
\n
\n
exec<\/span> sys.sp_cdc_enable_table <\/pre>\n
<\/p>\n
@source_schema='production'<\/span>,<\/pre>\n
<\/p>\n
@source_name='Culture'<\/span>,<\/pre>\n
<\/p>\n
@role_name='Culture_CDC'<\/span><\/pre>\n
<\/div>\n<\/div>\n
Kemudian setelah code diatas telah di execute, kita bisa periksa apakah CDC telah di-enabled-kan dengan query berikut :<\/p>\n
\n
\n
select<\/span> is_tracked_by_cdc from<\/span> sys.tables where<\/span> name='culture'<\/span><\/pre>\n
<\/div>\n<\/div>\n
jika hasilnya 1, maka fitur CDC pada tabel production.culture telah di enabled-kan.<\/p>\n
Setelah kita mengaktifkan CDC pada level database dan table, akan ada perubahan pada System Tables di database Adventure works seperti pada gambar berikut :<\/p>\n
\"CDC\"<\/a><\/p>\n
mari kita coba beberapa transaksi terhdap table production.culture.<\/p>\n
\n
\n
insert into<\/span> Production.Culture<\/pre>\n
<\/p>\n
  (CultureID,Name,ModifiedDate)<\/pre>\n
<\/p>\n
  values<\/span><\/pre>\n
<\/p>\n
  ('id'<\/span>,'Indonesia'<\/span>,'2010-06-01 00:00:00.000'<\/span>)<\/pre>\n
<\/p>\n
  go<\/span><\/pre>\n
<\/p>\n
  update<\/span> Production.Culture set<\/span><\/pre>\n
<\/p>\n
  Name='Ina'<\/span> where<\/span> CultureID='id'<\/span><\/pre>\n
<\/p>\n
  go<\/span><\/pre>\n
<\/p>\n
  delete<\/span> from<\/span> Production.Culture<\/pre>\n
<\/p>\n
  where<\/span> CultureID='id'<\/span><\/pre>\n
<\/p>\n
  go<\/pre>\n
<\/div>\n<\/div>\n
sekarang kita lihat bagaimana CDC merekam transaksi-transaksi diatas. saya menggunakan query terhadap table yang terbentuk di System Tables.<\/p>\n
\n
\n
SELECT<\/span> *  FROM<\/span> cdc.production_Culture_CT<\/pre>\n
<\/div>\n<\/div>\n
hasilnya adalah sebagai berikut :<\/p>\n
\"image\"<\/a><\/p>\n
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).<\/p>\n
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 \u2018Indonesia\u2019 berubah menjadi \u2018Ina\u2019.<\/p>\n
Bila kita tidak memerlukan aktivitas CDC untuk table production.culture ini, dapat di disabled-kan dengan query berikut :<\/p>\n
\n
\n
exec<\/span> sys.sp_cdc_disable_table<\/pre>\n
<\/p>\n
  @source_schema='production'<\/span>,<\/pre>\n
<\/p>\n
  @source_name='culture'<\/span>,<\/pre>\n
<\/p>\n
  @capture_instance='production_culture'<\/span><\/pre>\n
<\/p>\n
  go<\/pre>\n
<\/div>\n<\/div>\n
demikian, semoga bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u2026<\/p>\n
 Continue reading<\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[6,87],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-cdc","tag-sql-server"],"yoast_head":"\nChange Data Capture (CDC) - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Change Data Capture (CDC) - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"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 \u2026 Continue reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-02T13:06:26+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#article\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"Change Data Capture (CDC)\",\"datePublished\":\"2014-01-02T13:06:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html\"},\"wordCount\":291,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png\",\"keywords\":[\"CDC\",\"SQL Server\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html\",\"url\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html\",\"name\":\"Change Data Capture (CDC) - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png\",\"datePublished\":\"2014-01-02T13:06:26+00:00\",\"breadcrumb\":{\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage\",\"url\":\"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png\",\"contentUrl\":\"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Change Data Capture (CDC)\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"https:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Change Data Capture (CDC) - Suherman Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html","og_locale":"en_US","og_type":"article","og_title":"Change Data Capture (CDC) - Suherman Blog","og_description":"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 \u2026 Continue reading","og_url":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-01-02T13:06:26+00:00","og_image":[{"url":"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png","type":"","width":"","height":""}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#article","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"Change Data Capture (CDC)","datePublished":"2014-01-02T13:06:26+00:00","mainEntityOfPage":{"@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html"},"wordCount":291,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage"},"thumbnailUrl":"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png","keywords":["CDC","SQL Server"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#respond"]}]},{"@type":"WebPage","@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html","url":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html","name":"Change Data Capture (CDC) - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage"},"image":{"@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage"},"thumbnailUrl":"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png","datePublished":"2014-01-02T13:06:26+00:00","breadcrumb":{"@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#primaryimage","url":"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png","contentUrl":"http:\/\/mugi.or.id\/cfs-file.ashx\/__key\/CommunityServer.Blogs.Components.WeblogFiles\/suherman\/CDC_5F00_thumb_5F00_20718AA0.png"},{"@type":"BreadcrumbList","@id":"http:\/\/suherman.asia\/w2\/change-data-capture-cdc.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"Change Data Capture (CDC)"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"https:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":1,"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"predecessor-version":[{"id":16,"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/15\/revisions\/16"}],"wp:attachment":[{"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}