Solved

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

Posted on 2009-04-02
6
551 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in query 3 52
Storage Spaces 3 39
Getting same value for every field in SQL 2 31
Sql Query 4 16
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

13 Experts available now in Live!

Get 1:1 Help Now