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:

$rbs.set_MinimumBlobStorageSize(number)

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:

$DBList="Content_1,Content_12,Content_3"
$DBs=$DBList.split(",")
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
$rbs.set_MinimumBlobStorageSize(25*1024)
}

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

$DBList="Content_1,Content_12,Content_3"
$DBs=$DBList.split(",")</pre>
for ($i=0; $i -lt $DBs.count; $i++)
{
$cdb=Get-SPContentDatabase -identity $DBs[$i]
$rbs = $cdb.RemoteBlobStorageSettings

write-host "RBS installation for $($cdb.name) is $($rbs.Installed())"

write-host "MinBlobStorageSize for $($cdb.Name) is $($rbs.MinimumBlobStorageSize)"
}
<pre>

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.

Overview

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 Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer

.NET framework location:
C:\Windows\Microsoft.NET\Framework\v2.0.50727\

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.
aspnet_regiis.exe

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

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_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 Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer\web.config" "C:\Program Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer\Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config"

To decrypt from the Maintainer directory:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -pdf connectionStrings .

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

%programfiles%\Microsoft SQL Remote Blob Storage 10.50\Maintainer\Microsoft.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.50\Maintainer”

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.50\Maintainer" -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. <add name=”… is required.  This name is referenced later.
2. “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.

<configuration>
    <connectionStrings>
        <add name="RBSMaintainerConnection" connectionString="Data Source=NJ-SRV-SQLPRD02;Initial Catalog=Content_Intranet_IT;Integrated Security=True;Application Name=&quot;Remote Blob Storage Maintainer&quot;"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <RemoteBlobStorage>
        <Logging>
            <add key="ConsoleLog" value="0" />
        </Logging>
    </RemoteBlobStorage>
</configuration>

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

<configuration>
    <connectionStrings configProtectionProvider="DataProtectionConfigurationProvider">
        <EncryptedData>
            <CipherData>
                <CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAvyx0EESER0Kn9Ui9t9ZzcgQAAAACAAAAAAADZgAAwAAAABAAAAA1gYCb5s1usLg/P7uwA7TmAAAAAASAAACgAAAAEAAAACVQO6o7eVm/lmikyJUtSeRIAgAAX5uFsFeWOEZQycBwOhxJmFN11JFnTdM+PycItclQJYk90gQZhZ2B7E6bf6h3MovJB/jnWM4cEKbOG3w+9pPPEbAuk9c7Y+zQj4atoHdnlNX1D0kKge39A1LQK+C+JQ575bx4TWVI/Zl5Edc5hbLWt+IifMytGHrZ4MHHENQOR3S001yMtBlaISuPQVa1DUDpzoBS3rpTDej2UAmHmBIjtHF1vXfmBz6R+p2xdQlpBajPRLOfQ28gXoT25HrpEyKTZtWyeFyWcYslqm7msowJ6FOP7iwatY9/H9LkvWj0pAegHUQrmaCJnl9M+xGGiOigNeBe1o20tQFxYrW4RIJ/MSOrTZthbccAaRMmgoR8PjMBNOAzE3eDMihGCpeEFtIxSDYZnT7OCuAkSCmqCWqLpDAJyqCmUJbCRrttU2xo7VnfCCGACzI4jfOHWVIFKYaIIfPFD6KVeDSdBDt9J4xeR/sE5HV6Jcugcm8yAcW1CIq6/w5QwfjSN20pjzOHXo9SFukhMJPJIWTf0GnGuwEdO2ci4a6mL0Y8me6BhZxpc3228Hegp+C7/3p3kyrm0H93GwNTB2XkdUajg8K62buGve/OLwIbgLH3pG1jkyhkgm1l5W/CC8lA/6QsdNiLTWHB4fq1AsbxpEIGgOy9sDvJmL3dvcaOeMpMv5g9mVetbYvE0D+WsZVH+ILBeZ0HwsZ4kty3E+5yVTG3TlkQ48j07e8QjE3o4xYv/j5bSB7T+2Udlsi1rlNy93C1iggCfDrGCfpnhhMn5ZsUAAAACeMf9OAnNDQzMKpZ4HseVXcUBpg=</CipherValue>
            </CipherData>
        </EncryptedData>
    </connectionStrings>
    <RemoteBlobStorage>
        <Logging>
            <add key="ConsoleLog" value="0" />
        </Logging>
    </RemoteBlobStorage>
</configuration>

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.

    <connectionStrings>
        <add name="aaa" connectionString="Dataaaa… />
        <add name="xxx" connectionString="Dataxxx… />
    </connectionStrings>

Encrypt the web.config file again by using following command

cd /d %windir%\Microsoft.NET\Framework64\v2.0.50727

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

Rename the file back to original

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

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.50\Maintainer \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 “<Database Name> RBS Maintainer”, where <Database Name> 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]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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'
GO

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'

Tips

• 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

To get RBS going we are going to configure SQL Server, install the RBS libraries on the SharePoint servers, and configure it.

Configure SQL Server

First enable FILESTREAM on the SQL Server instance by:

1. Right-clicking SQL Server Properties, enable “FILESTREAM for Transact-SQL access”
2. Enable “FILESTREAM for file I/O streaming access”
3. Enable “Allow remote clients to have streaming access to FILESTREAM data”
4. Run the following SQL:

   EXEC sp_configure filestream_access_level, 2
   RECONFIGURE

