Revenge of the BLOB

Question

Revenge of the BLOB

Andrew Sears

SSC Veteran

Points: 251
More actions
January 29, 2008 at 11:37 pm

#183651

Comments posted to this topic are about the item Revenge of the BLOB

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Great article. I must admit that we use BLOBs to store data in MS SQL, but the BLOBs (which are documents) are stored in a separate (or many separate) DBs containing just a single table for the BLOBs.

It makes it easier to backup a site if they just need to ensure they have all MS SQL DBs backed up properly. We split the DBs at around 3.5GB or so which makes it easier to archive them as the old ones never change. Performance is great as far as I can tell. Finally, since the data is locked up in a DB, we can better restrict and record who accesses the BLOBs - perfect for medical records. However, as always, the answer is a bit of "it depends..." 😀

mbuttazzoni Old Hand Points: 314 More actions · Answer 2

The decision to store real data in a blob fields instead of in a table (one or more) looks quite unusual, and the solution you propose is savvy and natural.

Regarding the way to archive images and documents directly into the db. I know the cost in term of performance, due to the size of the blob fields, but, with a proper division of the DB in more than a single filegroup and the consequently mapping of blob fields to a dedicated filegroup, is possible to mitigate the performances tax.

Moreover, it's to remember the the choice to incorporate docs, files, images directly into the db (unacceptable by a noble and pure DBA) is the one adopted from MS itself for the entire Sharepoint architecture!

And the performance of a Sharepoint server aren't so bad, to discourage the use of blobs in the db. But it's strange that the structure of the Sharepoint dbs is flattened in a single filegroup: perhaps why the principal focus in these db are pages and documents and not the related metadata.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

This was an interesting and good article, but I am curious what trends you are seeing to make you say that BLOBs are becoming more common. I can certainly see situations where they are necessary and we make use of them in certain situations in the database back end to an application, but at least where I am I have hardly seen anything to indicate a trend.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Andrew Sears SSC Veteran Points: 251 More actions · Answer 4

With regards to blobs becoming more common, I was referring to discussions around SQL 2008's filesystem data type, and not specifically storing blobs in databases.

I have no evidence of a trend either, other than my observations and the fact that I seem to be working more with the data type for things like document management.

Looking forward to SQL 2008.

cheers,

Andrew

localadm Valued Member Points: 53 More actions · Answer 5

Ian Yates,

You mentioned that you split the database containing BLOB into smaller chunks. I am very interested in that topic. I have a database of 0.5TB, and 95% of the data are in a particular column of type text in a table. I am trying to split the database from a single .MDF into several smaller .NDFs. The challenge is, how to move this column from MDF to several data files (either within same file group or different group). I have tried moving the clustered index, but seems like only the index is moved, data is still in .MDF.

I asked MS support and was told it "could" be done within same file group, but need to do some research. I'd much appreciate if you can shed some light one what you have accomplished to split LOB.

Thanks.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 6

localadm (2/12/2008)
Ian Yates,
You mentioned that you split the database containing BLOB into smaller chunks. I am very interested in that topic. I have a database of 0.5TB, and 95% of the data are in a particular column of type text in a table. I am trying to split the database from a single .MDF into several smaller .NDFs.
Thanks.

Localadm, have you considered partitioning your table either horizontally or vertically?

Obviously it is not precisely what you are asking for, but depending on your situation it could bring greater benefit, especially if there are frequent queries against the table that do not need the text column, you could easily split it off into a table by itself with a primary-foreign key relationship to the main table and keep the main table smaller and faster that way.

If there is some logical way of splitting it into two logical pieces that would give you smaller files to work with and again mean you don't have to query the entire data set when you know the result you need will be in one or the other. You could, naturally, use a view to union the tables it was split into for when you do need to query the entire thing.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/