• Article

Deploying Remote BLOB Store

This topic describes how to deploy remote BLOB store (RBS) on a database by installing and enabling it, registering one or more RBS columns, and adding a BLOB store to the database.

Enabling RBS

To enable the RBS improvement on a database, it must first be installed and enabled. To enable RBS, follow the procedure listed below.

  1. To install RBS, run the RBS .msi installer or execute the RBS install script, included with the RBS Server Pack, on the database. The scripts requires Database owner privileges to execute.

  2. Assign the database users to the RBS roles created in step 1.

  3. If you chose to install RBS manually with the installation script, execute the rbs_sp_enable_rbs stored procedure to enable RBS. This procedure may be executed with an optional parameter that specifies the file-group into which RBS will add its auxiliary tables.

  4. Use the rbs_sp_set_config_value stored procedure to configure Remote BLOB Store and overwrite default RBS settings, if needed.

  5. Schedule the RBS Maintainer component to perform periodic maintenance operations for the database. RBS Maintainer must connect to the database using an account that is a member of rbs_admin role. If Windows Authentication is used, that account must be the one the user specified in the "Run as" field of the task scheduling dialog. Using the default value of "NT AUTHORITY\SYSTEM" account in that field may also cause scheduling the task to fail. If you chose to install RBS with the .msi installer, it will prompt you to enter scheduling information.

Register RBS Columns

In order to track BLOB data stored outside of a database, a varbinary column in a table that is used to store RBS Blob IDs must be registered as an RBS column. To register the column, execute the rbs_sp_register_column stored procedure, which takes the table and column names as input parameters and registers the column as belonging to RBS. This operation requires rbs_admin privileges to execute.

Adding, Modifying, and Removing Blob Stores

To add a BLOB store can to the RBS deployment, call the sp_RemoteBlobStorageAddBlobStore stored procedure with the specified BLOB store name and configuration XML. The following configuration settings are required for all BLOB stores:

  • BlobStoreType

  • Version

  • MinimumProviderVersionRequired

  • StoreLocation

Some BLOB stores may have additional configuration requirements. These requirements include core items, which are required to read BLOBs, and extended, which are required to write BLOB information. The BLOB Store configuration information can be retrieved using a read-only view, rbs_blob_stores, which is exposed by RBS. This view displays the blob store name, type, version, store location and configuration settings.

The BLOB store configuration can be altered at any time by calling the rbs_sp_modify_blob_store stored procedure, which accepts two configuration setting XML parameters, one to specify the name of the BLOB store and another to specify the XML configuration. A BLOB store can be deleted by calling the rbs_sp_delete_blob_store stored procedure.

RBS Credentials

Some BLOB stores require users to submit credentials before they are able to access information. In RBS, user credentials consist of a credential name, such as the user name, and a credential secret, such as a password, which is stored in an encrypted format.

Each credential entry must be associated with a database user and a BLOB store. A special value of 0x can be specified for the database user, meaning that the credential will be used for every user that doesn't have a unique credential defined for them. Other user identifiers must correspond to SQL Server user ID values obtained by calling suser_sid().

Multiple credentials can be specified for a single user-BLOB store pair, in which case the BLOB store must define how the credentials are used. In general, the BLOB store documentation should specify what types of credentials are expected.

The following stored procedures are used to perform common RBS credential tasks:

  • rbs_sp_set_blob_store_credential @sql_user_sid varbinary(85), @blob_store_id smallint, @credential_name nvarchar(256), @credential_secret varbinary(max). Adds a credential with the specified name, secret, and associated user to the BLOB store.

  • rbs_sp_delete_blob_store_credential @sql_user_sid varbinary(85), @blob_store_id smallint, @credential_name nvarchar(256). Deletes credentials with specified name and user from the specified BLOB store.

  • rbs_sp_get_all_blob_store_credentials @sql_user_sid varbinary(85). Lists all credential names for the specified user ID. If no user ID is specified, all credential names will be listed.

  • rbs_sp_get_blob_store_credentials. Lists all credentials for the current user, including decrypted version of their credential secret.

RBS Restrictions

SQL Server Enterprise is required to connect to BLOB Stores other than FILESTREAM.

SQL Server Developer can be used with any RBS provider for testing and developing applications, but it cannot be used for production deployments.

If an RBS provider is registered with an incompatible instance of SQL Server, an error will be thrown.