April 25, 2024 at 3:54 pm
Have anyone implemented CDC, and if so, what has been your experience with it? We are considering using this CDC data as the foundation for our ETL process, which will also support our Audit database. We have around 100 tables that we need to put in CDC and then use them in our ETL process. Any pros/cons (performance, scalability, maintenance, storage etc.) will be greatly appreciated.
April 25, 2024 at 5:17 pm
Yes, I've used CDC. I ended up creating my own proc to view change data (basically pre-prepping the params for the MS proc) because the MS proc itself is so crash prone. Other than that, it works well.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 25, 2024 at 10:27 pm
works fine with a few caveats
performance impact will depend on your activity and server spec - bad server with lots of activity and it will be noticeable.
using a custom view/proc to process data is a must as one of the supplied MS bits of code fails a significantly number of times when getting the correct LSN's to process
another issue you will have is when there is a need to change one of the underlying tables - in order not to loose changes you will need to implement a process that does the ETL just before deployment window, removes CDC from affected table, deploys changes and re-adds CDC - or use the 2 table CDC allows and swap ETL to use both during a period of time (e.g. exhaust the old table entries from CDC table at which time that should be removed (and reused for next deployment)
April 26, 2024 at 11:42 am
I’ve used it in the past and it works but there are better dedicated products available but cost is the issue generally
CDC doesn’t play nicely with certain features such as AGs so just have the interoperability in mind when looking to use it, MS kb below
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply