Azure Search DataSource with on-premise SQL Server and best practices?

Question

Documentation on Azure Search says I can use Azure Sql server as a datasource(https://azure.microsoft.com/en-us/documentation/articles/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers-2015-02-28/). Can I do the same with an on-premise SQL server?

I have a typical relational structure like

User Table -> Address Table
User Table -> UserDetails table
etc..

All linked to each other via foreign keys. My search should end up with an UserId, so I can link to my UserDetailsPage.aspx?UserId=xxx

What will be the best suggested way to build the datasource? Should I Create a view and apply change tracking on it? or Should I create a different datasource for each table and sync the concerned index?

Please shed some light on best practices in a typical relational database scenario.

Answer 1

you would need to allow the IP address of your search service to connect to your on-prem DB. In terms of view vs. multiple indexers targeting the same index - both approaches might work. What info will your users be searching on - address, details, or both? If it's only one of those, then you wouldn't have to index both tables.

Keep in mind that if you decide to index a view joining both tables, you won't be able to use SQL integrated change tracking, and will have to rely on a rowversion or timestamp column in the view.

HTH