SharePoint is great for document management system, collaborative file storage, record management system, you name them all. One downside (or upside, depend on your perspective ) in SharePoint, it stores the files (or the blobs) in content database. The advantage of that approach is it simplify the backup and restore process. You backup a site collection, all related data of it are backed up in one file. The disadvantage is the database size can be gigantic if your SharePoint manages million of files. There’s a critical problem if you use SQL Server Express Edition which has a limitation in database file size, which is 4GB in 2008 and 10GB in 2008 R2.
Fortunately, we can configure SharePoint so that it store the blob in the NTFS file system, instead of in database. It’s possible by leveraging FILESTREAM feature of SQL Server 2008 (both Express and non Express), and Remote BLOB Storage (RBS). RBS is a library API set that is incorporated as an add-on feature pack for Microsoft SQL Server 2008 and Microsoft SQL Server 2008 Express. RBS is designed to move the storage of binary large objects (BLOBs) from database servers to commodity storage solutions. More details about RBS is here.
This blog post is all about the configuration. Let’s start. I spent a whole afternoon for this.
Check SharePoint Web Application’s database
First thing first, to configure FILESTREAM and RBS, you need to know what is SQL Server instance and database used by SharePoint.
1. Open SharePoint Central Administration (Start –> All Programs –> Microsoft SharePoint 2010 Products –> SharePoint 2010 Central Administration)
2. Click Application Management –> Manage content databases
3. Change web application you want to configure (1), and check the database name (2). In this case (generally), WSS_Content
Enable FILESTREAM on SQL Server 2008/2008 R2
To enable and change FILESTREAM settings (adapted from How to: Enable FILESTREAM
On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
In the list of services, right-click SQL Server Services, and then click Open.
In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
Right-click the instance, and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQL access check box.
If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
In SQL Server Management Studio, right click database server name, and click New Query to display the Query Editor.
In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure filestream_access_level, 2
Provision a BLOB Store with the FILESTREAM provider
1. Fire up SQL Server Management Studio
2. Right click to WSS_Content database, and click New Query.
Write this TSQL
if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##')
create master key encryption by password = N'Admin Key Password !2#4'
if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')
alter database [WSS_Content] add filegroup RBSFilestreamProvider contains filestream
alter database [WSS_Content] add file (name = RBSFilestreamFile,
filename = 'c:\BlobStore') to filegroup RBSFilestreamProvider
I use “C:\BlobStore” folder to store all the files. You can change it to any folder you like.
Tips from TechNet: For best performance, simplified troubleshooting, and as a general best practice, we recommend that you create the BLOB store on a volume that does not contain the operating system, paging files, database data, log files, or the tempdb file.
After executing those TSQL, make sure the folder (C:\BlobStore in this case) is created.
Install Remote Blob Storage (RBS)
You must install RBS on the database server and on all Web servers and application servers in the SharePoint farm. You must configure RBS separately for each associated content database. Here’re the links to download the bits.
Execute downloaded bits, RBS.msi. Here’re the wizards:
Select all features
Configure database connection. Adjust the server\instance (by the way, I use Sharepoint instance name, change it to reflect your environment). Database name is WSS_Content, the one I mentioned above. Change Filegroup for internal tables to PRIMARY.
Change FILESTREAM blob store filegroup to RBSFilestreamProvider. That’s a must, as we use RBS FILESTREAM provider for storing files.
Check the checkbox.
Name the Store.
Next, next, next. You’ll be prompted Task scheduler for RBS maintenance, configure it. Next, next, next, and finish successfully, hopefully :)
Check the RBS installation
Fire up SQL Server Management Studio, open New Query, write this:
select * from dbo.sysobjects where name like 'rbs%'
If you see some results, then RBS installation is successful.
The last step is enabling RBS on Web Server in the farm. For me, the web server is at the same server as the database server.
Let’s go scripty by firing up SharePoint 2010 Management Shell (Start –> All Programs –> Microsoft SharePoint 2010 Products –> SharePoint 2010 Management Shell).
The script: (Change the http://SiteName to your web application URL)
$cdb = Get-SPContentDatabase -WebApplication http://SiteName
$rbss = $cdb.RemoteBlobStorageSettings
Now, you should see “True”, continue:
After the last line, you should the result like mine above.
Let’s test it. I refresh the SharePoint site. My first attempt is failed, SharePoint said that there’s a memory corrupt. What?!! Don’t be affraid, just restart the Web Server, and the site is back online.
I test by uploading a file with size 9,125KB. The uploading is successful, now it’s time to check whether it’s stored in RBS. I open Windows Explorer, go to C:\BlobStore (the folder I used in above steps), drill down to some GUID-named folders. And I find this:
It’s exactly what it is, 9,125KB. And my afternoon is great, and now I want to eat some Sushi.