Setup Azure Search on SQL Azure Database

Setup Azure Search on SQL Azure Database

In last few articles, I have explained how to create Azure Search Service using Azure Portal and how to create index.

If you are new to Azure search, I would recommend to have a look at below articles:

In this article, let’s have a look at how to setup Azure search to work with the database published in SQL Azure database.

For following steps in below article, you will need access to Azure Portal. If you do not have access, then you can create free account on Azure.

Let’s have a look at demo scenario!

Azure search can be setup using either Push model or Pull model.

While setting up push model, you just need to create index and then it will be responsibility of your application to push appropriate data in index. Azure search will not generate it.

In pull model, there is a concept of Indexer. The sole responsibility to connect with data store where the data is stored and populate index with all the required information. It is like a background job which is triggered based on selected execution frequency. Below diagram shows how the pull model works.

In this article, we will create a database in SQL Azure. Then we will import that data in Azure search. During the data import, we will also create index schema and indexer.

Prerequisite: Create Database

In left side panel, find “SQL databases” option and then click on “Add” button. It will show a panel as shown below.

You will have to provide subscription, resource group, server, pricing tier.

If you do not have a SQL Azure server, you can create a new one after clicking on Create New option just below the dropdown.

Then instead of clicking on Review+Create, click on advanced setting button.

On advanced setting, you can select Sample option and then click on review and create button. This would create sample AdventureWorksLT database with some sample data.

Azure Search Configurations

Assuming here that you already have Azure search service created.

Go to overview blade of Azure search service instance. Then click on “Import Data”.

This would bring import wizard.

Connect to your data

Select data source to be Azure SQL Database. Then click on choose existing connection to select your Azure SQL database.

Then click on “Test Connection” to make sure that all the details are valid.

Selecting Table or View for Search

After successful test connection, the table or view should be provided. This table / view would be used by user for search. I have selected Product table here. Then click on next button.

Add Cognitive Search (option step)

This is optional step. This is required in case you want to include optical character recognition (OCR) of text in image files, or text analysis over unstructured data. 

There are two parts of this step:

Attaching Cognitive Service

You can attach free cognitive services or you can create new Cognitive Service resource in Azure and associate it with this Azure search service instance. For this demo, we do not need it.

AI Enrichment

You can choose which AI enrichment to be included in the skill set. I am not selecting anything here, just keep the default settings and then click on skip button.

Ideally you should select the cognitive skills based on your requirements.  Please note that if you want to use this feature, then you will have to create a cognitive service account.

Customize Target Index

In this step, you can either accept or modify the index schema generated by the wizard. The wizard automatically identifies the data types and fields by sampling data in the database table and by reading the metadata.

Here you need to specify index name. This should be unique inside the selected Azure search instance.

You also can modify the key field as well. This key field is used to identify every document in index uniquely.

You can enable suggester by typing suggester name. Below are settings selected by me. Click on next to move to last step in this wizard.

Create an Indexer

An indexer is a crawler that extracts searchable data and metadata from an external Azure data source. 

On this last step, you can specify the indexer name and frequency at which the indexer should be triggered.

This indexer would crawl into the database and will get our index populated with all the information.

If you want information to be deleted from the index provided the data from database is deleted, then you can turn on the “Track Deletions” checkbox. Once it is enabled, you will have to specify additional input – the column which is used to identify that the record is deleted (i.e. the soft delete identifier).

Once this step is done, your Azure search service is ready.

You have successfully setup the Pull model in Azure search service instance.

Let’s test this!

Good thing is you DO NOT NEED to write a single line of code in order to test this.

You just need to go to indexers tab and then select the indexer which you want to run.

This will bring another panel where you can manually trigger the indexer.

Click on Run button to run the indexer.

Then if you come back on Indexers tab as shown in the above snapshot , you will be able to see last run status as shown below.

Now, you can either send HTTP requests using Postman or you can write a .Net code to connect with Azure search service and get the search results.

On Azure portal, you can verify this by clicking on Search Explorer.

This would open a search window, where you can select index, which you want to use for testing. Select index and then enter the text you want to search. It would show you the search results.

That’s it !!!

You have setup and tested your search solution successfully.

Now, you know how easy is to setup an Azure search service and use it in your application. Azure search service provides default support for below azure services

You can also connect to SQL Server hosted inside an Azure VM.

I hope this article has provided your very good overview for setting up indexers in Azure search. Please do comment and let me know your thoughts and experiences.

Leave a Reply