Connector: SQLServer

Current behavior

For an existing table, the Fivetran SQL Server connector only updates the schema of the destination table in two situations:

• When the table is brand new from the connector's perspective, or
• When at least one row flows through the sync (schema currently travels along with row events)

The proposed workaround for this is to disable the table, sync the connector, re-enable the table with "do not re-sync" and then sync the connector again. This removes the table from the connector's internal sync state.

Expected behavior

The expected behavior is that the connector would reflect the selected schema in the destination table, without syncing any records. Schema updates on destination tables would happen independent of records being synced. Potentially this should be a configuration on the connector.

Impact

Current behavior breaks downstream analytics models which depend on the ingestion tables having the same schema across many connectors as our source product has single-tenant databases. As we scale we expect to have >10,000 connectors and with established products with wide scope this is a common occurrence as customers use distinct modules/features within the product. Scanning to identify and heal this problem at scale is non-trivial as it requires a significant volume of queries against the destination database and the Fivetran connectors.