Dealing With DDL and CDC
Chapters
- 00:00:00 – Introduction
- 00:01:28 – Script Overview and Problems
- 00:04:05 – DDL History Table and CDC Tables
- 00:06:37 – Handling New Capture Instances
- 00:09:00 – Backfilling Data Strategies
Full Transcript
Erik Darling here with Darling Data, and we are so excited to be talking about how to deal with DDL in CDC. We are just really beyond thrilled. So I’ve had to deal with this with some clients recently, and the problem with CDC, of course, is that if you change, add, drop columns from CDC tables, or tables that are covered by CDC, rather, the current change capture table does not reflect those changes. You have to do some work to figure it out. What I’ve got in this video is I’m just going to, a script that I can walk through, I can hit F5 on it. At least I think I can. At least the last time, I believe it is idempotent, so we will hit F5 on this, see what happens. We hit errors, so be it. It wouldn’t happen, it didn’t happen the first time. Sneaky. So I’m going to show you a couple, well, I guess walk through the problem first, and then show you a couple ways of handling it, and I will make this script available in a GitHub gist, because it is not worth canonizing in the Darling Data repo in any way, shape, or form.
So in the video description, way down here, next to me, you will see all sorts of helpful links. If you would like to hire me for consulting, perhaps you are having CDC problems of your own, you can do that. You can also purchase my training. I’ve got coupon codes and all sorts of stuff down there if you want to save some money on high quality SQL Server training, performance tuning stuff, things like that. You can become a supporting member of the channel if you so wish. If you feel like what you get out of here is worth like four bucks a month, you can do that. You can also find a link to ask me office hours questions, which I will do my best to answer. And of course, as always, if you enjoy this content, please do like, subscribe, tell a friend and all that, or else I’ll have to bring the gaffer tape to your house. I don’t know, maybe that might be fun. If you like free SQL Server performance monitoring, and how could you hate free, right? I’ve got my free SQL Server performance monitoring tool available. It’s over on GitHub. It’s at this link. That link is also down in the video description. And I’ve got some big changes coming out for that. I hope to be done. Hope to have some stuff out this week for you. And we will, we will, we’ll talk more in depth about that when it when it finally comes out.
But with that out of the way, let’s talk about this CDC business, because that is absolutely important. That is the that is the crux load bearing and arting its keep in this video. So this is the script that I’ll be handing off. It’s got some stuff in here to create tables and whatnot and enable CDC. And it’s got a description of some of the problems that you’ll run into. For example, if you add a column to the post table, it will not immediately show up, even if you force a CDC scan, it will not show up in the CDC table. However, we will have some columns in the DDL history table. And the DDL history table will will tell us what DDL occurred on the table that we are CDCing. So we’ll at least be able to figure that out. But we will not see the results of that DDL show up.
The other problem that we have is that if we create so something that I learned as I was going through this, and I’m a little surprised, because I’ve worked with CDC a lot, and this has never come up. I’d always done things in one way, and I didn’t realize there was another way is that CDC tables support up to three capture instances. So at any given time, you can be CDC capturing to three different places. This is wild. So like, but the problem is, if you start a new one, it only starts from like, it only starts getting data from when you turn it on, and it will reflect the new state of the table, but it won’t have any CDC data from before.
So depending on like the cadence of how you’re pulling data out of CDC, that could be no good, right? Because you need to make sure that you got everything you needed before you start pulling from a new capture instance. And like the thing with a lot of third party tools, and even scripts that, you know, people use out in the wild that sort of drain data off of CDC tables, put them in another source, like, I don’t know, Snowflake or something, is they sort of hard code the change capture instance, or they’re not wired up to look for like multiple change capture instances for a table. And so like, even like the metadata discovery is a little sloppy on those, like they don’t, like, it doesn’t always work the way you want it to.
So like, you know, like, you really have to be careful about juggling the two capture instances, because at any given time, you can switch capture instances, and that might not be fun. So you do have to be careful in there. But you like when you start the new one, you don’t get stuff from the old one. So you basically have two strategies that you can use to backfill or to port data over, you could very easily start the second change data capture instance, and then move data into the new one.
But one thing you’re going to have to do is make sure is get the start LSN from the current capture instance, and then update the change tables table to make the start LSN for that for the new one match the start LSN for the old one. So that you like the next time you like run one of the CDC functions to get it, you actually get all the all the data that you want. Now, depending on how your tooling or scripting works to get this, you might want to do this.
And you might want to rename the capture instance from the new table to that of the old table, and then get rid of the old cap, the original capture instance so that there’s nothing, there’s no two capture instances for things to get confused about. That’s something that I’ve run into and something that I’ve had to protect against. So it’s out here for you as well.
And then another thing that gets or another strategy for that would just be to so like what like the simple sort of like the simpler way of doing that, depending on depending on your preferences and that this one, this one has downsides to which we’ll talk about. So one thing that you could do is instead of starting a new capture instance, moving the data over, all that stuff, you could disable and re-enable CDC, but you have to save your data off. And if you have a lot of data stored in CDC, that could not be fun.
And the other kind of annoying one about this is that like you do need a transaction. You do need to make sure that like you don’t allow changes to the base table that you might miss while it’s disabled. So what you do in this one is a little bit different where you would sort of create a backup table of your CDC contents.
You would grab the start LSN from the current table, then start a transaction and you do something like this, right? Select top one from comments with tablock X serializable to make sure nothing changes your table. Disable CDC for the table, add the column that you want, and then re-enable CDC with that column in there.
And then you would have to insert the data from your backup table. You would have to get all the data from your backup table into the re-enabled CDC table and then update the start LSN to the original LSN that you pulled up before that. And then you would be free to commit the transaction and move on.
So once you do that, though, everything is back in a good state. Again, the approach that you would take here does depend a bit on exactly how your ETL tooling or scripting, how much data is in exchange data capture, things like that. So there are some things to think about, but you at least have two approaches.
Choose the one that best suits you. That’s my advice there. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you were just as thrilled to hear about CDC as I was to talk about CDC.
Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.