Save .pdf to SQL or path?

Question

I am wondering what the best way to save .pdf would be. Is it possible to store them as drops in SQL or is it better to store them on a data disk and store the path in a table?

thanks

Answer 1

There is not a single correct answer here, but one consideration is that the amount of data will be a problem. If it is not too large, I prefer to store them in a database for the same reason that I like to have a database with backups, consistency and the ability to restore my data. If you use a file system or even filestream, you do not back up your pdf file when backing up your database, and if you go back to another server or roll back to a previous point in time, you may end up in inconsistent pdf files and your data conditions, I am sure that it will work better, and you should definitely keep this in mind, but if performance is not a problem, of course, it’s nice to be able to restore the database to another server, even in development or and know that you have all of your data, not all of your data EXCEPT for your PDF files.

UPDATE: Martin is right, the backup data is backed up by your regular backups, and you can use the WITH MOVE option when restoring to restore data elsewhere, if necessary. Therefore, to clarify, if you use simple old pointers to files on disk somewhere, you will not have backup copies of your pdf files when backing up your database, but you will if you use filestream. You still need to be careful that the files are not modified outside the database, since the database will no longer ensure the integrity of your records, for example, the user can delete a file from the file system that indicates the stream stream, or the antivirus could quarantine the file if he was configured incorrectly. With a little planning, filestream looks like a great option for many scenarios.

Answer 2

If you plan that your database will be very large, then it is better to store. PDF files on the data disk (and only in the DB path) due to performance issues. But if your database will not be very large - saving .pdf files in the database is in order.

Answer 3

If you only store the path in the database, make sure you think about archiving. Will PDF files live forever on disk? What happens if you run out of free space? How will the database be updated in these cases?

Not an argument against storage on disk, just some things to keep in mind.

Answer 4

If you save them to a database, performance will decrease and you will not be able to use third-party tools to view the data.

Answer 5

If the file size is less than 1 MB and / or the file is rarely edited, you should save the file on the SQL server due to performance. If the file will be edited a lot and / or more than 1 MB, you must save the file in the file system.

See this question for more information: Save documents as a BLOB in SQL or in the file system