I've got a relatively high-traffic public-facing product-based website backed by SQL Server as the point of authority. It has some search capability on some of the columns of the Items table (year, color, that kind of thing), and then a need for a full text search on the Description column (also in the Items table, each description is typically less than a kilobyte). At any given point in time, there are ~30k active rows in the Items table.
The search and search results are done using ElasticSearch. I asked the other dev when I started about this, and why not just use SQL Server itself; his response was that full text indexes in SQL Server are "icky," and since I had other stuff to do, I shrugged and went on with life.
That developer has since left, so now it is my responsibility. In the quest of simplifying things, I'm thinking about removing ElasticSearch and going directly to SQL. For a low rowcount (again, 30k active records), does ElasticSearch make any sense over SQL Server, other than "Don't fix what isn't broken"? What if we increase the active rowcount to 100k? Are there performance gotchas I might not be aware of in SQL Server full text search that ElasticSearch doesn't have at that level?
For the sake of this question, let's assume there isn't enough memory to make in-memory-caching work. :)