I am planning to use the Change Data Capture feature of SQL Server 2016, but I read that It has a clean-up job enabled automatically (which I can disable). I would like to store every change that happened in the database for certain tables (~50% of the tables). Do I really need to manually process the change table rows and archive them in my own tables or can I just leave the clean-up job disabled and query the cdc.* tables in my application?
It is highly advisable to archive them to another table. Yes, the cleanup job can be disabled but consider the scenario where a new column needs to be added to the table included in the CDC. This table will 1st need to be disabled for CDC which will automatically drop the Change Table. The documentation does not make this explicitly clear but trust me when I say this is what happens!