Search code examples
sql-serverazuresearchazure-cognitive-search

Azure Search from existing database

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?

Solution

  • 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.