Configure SharePoint 2013 content database to use Remote BLOB Storage using custom REST web service and SQL store procedure

When customer requirement includes BLOB configuration for content database, we tend to perform it using SQL Query . But when we have separate content database for each Site Collection then this task becomes redundant.
In that special case if we automate BLOB configuration process while creating new Site Collection, it can be time saving.

 

Overview

The Idea is to host custom web service on SharePoint 2013 server, which triggers stored procedure located in SQL server database.

sp2013_rest_blob

As described in figure, custom web service is located in ISAPI of SharePoint 2013, from where client can consume it using {site}/_vti_bin/SQLManager.svc  path.

First we will see how REST web service is implemented for calling store procedure, then we will see store procedure.

 

REST Web Service

RBSProcedureVO.cs  and RBSProcedureOutcomeVO.cs  are simply value objects classes which will help while passing parameters to function and while returning outcomes.

 
Web Service Interface and Implementation

Below class shows how to complete our HTTP mapping for the SQLManager service using [WebInvoke]. Invoke method is configured for POST method take request format as JSON. ISQLManager.cs  is an Interface class which includes function definition and required configuration.

In class SQLManager.cs functions are implemented for calling stored procedure. If you are familiar with SQL connection and query execution methods then you will find this function very similar to that. It includes connection string, SQL statement and execution part which will do most of our work.

 
Stored Procedure

Below code described stored procedure to configure FILESTREAM for defined database. It will take system path of database files and create FILEGROUP with {Database name}_BLOB name.

 
Consuming Web Services

Once web service has been implemented and required stored procedure is defined, you can consume it in client application using various way like JavaScript, Workflow, Client Objects Model etc. It just requires to execute web service URL as POST with required parameter in JSON format.

Leave a Reply

Your email address will not be published. Required fields are marked *