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=”http://<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 (11)

  1. Addy Santo says:

    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.

  2. 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

  3. John Kane says:

    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?

  4. Hey Addy, so where is the presentation and code samples? <grin>

  5. 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!

  6. IanBren says:

    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.

  7. 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>

  8. IanBren says:

    SS3 was a product that was like a toolset. Imagine a swiss army knife... useful things in there but also obscure bits [like that thing for getting boy scouts out of horses hooves <g>].

    The SQL search piece was one of these obscure blades.

    The Knowledge Manager was an ASP application that let you save searches - what it called as "briefs" (as in legal rather than clothing). KM could then notify you or give you a summary.

    Remember Channels in IE (kind of like RSS--) [I.E. 4 - showing my internet years here] well these briefs would appear there in one place. And if you have a Windows CE device then there were mobile channels. So your briefs could end up in your pocket.

    It worked well considering the lack of integration that the MSFT internet groups had to live with.

    Also, is it fair to say that RSS is like IE channels. Everything old is new again.

    See you next time your in Oz.

  9. John Kane says:

    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

  10. Point2Share says:

    Just found someone (birchTree, hi guys!) out there wondering how they might go about indexing a SQL Server...

Skip to main content