Enable FILESTREAM and provision a Remote Blob Store

Binary large objects, known as BLOBs, are used to store large binary data such as Office documents and media.  By default BLOBs are stored in the content database on the SQL server.  Today I am going to enable FILESTREAM on my SQL server and install a RBS provider on the SharePoint server to store large files directly on disk.  Why?  Primarily to increase performance but also to keep the size of the database at a manageable size.

Step 1 – Enable and configure FILESTREAM on the SQL server

1. Open SQL Server Configuration Manager

2. Click SQL Service Services

3. Right-click SQL Server (<instance>) and then click Properties

4. On the FILESTREAM tab tick the Enable FILESTREAM for Transact-SQL access, Enable FILESTREAM for file I/O streaming access, and Allow remote clients to have streaming access to FILESTREAM data.  Also enter a name for the shared folder and then click OK.

5. Start SQL Server Management Studio and connect to the required instance

6. In the Object Explorer pane right-click the SQL server and click Properties

7. On the Advanced page set Filestream Access Level to Full access Enabled and then click OK

8. Right-click the server and click Restart.  Click Yes to confirm and wait while the service restarts.

9. In the Object Explorer pane select the desired SharePoint content database and click New Query

10. Execute the following query to provision a BLOB store, replacing the database name and password with your own:

Use [WEBBWORLD_Content_Portal]

if not exists (select * from sys.symmetric_keys where
name = N’##MS_DatabaseMasterKey##’)create master key
encryption by password = N’Pa$$w0rd’

11. Click New Query

12. Execute the following query to enable a new filegroup, replacing the database name with your own:

use [WEBBWORLD_Content_Portal]

if not exists (select groupname from sysfilegroups where
groupname=N’RBSFileStreamProvider’)alter database [WEBBWORLD_Content_Portal]

add filegroup RBSFileStreamProvider contains filestream

13. Execute the following query to create a file system mapping, replacing the database name and BLOB store path with your own:

use [WEBBWORLD_Content_Portal]

alter database [WEBBWORLD_Content_Portal] add file (name = RBSFileStreamFile,
filename = ‘D:\BLOBSTORE’) to filegroup RBSFileStreamProvider

14. Use Windows Explorer to check that the folder has been created

 

Step 2- Install RBS on the SharePoint server

1. Download RBS.msi from the SQL 2008 R2 Feature Pack

2. Run the following command to install the RBS provider, replacing the log file name, database name, instance and file stream store name with your own:

msiexec /qn /lvx* d:\rbslog.txt /i D:\RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="WEBBWORLD_Content_Portal" DBINSTANCE="WW-SQL\SHAREPOINT" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=SHAREPOINT

The installation may take a few minutes (there will be no status or message displayed) but you can check the log file to check when it has completed successfully.

3. Execute the following PowerShell commands to enable RBS

$cdb = Get-SPContentDatabase “WEBBWORLD_Content_Portal”
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss

4. Execute the following PowerShell commands to configure the BLOB size threshold

$cdb = Get-SPContentDatabase “WEBBWORLD_Content_Portal”
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.MinimumBlobStorageSize = 1048576
$cdb.update()

Now, transfer some large files (preferably above the specified threshold) into a document library and you should see them appear in the BLOB store.