Tag Archives: content database

Questions on SharePoint 2010 Databases

What’s the maximum size for a SharePoint database?

This is a very common question across the board. Microsoft’s documentation recommends a maximum of 200GB per content database. This isn’t a limitation, just a recommendation. They are recommending this level due to performance and maintenance considerations. When your database gets much larger than that, there are some additional steps you need to take to optimize SQL’s performance. Check out MS’s white paper on Managing Multi-Terabyte Content Databases with Microsoft SharePoint 2010. This paper explains what’s needed to optimize databases between 200GB and 4TB, and then databases even larger than that!

What about Remote Blob Storage (RBS)?

RBS externalizes the documents SharePoint stores in SQL. Read more about RBS. This does allow for smaller content databases, however you’re still using up a large amount of space, somewhere. Microsoft’s recommendations as mentioned above should be considered including the space used in RBS.

Can I use the free SQL Express?

SQL Express is a great solution for development environments, and even staging if budgets are tight and the full SharePoint Server experience isn’t required. I wouldn’t recommend it for a production environment at all. Maintenance is a lot more difficult and performance will be significantly limited with SQL Express. You cannot schedule maintenance tasks like backups. You’ll have to manage all of that outside of SQL using scripts or third-party tools. Also, the Express edition has a database size limit of 10GB, and will only use 1GB of RAM. SQL wants as much RAM as you can give it, 1GB for production will slow things down considerably.

Should I run multiple site collections in a single database?

This can go two ways, though you’ll quickly see which is preferred. As noted above, the size of the database doesn’t doesn’t matter, too much, since you can work around maintenance tasks and performance issues. I prefer and recommend to load a site collection into its own content database, instead of managing multiple sites in one database. Not only does this keep the database sizes small, it also helps with recovery.

For instance, lets say you have a site collection for each of your departments, and the HR department does something that warrants a restore. If all of your sites were in one databases, you’d be forced to restore all of the site collections within that same database (or restore it to another farm, then backup that one site collection and move it over). Instead, with each site collection in its own databases, you can restore that one database and be back to work.

It’s not too late if you already have several site collections in your database. Check out the following article on Moving site collections between databases.

Additional resources on the SharePoint databases and capacity planning

Storing your SharePoint files outside of the database (RBS)

A new feature provided with SQL Server R2 is the ability to save your BLOBs (binary large objects)(the files you upload into SharePoint) to be stored outside of the content database and in the server’s file system. This functionality is called Remote BLOB Storage (RBS). I’ve had a few of customers ask for this on previous versions. Now that it’s possible, I went ahead and did it on my development box and it worked really well.

With RBS enabled, SharePoint stores all of the files in the specified file system, but serves everything up through SharePoint. The user experience and customizations will not change, users won’t know the difference.

So now that I can do it, I question why. Why store files outside of the safety and comfort of the SharePoint Content Database and in the Windows File System which is wrought with peril?

There are some pros and cons. Keep in mind, this is a per content database setting, not per library or site. If you see a need for enabling RBS for a specific need, consider creating a new site collection in a new content database.

Real Quick Technical Overview

Remote BLOB Storage utilizes SQL server’s FILESTREAM feature. This feature is independent of SharePoint and RBS, and is required to be installed and configured before installing RBS. RBS is a provider which allows applications, like SharePoint, to effectively and efficiently use FILESTREAM. Once everything is enabled, when a document is uploaded to SharePoint, the document is saved to the file system as a unique number and the SQL database is updated to reference that file when we come in looking for it later.

The Benefit

RBS was made to offload your BLOB data to a storage device instead of taking up the expensive storage on your SQL box. You can host your BLOB store on a storage area network (SAN) and alleviate performance and storage from your SQL server. If your SQL server is slower/older, switching to RBS might improve performance significantly.

Of course, there are costs and significant considerations to weigh before jumping on the band wagon.

Operational Costs

RBS increases operation costs as your IT dept has more to do and monitor with RBS. RBS will slow down tasks like backup and restore, upgrading to a new version of SharePoint, migrating sites to another environment, etc. There is in affect a second repository for your SharePoint database which IT has to monitor and manage.

Database Size

