Thank you toMichael 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'sAzure 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.

1. Go into theAzure Portal.
2. Search for "SQL Server" and click theAdd button.
3. Give it a database name and resource group.
4. Fill out the new server information.
5. Click on the Additional settings tab.
6. In "Use existing data" select "Sample". This will useAdventureWorksLT 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 useQuery 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 onQuery Editor and login as instructed, then you’ll see the following:

As you can see, I expanded theTable dropdown and I can drill down again to the column names.

It looks likeCustomer would be a great table to implement search and we could pass in theFirstName orLastName field.

Trying writing a simple query to select theFirstName andLastName fromSalesLT.Customer. ClickRun 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.

1. Open theAzure Portal.
2. Navigate to your SQL Server instance and begin by looking at theSettings pane.

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

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

1. Give it a name.
2. Provide theuserid andpassword specified when setting up the SQL database.
3. PressTest Connection.
4. If everything goes well, then you’ll be able to select theCustomer table.

Next, set an index.

1. Give it a name.
2. SelectCustomerID as your key.
3. Clean up the fields by deleting any you don't want.
4. 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 theModifiedDate 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 theRun 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 ourquickstarts and tutorials!

We'll be posting articles every day in April, so stay tuned! Or jump ahead and check out more tips and tricksnow.