Remote BLOB Storage (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Microsoft SQL Server offers several options for storing large-value data types (explained in Using Large-Value Data Types1), regardless of whether they are character large object (CLOB) or binary large object (BLOB) based. By default, SQL Server stores these in the database, and SQL Server 2008 R2 also provides FILESTREAM and Remote BLOB Storage (RBS) as alternatives (see FILESTREAM Overview2 and Remote BLOB Store Provider Library Implementation Specification3 respectively for more information).

RBS is designed to move the storage of BLOBs from database servers to commodity storage solutions. While using RBS does have implications (especially for application design and for backup and restore), RBS has the potential to reduce server resource utilization and enable optimal handling of BLOB data when properly deployed.

RBS is a library API set that is incorporated as part of the add-on feature pack for SQL Server 2008 and SQL Server 2008 R2 (see Microsoft SQL Server 2008 R2 Feature Pack4 for more information). It uses auxiliary tables, stored procedures, and an executable to provide its services. A reference to the BLOB (provided by the BLOB store) is stored in RBS auxiliary tables, and an RBS BLOB ID is generated. Applications store this RBS BLOB ID in a column in application tables. The RBS column is not a new data type; it is just a simple binary(20).

Best Practices

The following resources provide additional information about implementing RBS. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

  • Several Microsoft RBS samples are available online on the Codeplex site for RBS.5

  • RBS needs careful consideration when designing backup and restore strategies. Prior to production deployment, you should validate application data integrity at all stages of the disaster recovery process. The reason for this is that the database backup and restore is a separated and discrete operation from that of the BLOB store.

    In general, the backup process for relational data should be started before that for non-relational data, and non-relational data restores should be performed before SQL Server restores.

  • The white paper Remote BLOB Store Provider Library Implementation Specification,6 is useful for customers who are considering building RBS provider software instead of buying it.

Case Studies and References

Customers have found that rather than building their own RBS drivers, the following commercial alternatives could be considered:

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Customers have suggested that Todd Klindt's SharePoint Admin Blog12 is a useful resource, even for solutions that are not related to Microsoft SharePoint but that use RBS. Search the blog specifically for the term RBS.

  • The SQL Remote Blob Storage Team Blog13 contains articles that offer in-depth discussions of RBS.

  • Gather BLOB storage requirements. Unless it is a prerequisite that you store remotely, FILESTREAM and in-database storage may be a viable design option.

  • RBS uses a provider to connect to any dedicated BLOB store that uses the RBS APIs. Storage solution vendors can implement providers that work with RBS APIs. In addition to third-party providers, you can use the RBS FILESTREAM provider or even create your own provider, although this is not recommended (see Plan for Remote BLOB Storage (RBS) (SharePoint Server 2010)14 for more information).

  • While most RBS resources are provided in the context of a SharePoint implementation, this is not the only way RBS can be used. The SharePoint implementation, however, serves as a real-world implementation.

  • The Microsoft Developer Network (MSDN) blog post SQL Server Remote BLOB Storage and FILESTREAM feature comparison15 provides a basic feature comparison between RBS and FILESTREAM (non-RBS) design. Check with storage providers for additional considerations. You should also consider the following:

    • Full-text search does not support RBS data.

    • RBS is not constrained to the underlying NTFS volumes of the SQL Server instance.

    • The RBS Security Model16 differs from the SQL Server security model.

    • Backup of RBS data is a separate step, and a discrete operation from the database backup.

    • RBS provides a relatively easy mechanism to direct small values (configurable) to in-database storage and larger values to the RBS provider, as described in the blog post RBS Filestream Provider Small Blob Optimization Settings.17

    • RBS requires application tier changes, while FILESTREAM implementation can be transparent to the application tier.

  • FAQ: SharePoint 2010 Remote BLOB Storage (RBS) 18 explains that if the BLOB objects are stored on the local SQL Server file system, then you can use any SQL Server SKU. For any other scenarios, the local SQL Server needs to be SQL Server Enterprise Edition.

  • It is important to recognize that because RBS is an API library, the BLOB value passes through SQL Server. With this understanding, it is clear that you cannot use full-text search, database mirroring, and SQL Server-based backup and restore for the BLOB store.

  • RBS is typically considered when architecting a SharePoint 2010 solution with a very large content database. Note that at least one vendor has distinct RBS offerings for SQL Server and SharePoint.

  • RBS allows an architecture where multiple RBS providers can be leveraged; this lets you choose an appropriate data store (RBS provider) depending the provider’s value proposition. See the blog post Multiple BLOB Stores in RBS - Defaults and Explicit Store Selection19 for more information.

Appendix

Following are the full URLs for the hyperlinked text.

1 Using Large-Value Data Typeshttps://msdn.microsoft.com/en-us/library/ms178158.aspx

2 FILESTREAM Overviewhttps://msdn.microsoft.com/en-us/library/bb933993.aspx

3 Remote BLOB Store Provider Library Implementation Specificationhttps://msdn.microsoft.com/en-us/library/cc905212.aspx

4 Microsoft SQL Server 2008 R2 Feature Packhttps://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52

5 CodePlex Site for RBShttp://sqlrbs.codeplex.com/

6 Remote BLOB Store Provider Library Implementation Specificationhttps://msdn.microsoft.com/en-us/library/cc905212.aspx

7 DocAve Software for SharePoint 2003, 2007, and 2010http://www.avepoint.com/sharepoint-extension-archiving-docave/

8 EMC Centera Provider for Microsoft SQL Server Remote Blob Storagehttp://www.emc.com/collateral/hardware/data-sheet/h6117-centera-sql-serv-ds.pdf

9 StoragePoint Websitehttp://www.storagepoint.com/

10 SnapManager for SharePoint Serverhttp://www.netapp.com/us/products/management-software/snapmanager-sharepoint-server.html

11 OpenText ECM Suite for Microsofthttp://www.opentext.com/2/global/products/products-opentext-ecm-suite-for-microsoft.htm

12 Todd Klindt's SharePoint Admin Bloghttp://www.toddklindt.com/blog/default.aspx

13 SQL Remote Blob Storage Team Bloghttps://blogs.msdn.com/b/sqlrbs/

14 Plan for Remote BLOB Storage (RBS) (SharePoint Server 2010)https://technet.microsoft.com/en-us/library/ff628583.aspx

15 SQL Server Remote BLOB Storage and FILESTREAM feature comparisonhttps://blogs.msdn.com/b/sqlrbs/archive/2009/11/18/sql-server-remote-blob-store-and-filestream-feature-comparison.aspx

16 RBS Security Modelhttps://blogs.msdn.com/b/sqlrbs/archive/2010/08/05/rbs-security-model.aspx

17 RBS Filestream Provider Small Blob Optimization Settingshttps://blogs.msdn.com/b/sqlrbs/archive/2010/03/31/rbs-filestream-provider-small-blob-optimization-settings.aspx

18 FAQ: SharePoint 2010 Remote BLOB Storage (RBS)https://blogs.msdn.com/b/opal/archive/2010/03/24/faq-sharepoint-2010-remote-blob-storage-rbs.aspx

19 Multiple Blob Stores in RBS—Defaults and Explicit Store Selectionhttps://blogs.msdn.com/b/sqlrbs/archive/2010/01/04/multiple-blob-stores-in-rbs-defaults-and-explicit-store-selection.aspx