Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Revenge of the BLOB Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 11:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:59 PM
Points: 11, Visits: 352
Comments posted to this topic are about the item Revenge of the BLOB


Post #449191
Posted Wednesday, January 30, 2008 2:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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..." :D



Post #449221
Posted Wednesday, January 30, 2008 3:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 17, 2011 2:01 AM
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.



Post #449241
Posted Thursday, January 31, 2008 10:42 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #450314
Posted Monday, February 11, 2008 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:59 PM
Points: 11, Visits: 352
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



Post #453859
Posted Tuesday, February 12, 2008 4:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:43 PM
Points: 3, Visits: 221
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.



Post #454808
Posted Friday, February 15, 2008 1:29 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #456183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse