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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior 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
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.