FAST search for SharePoint 2010 and JDBC connector

I have decided to publish some articles regarding SharePoint FAST Search and JDBC connector. This article is not treating any performance issue.

The scope of this article is to present some of the key points regarding JDBC connector.

1. FAST SEARCH INSTALATION

If you plan to install FAST Search Server 2010 I recommend using the steps that are presented in this TechNet article http://technet.microsoft.com/en-us/library/ff381243.aspx (Install FAST Search Server 2010 for SharePoint).

2. BCS or index connectors

In order to index external databases we have two options: BCS and FAST Search database connectors.

BCS

  • Use if the preferred configuration method is using the Microsoft SPD 2010.
  • Use when you want to use time stamp based change detection for incremental database crawls.
  • Use when the preferred operation method is using the SPS 2010 Central Administration.

FAST Search database connectors

  • Use when the preferred configuration method is using SQL queries.
  • Use when you want advanced data joining operation options through SQL queries.
  • Completely customizable

More information in this article http://technet.microsoft.com/en-us/library/ff383278.aspx Plan for crawling and federation (FAST Search Server 2010 for SharePoint)

Item processing and indexing

  • A content connector extracts items, access control information (ACLs), and user directory information from a source system.
  • The content and item ACL are fed into item processing pipeline for indexing.
  • FS uses a process that is known as principal aliasing to map information in the user’s claim to their equivalent IDs in the user store
  • FS enforces security trimming, limiting search results based on the identity of the user who submitted the query
  • When a search request is created FS builds a user search security filter that specifies content permissions for that specific search user
  • FS Query Server intersects the query with the user’s search security filter, checks the item ACLs against the filter and delivers the results.

More information in this article: http://technet.microsoft.com/en-us/library/ff393756.aspx FAST Search Authorization (FSA) data flow

3. Configure FAST JDBC Database Connector

a. Connect to your FAST Search Server

b. Install the 32 bit Java Runtime Environment (JRE) version 1.6 or newer http://www.java.com/en/download/index.jsp

c. Click Start, right click on Computer, select Properties, then Advanced System Settings click Environment Variables and click New below System variables.

d. In the Variable name type: JAVA_HOME; in the variable value type: C:Program Files (x86)Javajre6

clip_image001

e. Access to C:FASTSearchetc folder, make a copy of jdbctemplate.xml and name it myjdbcconnctor.xml

clip_image003

f. Edit the new file with an XML editor. More information regarding JDBC parameters in this article http://technet.microsoft.com/en-us/library/ff381259.aspx (Configure the FAST Search database connector)

Example with SQL 2008 connection:

Parameter Value
JDBCDriver com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBCURL jdbc:sqlserver://server_name:1433;database=Products;integratedSecurity=true
JDBCSQL SELECT ProductKey, ProductName, ProductDescription, BrandName FROM Table_name
JDBCPrimaryKeyField ProductKey (Product name)
Collection sp

You can create complex SQL queries and views using SQL Management Studio. Once you have done it and tested in SQL you can paste the query in the JDBCSQL field. More info in this article https://www.nothingbutsharepoint.com/sites/devwiki/articles/Pages/Searching-Database-Content-with-FAST-Search-for-SharePoint-2010—Part-2-the-FAST-JDBC-Database-Connector.aspx

g. Test your new JDBC connector, click on Start menu, and then click All Programs.

h. Click Microsoft FAST Search Server 2010 for SharePoint.

i. Click the Microsoft FAST Search Server 2010 for SharePoint shell.

clip_image004

j. In Microsoft FAST Search Server 2010 for SharePoint shell command prompt, type the following command in order to test your connector:

jdbcconnector.bat testconnections –f ..etcmyjdbcconnector.xml

k. In order to start crawling execute the following command

jdbcconnector.bat start –f ..etc myjdbcconnector.xml

clip_image006

l. Check in the content collection for indexed documents by executing the following PowerShell command

Get-FASTSearchContentCollection -Name sp

clip_image008

4. Indexing using multiple queries

If you want to index content using multiple queries you need to create multiple JDBC connectors and content collections, and then associate each JDBC with his content collection.

