Azure Search with SQL Server
Tags: azureapril, azure, webdev, database
Published: 2019-04-29 01:14:47

---
title: Azure Search with SQL Server
published: true
description: 
tags: azureapril, azure, webdev, database
series: Azure April
cover_image: https://thepracticaldev.s3.amazonaws.com/i/pblyvmtj4y8bdqisyzey.jpeg
---

*Thank you to [Michael Crump](https://twitter.com/mbcrump) for creating this content!*

**Don't have Azure?** [Grab a free subscription](https://azure.microsoft.com/en-us/free?&WT.mc_id=azureapril_devto-blog-cxa). 

----

# 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](https://azure.microsoft.com/en-us/services/search/?&WT.mc_id=azureapril_devto-blog-cxa)?

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 the [Azure Portal](https://azure.microsoft.com/en-us/features/azure-portal/?&WT.mc_id=azureapril_devto-blog-cxa). 
2. Search for "SQL Server" and click the `Add` 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 use `AdventureWorksLT` as the sample database.

![Additional Settings, Select Sample](https://thepracticaldev.s3.amazonaws.com/i/xaxwli3lklk77cv5dp55.png)

> Remember! Double check you selected the`AdventureWorksLT` database if you want to follow along with this tutorial.

> [AdventureWorks](https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-2017?&WT.mc_id=azureapril_devto-blog-cxa) 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](https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-portal?&WT.mc_id=azureapril_devto-blog-cxa) 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:

![query editor](https://d33wubrfki0l68.cloudfront.net/1ebd0ad631fa85fe3ba6979de294fd86f46a36ed/bfc78/files/azuresearch2.png)

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.

![implement search](https://d33wubrfki0l68.cloudfront.net/da9a8012311620a4ad0ab0d1a443257a7d9dc7dd/cfd3f/files/azuresearch3.png)

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

![run](https://d33wubrfki0l68.cloudfront.net/3ecd830b0804ae0f3e4a06456f1882ca8e9281e2/6c48e/files/azuresearch4.png)

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 the [Azure Portal](https://azure.microsoft.com/en-us/features/azure-portal/?&WT.mc_id=azureapril_devto-blog-cxa).
2. Navigate to your SQL Server instance and begin by looking at the `Settings` pane.

![settings pane](https://d33wubrfki0l68.cloudfront.net/b4cdb15066db05002a4875db8d5f1ace1b0588a3/f9d31/files/azuresearchsql1.png)

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

![add search](https://d33wubrfki0l68.cloudfront.net/e543c245b51568117e1033f82ee68c3907276708/c2ad6/files/azuresearchsql2.png)

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

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

![customer table](https://d33wubrfki0l68.cloudfront.net/95722d60b0ab80ac38eed31958f8aefd5b5e5065/161cf/files/azuresearchsql3.png)

Next, set an index. 

1. Give it a name. 
2. Select `CustomerID` 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.

![add a check](https://d33wubrfki0l68.cloudfront.net/1b77fbb13433e23465600a660a29fd3d95ae5fae/f9f9f/files/azuresearchsql4.png)

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.

![create an indexer](https://d33wubrfki0l68.cloudfront.net/deebc5cb2f7ad177b8685c720005f988a198583a/60b80/files/azuresearchsql5.png)

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.

![notifications](https://d33wubrfki0l68.cloudfront.net/3be1df9a667373e1f7c43341c7c66dd99357910a/31103/files/azuresearchsql6.png)

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

![execution history](https://d33wubrfki0l68.cloudfront.net/892bda2b3da14e80e56d2341f80e1c9f6b6f01ae/d1364/files/azuresearchsql7.png)

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

![run it](https://d33wubrfki0l68.cloudfront.net/c8eed6b52ed093c8f3e5cd4880ff9bf542e4efa4/7ed41/files/azuresearchsql8.png)

Excellent! Our SQL database and Azure Search indexer are in place. 

**Want to know more about Azure Search?** Check out our [quickstarts and tutorials](https://docs.microsoft.com/en-us/azure/search/?WT.mc_id=azureapril_devto-blog-cxa)!

----
We'll be posting articles every day in April, so stay tuned! Or jump ahead and check out more tips and tricks [now](http://azuredev.tips).