SharePoint 2016 with SQL Server 2016 RBS – Installation Guide

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:

sql2016rbs
Enable: FILESTRAEM for Transact-SQL, FILESTREAM for file I/O and allow remote clients to access FIELSTREAM data

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
  • Application

Prerequisites

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:

rbs_tables_sql2016
The RBS Setup creates some new tables

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
  • db_owner

Add-PSSnapin *sharepoint*

$db = Get-SPContentDatabase WSS_Content
$rbs = $db.RemoteBlobStorageSettings
$rbs.Installed()
$rbs.Enable()
$rbs.SetActiveProviderName($rbs.GetProviderNames()[0])
$rbs

# Run the following line to migrate existing content
$rbs.Migrate()

Please change WSS_Content according to your environment.

Issues

The URL ‘FILENAME’ is invalid. It may refer to a nonexistent file or folder…

rbs_sp2016_error
Uploading a file results in an error.

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

      • 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.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.