In the last few weeks I tried to enable SQL Server 2016 Remote BLOB Storage (RBS) for SharePoint Server 2016. As mentioned in my earlier post I had no success installing the SQL Server 2016 RBS components on my SharePoint 2016 WFEs.
By now I was able to install and configure SQL Server 2016 RBS for SharePoint 2016 and I want to share my experience.
In the moment Microsoft is certifying SQL Server 2016 RBS for SharePoint Server 2016. By now be prepared to experience some minor issues. For SQL Server 2014 RBS there is additional information available here.
Prepare your SQL Instance – Enable FILESTREAM
By default in SQL Server 2016 the FILESTREAM feature is installed when you install SQL Server 2016. You mus enable it first on the SQL Instance that hosts the SharePoint Server 2016 Databases:
The Windows share will be created automatically by SQL server during the further steps of the installation process.
Click apply and go to the SQL Server Management Studio an run a new Query:
EXEC sp_configure filestream_access_level, 2 RECONFIGURE
Execute this query and restart the SQL service to complete this section.
Prepare your Databases – Provision a BLOB store for each content database
Confirm that the user account performing the following steps is a member of the db_ower database role on each of your SharePoint Server 2016 Content Databases.
Run the following queries to configure your database specific blob store. Please modify the [WSS_Content], FOLDER and PASSWORD placeholders to meet your requirements.
use [WSS_Content] if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##') create master key encryption by password = N'PASSWORD'
use [WSS_Content] if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider') alter database [WSS_Content] add filegroup RBSFilestreamProvider contains filestream
Finally configure the storage path for the RBS contents:
use [WSS_Content] alter database [WSS_Content] add file (name = RBSFilestreamFile, filename = 'FOLDER') to filegroup RBSFilestreamProvider
In a best practice environment use a separate volume for the RBS store to achieve the best performance and simplify troubleshooting.
After running the scripts check if FOLDER got provisioned correctly.
Prepare your SharePoint 2016 Servers
Installing the SQL Server 2016 RBS Provider requires the following prerequisites and steps to complete the setup. Please use an account that is member of the Administrators group on the SharePoint Server.
You need to perform these steps only on SharePoint 2016 Web Front-End servers or on servers hosting the SharePoint Web Applications (WFEs). In a Min-Role environment this would be the following roles:
- Web Front-End
The SQL 2016 RBS installer needs the Microsoft ODBC Driver 13 for SQL Server to connect to SQL Server 2016 databases . You can download the driver here.
I ran a standard installation with the recommended settings. Please stop the World Wide Web Publishing Service prior to install the ODBC driver, cause this blocks the installation:
Installing the RBS Provider
The SQL Server 2016 RBS installer can be found on the SQL 2016 ISO: ..\x64\redist\RemoteBlobStore\RBS.msi or it can be downloaded from: Microsoft SQL Server 2016 Feature Pack
I ran the installation with the predefined settings.
Running the installation via CMD is also available. For the the commands see here.
Verify the installation by checking your database, if there are the following tables available:
Enable RBS for each content database
To enable the RBS functions on the Content Database start a new Power Shell session as a SPShellAdmin with the following permissions on the SQL Server 2016 Instance:
- security admin
Add-PSSnapin *sharepoint* $db = Get-SPContentDatabase WSS_Content $rbs = $db.RemoteBlobStorageSettings $rbs.Installed() $rbs.Enable() $rbs.SetActiveProviderName($rbs.GetProviderNames()) $rbs # Run the following line to migrate existing content $rbs.Migrate()
Please change WSS_Content according to your environment.
The URL ‘FILENAME’ is invalid. It may refer to a nonexistent file or folder…
Checking the ULS Log I found the following message:
Exception thrown storing stream in new SqlRemoteBlob: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'rbs_sp_get_config', database 'SP2016_Content_RBS', schema 'mssqlrbs'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() ...
Turns out my application pool did not have the necessary db roles. I gave the application pool the “db_rbs_admin” role on the rbs enabled content database. Please be aware, that this might not be the least privilege approach.
4 thoughts on “SharePoint 2016 with SQL Server 2016 RBS – Installation Guide”
[…] RBS setup for SharePoint Server consists of several parts. See my installation guide here. Which goes into the details for configuring the SQL Server 2016 and reflects the latest Update 4 […]
Great article! Do you know if SQL 2016 Standard can handle RBS or does it require SQL 2016 Enterprise?
If I understand it correctly, it is possible to use SQL 2016 Standard in conjunction with the default filestream provider. (Source: https://msdn.microsoft.com/en-us/library/gg638709.aspx#RBS%20Requirements)
In previous versions of SQL Server you were able to use SQL Express with RBS. (Source: https://sqlrbs.codeplex.com/)
Thanks for the reply Andi! I guess what’s still confusing me is the following paragraph from MS and how this translates to SP 2016, SQL 2016 Standard and RBS:
RBS requires SQL Server Enterprise for the main database server in which the BLOB metadata is stored. However, if you use the supplied FILESTREAM provider, you can store the BLOBs themselves on SQL Server Standard.
In the case of SP 2016 with RBS, I’m wondering if it means that RBS will work provided the blob and SP databases are on the same server SQL Standard and we use the supplied FILESTREAM.