Posts

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
Guid
----
00000000-0000-0000-0000-000000000000

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

selectCOUNT(*)
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]"
$blobstoragesettings=$cdb.remoteblobstoragesettings
$rbs = $cdb.RemoteBlobStorageSettings
$rbs.MinimumBlobStorageSize
$rbs.GetProviderNames()
$rbs.Installed()
$rbs.Enable()
$rbs.MinimumBlobStorageSize =1mb
$rbs.update
$cdb.update()
$rbs.Migrate()

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
where
( 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.

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.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:

    
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:
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]
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

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