June 15, 2012 at 4:59 am
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...
June 15, 2012 at 5:05 am
Some more info...
8 million rows per year.
40kb per BLOB on average.
Makes me think mirroring is a good option?
June 15, 2012 at 5:25 am
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
June 15, 2012 at 5:39 am
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...
June 15, 2012 at 5:47 am
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
June 15, 2012 at 6:19 am
Yes unfortunantly I am also waiting on more details.
Hopefully more on Monday!
July 27, 2012 at 2:08 am
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).
July 27, 2012 at 3:26 am
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...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply