SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Revenge of the BLOB


Revenge of the BLOB

Author
Message
Andrew Sears
Andrew Sears
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 358
Comments posted to this topic are about the item Revenge of the BLOB



Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1898 Visits: 445
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..." BigGrin



mbuttazzoni
mbuttazzoni
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 16
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
timothyawiseman
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 920
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
Andrew Sears
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 358
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
localadm
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 357
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
timothyawiseman
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 920
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search