Latest Video Shows
Watch: SelectViews – Replication, Part-Time DBAs, 60-Second Tip of the Day
Watch: SQLonCall: How to Hire a DBA – How to review resumes, what to look for, what to avoid.
Featured Article(s)
Understanding Software Testing (Part I)
As we all know, Software Testing is an important facet in Software Development, be it product development or project development. It is now considered as one of the most important and mandatory phases in a software development life cycle. In this series of three articles, we will take up various common methods of Software Testing adopted by any project/product development team and also look through the various levels of testing.
Why The Debate? (Images, Blobs in the Database)
I’ve had a real landslide of messages on this topic – whether you should store images in the database, how you manage the physical files if you don’t, and all sorts of other aspects to this topic. Traditionally, you wouldn’t have much of a debate – you just wouldn’t store them in the database. The overhead, performance impact and such would have been clearly against doing so. BUT, things are changing – both now and even more so in the future. Changing toward supporting these types of unstructured objects in the database and being efficient about doing so. That was sort of the genesis of the question and the follow-on discussion. I hope that, collectively, we’re keeping track as technology moves forward to support this.
So, without further ado, here are some additional items of feedback from SSWUG readers.
Jesper: "I think there is a clear answer to this question, but not to the questions that might follow: There is no reason not to store the images in a database, it will help keep the data consistent. The question is if they are to be stored in a table of their own or if they should be part of another table. To decide this you need to know how the queries you will run. If the queries always will need to fetch the images, then storing the images in another table can be expensive – unless your queries will generate a small resultset. If your queries generate a small resultset and you cannot use indexes to pick the rows (but resort to tablescan) images in the table will be expensive. If on the other hand you have great indexes, then not reading the images from the same table requires disk seeks (having the index already is an indirection adding an image table is another)!! And you need to read sequentially from disk to get speed. "
Mary: "Storing images in the database can cause performance and efficiency issues and there is more of a preference to store the link to the file within the database instead of the image, especially for databases used for web/oltp systems.
There are new financial industry compliance requirements that may be met by storing the image within the database instead of on the file system (and less management of the file system and security versus a database/table). Make sure you test performance, shrink/re-indexing, backup/recovery, migration and disaster recovery since this may change your processes if you do store images in the database; managing database performance, backups and growth/capacity planning will become a higher priority."
Ralph: "I am now working in an environment that deals with a lot of images. We have document images that come in as faxes as well scanned pages, either attached to emails or scanned on site. We also have to deal with photos.
In some cases (both home-grown and vendor developed), we have stored the images on a file server and saved a link (usually a fully-pathed file name) to the image in the database. In a couple of cases (one of which I am currently working with for other reasons), we have stored the images in the database. We, too, are currently discussing the pros and cons of the issue.
Here is the basic set of pros and cons that we are discussing for the two options:
Images Stored on a File Server:
PROS:
– The link takes up considerably less space than the image;
– Backups of the database take less time;
– Back ups of the images is easier;
– Virtually anyone can code to retrieve the image from the file server.
CONS:
– Once the database is accessed, a second access has to be made for the image;
– There is an additional server involved in retrieving the image and, therefore, there
is another potential failure point;
– Images can be deleted from the file server, thus orphaning the database links;
– Rows can be deleted from the database, thus orphaning the images;
– It is more likely that, because any developer can create the routine to load the
image from a file server, _every_ developer will develop his _own_ routine.
Images Stored in the Database:
PROS:
– Retrieval of the data can include retrieval of the image without another access;
– The blob/image columns can be partitioned to decrease some of the size and
handling issues;
– Indications are that by SQL Server 2008, for sure, whether the image is stored
in the database or on a file server will be rather transparent;
– Backups of the database also back up the images;
– A standard routine can be created and used by all developers to extract the
images from the database.
CONS:
– The blob/images make the table take up considerably more space than the link ;
– Backups of the database take more time;
– The process of streaming the image from the blob column to its display
destination is more complex than loading it from a file server.
Part of the reason we are revisiting this issue is that we are, kind of, hoping to standardize our approach, at least internally, as we consider the transition from SQL Server 2000 to SQL Server 2005 or, (as I am championing 😉 to SQL Server 2008."
More to come (There are SO many comments that have been received – thanks!) – send in your thoughts here. (And if you have a question for the masses, please drop me a note here)
Featured White Paper(s)
SQL Injection Protection White Paper
This white paper demonstrates various techniques that can be used to evade SQL injection signatures, and explains why it is n… (read more)
File Fragmentation, SANs, NAS and RAID
Does fragmentation affect SANs, NAS, and RAID? Many people think it doesn’t-but that perception is incorrect. Disk arrays imp… (read more)
Foolproof Upgrades for SQL Server 2005
SQL Server 2005 is a major new release of Microsoft’s flagship database platform. But how do you know when is the right time … (read more)
A Network Engineer’s Guide to Troubleshooting User Satisfaction Problems with SAP Applications
Is It the Application or the Network? If you’re a network engineer in an organization that runs SAP, you’re probably famil… (read more)