Solved

vb.net sql server solution - store images in file system or database ?

Posted on 2009-04-02
6
558 Views
Last Modified: 2012-06-21
I am building a VB.NETsolution that allows users to load their own images. The images will be mostly JPG format with a few other formats

I am trying to work out whether I should store these images inthe SQL Server database, or as files outside the database.

If I do store them in the database, how do I provide the paste / upload function that allows users to create a new image ?

I would appreciate feedback on what the best approach would be.

Thanks




0
Comment
Question by:dkmarsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24047793
I suggest you store the images in a folder and not in the database. This will keep your database lean because images tend to occupy too much space in a database.

I have done this on one of my solutions where I had to keep photos of all visitors at some company.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 24047861
I also recommend you store the images in the filesystem and not in the database, just store the paths in the db. SQL Server was not designed to be a filesystem and so doesn't work as well as one.

This question crops up quite often, and the answer is always the same. Have a look at some of these articles too:
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
http://www.sqlteam.com/article/images-and-sql-server

HTH
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24047992
SQL Server 2008 as a new feature called FileStream. It is exactly what you want.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 22

Expert Comment

by:dportas
ID: 24048068
Definitely consider using FileStream. For many if not most scenarios Filestream supersedes the old dilema about whether to put large objects in the database or filesystem. There are one or two constraints on what you can do with Filestream but it can also give you the best of both worlds.
http://technet.microsoft.com/en-us/library/bb933993.aspx
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24059339
FILESTREAM is good, but really needs to be considered in many ways as if it is owned by the database, and for large blob type images / data it is not always the most desirable case.

Regardless, you need to avoid adding the images directly into the database - plays havoc with all kinds of database issues - not least of which is size and manageabilty of the database in general.

The thing with filestream can depend on who "owns" the data and what external resources need access (and type of access). If you consider the single main difference as a point of ownership, it helps put things into perspective.

SQL owns filestream. It has to be enabled : EXEC sp_filestream_configure @enable_level = 3  The owner of the SQL Server Service should really be a domain user so that the domain user can "own" the directory / data repository on disk (ie  in its own private namespace on the local NTFS) and uses it's own naming philosophies based on a mandatory row GUID, and so is essentially masked from the user. From a web / internet perspective, it is not the user or web server needing access, it is the SQL Server Service needing access. It does have benefits in being "owned" by SQL server - things like backups and versioning are readily handled.

Going external means you have to manage. But then it can be quite seperate to the SQL Server, can have different security policies in place and can have other applications directly accessing the images.

So, there are two viable alternatives. One which is the equivelent of capturing the image as at a point of time within the database realm, and the other using a file / path pointer inside the database to and externally available folder. Both keep the blob data outside the database and that is really the bottom line for the question as asked. Keep it external.
0
 

Author Closing Comment

by:dkmarsh
ID: 31565698
great summary - thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question