SQL Full-Text Search vs ElasticSearch
We're looking to implement a full-text search of .pdf documents we have stored in a SQL database. The application front-end is Angular. The plan is a textbox within the application that users can type a search term into and have it bring up all .pdfs that contain that term.
The documents are stored as [VARBINARY](MAX) FILESTREAM in a single SQL table. There are currently around 500,000 .pdfs in the table and we average approx. 4,800 new .pdfs added each month.
I want something that will return results to the user within a couple of seconds and that won't require any manual process when new .pdfs are added. It needs to handle multi-page .pdfs and should allow us to retain our existing security restrictions on what documents the user is allowed to see.
Based on my research it seems like Elasticsearch is the best tool for this, but I've also been looking at the native SQL Server full-text search feature. It seems like it would be significantly easier to implement and maintain, but I'm worried about performance given the number of files.
I'm new to full-text search. Does anyone have any experience with these tools? Or have a recommendation for a different one?
Thanks!