Why can't I index SQL Server with SharePoint?

This question is actually answered with another question. Exactly how do you index a database?

The problem being that a database is just that, it’s merely data. There is no “Standard User Interface” out there that works for all databases, so how do you display search results? Sure there are some techniques for creating a “generic” user interface for tables, think DataGrid and the like, but I’m not convinced wrapping that sort of feature into SharePoint would be all that useful.

Now, enough background, what about a solution. Well there are many, ranging from building your own protocol handler to using Access to do an “Export to Web Page” (you can do that right?), but the one I like the most goes like this:

1. Create a Web Page (eg. MyDataBase.aspx (try something more creative than that)) that iterates through all the items in a table outputting something like:
                  <a href=”https://<server>/GetDatabaseRow.aspx?<PrimaryKey>=XXXXXXX”>Database Row</a>
2. When you click on this link, another web page you have written digs into the database, pulls out that item, and renders the detail in a nice web page.
3. Point the SharePoint Web Crawler at “MyDatabase.aspx” telling it to follow complex links (via the creation of a include rule)
4. Kick off the index, then “Hey presto!”, your database is “indexed” and more importantly it has a user interface to make sense of the data that is returned in the result.

With all this said, I can see some value in indexing a database, particularly around alerting and subscriptions, but that is another post…..

P.S. As a final footnote, I remember many years ago that “Microsoft SiteServer” did this, it could index ODBC source, but that is about as much as I remember, so I dont think it was too widely deployed. Anyone else out there remember?

Comments

• Anonymous
  December 20, 2004
  I covered this is in the "10 most common development tasks" presentation @ TechEd Israel back in 2002. In those days it was still SPSv1 (with the Webstore, ugh) but the presentation included code samples and all...
  
  BTW, regarding "alerting and subscriptions", Yukon is going to totally change the model with it's new alerting engine. Just food for thought.
• Anonymous
  December 20, 2004
  Your correct - Site Server indexed ODBC sources in the same exact manner - as does/did index server.
  
  The trick was to use the query syntax to redirect the user from the result to a page that puts the indexed info into it's appropriate context.
  
  -keith
• Anonymous
  December 20, 2004
  How about asking the question in reverse? Why can't I index SharePoint with SQL Server? As since SPPSv2 the SharePoint store is SQL Server 2000?
• Anonymous
  December 21, 2004
  Hey Addy, so where is the presentation and code samples? <grin>
• Anonymous
  December 21, 2004
  Hi John,
  
  Actually that's exactly the way search within a Windows SharePoint Services Team Site works. Behind the scenes it is leveraging SQL Full-Text Search, which in turn is based on MS Search. SharePoint Portal Server uses it own search, which is also built on top of MS Search, just with a whole lot of extra goodies.
  
  So, good question!
• Anonymous
  December 21, 2004
  Hi DanMc - merry Xmas from South Australia.
  
  The SS3 tool dynamically created ASP and then crawled it.
  
  It was a bit basic and you didn't have control of optimising the query underneath, but it did a reasonable job.
  
  I don't know anyone who used it in real life.
• Anonymous
  December 22, 2004
  Mr Brennan, I just knew you would know the answer to that one. (and of course correctly called it a tool rather than a product)
  
  All this talk of Site Server really does take me back, what was that tool it shipped with "Knowledge Manager" or something? I remember watching you explain it to people over and over (often the same person). Hmmm....What did it do again? <grin>
• Anonymous
  December 22, 2004
  The comment has been removed
• Anonymous
  December 22, 2004
  Hi Daniel,
  Actually, my question was not so much to get an answer, but to discuss the reverse topic. I've worked for years with many on the SharePoint/MSSearch and SQL/FTS dev teams, and the "goodies" are the point. SQL/FTS is limited in its functionality by MSSearch, but SQL Server has a very good scale-up & scale-out story, but FTS cannot use many of these SQL Server features, such as Distributed Partitioned Views (DPV's) to achive the scale-out performance gains. Such features would be useful in propgrating the SQL Server FT Catalog to other servers as either Indexing or Search servers. However, a pure T-SQL Search solution can take advantage of all of SQL Server's features, including FT Indexing the SharePoint database.
  
  What are your thoughts? BTW, say hello to Anne Zorner for me.
  
  John
• Anonymous
  January 31, 2006
  Just found someone (birchTree, hi guys!) out there wondering how they might go about indexing a SQL Server...
• Anonymous
  April 05, 2008
  The comment has been removed