Configuring SharePoint 2010 RBS-Enabled Content Database with Remote Filestream Provider
Introduction
There are a several articles that mention the use of RBS for SharePoint 2010 content database as a method for blob storage. However, they focus on using RBS with a local Filestream provider and do not mention the drawbacks of this configuration. The list of configuration steps acknowledges the RBS with local Filestream, but those steps do not provide detailed architectural differences between RBS and Filestream. I will present a more practical architectural design with Remote Filestream store for SharePoint content that showcases high availability and disaster recovery.
The drawback of RBS with local Filestream will cause the database to be incapable of database mirroring. The combination of RBS and Filestream on one SQL server will reveal faults in the architecture for disaster recovery and performance impact during blob access.
Server List with RBS components
I will list the server name for better architecture description. Each individual server is on a separated node which could be either a virtual or physical server.
| Server Name | Purpose | RBS DLL | SQL Filestream |
| SPWFE1 | The SharePoint web front end server 1 | SqlRemoteBlobs and FilestreamBlobStore in GAC | |
| SPWFE2 | The SharePoint web front end server 2 | SqlRemoteBlobs and FilestreamBlobStore in GAC | |
| SPAPP1 | The SharePoint application server 1 | ||
| SQLSP | The SQL server with default instance for SharePoint content and configuration databases | ||
| SQLFS1 | The dedicated SQL Filestream server with default instance to serve the store for SharePoint blob content | Enable SQL Filestream | |
| SQLFS2 | The secondary SQL Filestream server with default instance to serve the store for SharePoint blob content | Enable SQL Filestream | |
| Job | The RBS maintainer job scheduler | SqlRemoteBlobs and FilestreamBlobStore in GACMaintainer.exe |
I used the RBS.msi from SQL 2008 R2 SP1 feature pack for this testing. (http://www.microsoft.com/download/en/details.aspx?id=26728)
This shows the selection for both Microsoft.Data.SqlRemoteBlobs and Microsoft.Data.BlobStores.FilestreamBlobStore DLLs installed on SharePoint WFE server.
Configuration Steps
The following configuration steps are based on each content database which will have an associated database on SQL Filestream server. Each database on the Filestream server will have its own data store for a Filestream group under local LUN.
| 01. Create master key encryption on content database | USE [WSS_Content1]If not exists (select * from sys.symmetric_keyswhere name = N’##MS_DatabaseMasterKey##’)create master key encryption by password = N’Password!’ |
| 02. Run Server folder script under msi on content database | RemoteBlobEnable.sql |
| 03. Exec first RBS SP rbs_sp_enable_rbs on content database | exec mssqlrbs.rbs_sp_enable_rbs |
| 04. Created associated FS database on Remote Filestream Instance | CREATE DATABASE [dbfsWSS_Content1] ON PRIMARY( NAME = N’dbfsWSS_Content1′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.NI2MSSQLDATAdbfWSS_Content1.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON( NAME = N’dbfsWSS_Content1_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.NI2MSSQLDATAdbfsWSS_Content1_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE [dbfsWSS_Content1] SET COMPATIBILITY_LEVEL = 100GO |
| 05. Create FS file group on FS database | USE [dbfsWSS_Content1]If not exists (select groupname from sysfilegroups where groupname=N’fgRBSFilestreamProvider’)alter database [dbfsWSS_Content1]add filegroup fgRBSFilestreamProvider contains filestream |
| 06. Add storage folder file to file group on FS database | USE [dbfsWSS_Content1]Alter database [dbfsWSS_Content1]add file (name = RBSFilestreamRoot, filename = ‘H:FSStoreWSS_Content1’)to filegroup fgRBSFilestreamProvider |
| 07. Create master key encryption on FS database | USE [dbfsWSS_Content1]If not exists (select * from sys.symmetric_keyswhere name = N’##MS_DatabaseMasterKey##’)create master key encryption by password = N’Password!’ |
| 08. Run File Stream Provider folder script under msi on FS database | FileStreamProviderSetup.sql |
| 09. Exec first FS SP rbs_fs_sp_setup on FS database | — Use the schema_suffix as 1USE [dbfsWSS_Content1]GODECLARE @return_value int, @schema_suffix nvarchar(80)SELECT @schema_suffix = N’1’EXEC @return_value = [mssqlrbs_filestream].[rbs_fs_sp_setup] @schema_suffix = @schema_suffix OUTPUT
SELECT @schema_suffix as N’@schema_suffix’ SELECT ‘Return Value’ = @return_value GO |
| 10. Exec second FS SP rbs_fs_sp_initialize on FS database | — USE allow_remote as 1 for Remote FS ProviderUSE [dbfsWSS_Content1]GODECLARE @return_value int, @schema_version nvarchar(128), @share_prefix nvarchar(4000)EXEC @return_value = [mssqlrbs_filestream].[rbs_fs_sp_initialize] @schema_suffix = N’1′,
@filegroup_name = ‘DEFAULT’, @data_filegroup_name = ‘DEFAULT’, @allow_remote = 1, @schema_version = @schema_version OUTPUT, @share_prefix = @share_prefix OUTPUT SELECT @schema_version as N’@schema_version’, @share_prefix as N’@share_prefix’ SELECT ‘Return Value’ = @return_value GO |
| 11. Add blob store record on table rbs_internal_blob_stores on content database | ———————————————————- ————————————————————– The “schema_suffix” needs to match the– “schema_suffiex”when the first — FileStreamProvider rbs_fs_sp_setup executed. — — Added by James Cheng on 12/03/2011 ———————————————————-
declare @configXml xml, @blob_store_id smallint set @configXml = N’ <blob_store_config> <common> <blob_store_type> RemoteFilestream </blob_store_type> <backend_blob_store_version> 1.0.0.0 </backend_blob_store_version> <min_provider_version_supported> 1.0.0.0 </min_provider_version_supported> <blob_store_location>Data Source=SQLFS1;Initial Catalog=dbfsWSS_Content1;Integrated Security=True;Application Name=”Remote Blob Storage FILESTREAM Provider”</blob_store_location> </common> <core> <config_item_list> <config_item key = “schema_suffix” value = “1” /> <config_item key = “max_size_inband_read” value = “1258290” /> <config_item key = “tran_pool_size” value = “5” /> <config_item key = “tran_pool_tran_lifetime” value = “time 00:01:00” /> </config_item_list> </core> <extended> <config_item_list> <config_item key = “filegroup_name” value = “fgRBSFileStreamProvider” /> <config_item key = “data_filegroup_name” value = “DEFAULT” /> <config_item key = “max_pool_size” value = “200000” /> <config_item key = “max_size_inband_write” value = “1258290” /> <config_item key = “max_size_inline_blob” value = “61140” /> <config_item key = “write_allocation_size” value = “0” /> </config_item_list> </extended> </blob_store_config>’ execute mssqlrbs.rbs_sp_add_blob_store N’FilestreamServer_1′, @configXml, @blob_store_id output go |
| 12. Enable RBS through SharePoint object model on content database | $cdb = Get-SPContentDatabase WSS_Content1$rbss = $cdb.RemoteBlobStorageSettings$rbss.Installed()$rbss.Enable()$rbss.GetProviderNames()[0]$rbss.SetActiveProviderName($rbss.GetProviderNames()[0]) |
| 13. Add AppPool of web application account to FS database | USE [dbfsWSS_Content1]GOCREATE USER [MyDomainSPAppPool1] FOR LOGIN [MyDomainSPAppPool1] WITH DEFAULT_SCHEMA=[dbo]GO |
HA and DR Concerns
The secondary SQL Filestream server, SQLFS2, will be the standby server for SQLFS1 using log shipping. In the case of a disaster recovery, the standby server will become active. Therefore, the SharePoint record on the RBS blob store table, rbs_internal_blob_stores , of the content database will need to be manually updated from SQLFS1 to SQLFS2, but no SharePoint object will need to be updated.
References
- SQL Server 2008 R2 Remote BLOB Storage – http://go.microsoft.com/fwlink/?LinkId=210422
- Overview of RBS (SharePoint Server 2010) – http://technet.microsoft.com/en-us/library/ee748649.aspx
- FILESTREAM (SQL Server) – http://technet.microsoft.com/en-us/library/gg471497(SQL.110).aspx
- SQL Server Remote BLOB Store and FILESTREAM feature comparison – http://blogs.msdn.com/b/sqlrbs/archive/2009/11/18/sql-server-remote-blob-store-and-filestream-feature-comparison.aspx
- Review & musings of whitepaper “Remote BLOB Storage” – http://shauntj.wordpress.com/2011/07/11/review-rbsblobstorage-whitepaper/
James Cheng's SQL SharePoint Interconnections 