SQL Server: Configuring Remote Blob Storage for SharePoint

SQL Server databases can store and query a vast amount of information kept in the raw with little effort. But once we introduce SharePoint into the mix, those Content Databases can grow exponentially fast and under perform because we may now be storing actual files from a SharePoint Document Library in the database. While this is perfectly acceptable, it may introduce additional processing to pull documents to and from the Content Database. One way to improve processing time is to configure Remote Blob Storage on the SQL Server to keep large files outside the database in a separate file store.

Remote Blob Storage (RBS) is not enabled by default and must be configured on the SQL Server prior to installing the RBS module on the each of the SharePoint Web Front-End servers.

Configure Remote Blob Storage in SQL Server

1) Open the SQL Server Configuration Manager
2) Select SQL Services from the Left Panel
3) Right click on the SQL Server Instance in the Right Panel

4) Select the FILESTREAM tab
5) Check all the boxes on this tab

6) Press OK
7) Close the SQL Configuration Manager
8) Open SQL Server Management Studio with an account that has administrative privileges
9) Connect to the SQL Server Instance that you had configured previously
10) Right Click on the SQL Server Instance and choose Properties
11) Choose Advanced from the left pane.
12) Select Filestream Access Level and set it to “Full Access enabled”

13) Restart the SQL Server services

Configure a SharePoint Content Database to Use the Remote Blob Store

For each Content Database in the SQL Server instance, you will need to configure Remote Blob Storage.

1) Open the SQL Server Manager Studio
2) Click on New Query
3) Set the database master key by copying the following code and executing


use [ContentDBName]
if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##')create master key encryption by password = N'EncryptionKeyPassword'

4) Enable a new filegroup for your RBS Provider by copying the following code and executing


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

5) Add a file system mapping for your RBS provider* by copying the following code and executing


use [ContentDBName]
alter database [ContentDBName] add file (name = RBSFilestreamFile, filename = 'BlobStorePath') to filegroup RBSFilestreamProvider

*It is recommended that Blob Stores be kept on a partition separate from the OS, Program Files, and Database Files.

Install the Remote Blob Storage Module on the SharePoint Web Front-End Servers

1) Download the Remote Blob Storage Installer** from:
http://go.microsoft.com/fwlink/?LinkID=165839&clcid=0x409

2) To install RBS on the first Web Front-End Server, run the following code from a command line with Administrative Priveleges.


msiexec /qn /lvx* rbs_install_log.txt /i RBS-x64.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="ContentDBName" DBINSTANCE="DBInstanceName" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

3) For each additional SharePoint Server in the Farm run the following code:


msiexec /qn /lvx* rbs_install_log.txt /i RBS_x64.msi DBNAME="ContentDBName" DBINSTANCE="DBInstanceName" ADDLOCAL=Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer

Configure the Minimum Blob Storage Size of the Content Database

It is also recommended that you configure the Minimum Blob Storage Size
1) Open the SharePoint Management Console
2) Run the following code, replacing the MinimumBlobStorageSize value for your desired size (in bytes):


$cdb = Get-SPContentDatabase "ContentDBName"
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.MinimumBlobStorageSize = 1048576
$cdb.update()

Note: 1048576 = 1 MB

Advertisements
This entry was posted in SharePoint 2010, SQL Server 2008 R2 and tagged , , , , , . Bookmark the permalink.

One Response to SQL Server: Configuring Remote Blob Storage for SharePoint

  1. harry says:

    Finally some well written step by step instrunctions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s