Posted on:
Categories: SharePoint
Description:

In this post my goal is to give an end to end configuration guide to deploying Remote BLOB storage for SharePoint 2010. I have found that there are many great blog posts available that describe different aspects of the configuration process, but I have found that there isn’t one single reference point for the entire configuration that includes the initial installation, configuration and maintenance of Remote BLOB Storage for SharePoint 2010.

Prerequisites

  • The user account that you use the configure RBS on your SharePoint servers must have the following group memberships
  • Member of the Administrators group on the Web Servers and Application Servers
  • SQL Server dbcreator and securityadmin fixed server roles on the computer that is running SQL Server

Enable FILESTREAM on the database server

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In the list of services, right-click SQL Server Services, and then click Open.
  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
  4. Right-click the instance and then click Properties.
  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
  6. Select the Enable FILESTREAM for Transact-SQL access check box.
  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
  9. Click Apply. See below:
  10. In SQL Server Management Studio, click New Query to display the Query Editor.
  11. In Query Editor, enter the following Transact-SQL code:
    EXEC sp_configure filestream_access_level, 2
      RECONFIGURE
      
  12. Click Execute.

Provision a BLOB store for each content database

You will need to follow this procedure for each content database that you want to have configured to use Remote BLOB storage

  1. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
  2. Connect to the instance of SQL Server that hosts the content database.
  3. Expand Databases.
  4. Click the content database for which you want to create a BLOB store, and then click New Query.
  5. Paste the following SQL queries in Query pane, and then execute them in the sequence listed.
       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 = 'C:\Blobstore') to filegroup
       RBSFilestreamProvider
      

    *Replace ‘WSS_Content’ with the name of your specific content database. By default this command will create a folder called ‘Blobstore’ in the root of the C:\ on your SQL Server. You can change this location if desired. You must ensure that you will have enough space on the specified drive for your BLOBs. Also note that each content database will need to have its own folder on the file system (e.g. C:\Blobstore2, C:\Blobstore3 etc.).

Install the RBS provider components on each server (SQL and Web Server)

*First install RBS on your SQL server and then on the First Web server and then on additional Servers.

  1. Browse to http://www.microsoft.com/download/en/details.aspx?id=16978 find rbs.msi download X64 Package.
     *download the rbs_x64 link given in TechNet article http://technet.microsoft.com/en-us/library/ee663474.aspx
  2. Run cmd prompt as Administrator and then click OK.
    Change the directory to the place where rbs.msi is downloaded on the machine.
  3. Copy and paste the following command into the Command Prompt window.
       msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY
       DBNAME="WSS_Content" DBINSTANCE="DBInstance Name" FILESTREAMFILEGROUP=RBSFilestreamProvider
       FILESTREAMSTORENAME=FilestreamProvider_1
      
    * Replace WSS_Content and DBInstance Name in the above command with the values that correspond to your own environment.  **This command must be run on the SQL Server and on one of your SharePoint Web Front Ends.

The command will look like it executed and succeeded right away; however, the RBS.msi file initiates a misexe.exe process that will take approximately 1 minute to complete. You will have to check the log to ensure that the process completed successfully.

 After running the command a file called ‘rbs_install_log.txt will be created in the same directory where you ran RBS.msi. To verify if the process was success you can look for the following line in the text file

RBSInstallSuccess.png

*if the process fails you will have to uninstall the ‘SQL Server 2008 R2 Remote Blob Store’ program from Add/Remove programs and then run the RBS.msi file again

RBSAddRemovePrograms.png

Once the RBS software is successfully installed you can verify the state of the configuration by looking for the following tables in the content database that you have configured to use RBS

RBSSQLTables.png

If you do not see these tables you will have to double check the values that you entered into the msiexec command that was executed earlier. Once you have verified the command syntax you will have to uninstall ‘SQL Server 2008 R2 Remote Blob Store’ from Add/Remove Programs and run the command again.

*The SQL Server 2008 R2 Remote Blob Store software needs to be installed on all of your front-end SharePoint web servers.

"You must install RBS client library on all Web servers in the SharePoint farm. The RBS client library is installed only once per Web server, but RBS is configured separately for each associated content database."

http://technet.microsoft.com/en-us/library/ee748631.aspx?

To install the RBS client software on all additional Web Servers ensure that you have a local copy of the RBS.msi file and then execute the following command

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi DBNAME="WSS_Content" DBINSTANCE="DBInstanceName"
ADDLOCAL=Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer

*Replace DBNAME with the name of your additional content database. Replace DBINSTANCE with the name of your SQL Server Instance.

