Is it possible to store SQL path instead of uploading entire BLOB data?

Question

I've a c# site which is uploading images in SQL database and now that SQL table size is around 300GB. Is it possible to store the path of those images instead of storing entire blob data while uploading images from the C# site? Also, I would like to store those images in a NTFS file system. Along with that, I would like to transfer those blob data to NTFS file system and store the path of those images. If so, how could we achieve this using SQL Script without using filestream and filetable?

Answer 1

The question of writing the files from C# to an NTFS filesystem is better asked on https://stackoverflow.com/ where you can get advice regarding the C# code changes required.

For extracting the existing files, this article has some examples of different methods for performing this one-time extraction of data, but basically it is as simple as reading the blob data from the column in SQL Server and then writing the blob data to disk via an appropriate method (such as using System.IO.FileStream and System.IO.BinaryWriter in .NET).

This assumes the blob data is currently stored in a VARBINARY/BINARY data type field.

As part of the extraction process, you need to take into consideration things like:

• Naming the file (can you pull a filename and extension from the table?)
• Writing back the path of each extracted file to the database (will require the path field to be added prior to the extraction)
• Suitable safety checks before removing the blob data from the DB to verify the data on disk (sizes match, depending on the filetype some kind of verification that it is not malformed, etc)

As far as the future storage, you can simply store the path to the file in a string field (VARCHAR, NVARCHAR) of suitable length. You C# application could then read the file using this part as part of the record loading process. Creating a new record would involve saving the file to the NTFS filesystem and writing the resulting path to the SQL record in the new path field.

Answer 2

You should be aware of the three main problems of moving images out of the database:

1. File drift - where the files are moved or deleted by someone/something else. This can be mitigated by running a regular process to check the files are where you expect them to be.

2. File corruption - All images in the database will be looked after by a DBA with regular DBCC CHECKDB etc. SysAdmins will not be checking if files are corrupt. You could hold something like a MD5 hash of the image in the database and have a process that regularly checks it matches the MD5 hash of the files. You could also consider putting the images in S3 like object storage where redundancy makes corruption much less likely.

3. Transactional Consistancy. This is a lot easier to achieve when everything is in the database. Also, databases will have backups (log backups) more often than file systems or object stores so images could be missing in a disaster recovery situation. You could mitigate against this by storing the images in the database, as well as the file system, for a few days.

There are, of course, advantages to having the images outside the database. Fewer expensive SQL Server core lisences, quicker backup/restore, quicker database maintenance etc. However you can see why a lot a third party applications put the images in the database to reduce their support costs.