How does Auto-indexing/sync of Azure SQL DB with Azure Search Works?

5
votes

As per the below articles,

http://feedback.azure.com/forums/263029-azure-search/suggestions/6540846-auto-indexing-of-azure-sql-db

and

https://azure.microsoft.com/en-us/documentation/articles/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers-2015-02-28/

Azure search will automatically sync/update the modified rows from the SQL table and update the same in the Azure Search index. But when i update the source table, it doesn't seem to affect my Azure Search index at all.

Can anyone clarify what does Auto-indexing/sync of Azure SQL DB with Azure Search really means?

Note: Strictly followed the instructions given in the article.

Datasource

POST https://servicename.search.windows.net/datasources?api-version=2015-02-28 api-key: <> Content-Type: application/json

{ "name" : "myazuresqldatasource", "type" : "azuresql", "credentials" : { "connectionString" : "Server=tcp:xxxxyyyy.database.windows.net.database.windows.net,1433;Database=dvdlist;User ID=aaaabbbb;Password=aaaaabbbbb;Trusted_Connection=True;Encrypt=False;Connection Timeout=30;"}, "container" : { "name" : "dvdlist" }, "dataChangeDetectionPolicy" : { "@odata.type" : "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy", "highWaterMarkColumnName" : "id" }, "dataDeletionDetectionPolicy" : { "@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy", "softDeleteColumnName" : "IsDeleted", "softDeleteMarkerValue" : "true" } }

Indexer

POST https://servicename.search.windows.net/indexers?api-version=2015-02-28 api-key: <> Content-Type: application/json

{ "name" : "myazuresqlindexer", "dataSourceName" : "myazuresqldatasource", "targetIndexName" : "sqlazureindex" }

2
azureazure-sql-databaseazure-cognitive-search
Are you on SQL DB v12? My hunch is that this feature requires change tracking enabled on the databases which is only supported in V12. Otherwise you need to make sure your High Water Mark Change Detection Policy is correct. – Sirisha Chamarthi
Hi Ilyas, Sirisha is correct - Integrated Change Tracking policy is only enabled for SQL DB v12 databases. If you're using a high water mark policy, you need to ensure that the column you select as the high water mark actually has the appropriate semantics (a rowversion column is a good candidate for the high water mark column). – Eugene Shvets
Hi Eugene, Please apologize for a delayed response. I did try this only on SQL DV v12 as well as High Watermark policy and SoftDeleteColumnDeletionDetectionPolicy, but no luck. I have edited my question to include the json snippets of Indexer and Datasource. Kindly refer! – Ilyas F
@EugeneShvets-MSFT, My understanding towards Auto Sync with Azure SQL DB is that, as soon as i update any record or soft delete any record and update row version/soft delete column, it must immediately sync with Azure search and return the modified result set to the end users. Kindly confirm if this is the expected behavior! – Ilyas F
Hi Ilyas, the changes to your data are not reflected immediately - they're reflected based on the schedule you specified for the indexer. If the indexer runs every 5 minutes, then the delay can be up to 5 minutes, and so on. Looks like you didn't specify a schedule for your indexer, so it only runs when you invoke it explicitly. – Eugene Shvets

2 Answers

0
votes

Even with a change tracking policy, the data on the index will not update until the indexer runs. You can either set the indexer to automatically run on a schedule (last I checked, the most often you can do is once every 5 minutes) or explicitly run the indexer using the api. Although if you need to update that often, you should probably be looking into using the api to post the documents rather than relying on the indexer.

-1
votes

Basically on Azure Search we have to create index with specific columns and we need to push data in defined index format on Azure Search index using scheduler time to time.

Auto-indexing/sync of Azure SQL DB with Azure Search is a technique to make this process easy and manageable, where Azure sql is managing the sync process.