Azure Search with SQL Server

Thank you to Michael Crump for creating this content!
Don’t have Azure? Grab a free subscription.

Implementing Azure Search with SQL Server and ASP.NET MVC

Today’s tutorial covers Azure Search and SQL Server in a ASP.NET MVC web app.

What’s Azure Search?

Azure Search is a SaaS — in this case, search-as-a-service — cloud solution that allows you to access REST APIs (along with an SDK) to search over your content using web, desktop or mobile apps.
There are a variety of services to which you can attach Azure Search, including SQL Server.

Creating the Indexer

This requires creating a SQL Server Database in which we’ll store the indexer.

Go into the Azure Portal.
Search for “SQL Server" and click the Add button.
Give it a database name and resource group.
Fill out the new server information.
Click on the Additional settings tab.
In "Use existing data" select "Sample". This will use AdventureWorksLT as the sample database.

Remember! Double check you selected theAdventureWorksLT database if you want to follow along with this tutorial.
AdventureWorks is an example database we’ve built just for you!

Taking A Peek at the Data

Now that we have a sample database and it has deployed, let’s use Query Editor inside the SQL Server blade to take a look at the table structure.

You can also use SQL Server Management Studio if you prefer it better.

If you click on Query Editor and login as instructed, then you’ll see the following:

As you can see, I expanded the Table dropdown and I can drill down again to the column names.
It looks like Customer would be a great table to implement search and we could pass in the FirstName or LastName field.

Trying writing a simple query to select the FirstName and LastName from SalesLT.Customer. Click Run to take a look at the data.

Now that we have our database in place and some solid data to work with, we’ll implement Azure Search.

Implementing Azure Search

Let’s add Azure Search to our existing SQL Server instance.

Open the Azure Portal.
Navigate to your SQL Server instance and begin by looking at the Settings pane.

Select a Add Azure Search and fill out the fields specified below and make sure to select the price as free.

Under Data Source, we can easily connect to our Azure SQL Database.

Give it a name.
Provide the userid and password specified when setting up the SQL database.
Press Test Connection.
If everything goes well, then you’ll be able to select the Customer table.

Next, set an index.

Give it a name.
Select CustomerID as your key.
Clean up the fields by deleting any you don’t want.
Make sure the fields can be retrieved, sorted, filtered and are searchable by adding a check like the photo below.

We need to create an indexer. I’m going to select the daily schedule and set my watermark column to the ModifiedDate as I assume data is unique in that column.

Once you kick that off, you’ll see the following notification. It states you can check the monitor progress and once complete you can start searching.

If you go ahead and click on the link on the notification window, then you’ll see the following screen.

Go ahead and press the Run button and it will start immediately. Eventually — expected time varies — you’ll see it has completed.

Excellent! Our SQL database and Azure Search indexer are in place.
Want to know more about Azure Search? Check out our quickstarts and tutorials!
We’ll be posting articles every day in April, so stay tuned! Or jump ahead and check out more tips and tricks now.

Link: https://dev.to/azure/azure-search-with-sql-server-4040