RBS (Remote Blob Storage) part 3

Joel Plaut

December 11, 2011

All Post

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 FilesMicrosoft SQL Remote Blob Storage 10.50Maintainer

.NET framework location:
C:WindowsMicrosoft.NETFrameworkv2.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: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.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.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:

      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=
            

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:

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:

iii. Click OK.earlier

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:

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:

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