Does SQL store any sort of file

I have a VB program where I am accessing SQL server.  I would like to also program the ability to connect a document and save it in the SQL database.  Is it possible to store pdf, jpegs, xls, and files of any sort within SQL?  If so, what is the datatype?  Does performance slow down over time as these types of files are stored since they take up more space?  Can I archive these files every couple years in the database to free up the space?

Any suggestions on this topic would prove to be helpful.
al4629740Asked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
You've a couple of options here. Firstly, you can use your application to store the file in binary form into a varbinary(MAX) column. This will store into the database. Or you can enable FILESTREAM which is a part of the file system that will be used by SQL Server to store files. You can customize it to restrict access to only be through SQL Server, or allow direct access to them.


Kelvin
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
When using SQL Server you could use the FILESTREAM data type for this use-case. Thousends of files are not a problem and BACKUP/RESTORE takes care of this data also. For backup and outaging old data you can use filegroups and partitioning over time.
0
 
al4629740Author Commented:
You can customize it to restrict access to only be through SQL Server, or allow direct access to them.

What does that mean?  Example?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ste5anSenior DeveloperCommented:
He meant file access via a network share. "direct access" is missleading.
0
 
al4629740Author Commented:
Pardon my stupidity on the subject, but what is the difference between binary form varbinary(MAX) and FILESTREAM?  What is the advantage and disadvantage of both?
0
 
PortletPaulConnect With a Mentor freelancerCommented:
varbinary(MAX)  = this data is stored INSIDE the database itself
FILESTREAM = this data is stored outside the database as individual files (hence can be accessed by network share)


the following is VERY brief and I am NOT a SQL Server dba, but in general (regardless of databases):

The choice depends (largely) on your backup/restore strategy

backups are (arguably) simpler if the binary data is inside the database (but the backup files are much bigger and hence also slower)
restores are much simpler if the binary data is inside the database (but the restore could be slower)

if you store the data outside the database it is possible that backup/restore cycles become unsynchronized
0
 
al4629740Author Commented:
Is performance better on the one over the other?

Also, so FILESTREAM utilizes a different spot on the SQL server machine to store the files?
0
 
al4629740Author Commented:
Also, between varbinary(max) and Filestream, which one is smaller, hence faster, to transmit over the internet?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.