I've got a reasonably large MSSQL database with a table of about 6 million rows and around 50k inserts daily. One of the things I'm starting to struggle with is fulltext search, as I've a requirement for one of the columns in the table to be fulltext indexed.
I've initially gone with MSSQL 2008 R2 built-in fulltext running on the same host as the database engine, but I've recently run into performance and reliability issues. I often see the CPU pegged at 100% and assume that this is the fulltext index running a population. I also see my frontend return innacurate or not up-to-date data in the results of a search which uses the fulltext index.
What are my options? I'm dimly aware that it might be possible to run the fulltext processing on a separate server, but I'm not sure how to implement that or whether I'd want to. I'm also aware of other options like Lucene.NET, but don't know if this would be the right approach to take.
My requirements are simply the ability for fulltext searches on this table to be as up-to-date as possible, bearing in mind the insert rate on the database.
What would people suggest?