Editorials

Databases Vs. Business, BLOBS and More

Latest Show Posted
Watch: SQLonCall: How to Hire a DBA – Part 3/3 – The Interview. Questions to ask, answers to look for, interpreting the responses, making the decision.

Also available:
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)
Databases vs. Business
The software package will allow the sales team a better chance to qualify their prospects. This in turn will mean that the time they spend talking to prospective clients will lead those clients into a better probability of buying whatever your company sells. Everyone at the company is excited about the new tool. Well, that is, everyone except for you.

Dedicated SQL Servers
Looking for expert support, outstanding DBO access to your databases and all of the enhancements and tools that are part of SQL Server 2005? Check out MaximumASP – they offer all of this and more. Full-text search, XQuery and all sorts of supports and options for SQL Server hosting are all available to you with your hosted SQL Server plan. Get more information here.

Headed to DevConnections
If you’re going to be at DevConnections, drop me a note – let me know. Both Chris Shaw and I will be looking to talk with folks and hear about what you’re doing, what you think of the show and much more. Drop us a note at swynk@sswug.rog or chris.shaw@sqloncall.com.

More (can you believe it?) on BLOBS and Images
Nick
wrote to say: "We do not store images in the DB because it makes EVERY backup very large. Our images do not change, so storing them in a folder and having pointers simplifies backups and makes them more robust. The image folder can be backed up, and if necessary regenerated by things like Server 2003’s Previous Versions, without losing anything on the coding or data end.

I have a client whose app stores the images in the DB. We were backing up to an offsite FTP server–and then the DB bloated up to 1.4 GB due to images :(. And whacking out unused bitmap images for non-current sales items, or items whose image had changed was not supported by the vendor. Very bad.

If the DB could store images efficiently maybe there’d be a better case. Not with the technologies I am using at present though."

Bryan’s thoughts: "I think its definitely a good a good idea to store images in a database. The debate should no longer be focused on speed as the technology has improved over the years in databases. The retrieval is just as fast coming from the file system and you will not notice a difference.

http://www.terraserver.com/ is a multi-terabyte database and is a very popular example of an application where images are stored inside a Sql Server database.


There is a lot more to be gained by storing images in a database; certainly there is a lot more control over access as files on disk can be deleted or modified by any external application. This increases the level of data integrity and is just an example of one advantage that can be achieved. A design pattern that is good in terms of performance is having a table just for images instead of adding your image column to an existing table with other columns and also turning on options such as "text in row" or "large value types out of row" depending on the types chosen to store these images.
"

Jim’s approach: "Whether to save the image in the database depends on the size of the image. I have worked with storing large objects/reports which can reach 2 GB in size. It is obvious to any DBA that writing and committing a single transaction of 2 GB takes some serous processing.

This is where storing the file in the file system and saving a reference to it is the way to go. When files are this large the application which stores the file in the database has far more problems with performance that the one which does not.

I worked with one system where the application generating the file of 2 GB worked so fast (since the file system was local) he/she did not even realize the file was so big. If the application was storing it in a the database it definitely would have take a LOT longer. Of course if the images are small then they should be stored in the database."

And, lastly today at least, Kevin wrote: "This debate is all very interesting, especially as it is an age old conundrum… I am currently working on a social network type system which will be utilising SQL 2005 and I outlined the pro’s and con’s for each method to the powers that be but the technical director has introduced an interesting 3rd option which we are currently looking at… Storing the images in a 3rd party online data store(i.e Amazon S3), this places the availability of the data onto a 3rd party and the associated risks with this but it does remove the argument about size/time of backups on the db and also providing the storage space on our own environment… this may be an interesting topic to add to the equation."

Featured White Paper(s)
Microsoft T-SQL Performance Tuning Part 1: Analyzing and Optimizing T-SQL Query Performance on Microsoft SQL Server using SET and DBCC
This white paper, authored by SQL Server expert Kevin Kline, discusses the basics of indexes, such as density and selectivity… (read more)

10 Steps to a Successful Modernization Project, Legacy Database or Application Upgrade
The goal of a modernization project is to transform aging applications to more-modern architectures while preserving the busi… (read more)

7 Steps to Successful SQL Server Auditing
This easy-to-read guide will explain and simplify the basic steps associated with successful SQL Server auditing & give you t… (read more)

Data Privacy Compliance in the Application Testing Environment – Data Masking and Transformation for Privacy Compliance
News of high-profile data breaches has increased awareness and heightened the need to protect data privacy in the more vulner… (read more)