Enable RBS for Each Content Database

After installing the RBS software you still have to enable RBS for each content database additional content database.

When you are configuring an additional content database you need to execute the following command before enabling RBS via PowerShell

msiexec /qn /i rbs.msi REMOTEBLOBENABLE=1 FILESTREAMPROVIDERENABLE=1 DBNAME=WSS_Content_RBSTest2
FILESTREAMSTORENAME=FilestreamProvider_1 ADDLOCAL=EnableRBS,FilestreamRunScript DBINSTANCE=localhost

*This command is similar to the one that was run to install RBS on your SharePoint servers; however, this command is specifically designed for additional content databases. Replace DBNAME and DBINSTANCE to suit your own environment.

Once RBS has been configured for your content databses you need to execute to following PowerShell commands to fully enable it

Open the SharePoint 2010 Management Shell as Administrator and perform the steps below:

$cdb = Get-SPContentDatabase –DatabaseName <Database Name>
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])

*It is important to specify the Database Name when using the SPContentDatabase cmdlet. If a web application contains multiple content databases, and you use Get-SPContentDatabase - WebApplication parameter, the command will fail.

After running the preceding commands you can verify if the configuration is successful by running the following command

$rbss.Installed()

If the preceding command returns ‘True’ then the content database for your web application has been successfully configured to use RBS.

Test your RBS Installation

In order to test that your RBS installation is working properly you can upload a file to your RBS enabled web application that is more than 60KB. If RBS is functioning correctly you should see a new file in your BLOB store that corresponds to your recently uploaded file.

In the example below I uploaded a text file to a SharePoint document library. The text file was approximately 1.3MB. As you can see below the file was placed in the BLOB store on the SQL Server’s local C:\.

BLOBFiles.png

Remote BLOB Store Maintenance

By default RBS will not clean up items that are deleted from SharePoint from the BLOB store. As a result, your BLOB store will grow increasingly large as new items are added to SharePoint. You need to setup an RBS maintenance job that will run periodically and perform ‘garbage collection’ on any items that have been deleted from SharePoint but are still in the BLOB store.

Configuration parameters for RBS maintenance are governed by the settings that are specified in the mssqlrbs_resources.rbs_internal_config table in the database that is configured for BLOB storage.

By default RBS will wait 30 days before deleting any BLOBs that have no reference in SharePoint but are still in the BLOB store. If you are testing your RBS configuration you can set this value to zero days so that you can verify that items are being properly deleted from the BLOB store. Once you have confirmed that garbage collection is working properly you can set the garbage_collection_time_window back to the default value of 30 days.

RBSConfigTable.png
GarbageCollectionSetting.png

garbage_collection_time_window specifies the minimum time that must pass between identifying a blob as having no references in the database and deleting the blob from the store. This guarantees the availability of BLOBs for the specified time in case a backup is restored. The default value is 30 days.

You can change the default garbage collection window by executing a SQL query on the database that has BLOB storage enabled. The default value for each is 30 days

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

Reference http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/e9bda580-b9e7-48c5-b18c-485e016677a1 

*When you are testing BLOB file deletion ensure that you delete the files from the secondary recycle bin, otherwise they will stay in the Content Database even after running the RBS maintainer job. In Full Recovery mode you will need to perform the following to delete files from the BLOB store: 1)Run your RBS Maintainer Scheduled Task 2)Run a Full Log Backup of your content database 3)run a SQL Checkpoint on your content database 4) run an additional log backup of your content database. At this point the BLOBs should be deleted from the BLOB store. For a good explanation of why this is necessary please refer to the following blog post

http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-garbage-collection.aspx

SecondStageRecylceBin.png Delete the file from the secondary recycle bin

Run your RBS Maintainance schedule task (see below for instructions on how to configure this scheduled task)

Run a Transaction log backup of your content database

Execute a manual CHECKPOINT on the content database

Repeat this process once more and the file should be deleted from the BLOB Store.

Run the RBS Maintainer

In order to perform RBS maintenance on a regular basis it is recommended to setup a scheduled task that will run the RBS maintainer executable. Before we configure this scheduled task we have to edit the RBS configuration file and input the name of the content database that we have enabled for RBS.

