Solved

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

Posted on 2009-04-02
6
549 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
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 69

Expert Comment

by:Éric Moreau
ID: 24047992
SQL Server 2008 as a new feature called FileStream. It is exactly what you want.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now