Eric M Russell - Wednesday, February 14, 2018 7:17 AM
Backups. Security. Audit.
To expand slightly on this,
Backups.
we had a similar situation where we had x million critical documents held on a filestore. Backups took, due to the sheer number of documents, 4+ days. Recovery Point of this type of document, meaning a potential loss of a week's worth of data, certainly caught the attention of the board when this was explained. Log backups every 5 minutes made them rather happier.
Security.
adding effective security to a filestore of this size and complexity is rather more challenging than controlling access to a database storage system. If your document store contains any personally identifiable or commercially sensitive information you absolutely have to make sure access to this information is effectively controlled. No excuses.
Audit.
as above. If all this stuff is dumped on a disk, where's your audit trail? Who changed what, and when, and why? Who searched documents (and this is clearly easier in a properly structured database than a data jumble sale), for people, by what criteria. Who viewed data?
In the new system the 25 million + documents are fully secured. All activity; creation, position in document lifecycle, reads, updates are logged. Documents are immutable - if you make a change, even changing just the date from the 12th to the 11th, both versions, with history are stored - no idea how you'd do this on a shared drive and effectively control it auditably - which is actually critical in many situations and legally required in a hell of a lot. Partitioning of the documents by date also makes management easier, particularly as the database grows. Hitting the limit on your partition on a single SAN location could prove a maintenance nightmare in terms of storage and potential app changes if not very, very carefully planned up front. I can put my partitions wherever I want as long as I tell SQL Server. The app is obviously totally blind to that.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.