In order to create a new content collection, execute this PowerShell command:

New-FASTSearchContentCollection -Name newscollection -Description “A collection for new content”

Remarks:

You can create only one SELECT for each JDBC connector; you cannot add multiple SELECT for the same JDBC connector.

If you add 2 or more JDBC connectors for the same content collection and you start crawling for each of them (jdbcconnector.bat start –f ..etc myjdbcconnector.xml) the index will contain data only from the last executed JDBC.

5. Managed Properties

a. Connect to SharePoint Central administration

b. Click on Service Applications

c. Click on your Fast Query service application and then click on FAST Search Administration

d. Click on Managed Properties

clip_image009

 

 

e. A new window will appear with the complete list of managed properties

clip_image011

f. Click on Add Managed Property

g. Name and Description fields complete with the following data

clip_image013

h. Type fileds

clip_image015

i. Mapping to crawl Properties, check “Include values from a single crawled property based on the order specified”

j. Click on Add Mapping

k. Select a category – JDBC

clip_image016

l. Select color name and add it to Selected crawled properties

clip_image018

 

m. For the others checkboxes select the following options then click on ok

clip_image020

Once you have created all the managed properties you need to create a Filter Category Definition XML, more info in this article https://www.nothingbutsharepoint.com/sites/devwiki/articles/Pages/Searching-Database-Content-with-FAST-Search-for-SharePoint-2010—Part-2-the-FAST-JDBC-Database-Connector.aspx

Remarks:

After you have created the managed properties you need to start a full crawl on your FAST Search server (jdbcconnector.bat start –f ..etc myjdbcconnector.xml) otherwise the mappings will not work.

If you want to delete JDBC crawled properties click on “Crawled Property Categories” and then edit JDBC category (you have an option remove crawled properties)

clip_image021

6. FAST search scopes

There is no user interface to create and administer scopes for FAST Search. More information regarding search scopes in this article http://blogs.msdn.com/b/jorgeni/archive/2010/02/26/search-scopes-in-fast-search-for-sharepoint-part-1.aspx Search scopes in FAST Search for SharePoint (Part 1)

7. JDBC and GeoSearch

I have found this feature FAST Spatial Search Toolkit v1.0 ( http://sharepoint-sezai-moss-2007.blogspot.com/2011/10/fast-spatial-search-toolkit-v10-search.html ) that enables geographical search in FAST Search center. This feature is working fine if you are using JDBC or BDC connector.

This is another article that describes GeoSearch connection: http://blog.baslijten.com/geosearch-with-fast-search-for-sharepoint-2010-part-2-custom-pipeline-extensions/. I didn’t test it yet, but I will come with an update soon.

Thank you for reading this article.

Sources of this article:

http://technet.microsoft.com/en-us/library/ff381243.aspx Install FAST Search Server 2010 for SharePoint

http://technet.microsoft.com/en-us/library/ff381259.aspx Configure the FAST Search database connector

http://technet.microsoft.com/en-us/library/ff383278.aspx Plan for crawling and federation (FAST Search Server 2010 for SharePoint)

http://technet.microsoft.com/en-us/library/ff393756.aspx FAST Search Authorization (FSA) data flow

http://technet.microsoft.com/en-us/library/ff383302.aspx Manage crawl rules (FAST Search database connector)

http://blogs.msdn.com/b/jorgeni/archive/2010/02/26/search-scopes-in-fast-search-for-sharepoint-part-1.aspx Search scopes in FAST Search for SharePoint (Part 1)

https://www.nothingbutsharepoint.com/sites/devwiki/articles/Pages/Searching-Database-Content-with-FAST-Search-for-SharePoint-2010—Part-2-the-FAST-JDBC-Database-Connector.aspx Searching Database Content with FAST Search for SharePoint 2010 – Part 2 – Using FAST JDBC Database Connector

http://sharepoint-sezai-moss-2007.blogspot.com/2011/10/fast-spatial-search-toolkit-v10-search.html

http://blog.baslijten.com/geosearch-with-fast-search-for-sharepoint-2010-part-2-custom-pipeline-extensions/ GeoSearch with Fast Search for SharePoint 2010