*You have to add an additional connection string for each database that is configured to use RBS. If you have only one content database configured for RBS then you do not need to perform this step because the configuration file will already have a reference to your RBS enabled content database. This step is only necessary for additional content databases that you want to enable for RBS.

  1. Rename the existing config file (Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config) to web.config using following command (located in %programfiles%\Microsoft SQL Remote Blob Storage 10.50\Maintainer )
       cd /d %programfiles%\Microsoft SQL Remote Blob Storage 10.50\Maintainer
       ren Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config web.config
      
    RBSMaintainerExecutable.png

    *By default all connection strings in the Maintainer configuration file are encrypted. The following process will show you how to unencrypt the file so that you can add your additional connection strings. After you have made your additions you will re-encrypt the configuration file.

  2. Un-encrypt the connection strings from the config file by using following command (aspnet_regiis is located in %windir%\Microsoft.NET\Framework64\v2.0.50727
       cd /d %windir%\Microsoft.NET\Framework64\v2.0.50727
       aspnet_regiis.exe -pdf connectionStrings "%programfiles%\Microsoft SQL Remote Blob Storage 10.50\Maintainer"
    
      
  3. Add an additional connection string to the web.config file for each content database that is RBS enabled as follows. Change the name of the Data Source and Initial Catalog as needed.

    Data Source = YourSQL Server Instance

    Initial Catalog= the name of the content database that is configured for RBS

    “The RBS installer creates one connection string that is named RBSMaintainerConnection by using the connection information that was provided during setup. However, new connection strings must be added for every additional database.”

    http://technet.microsoft.com/it-it/library/ff943565

       <configuration>
        <connectionStrings>
         <add name="RBSMaintainerConnection" connectionString="Data Source=localhost;Initial Catalog=WSS_Content_RBSTest;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>
      

    *You must use a unique connectionString name for each content database specified in the config file. The connectionString name will be referenced in the scheduled task that you will setup for each content database, so each one needs to be unique.

    UniqueConnectionString.png
  4. 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
      
  5. 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: 1) XML file is case sensitive; you need to use the exact string for 'connectionStrings' parameter above.

RBS Scheduled Maintenance Task

You need to create a scheduled task that will run periodically and cleanup files that have been deleted from SharePoint. The job will find files that have been deleted from SharePoint but are still in the BLOB store.

*Each content database needs to have its own scheduled task for BLOB maintenance.

Create a Maintenance Task using following steps (for each database).

  1. Click Start, point to Administrative Tools, and click Task Scheduler.
  2. Right-click Task Scheduler (Local) and click Create Task.
  3. Click the Actions tab and click New.
  4. On the New Action page, specify:
    1. Action as Start a Program.
    2. 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.  
    3. 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

      *The ConnectionStringName parameter refers to the name that you specified in the config file that was unencrypted and then re-encrypted in an earlier step. The first default name that is configured when RBS is installed is ‘RBSMaintainerConnection’. When you add additional connectionStrings to the RBS maintainer configuration file each one must have its own unique connectionString so that multiple scheduled tasks can be setup.

      As seen below I have two content databases configured for RBS. Each content database needs to have its own connectionString name which will then be referenced in its associated scheduled task.

    4. 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. 6.  In the New task dialog box, set:
    1. Begin the task to on a schedule.
    2. The trigger schedule to be Weekly, Sunday, at 2am (or at another time when system usage is low.)
    3. Click OK.
  7. 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:
  8. Make sure that the account under which the task is to be run has sufficient permissions to the database.
  9. Select the option to run whether user is logged on or not.
  10. Click OK.

*Once you are finished testing don’t forget to set your garbage collection parameters back to their default settings

exec mssqlrbs.rbs_sp_set_config_value 'garbage_collection_time_window', 'days 30';
exec mssqlrbs.rbs_sp_set_config_value 'delete_scan_period', 'days 30';
exec mssqlrbs.rbs_sp_set_config_value 'orphan_scan_period', 'days 30';

References

http://blogs.technet.com/b/sharepointjoe/archive/2011/02/01/sp2010-configuring-remote-blob-storage-with-sharepoint-2010.aspx
http://technet.microsoft.com/it-it/library/ff943565
http://blogs.msdn.com/b/sqlrbs/archive/2010/03/19/running-rbs-maintainer.aspx
http://sharepoint.nauplius.net/2011/03/enabling-rbs-on-multiple-content.html
http://reality-tech.com/2011/12/11/rbs-remote-blob-storage-part-3
http://ksmuraleedharan.blogspot.ca/2011/02/remote-blob-storage-in-sharepoint-2010.html
http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-garbage-collection.aspx
http://technet.microsoft.com/en-us/library/ee663474.aspx
http://msdn.microsoft.com/en-us/library/gg316763(v=sql.105).aspx
http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/e9bda580-b9e7-48c5-b18c-485e016677a1 
http://blogs.msdn.com/b/sqlrbs/archive/2008/08/08/rbs-garbage-collection-settings-and-rationale.aspx?