Creating A Search System from a SQL Server Database

Question

I have recently created a search system at my company using the Regain Search based on Lucene to search through all software support tickets. I run some queries in SQL to extract my data, export to Excel, import to Access, run some additional queries, create a report, export the report to .txt files, and use a file splitter to split the HTML text into pages before handing it off to the indexer. The total result is 90,000+ pages that are indexed and I only update about 2,000 or so daily as the rest are closed support incidents which do not change. The result is a great search functionality but it is a very tedious and manual process to update records.

I'd like to revamp this to a system that updates automatically each night (or at least try to automate this as much as possible). I have built a SQL 2005 server and linked it to the production server with which I have read access to. I'm trying to figure out the best approach to take from here. The tables are essentially as follows:

Case

CaseID CompanyID (FK) Description Resolution

Activities

ActivityID CaseID (FK) Date Technician Comments

Company

CompanyID CompanyName LicenseType

Escalations

EscalationID CaseID (FK) Details

So do I run a "select into" statement to make one large non-normalized table that can be indexed and searches run against? Are there some examples or books on this? Will I need reporting services? The most important thing to maintain is the fast query speed. Right now all queries return almost instantly. Any help is greatly appreciated.

Answer 1

Have you looked into SQL Server Full Text Searching? It sounds like a direct fit for what you are trying to acomplish here. If you have reporting needs, SQL Reporting services could fill this requirement.

Index maintenance can be setup to happen real time or on a schedule.

Both products are solid and productino ready in SQL 2005 but both offer some improvements if you are able to go with SQL 2008.

Some Full Text references: BOL - http://technet.microsoft.com/en-us/library/ms142571%28SQL.90%29.aspx aspalliance.com/1512_understanding_full_text_search_in_sql_server_2005

Some Reporting Services references: BOL - technet.microsoft.com/en-us/library/cc917530.aspx RulesToBetterSQLReportingServices - www.ssw.com.au/ssw/standards/Rules/RulesToBetterSQLReportingServices.aspx

There are many good books on both as well.

Not sure what the options are for Full Text directly against a linked server but you could always maintain local copies of what needs to be inexed. If you need to move data around, etc, Sql Server Integration Services is a solid product that I have used successfully in production for many years.