This was an obvious one I thought, but it really isn’t. Of course, when RBS is enabled, the database size won’t grow with each file that’s added. However, Microsoft’s recommendations for database sizes in SharePoint will include the size of the database and the BLOB store, so you’re not escaping their size recommendation. Using RBS will however allow you to use SQL Express past its limitations of 4GB per database, as the database won’t get that large as fast.


A small disclaimer: I have not performed any benchmark testing on these concepts. This is all according to Microsoft’s article RBS Planning. I am planning to perform some basic tests on my workstation soon, but the reasoning is sound.

If you’re working with a majority of larger files (over 1 MB) using RBS can improve performance. If you’re working with a majority of smaller files (less than 256kb), performance can be decreased.

If the files are going to be more frequently read but not revised, RBS improves performance. If files are going to be revised frequently, then RBS will decrease performance and the BLOB store will increase significantly in size as it creates and manages versions for each document.

With your files being stored in the file system instead of SQL, you remove some of the SQL overhead and memory usage when requesting the files. This allows SQL to process other important tasks and queries. Also, accessing the files from a file system is faster than pulling it out of a SQL table.

Check out High-performance FILSTREAM tips and tricks by Paul S. Randal, he covers a lot of additional tips for improving performance when enabling FILESTREAM (which is the technology behind RBS).


Maintenance efforts are increased using RBS. SharePoint will automatically flag BLOB data it no longer needs, however it’s not removed until the SQL Server RBS Maintainer tool is run. Fortunately this tool can be set on a schedule (automatically added during install) and run frequently to keep your BLOB store clean.

Maintenance on the SQL databases will perform quicker since there isn’t as much data to process. It’s important to keep the RBS Maintainer running as it keeps the database and the BLOBs sync’d and happy.

Backups via SharePoint or SQL will include the BLOB store data automatically. This is where the performance will be hit, it will take noticeably longer to backup an RBS enabled content database verse a normal database.

Other Considerations

  • Cannot use System Center Data Protection Manager 2010 to backup
  • Does not support database mirroring
  • Encryption occurs on the file system
  • Supports iSCSI drives, Network Attached Storage (NAS)
  • Does not support Direct Attached Storage (DAS)

With all that said, you gotta know what you’re doing with your sites!

My initial reaction, and I know many others think the same, is AWESOME let’s enable it!! STOP! Take the time and figure out if this makes sense for you first, and please check with your IT department, as their tasks will increase.

If you’re using your site collection as an active file share, not just posting a file once but actively editing and managing documents, you’ll need to consider additional storage for the BLOB store and consider any performance decrease as files are actively updated.

If you’re going to have a passive file share, like posting documents that are only going to be consumed and maybe edited annually the BLOB store should be minimal and performance will be improved for your end users.

For example, I uploaded a 2,289KB Word file to my library and it appeared in the BLOB store. When I first uploaded it, a copy of the file is made, then SharePoint immediately prompted me to specify certain field/metadata values, and I didn’t, I just pressed OK, which technically revised the original copy. I then changed my mind, went back in and added metadata. That changed the version of the document, therefore creating another copy in the BLOB store. I then opened the Word doc and added the word test to it. Saved it back, that created another copy and increased the size a hair. See screen shot below to see the same file listed in the BLOB store 4 times within 1 minute of uploading it.

What do the GUIDs mean?

If your site collection is going to target large files which won’t be edited frequently, then RBS is a great solution and performance will be greatly improved. For example, a photo site, videos, document repositories, record centers, ECM, etc. would benefit from RBS.

Excerpt from Microsoft’s article

Most optimal use of RBS

Because RBS is a solution created for a specific set of conditions, there is an optimal use of RBS in which the benefits outweigh the costs. The optimal environment for using RBS is an environment where the following is true:

  • You want to store fewer large BLOBs (256 KB or larger) for read-intensive or read-only access.
  • The resources on the computer that is running SQL Server might become a performance bottleneck.
  • The expense of high-cost drive space is greater than the expense of increased IT operations complexity that might be introduced by using RBS.

Least optimal use of RBS

RBS is not a good solution for all environments. The costs will outweigh the benefits most of the time. The least optimal environment for using RBS would be an environment where the following is true:

  • You want to store many small BLOBs (256 KB or less) for write-intensive access.
  • The resources on the computer that is running SQL Server are not a performance bottleneck.
  • The expense of increased IT operations complexity that might be introduced by using RBS is greater than high-cost drive space.

Other resources