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

BLOBs and DR Expand / Collapse
Author
Message
Posted Friday, June 15, 2012 4:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 3,002, Visits: 5,455
We are currently looking at options for a new database which will be mainly storing BLOB data and what our options are for DR.
This is what I know far.
DB has grown to 1 terabyte in a year.
SQL Server 2008 R2 Enterprise
Don't have details on daily activity yet, so no idea if a heavy insert or update environment yet.

But we need to have some form of DR in place.
We have four options;
Clustering
Mirroring
Third Party / custom
SAN replication.

And whether to use Filestream or have "pointers" to he BLOBs.

So the question is has anyone had experience of mirroring / clustering with the BLOBs as part of the Database, and any issues concerns?

Or should we be looking at storing the BLOBs outside the DB.

My feeling is as Filestream and use Clustering or mirroring, but this is probably dependent on the through put of the data? is there much latency with BLOB data and mirroring for example?

Goggled this and found a white paper from MS on RBS but this doesn't seem to cover DR.

Any thoughts?

Cheers,

Rodders...



Post #1316492
Posted Friday, June 15, 2012 5:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 3,002, Visits: 5,455
Some more info...

8 million rows per year.
40kb per BLOB on average.

Makes me think mirroring is a good option?



Post #1316495
Posted Friday, June 15, 2012 5:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
The main consideration here is that a database with filestream can't be mirrored (and a mirrored database cannot have filestream added)
For 40kb average for the blob, filestream's not the best. The point at which filestream becomes more efficient is about 1MB files.

Can't really recommend HA/DR without seeing RTO and RPO figures, but I do quite like mirroring.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1316506
Posted Friday, June 15, 2012 5:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 3,002, Visits: 5,455
Thanks Gail,

I either forgot that filestream and Mirroing don't mix, or it's never come up.

So it's VARBINARY(MAX)
and mirroring then?

I think a test environment is required here.

Cheers,

Rodders...



Post #1316514
Posted Friday, June 15, 2012 5:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
Well, as I said without a lot more technical details I can't recommend an HA/DR that will fit.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1316520
Posted Friday, June 15, 2012 6:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 3,002, Visits: 5,455
Yes unfortunantly I am also waiting on more details.
Hopefully more on Monday!



Post #1316538
Posted Friday, July 27, 2012 2:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 3:07 PM
Points: 15, Visits: 66
I was in a similar situation once. Decided to move our BLOBs to a flatfile system and use the database as an index to our new file's archive. Your blobs are actually not that big, so filestream won't make much improvement.

What's your read/write ratio? how often are your BLOBs being access and created? what's the ratio of BLOB queries vs regular queries?


Looks like you would benefit from a clustering solution in active/active state where you would query your BLOBs on the secondary node to reduce the bottlenecks in your application (if any).

Post #1336305
Posted Friday, July 27, 2012 3:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 3,002, Visits: 5,455
Sorry forgot to update this one.
We still don't have the full spec as such, but...

We all got excited over the BLOBS way too much.
We are actually talking word documents and the such, so currently they average 50k in size.
They built in a huge amount of overhead on space requirements. The data will only live in the db for a year. The supplier took current values, multiplied by three (just in case we needed a backup and backup of that) and 1/3 extra space.
Throughput is managable. Seem to remember in the region of <5 transactions a second.
And now the project has gone quiet. So waiting here back when the hardware is purchased etc.

Thanks Abel & Gail. It's certainly given me a chance to refresh my memory on Filestream, even though it's not needed here!

Rodders...



Post #1336344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse