SharePoint Search – A Different way to crawl and index SQL External Content

Since SharePoint 2013, SharePoint Search features are a lot more powerful than in previous versions. The information discovery potential is enormous, we can connect to external sources (SQL, Web Services, etc) drastically improving the potential of any solution using Search. But sometimes the configuration necessary to connect with external content sources can be painfull (BCS, External Content Types, etc).  In this post we will show you how you can easily index SQL external content without worrying about painfull configurations.

How it works

As we said earlier, instead of going through the usual BCS – External Content Type kind of solution, what we did was to configure the SharePoint Search Service Application to Crawl a Content Source that consisted of a Web Site exposing the items we wanted to index:

Let’s walkthrough the configuration together.

Configure the Search Service Application

First we need to create a new Content Source of the type Web Sites pointing to a web site we will create:

What we are doing here is telling the SharePoint Search Service Application to index the pages it will find at the address we gave. There we will have a page with a list of links pointing to each of the itens we want to index. For that to happen we must also tell the Search Service to follow complex URLs. That is done by creating a Crawl Rule as the following:

The path should be something like http://yourwebsiteurl/*.

After that we must create and provision our Web Site.

Web Site Setup

As we said earlier, we will have a website that will have only 2 functions:

  1. It will have a page with links pointing to the “Details Page” of each item we want to Crawl/Index.
  2. The “Details Page” will query the database for the items content and will write  it as metatags on the header section

What we did here was to create a simple MVC Web Application with two simple views, an Index view with the list of urls and a Details view.

Our Index.cshtml:

And our Detail.cshtml:

We created a controller with two actions (Index and Detail):

As you can see in the code, we get our information from a Stored Procedure and in order to keep the solution flexible we stored it in the web.config as an appsetting. We also have a DAL layer that communicates with the SQL Server:

Here we call the store procedure, either to get a list of items or a specific item.

Creating the Stored Procedures

We decided to use Stored Procedures because of the following reasons:

  • Easy to manipulate in order to add/remove columns to be indexed
  • Allow the possibility of paging results

We can see in the example below that we have the possibility of paging the results as well as returning a specific row:

We have an output value that tell us if we have more results to return after the current set.

In Summary

After that all we need to do is to run a full crawl. The metatag names will be our crawled properties names, you’ll just need to map them to a Managed Property. If you need to add/remove more columns to be indexed all you need to do is add or remove them from the store procedure!

As you can see, putting this all together is really easy but there is a downside, SharePoint Search will read the content as HTML pages. If you are using the FileType property you might find a bit inconvenient to see this results interpreted as HTML pages. You can easily overcome this by using the Content Enrichment Web Service as we described here.

Leave a Reply