Next, we’ll provivision a BLOB Store.  You want a dedicated drive (not used by SQL Server binaries, temp, or the actual databases), and preferably cheaper storage.  The BLOB Store needs to be configured for each database; otherwise the RBS configuration on the SharePoint fails (with a very unhelpful error).

use [WSS_Content]    --name of Content DB for which you are adding BLOB Store; you'll undoubtedly have a number of DBs if you are doing RBS, so name them with a standard
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'   -- replace with your master password

use [WSS_Content]
if not exists
(select groupname from sysfilegroups
where groupname=N'RBSFilestreamProvider')  -- SharePoint RBS has an undocumented bug/limitation in that there can only one File Stream Provider, so use the same one!
alter database [WSS_Content]
add filegroup RBSFilestreamProvider contains filestream

use [WSS_Content]
alter database [WSS_Content]
-- set the location to your new cheaper storage dedicated Blob Storage location. You can create separate drives per DB
 add file (name = RBSFilestreamFile, filename = 'c:\Blobstore') 
to filegroup RBSFilestreamProvider

Point to be aware of:

• Windows Firewall on the database server could generate an error.  This is solved by disabling the firewall, or unblock remote debugging.

 Install RBS on SharePoint servers

Before we get started, do yourself a favor, and snapshot or backup a server image, as well as the Content DBs.  Installing RBS actually changes the Content DBs by adding a number of Tables and Stored Procedures.  Make sure you use the x64 RBS drivers, and do not run the wizard to install by double-clicking the .MSI file.  Microsoft reports that the wizard configures it suboptimally and is then unchangeable.  Lastly, anything you ever install on SharePoint servers should be saved for reference, server reinstallation, DR, recovery, or even simply for adding another server to the farm.  This includes any web part, feature, WSP, executable, but I digress…

This is for the first install for the first database:

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="Content_Intranet_IT" DBINSTANCE="SQL Server Instance Name" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=[TheNameOfTheFileStream]

This is for subsequent databases:
The key here is the name of the File Stream Store cannot change between databases.  This is the RBS limitation mentioned earlier.  For each DB, it creates a set of Tables and Stored Procedures.

msiexec /qn /i rbs.msi REMOTEBLOBENABLE=1 FILESTREAMPROVIDERENABLE=1 DBNAME="your content db name" FILESTREAMSTORENAME=[TheNameOfTheFileStream] ADDLOCAL=EnableRBS,FilestreamRunScript DBINSTANCE="SQL Server Instance Name"

The next challenge is determining if the installation worked.  The command returns the command prompt instantly; even though it is running in the background.  It runs for a minute or so typically.  The logs are actually quite unclear.   Microsoft says to scroll to the end of the file and on the last 20 lines find “Product: SQL Remote Blob Storage – Installation completed successfully“.  Another way to tell is to look at the size of the logfile.  A failed install has a logfile has around 500kb and does not have the above text near the end of the file, while a successful install has a logfile of 1.4MB if the database was RBS enabled as part of the RBS install.  One last thing to check is that the RBS tables were created in the database.  To do that, fire up SQL Studio, open the content database, and look at the end of the list of tables.  If RBS is configured for the database, you’ll see roughly 20 tables starting with “rbs_”.  These tables track references to the Blob Store, as well as configuration and housekeeping such as the history and deleted items for managing the lazy delete cleanup process we’ll be discussing later on configuring and running the “Maintainer”.

Configuring the Blob Store in SharePoint

At this point we might think we are done, but there’s more to do; nothing is being stored yet in the FILESTREAM Blob Store.  SharePoint will continue to blindly store all files in the database.  Here are the simple steps to take in PowerShell (ensure the SharePoint snap-in is loaded):

# 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 Content_DB_Use_Your_Name  
$cdb = Get-SPContentDatabase –WebApplication http://MySite
# This grabs the Blog Storage Object. if you type "$rbs" it will show the status, which initially is not enabled
$rbs = $cdb.RemoteBlobStorageSettings
$rbs.GetProviderNames()
$rbs.Installed()  #this confirms it is installed correctly
$rbs.Enable()   # This is the key; it enables the Blob store
$rbs.SetActiveProviderName($rbs.GetProviderNames()[0])  # Setting the active provider is required; there's only one, at index 0
$rbs.MinimumBlobStorageSize =500kb # this is the minimum size of a file to be sent to the Blob Store; anything smaller is kept in the Content DB.
$rbs.Migrate()  # this forces content into Blob Store from the Content DB, or moves  content out of the Blob Store and back to the Content DB; based on the minimumBlobStorageSize
$rbs.update()
$cdb.update()  # both updates are absolutely required for the minimumBlobStorageSize size to "stick".  Existing documentation neglects this point.

Setting the MinimumBlobStorageSize is key. Setting it aggressively low results in a smaller Content DB, but relies more heavily on the speed of the Blob Store.  A range of 100kb to 1mb is reasonable.  You can experiment using different values, doing an $rbs.migrate() and testing.  Note the Blob Store can grow rapidly due to the design, where overwritten files in SharePoint are never overwritten in the Blob Store; instead new files are created.  A subsequent cleanup process needs to be configured and run called the “Maintainer”, but that’s tomorrow’s blog.

Check out RBS Part 3

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