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


BLOBs and DR


BLOBs and DR

Author
Message
rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6451 Visits: 8673
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...



rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6451 Visits: 8673
Some more info...

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

Makes me think mirroring is a good option?



GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223466 Visits: 46297
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, MVP, M.Sc (Comp Sci)
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


rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6451 Visits: 8673
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...



GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223466 Visits: 46297
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, MVP, M.Sc (Comp Sci)
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


rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6451 Visits: 8673
Yes unfortunantly I am also waiting on more details.
Hopefully more on Monday!



Abel A.
Abel A.
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 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).
rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6451 Visits: 8673
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...



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