Remote Blob Storage

Remote Blob Storage report

When configuring RBS (Remote Blob Storage), we get to select a minimum blob threshold. Setting this offers a tradeoff between performance and storage cost efficiency.

Wouldn’t it be nice to have a report of the RBS (Remote Blob Storage) settings for all content databases within a farm? Well, here’s a script that reports on each content database, whether it is configured for RBS, and what that minimum blob threshold size is.

Write-Host "DB Name$($sep)RBS Enabled$($sep)MinBlobThreshold"
Get-SPContentDatabase  | foreach {;
try {
$rbs = $_.RemoteBlobStorageSettings;
Write-Host "$($$($sep)$($rbs.enabled)$($sep)$($rbs.MinimumBlobStorageSize)"
catch {
write-host -foregroundcolor red "RBS not installed on $($!`n"
Write-Host "$($$($sep)False$($sep)0"

Rationalizing SharePoint Storage

SharePoint Storage Management

SharePoint storage tends to grow organically, and in an uneven fashion. Periodically it makes sense to take stock of how Site Collections are distributed amongst Content DBs. The goal should ideally be to keep Content DBs at 50GB or below where possible. When a Site Collection grows to 100GB or more, steps should be taken to manage the growth, as large Content DB performance can degrade, and backup/restore can become lengthy.

Here’s a one-line script that outputs how site collections are distributed among Content DBs, and the size of the Content DB. The results can be pasted into Excel. If needed Excel can separate Text to Columns, allowing you to to pivot larger data sets:

Get-SPContentDatabase | get-spsite -Limit all | % {write-host "$($_.rootweb.url)|$($_.rootweb.title)|$($|$($_.ContentDatabase.DiskSizeRequired)"}

Setting the Minimum BLOB Storage Size Correctly

How to set the Minimum BLOB Storage Size

Setting the minimum storage size for BLOBs is a bit tricky.  It turns out setting the property generally does not work, and the SPRemoteBlobStorageSettings object does not have an associated update() method.  Trying to update the container object (Content DB) doesn’t work either.  The trick is to use the associated method to set the value:


Here’s a script that iterates through a set of content DBs, and sets the min blob size.  I prefer to work with lists of DBs:

for ($i=0; $i -lt $DBs.count; $i++)
# Provide your content DB name using the name of the Content DB, or if there's only one Content DB for a web app, you can get it by identifying the web app
$cdb=Get-SPContentDatabase -identity $DBs[$i]
$rbs = $cdb.RemoteBlobStorageSettings
#here's what not to do. This does not work; min blob size is lost; save conflict
#$rbs.MinimumBlobStorageSize =25kb # this is the minimum size of a file to be sent to the Blob Store; anything smaller is kept in the Content DB.
#a much better approach is to use the method to set the value, this does not require an update() method. Note the SPRemoteBlobStorageSettings ($rbs here is that type) does not have an associated update() method

Here’s a script to ensure that RBS is configured correctly, and the min blob size is what you expect it to be:

for ($i=0; $i -lt $DBs.count; $i++)
$cdb=Get-SPContentDatabase -identity $DBs[$i]
$rbs = $cdb.RemoteBlobStorageSettings
write-host "RBS installation for $($ is $($rbs.Installed())"
write-host "MinBlobStorageSize for $($cdb.Name) is $($rbs.MinimumBlobStorageSize)"

Report on RBS Configuration by Content Database

RBS Configuration reporting by Content Database

Here’s a simple script that will report on the RBS configuration across all your Content DBs. It’s useful to be able to lower the minimum blob threshold size for your largest DBs. Just remember to do a PowerShell Migrate() to force the movement of documents in or out of RBS, and remember this command can take a while to run.

Get-SPContentDatabase | foreach {$_;
try {
$rbs = $_.RemoteBlobStorageSettings;
write-host "Provider Name=$($rbs.GetProviderNames())";
write-host "Enabled=$($rbs.enabled)";
write-host "Min Blob Size=$($rbs.MinimumBlobStorageSize)"
catch {write-host -foregroundcolor red "RBS not installed on this database!`n"}
finally {write-host "------------------------------------------------------------------`n"}

RBS external provider invalid reference

Inside every Content Database is a key table called AllDocs; when configured for RBS there is a field called DocFlags that can provide insights into the configuration.  If the value is 65868, it indicates to the RBS Provider to leverage the deprecated Farm ExternalBinaryStoreClassId, which was used as part of EBS in SP2007.  You can see whether this is set for your farm by running these two PowerShell commands. For my farm, it gives a zeroed out GUID:

PS C:\Users\SP2013Farm> $farm = Get-SPFarm
PS C:\Users\SP2013Farm> $farm.ExternalBinaryStoreClassId

The following SQL run in the context of your content database provides a count of this DocFlag:

from AllDocs where DocFlags = 65868 

This situation prevents the PowerShell Migrate() cmdlet from running.  As a refresher, here’s the full PowerShell set of commands to set the minBlobSize and call Migrate():

$cdb=Get-SPContentDatabase -identity "[replace with your content db]"
$rbs = $cdb.RemoteBlobStorageSettings
$rbs.MinimumBlobStorageSize =1mb

For me, I get a hideous “Object Variable Not Set” for the migrate() command.  Oddly, the underlying AllDocs records with this one 65868 flag all predate my installation of SP1 with June CU, hinting that this Service Pack may have fixed a condition going forward…

What occurs for DocFlags is the highest order bit (0x10000, or 65536) is set to indicate an external provider (EBS) is utilized; this value should be zero for the typical SharePoint 2010 out-of-box RBS configuration.

The simple solution is to fix the offending bit.  However this violates Microsoft’s rules telling us users not to muck in their database internals.  We would never do that of course, but “hypothetically” here is how you can fix it.   Take your hypothetical backup before hypothetically running this SQL:

update AllDocs
set DocFlags = DocFlags & 0xFFFEFFFF
( DocFlags & 0x10000 ) = 0x10000

What this does it clear the 65536 (0x10000) bit.  This SQL would run (hypothetically speaking) in a fraction of a second.

We can then (again, of course hypothetically) enable RBS and do a Migrate() of content back into FILESTREAM.

Ciao, and happy Blobbing!

RBS (Remote Blob Storage) part 3

A configured and running Remote Blob Storage (RBS) is a ticking time bomb until you’ve configured the Maintainer to run. That’s because RBS is designed to leave a trail of unused objects in its wake. RBS counts on a periodic process to run to eliminate all these unused objects. If you save a file in SharePoint a dozen times, even with versioning disabled, each save will leave a blob object behind, largely for performance reasons.

Setting up Maintainer to run is not easy; which is compounded by skimpy and inconsistent documentation.

Microsoft’s documentation indicates that Encryption is not required if a Trusted connection is used. However I have found Encryption was required, with Maintainer complaining if the connection string was unencrypted.


To configure the Maintainer, the following steps must be carefully done:
1. Decrypt Connection string, if needed
2. Define each connection string in the config file; all connections are defined in this one file
3. Encrypt the connection strings within the config file
4. Run the Maintainer for each connection, referencing each connection by name

Let’s go through these step by step. First establish the locations of some key components. I like to put shortcuts to each on the desktop that open in a CMD window. If you do this, you’ll thank me that you did. For me it was:

Maintainer location:
C:Program FilesMicrosoft SQL Remote Blob Storage 10.50Maintainer

.NET framework location:

This is the utility to encrypt and decrypt connection strings. It only works against files named “web.config”, so we will need to do a fair amount of file renaming along the way.

Command to encrypt a connection string, from Maintainer directory and rename it back:

C:WindowsMicrosoft.NETFrameworkv2.0.50727aspnet_regiis.exe -pef connectionStrings . -prov DataProtectionConfigurationProvider
RENAME web.config Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config

Alternatively, you can run the command to encrypt the connection string from the .NET directory:

aspnet_regiis.exe -pef connectionStrings . -prov DataProtectionConfigurationProvider
RENAME "C:Program FilesMicrosoft SQL Remote Blob Storage 10.50Maintainerweb.config" "C:Program FilesMicrosoft SQL Remote Blob Storage 10.50MaintainerMicrosoft.Data.SqlRemoteBlobs.Maintainer.exe.config"

To decrypt from the Maintainer directory:

C:WindowsMicrosoft.NETFrameworkv2.0.50727aspnet_regiis.exe -pdf connectionStrings .

This is the command to start maintainer, referencing the PATH variable:

%programfiles%Microsoft SQL Remote Blob Storage 10.50MaintainerMicrosoft.Data.SqlRemoteBlobs.Maintainer.exe -ConnectionStringName RBSMaintainerConnection   -Operation GarbageCollection ConsistencyCheck  ConsistencyCheckForStores -GarbageCollectionPhases rdo -ConsistencyCheckMode r -TimeLimit 120
Microsoft.Data.SqlRemoteBlobs.Maintainer.exe -ConnectionStringName RBSMaintainerConnection   -Operation GarbageCollection ConsistencyCheck  ConsistencyCheckForStores -GarbageCollectionPhases rdo -ConsistencyCheckMode r -TimeLimit 120

You will want to decrypt and re-encrypt multiple times to make sure the Go to .NET directory. Some suggestions:

  1. run from .NET directory
  2. rename the maintainer config file first to web.config
    REN Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config web.config
  3. If the web.config file has an empty connectionstring, then the filename/directory was incorrect
  4. Review and tweak the connection string
  5. after encryption, rename back:
    REN web.config Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config

Decrypt to examine (the “d” in -pdf is “Decrypt”): aspnet_regiis.exe -pdf connectionStrings “%programfiles%Microsoft SQL Remote Blob Storage 10.50Maintainer”

Encryption of connection string within Maintainer config file; run from .NET directory (the “e” in -pef is “Encrypt”):

aspnet_regiis -pef connectionStrings "%programfiles%Microsoft SQL Remote Blob Storage 10.50Maintainer" -prov DataProtectionConfigurationProvider

This is the command to start the Maintainer:

Microsoft.Data.SqlRemoteBlobs.Maintainer.exe -ConnectionStringName RBSMaintainerConnection -Operation GarbageCollection ConsistencyCheck  ConsistencyCheckForStores -GarbageCollectionPhases rdo -ConsistencyCheckMode r -TimeLimit 120

Note that the cofiguration file used by the Maintainer is called ” Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config”.

Here it is with the connection string unencrypted. A few things to note:

  1. “Application Name=”… must use the &quot and the name in those quotes. This is essential. Changing &quot to an actual quote causes the request to fail.

The connection string must be encrypted for it to work, here it is below encrypted. Note the EncryptedData and CipherData tags:


Specific steps

The connection string in the Maintainer are specified in the maintainer.exe.config file. This file has to co-exist with the executable. The connection string are either all encrypted or none. The default connection string that the RBS specifies is always encrypted and its a good practice to encrypt the connection strings .

At first glance, the documentation is not clear on where Maintainer should be run. My findings indicate it should be configured on a single WFE (Web Front End) SharePoint server.

Documentation does not indicate it, but I found a special name=tag needed to be added for each connection string, and it needs to be referenced when running the command. On the command line, for each database that the Maintainer needs to be run for, the ConnectionStringName parameter needs to be set to a corresponding Name= in the XML. This matching reference is what connects the command line Maintainer call to the specific connection to a database. depending on how RBS was installed, the default config file could have this tag, or it could be missing, so take care to check.

Logging to the screen is not too helpful, as log data scrolls out of buffer. Enabling logging is recommended in the XML file.

In order to encrypt and decrypt, the source (starting) file needs to be web.config, so the file needs to be repeatedly renamed between web.config and Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config

For each content Database that is RBS enabled, a content database connection string needs to be added to the Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config file. It needs to be named using the Add Name=, and then referenced at runtime using the ConnectionStringName parameter. So, for additional databases, simply add additional connection string to the web.config file for each content database that is rbs enabled.

Encrypt the web.config file again by using following command

cd /d %windir%Microsoft.NETFramework64v2.0.50727

aspnet_regiis -pef connectionStrings “%programfiles%Microsoft SQL Remote Blob Storage 10.50Maintainer ” -prov DataProtectionConfigurationProvider

Rename the file back to original

cd /d %programfiles%Microsoft SQL Remote Blob Storage 10.50Maintainer

ren web.config Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config

Note: the XML file is case sensitive, you need to use the exact string for ‘connectionStrings’ parameter above.

For the first time, run the Maintainer manually. Thereafter, you’ll want to schedule it to run:

  1. Create a Maintenance Task using following steps (for each database)
  2. Click Start, point to Administrative Tools, and click Task Scheduler.
  3. Right-click Task Scheduler (Local) and click Create Task.
  4. Click the Actions tab and click New.
  5. On the New Action page, specify:
i. Action as Start a Program.
ii. For the Program/script, click Browse and navigate to the RBS Maintainer application; by default, the location is %programfiles%Microsoft SQL Remote Blob Storage 10.50Maintainer Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.
iii. In the Add Arguments (optional) field, enter the following parameter string: (change the name of the connection string as specified in the config file earlier)
-ConnectionStringName RBSMaintainerConnection -Operation GarbageCollection ConsistencyCheck ConsistencyCheckForStores -GarbageCollectionPhases rdo -ConsistencyCheckMode r -TimeLimit 120
iv. Click OK

Note: XML file is case sensitive, you need to use the exact string for the connection string above.

5. On the Triggers tab, click New.

6. In the New task dialog box, set:

i. Begin the task to On a schedule.
ii. The trigger schedule to be Weekly, Sunday, at 2am (or at another time when system usage is low.)
iii. Click OK.
  1. On the General tab, enter a name for the task, such as “ RBS Maintainer”, where identifies the database associated with the task. In the Security settings section:
  2. Make sure that the account under which the task is to be run has sufficient permissions to the database.
  3. Select the option to Run whether user is logged on or not.
  4. Click OK.

Tuning the internal Maintainer parameters

There are several internal parameters that should be set that control the frequency that the Maintainer can be run, as well as how long deleted entries should be maintained before being truly removed. This later option is meant to save DBAs from trouble if they restore an older Content DB without restoring the FILESTREAM. If deletes are very “lazy” (ie, delayed by days) rolling back to a previous DB without a FILESTREAM restore could work. I set the parameters more aggressively, knowing I won’t fall prey to this issue. Here’s the SQL to apply the changes. Note it’s better to use the Stored Procedures than setting the values directly:

<pre>USE [Content_database_yourname]
exec mssqlrbs.rbs_sp_set_config_value delete_scan_period, 'days 1'
exec mssqlrbs.rbs_sp_set_config_value orphan_scan_period, 'days 1'
exec mssqlrbs.rbs_sp_set_config_value garbage_collection_time_window, 'days 1'

if you want to get aggressive on storage recovery use a smaller timeframe:

exec mssqlrbs.rbs_sp_set_config_value delete_scan_period, 'time 00:00:10'
exec mssqlrbs.rbs_sp_set_config_value orphan_scan_period, 'time 00:00:10'
exec mssqlrbs.rbs_sp_set_config_value garbage_collection_time_window, 'time 00:00:10'


  • Back up all configuration files first
  • Use Visual Studio to edit the XML files
  • XML is largely case sensitive, so take care
  • -pdf is for Decrypion, -pef is for encryption

RBS (Remote Blob Storage) part 2

RBS (Remote Blob Storage) part 1

Remote Blob Storage (RBS) is a Microsoft technology for managing large BLOBs (Binary Large Objects) in SQL Server and is fully supported within SharePoint 2010.  This technology basically allows you to take the large binary files that are expensive to maintain within a database, and offload them to a separate drive.

Who needs RBS?  You may need it if you:

  • Have very large Content Databases (100GB+)
  • Have a content mix that includes a large proportion of streaming media
  • Your content is large (1TB+)
  • You have more than one tier of storage

Few people actually use RBS as implemented by Microsoft.  Most users who need RBS simply pay for 3rd party software (StoragePoint/Metalogix or DocAve extender) which is the easy way to go.  However there are some drawbacks to this approach:

  • You’ll pay heavily for licensing. $7k-$14k per administrator/front end is typical, plus maintenance
  • The complication of going with yet another vendor
  • Vendor lock-in: it’s hard to change vendors when you have a production system set up with huge volumes of data stored via proprietary drivers

To be fair, the third-party vendors offer a few advantages:

  • Arguably more responsive support
  • More refined and customizable criteria for what content stays in the database and what gets moved into RBS
  • Better administration and maintenance interfaces
  • Better documentation: Microsoft’s RBS documentation leaves a lot of opportunity (putting it mildly)

Well, that’s the very high level view; for those intrepid souls looking to leverage Microsoft’s RBS, I’m going to walk through all the steps to set up, configure and administer RBS.

Check out RBS Part 2