Bang Two Pound Four: Remote Blob Storage (RBS) with SharePoint 2013 using SQL Server 2012

Check out the whole SharePoint 2013 Solution Series
New Titles Added Weekly!

I am in the process of evaluating SharePoint 2013 using the Preview (Beta) edition. Since we are moving from MOSS 2007 directly to SharePoint 2013, any new features that were introduced with SharePoint 2010 are fair game.

One of the advantages with the latest SharePoint versions and SQL Server versions is the ability to implement Remote Blob Storage (RBS) using the FileStream Provider. This allows for documents and files that are larger than a specified amount of bytes (default 100KB) to be stored on a connected file system of the SQL Server box instead of inside the database itself. The overall advantage is keeping your content databases smaller and more manageable.

The steps for implementing RBS with SharePoint 2013 and SQL Server 2012 are very similar of that with SharePoint 2010 and SQL Server 2008. Since I found several blog posts and articles that explain the exact same steps (who copied who???) with the exact same code, I decided to change it around a bit and not use the exact names and passwords.

Whoops! When setting up the MASTER KEY in SQL Server on my main content database, I decided to use a different password. The blog posts and articles all used
N'Admin Key Password !2#4' - so I decided I wouldn't copy their (I thought made up) password and use N'Admin Key Password SharePoint1'.

That sent me through some SharePoint hell since I did not see any files being stored in the blob storage folder. However, once I altered and regenerated the master key with the password using the correct !2#4 (bang two pound four), everything magically started working. You don't learn unless you make mistakes so I am actually glad I went through some trouble.

Anyway, here are the steps I took to convert an existing SharePoint 2013 (Preview) content database to use RBS within SQL Server 2012:


1. Enable File Stream on the SQL Server
On the SQL Server 2012 box, open SQL Server Configurations Manager.

Select SQL Server Services from the left pane. Right-click on the SQL Server process in the right window and select Properties:

The SQL Server Properties dialog appears. Select the FILESTREAM tab and check all of the check boxes:

Click OK.

Start SQL Management Studio and open a new query window.

Execute the following two SQL Statements (on any database):

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

Using your content database (the one I used was WSS_CONTENT_<guid> because I was playing around and was too lazy to remove the GUID or create a proper name in the first place for that matter), open a new query:

Create the master key using the following SQL:

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'

Create a new filegroup for the RBS:

if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')
      alter database [<your content database>]
      add filegroup RBSFilestreamProvider contains filestream

Add the "file" using the following SQL:

alter database [<your content database>]
add file (name = RBSFilestreamFile, filename= 'c:\SPBlobStorage')
      to filegroup RBSFilestreamProvider

The "file" is a folder on a connected drive. It can be a local drive on the SQL Server or an attached iSCSI drive. The folder cannot exist already. Executing the SQL statement above automatically creates the folder specified on the drive specified.


2. Install RBS on the DB and Web/Application Servers
The RBS bits need to be installed on the database server and on each Web server and Application server that exist in the SharePoint farm. The RBS bits are a separate installation as part of the SQL Server 2012 Feature Pack. You can access the feature pack for SQL Server 2012 here.

Scroll down on the feature pack download page and locate the RBS download:

Download the appropriate package (hopefully x64) and save it in a central location.

For the first web server (or database server), create a new batch file, in the same location as you downloaded RBS.msi, using the following code (all on one line - no line breaks):

 msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi

TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY
DBNAME="<ContentDbName>"
DBINSTANCE="<DBInstanceName>"
FILESTREAMFILEGROUP=RBSFilestreamProvider
FILESTREAMSTORENAME=FileStreamStore  

Open a Command Prompt and execute the first batch file:

Verify success by reviewing the rbs_install_log.txt file that was generated in the same folder. The completion message is not the very last thing but it is towards the end of the log:

On all of the other web servers and application servers, place the following code into a batch file (again no line breaks):

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

Open a Command Prompt and execute the second batch file on each web server and application server:

3. Enable RBS on Content Database
To enable RBS on the content database, you must use PowerShell. Therefore, open up PowerShell or Notepad and create the following PowerShell Script:

$cdb = Get-SPContentDatabase -WebApplication "<Web Application Name>"
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss

You may also add $rbss.MinimumBlobStorageSize=1048576 to increase the minimum file size that will be considered for RBS. The example number shows 1MB.

Save the script as a .ps1 file and then open the SharePoint 2013 Management Shell:

Execute the script:

 The first output is"True" which means RBS has been installed correctly. The second output displays the RemoteBlobStorageSettings object and shows that RBS is enabled and displays the name of the Active Provider (which should be the same name you used as the FILESTREAMSTORENAME in the RBS installation batch file.


4. Test RBS

Now it's time to see this working in action! Navigate to your SharePoint site related to the content database you just configured. Open a document library:

Upload a bunch of documents into the document library:

Investigate the folders within the local file system blob storage location:

Files appear in small chunks.