Azure Search - Creating a dedicated search table in SQL, for using with an Indexer -

i'm building assets search engine. data need indexed each assets scattered multiples tables in sql database. also, there many events in application trigger update asset indexed fields (in draft, rejected, produced, ...).

i'm considering creating new denormalized table in sql database exist solely azure search index. exact copy of azure search index fields. application responsible fill , update sql table, through various event handlers. use azure sql indexer schedule automatically import data azure search index.

pros:

• we used deal sql table operations, application code remains standard, no need learn azure search api
• both transactional , search model updated in same sql transaction (atomic). indexer update index in eventual consistent manner, , handle retry logic.
• built-in support change detection sql integrated change tracking policy

cons:

• indexed data space usage duplicated in sql database
• delayed index update (minimum 5 minutes)

do see other pros , cons ?

[edit 2017-01-26]

there big pro our usage.

during development, regularly add/rename/remove fields azure index. in current state, schema modifications azure index not possible, have drop , re-create index.

with dedicated table containing data, issue reset our indexer endpoint , new index gets automatically re-populated.