Ask HN: Best file storage solution for our product? | Hacker News Reader

Ask HN: Best file storage solution for our product?

2 pointskorpen5 comments

Hi Hackernews!

Longtime lurker here, and I finally have a question.

We have an existing product with a file archive as part of the feature set. Currently files are stored in MSSQL in the same database as the rest of the data. This isn't ideal, since it leads to long backup/restore cycles, not great performance, and expensive database SSD storage. The larger instances (single tenant) are ~2-3 TB of which 90% are files, but we predict file archive usage only going up.

Requirements: - On premise hosting - Search file contents (Word/Excel/PPT support) - Good backup support (differential backups would be great!)

View on HN

Are you using FILESTREAM (https://docs.microsoft.com/en-us/sql/relational-databases/bl...)?

I haven’t used it, but it seems designed for that use case (FTA: “If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.”)

Currently using varbinary(max) in database, which limits us to 2 GB per file. Filestream doesn't have that limit which is good.

There are some caveats we'll have to consider though.

> Encryption is not supported on FILESTREAM data.

We currently are under contract to always have data encrypted at rest for some customers. (Yay enterprise!)

> SQL logins will not work with FILESTREAM containers. Only NTFS or ReFS authentication will work with FILESTREAM containers.

We've got so many different environments that our database is hosted in, so this could be hard to do, but perhaps possible. A lot of governmental facilities as customers that will not connect their data to the internet means a real headache when introducing changes.

But I'll still will spend some time to evaluate this. It seems like the easiest technology to introduce and migrate to.

> Encryption is not supported on FILESTREAM data.

I think that means you can’t have MSSQL encrypt FILESTREAM columns, but still can have them stored in a file system that encrypts data (if so, that still may be different in the sense that the file system uses a single encryption key, while the database may support multiple ones, but data will be encrypted at rest)

File System with metadata in your database ?

It could kind of work. I'm looking into Apache Tika to parse text from document and Apache Lucene to index contents for full text search support.

Reply on news.ycombinator.com