SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should

One of the new things in SQL Server 2008 R2 is Remote Blob Storage (RBS) which allows admins to setup SQL to save data that would normally go into a BLOB field to be stored somewhere else using an RBS provider. This provider could store data on a cheaper disk solution (compared to the expensive disk solutions usually selected for SQL Server), to a SAN or maybe even into the cloud… it really doesn’t matter where. The point is that BLOBs can be kept out of a SQL Server DB.

For SharePoint this has big implications because as a document/attachment centric product, it stores a LOT of data in BLOBs generating large content databases. For a while people have asked if you could store that data outside of SQL which was pretty much not the case. OK, we did have External Blob Storage (EBS), but it was not recommended.

SharePoint 2010 when combined with SQL Server 2008 R2 allows you do RBS so you can keep everything in your content DB’s except the BLOBs. However this shouldn’t be a knee jerk decision…

One thing I’m seeing in many developer sessions I do are developers asking this same question and when you explain what RBS is and how it works, many folks jump on it. However this should be something that is discussed and debated with your SQL Server admins & server admins. Why?

Think about the backup & restore story. You need to make sure the backups of both your SharePoint servers, content databases and now, if you’re employing RBS, the place where the blobs are stored. Considering SharePoint is a collaborative solutions and those who are looking to employ RBS are usually those with heavy SharePoint investments & high usage. Therefore you need to carefully coordinate the availability of the SQL Servers as well as the underlying BLOB storage as well as the backup strategy.

Technorati Tags: sharepoint,sharepoint 2010,rbs,sql 2008

posted on Wednesday, March 10, 2010 1:36 PM

Feedback

# re: SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should 3/10/2010 7:10 PM Jeff Dalton

Paul Randal discussed streams and SQL Server in an old RunAs Radio. If you are considering BLOBS on SQL Server streams you should give it a listen.
http://www.runasradio.com/default.aspx?showNum=74

# re: SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should 3/10/2010 8:30 PM Samuel Yates

The synchronized backup/restore concern is a legitimate one, but I would fully expect to see Microsoft and other big vendors (EMC comes to mind) bring out end-to-end data protection solutions that deal with this. I'm a little surprised doesn't already have a plan for this with DPM (assuming they don't, I haven't checked). In any case, it's hardly a new concept, as other big ECM products like Documentum have handled the meta-data/BLOB split this way for many years. As long as you're aware of it, it's not impossible to plan around.

My biggest hope for RBS is that it gets adopted on a widespread basis to the point that it forces Microsoft and other self-proclaimed SharePoint experts to stop telling we should have content databases under a certain number of GB for "performance reasons". I'm pretty tired of hearing this when the number of GB is such a meaningless metric for most performance discussions. Now whenever I hear oh best practice says you should keep your site collections at less than 20/50/70/100GB for performance reasons, I respond with something like - "so if I have 1000 100MB documents in a single document library that all share the same permissions and have a very simple group structure, I'm going to have performance concerns on that site collection". Almost always the answer is no (if they are honest), because really the performance in many/most cases to be a lot more dependent on things like how many rows there are in AllUserData, Perms, RoleAssignment, etc than on the size of the BLOB content stored in the DB.
Woah, sorry about the mini-rant there. Anyway, I'm glad to see people starting to discuss the pros and cons of RBS

# re: SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should 3/11/2010 8:02 AM AC [MVP SharePoint]

Sam-
MSFT's take with the size of a content database isn't a hard & fast rule... it's more of a guideline just as your example states. The biggest reason to watch for the size issue is backup & restore IMHO.

While your point about RBS and backup tools keeping the various stores in sync, this is just but one example. I didn't want to go off on all these reasons why you should NOT use RBS because when used correctly under the right conditions it can be a great solution. I elected to not enumerate different scenarios for fear someone would think I was dumping on RBS which isn't the point here.

The reasoning behind this post is more because I see just too many folks iwth the knee-jerk reaction of "that's how I can keep stuff out of the DB" when it should be carefully evaluated.

# re: SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should 3/11/2010 8:09 AM Spence

Hey Samuel,

Microsoft indeed have a plan for integrated backup of both the content dbs and any externalised storage. You can be sure of a decent story here from MS (and hopefully other SharePoint Backup vendors).

