Andri Yadi

A geeky technopreneur, trying to do something big with his startup

Configure SharePoint 2010 to Store Files (Blob) to The File System

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

image

3. Change web application you want to configure (1), and check the database name (2). In this case (generally), WSS_Content

image

 

Enable FILESTREAM on SQL Server 2008/2008 R2

To enable and change FILESTREAM settings (adapted from How to: Enable FILESTREAM)
  1. 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.

  2. In the list of services, right-click SQL Server Services, and then click Open.

  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

  4. Right-click the instance, and then click Properties.

    image

  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.

  6. Select the Enable FILESTREAM for Transact-SQL access check box.

  7. 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.

  8. 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.

  9. Click Apply.

  10. In SQL Server Management Studio, right click database server name, and click New Query to display the Query Editor.

  11. In Query Editor, enter the following Transact-SQL code:

    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE
  12. Click Execute.

 

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.

image

Write this TSQL

use [WSS_Content]
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'

use [WSS_Content]
if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')
alter database [WSS_Content] add filegroup RBSFilestreamProvider contains filestream

use [WSS_Content]
alter database [WSS_Content] add file (name = RBSFilestreamFile,
filename = 'c:\BlobStore') to filegroup RBSFilestreamProvider


Notes:

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.

X64 Package

X86 Package

IA64 Package

Execute downloaded bits, RBS.msi. Here’re the wizards:

image

Select all features

image

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.

image

Change FILESTREAM blob store filegroup to RBSFilestreamProvider. That’s a must, as we use RBS FILESTREAM provider for storing files.

 

image

Check the checkbox.

image

Name the Store.

image

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:

use [WSS_Content]
select * from dbo.sysobjects where name like 'rbs%'

If you see some results, then RBS installation is successful.

 

Enable RBS

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).

 

image

The script: (Change the http://SiteName to your web application URL)

$cdb = Get-SPContentDatabase -WebApplication http://SiteName
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()

Now, you should see “True”, continue:

$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss

After the last line, you should the result like mine above.

Test RBS

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:

image

It’s exactly what it is, 9,125KB. And my afternoon is great, and now I want to eat some Sushi.

Share this post: | | | |
Published Jul 27 2010, 06:49 PM by andriyadi
Filed under: ,

Comments

 

Twitted by msdnindo said:

Pingback from  Twitted by msdnindo

July 27, 2010 11:41 PM
 

Box iPad/iPhone apps to get offline, iOS4 support :Apple On The Longtail said:

Pingback from  Box iPad/iPhone apps to get offline, iOS4 support :Apple On The Longtail

August 17, 2010 3:05 AM
 

SharePoint 2010 Server with FILESTREAM RBS Provider « Bugra Postaci's Blog said:

Pingback from  SharePoint 2010 Server with FILESTREAM RBS Provider « Bugra Postaci's Blog

October 1, 2010 7:39 PM