Installing Remote BLOB Storage (RBS) with SharePoint Foundation 2010 and Insight

When SharePoint was originally introduced all files saved to SharePoint were stored in the database as BLOBs. BLOBs are not necessarily ideal for a database if there are a lot of files stored or if the files stored have large file sizes. Unfortunately Insight data stored in SharePoint is typically of a larger file size and engineering groups typically generate a lot of these larger files. BLOBs bloat the size of the physical database files resulting in reduced database performance. Therefore Insight performance can be negatively impacted.

Thankfully Microsoft has recognized this and now provides Remote BLOB Storage (RBS) with SQL Server and SharePoint. RBS is a way to store the BLOB files outside of the database on the physical disk system itself. By moving the BLOB storage to the physical disk this in turn reduces the physical database size. Implementing RBS has the potential to improve your overall SharePoint and Insight performance.



However, simply implementing RBS is no guarantee of automatically improved performance. If the disk system RBS is implemented on is slow or most of your files are smaller in size then you may actually notice a reduction in performance. In other words before simply rolling this out to production back up your systems and perform your own testing!

Notes:

• Instructions are for an already fully configured and working SharePoint with Insight installation.
• Instructions assume that SQL and SharePoint servers are on the same physical server. There are additional steps necessary for multiple server configurations.
• Uses the Microsoft FILESTREAM and RBS provider.
• Microsoft RBS only works with locally attached storage – does not work with NAS.
• Bit versions must match i.e. 32-bit SQL on 64-bit Windows will not work.
• For performance the RBS storage should be its own drive(s) i.e. not on the drives with the OS, swap, or database files.
• Instructions are based on this Microsoft TechNet article but the TechNet article has “assumed” steps either missing and/or errors. In other words if you follow the TechNet article blindly RBS may not install.
• More details on Microsoft RBS including pros, cons, limitations etc. are available at http://technet.microsoft.com/en-us/library/ff628583.aspx

Installation Overview:

To install and use RBS you perform the following steps:

• Enable FILESTREAM on the database server.
• Create a BLOB store for the content database.
• Install the RBS client on the web server.
• Enable RBS for the content database.
• Test the RBS install.

Installation Instructions:

Enable FILESTREAM on the Database Server

1. Log into the SQL Server as the domain account used to install and configure SharePoint and Insight.

2. Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager:

 

3. Select and expand SQL Server Network Configuration:

 

4. Under Protocols for MSSQLSERVER select Named Pipes, right-click -> Properties:

 

5. Set Enabled to Yes and select OK:

 

6. Select SQL Server Services:

 

7. Select SQL Server (MSSQLSERVER), right-click -> Properties:

 

8. Select the FILESTREAM tab and check all options. Select OK:

 

9. Select SQL Server (MSSQLSERVER), right-click -> Restart:

 

10. Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio:

 

11. Connect to Server using Windows Authentication (assuming you are logged into Windows as the domain account that installed and configured Insight initially):

12. Select New Query. In the Query window enter the following code and Execute:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE 

Create a BLOB Store for the Content Database

1. Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio:

 

2. Connect to Server using Windows Authentication (assuming you are logged into Windows as the domain account that installed and configured Insight initially):

 

3. Expand the Databases and select the content database to create the BLOB store on:

 

4. Select New Query and Execute the following code. Replace WSS_Content with the content database name – note that the square brackets around WSS_Content are required. Replace D:\Blobstore with the drive and directory with where to create the BLOB storage:

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 =
'D:\Blobstore')
to filegroup RBSFilestreamProvider

 

5. The BLOB store should now be created on the Windows file system:

 

Install the RBS Client on the Web Server

1. Download the Remote Blob Store installer from the Microsoft SQL Server 2008 R2 Feature pack web page. Select the correct version (X64) for the server:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16978

 

2. Do *NOT* attempt to install by double-clicking and running the installer GUI. Open a Command Prompt wherever the RBS installer was downloaded to:

 

3. In the Command Prompt using the following command line to silent install RBS. Change WSS_Content to the content database name. Change HSVNT422B to your SQL Server host name:

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="WSS_Content" DBINSTANCE="HSVNT422B" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

 

4. It should take approximately one minute for the silent install to complete. Check the install log rbs_install_log.txt in the same location as the as the RBS.msi file for successful installation of RBS. The log file should end with “Verbose logging stopped” if the installation is complete. Approximately 20 lines from the end of the file should read “Product: SQL Server 2008 R2 Remote Blob Store -- Installation completed successfully.” if RBS installed successfully:

 

5. In SQL Server Management Studio expand the tables under the content database:

 

6. If RBS is successfully installed to the content database there will be multiple tables in the content database beginning with “mssqlrbs”:

 

Enable RBS for the Content Database

1. Select Start -> All Programs -> Microsoft SharePoint 2010 Products -> SharePoint 2010 Management Shell:

 

2. In the Management Shell window run the following command where http://hsvnt422b is the URL to the top level site for the content database:

$cdb = Get-SPContentDatabase –WebApplication http://hsvnt422b

  

3. In the Management Shell window run the following command:

$rbss = $cdb.RemoteBlobStorageSettings

 

4. In the Management Shell window run the following command. If everything is installed correctly to this point the command should return True:

$rbss.Installed()

 

5. In the Management Shell window run the following command:

$rbss.Enable()

 

6. In the Management Shell window run the following command:

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

 

7. In the Management Shell window run the following command. If everything is installed correctly and RBS is properly activated the command should return the details about the RBS installation:

$rbss

Test the RBS Install

1. Launch Solid Edge. Create and save a file into Insight:

 

2. In the BLOB storage folder location should be a new set of folders and files created. If so, RBS is correctly installed and working:

 

In Closing:

If there are already Solid Edge files stored in the database before installing RBS these files remain stored in the database until such time as they are opened and saved back to Insight. Once saved back to SharePoint the files are then stored in the file system.

The system generates a lot of versions of a file (even with versioning turned off) when saving and uploading from Solid Edge. You may need to enact some form of scheduled maintenance tasks to purge these old unused versions from the file system to avoid the storage system filling up quickly with unused versions of files.