So lets say that you want to add/drop a column from an existing Capture Instance but don’t want to lose any data that is currently in the capture instance and also you cannot afford to have any downtime, how do you achieve this. Please read on to find out.
So Microsoft have provided you with the ability to be able to have two capture instances on a table at any one time, I’d imagine to allow for this very situation.
For purposes of illustration lets say I have a table called dbo.Country and I now want the column countryid to be included in the already existing capture instance.
Step 1 – Create a 2nd version of the capture instance to now include the column countryid
----Step 1 - Create a 2nd version of the capture instance, we now want the capture instance to include countryid
--Enabling CDC on a table with net changes turned on
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'country',
@role_name = NULL,
@filegroup_name = NULL,
@supports_net_changes = 1,
@captured_column_list = 'countryname,countryid',
@capture_instance = 'dbo_country_2'
Step 2 – Now copy all of the data from the original capture instance (cdc.dbo_country_ct) into the newly created capture instance (cdc.dbo_country_2_ct).
INSERT INTO cdc.dbo_Country_2_CT
(__$start_lsn, __$end_lsn,__$seqval,__$operation,__$update_mask,countryname)
SELECT
__$start_lsn,
__$end_lsn,
__$seqval,
__$operation,--
__$update_mask,
countryname
FROM cdc.dbo_country_ct
Step 3 – Make a note of the start_lsn for the dbo_Country capture instance, we will need this later on when we will need to reset the lsn that is stored in the cdc.change_table.
select * from [cdc].[change_tables] where capture_instance = 'dbo_Country'
Step 4 – So you now have got everything you needed from the original capture instance (cdc.dbo_country_ct), it can now be dropped and recreate it again will mean the column countryid will now be included in it.
--Dropping the original capture instance
exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'country', @capture_instance = 'dbo_country'
--Creating a new capture instance where countryid will also be captured
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Country',
@role_name = NULL,
@filegroup_name = NULL,
@supports_net_changes = 1,
@captured_column_list = 'countryid,countryname',
@capture_instance = 'dbo_country'
Step 5 – Copy all the data from capture instance 2 (cdc.dbo_country_2_ct) into the newly created version of the original capture instance (cdc.dbo_country_ct).
INSERT INTO cdc.dbo_country_ct
(__$start_lsn, __$end_lsn,__$seqval,__$operation,__$update_mask,countryId,countryname)
SELECT
__$start_lsn,
__$end_lsn,
__$seqval,
__$operation,--
__$update_mask,
countryId,
countryname
FROM cdc.dbo_country_2_ct
Step 6 – Now that both capture instances have the same data we can drop our version 2 of the capture instance, as we no longer need it.
exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'country', @capture_instance = 'dbo_country_2'
Step 7 – Ok so now we are almost done, we are back to just having one capture instance on our dbo.country table, with the same name as the original capture instance but it now contains the additional column countryid and we achieved all that without losing any data. So just one final thing to do and that is resetting the start_lsn for this table.
Why do we need to do this? So by creating a new capture instance, the start_lsn gets set in the cdc.change_table for the table dbo.country to the latest lsn that is in the database, that is find for any changes going forward but remember we have copied older changes into our new capture instance and if we want to be able to access them then we need to reset the start_lsn to a time further in the past, se we can set it to its original value, remember the value we saved before we dropped the original instance.
--This query does not return any data
DECLARE @beginTime as Date = CAST(GETDATE()-1 AS Date)
DECLARE @endTime AS Date = CAST(GETDATE() AS DATE)
declare @from_lsn binary(10), @to_lsn binary(10), @lowest_lsn binary(10), @max_lsn binary(10)
set @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @beginTime)
set @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @endTime)
set @lowest_lsn = sys.fn_cdc_get_min_lsn('dbo_country')
set @max_lsn = sys.fn_cdc_get_max_lsn();
IF (@from_lsn IS NULL) OR (@lowest_lsn > @from_lsn)
BEGIN
SET @from_lsn = @lowest_lsn;
END
IF (@to_lsn IS NULL) OR (@max_lsn < @to_lsn)
BEGIN
SET @to_lsn = @max_lsn;
END
select * from cdc.fn_cdc_get_net_changes_dbo_country(@from_lsn, @to_lsn, 'all')
The reason that no data is being returned is that the function ‘sys.fn_cdc_get_min_lsn(‘dbo_country’)’ returns the min lsn by reading the start_lsn from the cdc.change_tables table for the table ‘dbo.country’ and we know that the start_lsn was reset when we re-created the capture instance.
So to overcome this we will run the following code.
UPDATE [cdc].[change_tables] set [start_lsn] =@startLsn
WHERE [capture_instance] ='dbo_country';
--Where @startlsn is the value that you would have saved before
--dropping the original capture instance, see Step 3.
Ok, so you’re probably wondering why go this all this bother right? Why don’t we just use the 2nd version of the capture instance (cdc.dbo_country_2_ct) and away we go. The problem is, when you create a capture instance the functions that you are recommended to use for querying that capture instance are created and they will have the capture instance name in them. So for example if you had some other application that was looking for the function cdc.fn_cdc_get_all_changes_dbo_country and this will no longer exist as the function name will now incorporate that of the new capture instance cdc.dbo_country_2_ct, then your application breaks. Hence why IMHO its best to keep the capture instance name the same and so the function names will remain the same and so any applications that might be querying the functions (good practice) or querying the capture instance directly(bad practice) will not break.
As always, if you liked this blog please follow me on Twitter to be kept up-to-date with my latest blog releases and more fun stuff.
Until next time, Slán!
Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.
The Front-line Database Blog 
Hi Richard.
Very nice article! I tried it myself and added a Step 0: Change the capture job to have a polling interval of one hour, to ensure that no changes came in while I chopped and changed the capture instance data. At the end of the process I set the polling interval back to normal.
It looked like everything worked nicely, however CDC would then refuse to put new entries in the updated capture instance. I could not see any problem anywhere – even disabling CDC on the table and re-enabling CDC didn’t fix it.
I ended up contacting MS support who were also unable to repair whatever damage I’d caused. The final fix suggested was to disable CDC on the entire database and re-enable CDC on every table, which of course lost all my updates. Luckily this was all in a test environment.
Has the CDC implementation changed since you wrote this? Or did I just monumentally mess up?
LikeLike
Hi Philip,
Thanks for reading :).
To the best of my knowledge there has been no change by Microsoft on how CDC is implemented and so the information listed in the article should still be good.
When you created the 2nd capture instance, was the CDC data being inserted into both capture instances at that point? Or was the data only being added into your original Capture Instance?
LikeLike
Curious if you’ve tried this recently. Once I run that update against cdc.change_tables the CDC agent job starts throwing errors about a primary key violation trying to insert into the lsn_time_mapping table. Once this starts, all change tracking for the table stops. The only way to get it going again is to drop/recreate the capture instance and restart the sql agent job.
LikeLike
Hi Chris, thanks for reading. Just wondering the lsn that you run in your update of the cdc.change_tables table, this should in most cases be the 1st entry in the cdc.lsn_time_mapping table, if that update succeeds its very strange that a row with the same start lsn would then be inserted at a later point causing the PK violation. I have never come across this issue, and without seeing the code I would probably go for the issue either being the incorrect lsn being set or in some older versions of SQL Server there were issues around pk violation in the cdc.ls_time_mapping table.
Regards
Richard
LikeLike
Thanks for the response. I have a stored procedure I wrote that immediately after re-creating a new capture instance with the original name, it updates the starting LSN accordingly. It looks like what’s happening is when a new capture instance is created, the starting LSN for that capture instance is inserted into the lsn_time_mapping table. If I run that update too quick after creating the new capture instance, I get that error because it must be grabbing that LSN value from the change_tables table.
If I remove that update from the stored procedure I wrote and wait some time before running it, everything works fine.
LikeLike
Hi Chris, well that is an interesting scenario, do you mind telling me, what LSN is your stored procedure using, is it the start LSN from your original table?
LikeLike
It currently calls sys.sp_cdc_help_change_data_capture to get all the info about the capture instance for the table that had a column added. I am using the start_lsn that procedure returns, which seems to match the start_lsn value in the cdc.change_tables table.
After I have the current capture instance info from sys.sp_cdc_help_change_data_capture, it creates a second capture instance and moves the data over to that. Then it disables the original instances. Then it creates another instance with the original name, and moves the data back over to that. Finally it runs that update. Almost immediately after that update I can see the agent job throwing errors.
I’m not sure if it matters, but this is on SQL 2019
LikeLike
Hi Chris, apologies for the delay in replying. I had written this blog based on SQL Server 2016, so I wanted to test the scenario out on SQL Server 2019, which I did this morning. It worked fine and I got no error on the Agent Job. The lsn in both the cdc.change_tables and 1st entry in the cdc.lsn_time_mapping match each other. Admittedly I did not use a store proc, but ran the update within a few secs. No doubt there is probably some minor differences in our environment setups that might be contributing the differences we are seeing. Sorry I can’t be much more help.
Regards
Richard
LikeLike