SQL Server – How to Rebuild the Fulltext Catalog

Very interesting approach. My upvote for the creativity.

Since you reclaimed the space, I assume the original indexes are no longer in place? The downsides of filtered indexes then are:

• Too many of them may cause the search space of the optimiser to grow too large, leading to poor query plans as the optimiser times out
• There are several situations where a filtered index will not even be considered, even though the non-filtered equivalent would be. Notably, this can happen when you get a hash join on the indexed column or if you try to ORDER BY the column (without a filter)
• Query parameterisation doesn't work with filtered indexes (see: http://www.sqlservercentral.com/blogs/practicalsqldba/2013/04/08/sql-server-part-9-filtered-index-a-new-way-for-performance-improvemnt/)

In practical terms, this means that you have to be extremely careful with filtered indexes as they will often result in horrible query plans. I would not go so far as to call them useless, but I view them as an addition to traditional indexes, not as a replacement (as you are trying to do).

stoplist can't be replicated but there is one ugly way to do it. Words in Stop lists are stored in sys.fulltext_stopwords . Using linked server you can query this table and compare it on secondary server . If there are any changes then run alter command on secondary to add\remove those changes from stoplist.

ALTER FULLTEXT STOPLIST YourStopLIstName ADD 'en' LANGUAGE 'Spanish';

Can be setup as recurring job. Not a decent method but should work fine.