Records are not getting updated/deleted in Search Index despite enabling Track Deletions in SQL DB

Symptom:

The count of records in the indexer and the index did not align even after activating the change detection policy. Even with record deletions, the entries persisted in the Index Search Explorer.

 

To enable incremental indexing, configure the "dataChangeDetectionPolicy" property within your data source definition. This setting informs the indexer about the specific change tracking mechanism employed by your table or view.

 

For Azure SQL indexers, you can choose the change detection policy below:

"SqlIntegratedChangeTrackingPolicy" (applicable to tables exclusively)

It is recommended using "SqlIntegratedChangeTrackingPolicy" for its efficiency and its ability to identify deleted rows.

Database requirements:

Prerequisites:-

1. SQL Server 2012 SP3 and later, if you're using SQL Server on Azure VMs
2. Azure SQL Database or SQL Managed Instance
3. Tables only (no views)
4. On the database, enable change tracking for the table.
5. No composite primary key (a primary key containing more than one column) on the table.
6. No clustered indexes on the table. As a workaround, any clustered index would have to be dropped and re-created as NonClustered index, however, performance may be affected in the source compared to having a clustered index.
7. When using SQL integrated change tracking policy, don't specify a separate data deletion detection policy. The SQL integrated change tracking policy has built-in support for identifying deleted rows.
8. However, for the deleted rows to be detected automatically, the document key in your search index must be the same as the primary key in the SQL table.

 

Once you have done all the above steps, still you see the discrepancy in the count of Indexer and Index Count

 

Approach:

 

Enabling change tracking before or after inserting data can affect how the system tracks changes, and the order in which you enable it matters. It's important to understand how change tracking works in your specific context to resolve the issue. 

 

• Check whether you have enabled Change tracking at the Table level as well along with Database level.
• Check whether you have enabled Change Tracking before or after Data Insertion.

 

                            ALTER TABLE [TableName] ENABLE CHANGE_TRACKING

 

Here are some general guidelines on how change tracking typically works:

1.  Enable Change Tracking Before Inserting Data:
      - If you enable change tracking before inserting data, the system will start tracking changes from the beginning.
      - This is the recommended approach if you want to track changes to existing data and any new data that will be added.
2. Enable Change Tracking After Inserting Data:
      - If you enable change tracking after inserting data, the system might not have a baseline for the existing data.
      - You may encounter errors if you attempt to retrieve change information for data that was already in the system before change tracking was enabled.

Solution :

1. To ensure that the Indexer starts tracking deletions from the beginning, it is important to enable Change Tracking before inserting data.
2. This approach also helps to match the count of the Indexer and Index without having to reset the Indexer repeatedly.

Reference Links :–

Enable and Disable Change Tracking - SQL Server | Microsoft Learn

Azure SQL indexer - Azure AI Search | Microsoft Learn