SQL server data sync to Azure cognitive search

humpty Dumpty 1 Reputation point
2021-01-07T10:14:04.247+00:00

Hi,

We are trying to move some of the sql server data into azure cognitive search so we can perform search.

These data sitting in multiple databases /multiple tables. Please see basic flow below

54402-basic-flow.jpg

Have tried adding trigger to tables which send the message out so it can go update the index. Key field of the index is ReferenceId, so when for example if the GuestName is changed in transaction database it currently follow below flow

Search azure index for all holidays which are linked to the Guest Id
Update the guest name in returned results
Send update api request
Would like to know if there is better way of handling this

Thanks

Azure SQL Database
Azure AI Search
Azure AI Search
An Azure search service with built-in artificial intelligence capabilities that enrich information to help identify and explore relevant content at scale.
834 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ryan Hill 26,866 Reputation points Microsoft Employee
    2021-01-07T22:14:15.36+00:00

    Hi @humpty Dumpty ,

    Since you have to two different tables, I don't believe you can utilize Azure SQL to update Azure Search Index as you can't use views in change tracking.

    What I would suggest is when you app makes the changes to Guest or Travel DBs, pull the relevant information from the two database into your DTO and update/insert the index document. There are different methods you can use as a trigger such as QueueTrigger for a webjob or HTTP Trigger for an Azure Function.

    Regards,
    Ryan