Unfortunately the SharePoint "experts" have been giving you a bum steer re the size of content dbs. The primary reason for such recommendations are not performance at all, but indeed as AC points out time to backup and more importantly restore - in line with SLAs.

Fundamentally using RBS doesn't really influence this at all. RBS is absolutely not a solution for "large content dbs" because there is no solution neccessary for a problem that doens't actually exist.

Widespread adoption of RBS for SharePoint when it's not neccessary (and it isn't to deal with the 100Gb magic number) is not a good thing. BLOBs are not a bad thing! there are specific scenarios this support is intended for.

As with most things in this arena, it's a compromise between cost, requirements and complexity.

# re: SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should 3/12/2010 9:16 AM Paul Swider

IMO the snippet from TechEd below outlines any consideration for RBS. The majority of SharePoint implementations will never approach the performance and cost limits to justify RBS. One might also submit the decision should only be considered where SharePoint is used for ECM. It’s my understanding that one big reason for RBS is to help administrators with a monetary cost concern for massive storage requirements in very large repositories.

Thanks for starting the conversation AC. I share your concerns, It would be a shame to see lots of folks “jump through hoops” to solve a problem that doesn’t exist. I like your comments as well Spence. I couldn’t agree more. It occurred to me some time ago that most SharePoint projects I work on are guided by determining SLA upfront and then architecting solutions within those parameters.

TechNet- “By default, Microsoft SQL Server stores BLOB data in its databases. As a database’s usage increases, the total size of its BLOB data can expand quickly and grow larger than the total size of the document metadata and other structured data that is stored in the database. This activity consumes large amounts of file space and uses server resources that are actually optimized for database access patterns. “

# re: SharePoint 2010, SQL Server 2008 R2 & RBS 3/16/2010 1:12 PM stefan demetz

Paul, for instance today I spoke to a customer which produces around 5 milion invoices a month (+ all invoices of past 10-15 years) and RBS is a BIG topic as the alternatives are Documentum or Filenet. MOSS 2007 would not be able to support that kind of data volumes in a standard content database ... Probably not a Sharepoint problem, but SQL Server, as it does not support clever partitioning on Sharepoint data

# re: SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should 4/9/2010 10:26 AM Michael Weber

I think what's at the heart of the issue is that we are starting to see a standardization effort in separating BLOBs from metadata. We all know SQL Server is phenomenal at managing text/metadata. However, prior to SharePoint, it often took a pretty-darn good reason to store BLOBs in the DB when I developed ASPX solutions - and rightly so. Microsoft's recent white papers indicate degradation in SQL Server performance with BLOBs over 1 MB. Given that the RBS Filestream (which is RBS-lite in my opinion) has an option to store data based on size in the BLOB store and SQL Server, I think this is really slick stuff. Not to mention you can swap out providers that implement RBS. AC nailed it though - PROCEED WITH CAUTION. This drastically changes the admin landscape, tool sets and potentially your organization's DR.

# re: SharePoint 2010, SQL Server 2008 R2 & RBS: Just because you can doesn’t mean you should 4/20/2010 11:09 AM Stephen Cawood

I've seen substantial performance benefits when third-party software was used to remove BLOBs. It was eye opening.

Even on small DBs, the difference was impressive. One reason why is that you don't have to upload everything into SQL in the first place. This substantially effects the performance of many operations.

Much quicker backup is one good reason to do it, but don't dismiss performance before you try it.

Post Feedback

Title:
Name:
Email:	(email will not be displayed)
Url:
Comments:
Please add 6 and 1 and type the answer here: Remember Me?

All Comments Are Filtered & Moderated
Unfortunately comment spammers are just too effecient and are constantly dirtying up blogs with irrelevant and unwanted comments trying to improve their standing on search engines. All comments on this blog are moderated. I do not censor comments, but I don't approve comments with vulger language or those soliciting products. Most of the time comments are approved within a few hours of being submitted with the only exception when I'm traveling.

Why are you asking for my email address?
The only reason I'm asking for your email address, which isn't required to submit a comment, is to provide a gravatar if you've created an account for yourself and associated your email address with a small image. If you have a gravatar created for the email address you submit, it will appear next to your comment. Otherwise nothing will appear.

What is a gravatar?
A gravatar is a "globally recognized avatar." You can get more information about gravatars, as well as create your own for free, at www.gravatar.com. You can also view my gravatar here.

This work is licensed under a Creative Commons License
Site design by Heather Solomon.