Azure Search from existing database

Question

I have an existing SQL Server database that uses Full Text Search and Semantic search for the UI's primary searching capability. The tables used in the search contain around 1 million rows of data.

I'm looking at using Azure Search to replace this, however my database relies upon the Full Text Enabled tables for it's core functionality. I'd like to use Azure Search for the "searching" but still have my current table structure in place to be able to edit records and display the detail record when something has been found.

My thoughts to implement this is to:

1. Create the Azure indexes
2. Push all of the searchable data from the Full Text enabled table in SQL Server to Azure Search
3. Azure Search to return ID's of documents that match the search criteria
4. Query the existing database to fetch the rows that contain those ID's to display on the front end
5. When some data in the existing database changes, schedule an update in Azure Search to ensure the data stays in sync

Is this a good approach? How do hybrid implementations work where your existing data is in an on-prem database but you want to take advantage of Azure Search?

Answer 1

Overall, your approach seems reasonable. A couple of pointers that might be useful:

1. Azure SQL now has support for Full Text Search, so if moving to Azure SQL is an option for you and you still want to use Azure Search, you can use Azure SQL indexer. Or you can run SQL Server on IaaS VMs and configure the indexer using the instructions here.

2. With on-prem SQL Server, you might be able to use Azure Data Factory sink for Azure Search to sync data.

Answer 2

I actually just went through this process, almost exactly. Instead of SQL Server, we are using a different backend data store.

Foremost, we wrote an application to sync all existing data. Pretty simple.

For new documents being added, we made the choice to sync to Azure Search synchronously rather than async. We made this choice because we measured excellent performance when adding to and updating the index. 50-200 ms response time and no failures over hundreds of thousands of records. We couldn't justify the additional cost of building and maintaining workers, durable queues, etc. Caveat: Our web service is located in the same Azure region as the Azure Search instance. If your SQL Server is on-prem, you could experience longer latencies.

We ended up storing about 80% of each record in Azure Search. Obviously, the more you store in Azure Search, the less likely you'll have to perform a worst-case serial "double query."