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:

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:

Installation of the ODBC driver with the recommended settings

w3wp blocks the installation of the odbc driver

 

 

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:

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…

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.

 

Advertisement