Deploying Debezium for the first time, it not captures the schema of all tables in the database.

What Debezium connector do you use and what version?

 - Debezium connector for SQL Server version 2.5

What is the connector configuration?

{
  "name": "debezium-Source-Connector-MsSql-test_internalTopic-002",
  "config": {
  "connector.class":
  "io.debezium.connector.sqlserver.SqlServerConnector",

  "database.encrypt": "false",
  "database.hostname": "mssql.xyz",
  "database.port": "1433",
  "database.names": "BAY_POC",
  "database.user": "user",
  "database.password": "password",
  "table.include.list": "dbo.WorkProfile",

  "topic.prefix": "test_internalTopic-002",
  "schema.history.internal.kafka.bootstrap.servers": "broker.xyz:9092", 
  "schema.history.internal.kafka.topic": "test_internalTopic-002_schemahistory",

  "tombstones.on.delete": "false",  
  "time.precision.mode": "connect",

  "key.converter": "io.confluent.connect.avro.AvroConverter",
  "value.converter" : "io.confluent.connect.avro.AvroConverter",
  "value.converter.schema.registry.url" : "http://schema.xyz:8081",
  "key.converter.schema.registry.url" : "http://schema.xyz:8081",  

  "transforms": "unwrap",
  "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
  "transforms.unwrap.delete.handling.mode": "rewrite"  
 }
}

What is the captured database version?

 - Microsoft SQL Server 2019

What behaviour do you expect?

 1. Normally, when deploying Debezium for the first time, it captures the schema of all tables in the database.

 2. After the initial deployment of Debezium, if a new table is added to MSSQL, its schema is not captured.

What behaviour do you see?

 let me explain what I've tested:

  1. On MSSQL, I have tables WorkProfile and WorkProfile_2, both with CDC enabled.
  2. I deployed Debezium with table.include.list set to table WorkProfile only and left schema.history.internal.store.only.captured.tables.ddl at its default value, which is false.
  3. After deploying Debezium in step 2, I found that the schema history topic had only one record, which described the schema of table WorkProfile.
  4. I updated table.include.list to include both tables WorkProfile and WorkProfile_2, and then re-deployed Debezium.
  5. After re-deploying Debezium in step 4, I found that the schema history topic had one more record, which described the schema of table WorkProfile_2. So, at this point, the schema history topic had a total of 2 records.
  6. I created a new table WorkProfile_3 on MSSQL and enabled CDC.
  7. I updated table.include.list to include tables WorkProfile, WorkProfile_2 and WorkProfile_3 and then re-deployed Debezium.
  8. After re-deploying Debezium in step 7, I found that the schema history topic had one more record, which described the schema of table WorkProfile_3. So, at this point, the schema history topic had a total of 3 records.

 The bugs I encountered were:

  1. Normally, when deploying Debezium for the first time, it captures the schema of all tables in the database. However, in the case I encountered, it only captured the schema of tables specified in the table.include.list  and as I gradually added new tables to the table.include.list , it began to gradually capture the schema of those new tables.

  2. After the initial deployment of Debezium, if a new table is added to MSSQL, its schema is not captured. However, in the case I encountered, the schema of the newly added table was captured.

Do you see the same behaviour using the latest relesead Debezium version?

 -  Yes, I encountered this behavior in the Debezium connector for SQL Server version 2.5, which is the latest stable version.

For further information

https://debezium.zulipchat.com/#narrow/stream/348252-community-sqlserver/topic/Adding.20a.20table.20.20after.20deploy.20debezium