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 the
AdventureWorksLT
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
andpassword
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.
Top comments (0)