Combine relational DB and Elastic search
Use both.

There is a line that needs to be drawn by you and your team here. SQLSERVER is more expensive than ElasticSearch so when I faced a similar issue it made more sense to spend CPU resources on elasticsearch than on sqlserver.

There's a few things that can make you decide to index your text data in elasticsearch

What kind of load are you looking at ?

A few searches per minute or dozens per seconds ?
This is subjective but again if a large amount of your database resources are spent on this one query you might want to offload that.

Keep your data structured

I find elasticsearch query language to be far less intuitive than SQL. I strongly suggest to have an as-normalized-as-possible version of your data in a standard relational database. Then base your elastic index on that.

Elasticsearch is great at many things but writing complex ad hoc queries with aggregations and/or subqueries isn't one of them.

How do you sync the data then ?

Triggers and queues are what I've been using.

Add a trigger on the table that has data you want to keep track of. Here's what one of the queues I've done looks like.



The trigger logs the action (insert/update/delete) and from there you know what to do in your Elasticsearch index. I've found rebuilding the entire record in elastic wasn't too expensive so this is what I do.

This way, you can take a project with a large code base and index any data you like in elasticsearch without having to do any code changes. Everything is handled by the state of your data in your RDBMS of choice.

Elasticsearch (and all the other nosql/document stores) have amazing use cases but storing relational data as a main database is not one of them. Use relational databases for that.