Wednesday, July 25, 2012

Bang Two Pound Four: Remote Blob Storage (RBS) with SharePoint 2013 using SQL Server 2012


Check out the whole SharePoint 2013 Solution Series
New Titles Added Weekly!

I am in the process of evaluating SharePoint 2013 using the Preview (Beta) edition. Since we are moving from MOSS 2007 directly to SharePoint 2013, any new features that were introduced with SharePoint 2010 are fair game.

One of the advantages with the latest SharePoint versions and SQL Server versions is the ability to implement Remote Blob Storage (RBS) using the FileStream Provider. This allows for documents and files that are larger than a specified amount of bytes (default 100KB) to be stored on a connected file system of the SQL Server box instead of inside the database itself. The overall advantage is keeping your content databases smaller and more manageable.

The steps for implementing RBS with SharePoint 2013 and SQL Server 2012 are very similar of that with SharePoint 2010 and SQL Server 2008. Since I found several blog posts and articles that explain the exact same steps (who copied who???) with the exact same code, I decided to change it around a bit and not use the exact names and passwords.

Whoops! When setting up the MASTER KEY in SQL Server on my main content database, I decided to use a different password. The blog posts and articles all used
N'Admin Key Password !2#4' - so I decided I wouldn't copy their (I thought made up) password and use N'Admin Key Password SharePoint1'.

That sent me through some SharePoint hell since I did not see any files being stored in the blob storage folder. However, once I altered and regenerated the master key with the password using the correct !2#4 (bang two pound four), everything magically started working. You don't learn unless you make mistakes so I am actually glad I went through some trouble.

Anyway, here are the steps I took to convert an existing SharePoint 2013 (Preview) content database to use RBS within SQL Server 2012:


1. Enable File Stream on the SQL Server
On the SQL Server 2012 box, open SQL Server Configurations Manager.



Select SQL Server Services from the left pane. Right-click on the SQL Server process in the right window and select Properties:



The SQL Server Properties dialog appears. Select the FILESTREAM tab and check all of the check boxes:


Click OK.

Start SQL Management Studio and open a new query window.

Execute the following two SQL Statements (on any database):

EXEC sp_configure filestream_access_level, 2

RECONFIGURE



Using your content database (the one I used was WSS_CONTENT_<guid> because I was playing around and was too lazy to remove the GUID or create a proper name in the first place for that matter), open a new query:



Create the master key using the following SQL:

if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##')
     create master key encryption by password = N'Admin Key Password !2#4'




Create a new filegroup for the RBS:

if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')
      alter database [<your content database>]
      add filegroup RBSFilestreamProvider contains filestream



Add the "file" using the following SQL:

alter database [<your content database>]
add file (name = RBSFilestreamFile, filename= 'c:\SPBlobStorage')
      to filegroup RBSFilestreamProvider



The "file" is a folder on a connected drive. It can be a local drive on the SQL Server or an attached iSCSI drive. The folder cannot exist already. Executing the SQL statement above automatically creates the folder specified on the drive specified.


2. Install RBS on the DB and Web/Application Servers
The RBS bits need to be installed on the database server and on each Web server and Application server that exist in the SharePoint farm. The RBS bits are a separate installation as part of the SQL Server 2012 Feature Pack. You can access the feature pack for SQL Server 2012 here.

Scroll down on the feature pack download page and locate the RBS download:




Download the appropriate package (hopefully x64) and save it in a central location.

For the first web server (or database server), create a new batch file, in the same location as you downloaded RBS.msi, using the following code (all on one line - no line breaks):

 msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi
TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY
DBNAME="<ContentDbName>"
DBINSTANCE="<DBInstanceName>"
FILESTREAMFILEGROUP=RBSFilestreamProvider
FILESTREAMSTORENAME=FileStreamStore  



Open a Command Prompt and execute the first batch file:



Verify success by reviewing the rbs_install_log.txt file that was generated in the same folder. The completion message is not the very last thing but it is towards the end of the log:


On all of the other web servers and application servers, place the following code into a batch file (again no line breaks):

 msiexec /qn /lvx* rbs_install_log.txt /i  RBS.msi DBNAME="ContentDbName" DBINSTANCE="DBInstanceName" ADDLOCAL="Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer"


Open a Command Prompt and execute the second batch file on each web server and application server:


3. Enable RBS on Content Database
To enable RBS on the content database, you must use PowerShell. Therefore, open up PowerShell or Notepad and create the following PowerShell Script:

$cdb = Get-SPContentDatabase -WebApplication "<Web Application Name>"
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss

You may also add $rbss.MinimumBlobStorageSize=1048576 to increase the minimum file size that will be considered for RBS. The example number shows 1MB.


Save the script as a .ps1 file and then open the SharePoint 2013 Management Shell:


Execute the script:


 The first output is"True" which means RBS has been installed correctly. The second output displays the RemoteBlobStorageSettings object and shows that RBS is enabled and displays the name of the Active Provider (which should be the same name you used as the FILESTREAMSTORENAME in the RBS installation batch file.


4. Test RBS

Now it's time to see this working in action! Navigate to your SharePoint site related to the content database you just configured. Open a document library:



Upload a bunch of documents into the document library:


Investigate the folders within the local file system blob storage location:


Files appear in small chunks.



47 comments:

  1. Hello thank you it's very informative
    I have been successful installing RBS 2012 version with the first command on my DB server but the command does not work on my main web-front end server i get an error

    Product: Microsoft SQL Server 2012 Remote BLOB Store -- Installing the FILESTREAM blob store failed with error message:
    RBS Error. Original Error: Number 2601, Severity 14, State 1, Procedure rbs_sp_add_blob_store, Line 64,
    Message: Cannot insert duplicate key row in object 'mssqlrbs_resources.rbs_internal_blob_stores' with unique index 'rbs_internal_blob_stores_ix_name'. The duplicate key value is (FilestreamProvider_1).
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

    a lot of the technet and related articles i've read say to use the first command on both the Db server and main front end server

    anyhow i however install with the second install command on both. It appears to successfully install and enable RBS for the first content database but i'm unsuccessful installing RBS for my other content databases with this error

    Product: Microsoft SQL Server 2012 Remote BLOB Store -- Installing Remote Blob Storage on the database has failed with error message:
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    Papa

    ReplyDelete
    Replies
    1. In the PowerShell script, instead of getting the content database from the web application, get the exact content database itself:

      $cdb = Get-SPContentDatabase "contentdatabasename"

      See if that works.

      Delete
    2. topology
      2 node SQL Server 2012 failover cluster
      2 sharePoint 2010 server dual WFE/App server roles

      I install RBS.msi on my active node with this command:
      msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi
      TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY
      DBNAME=""
      DBINSTANCE=""
      FILESTREAMFILEGROUP=RBSFilestreamProvider
      FILESTREAMSTORENAME=FilestreamProvider_1

      I get an error when i try to install with the same command on my Frontend server :
      ......The duplicate key value is (FilestreamProvider_1).


      So I installed with the other command

      msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi DBNAME="ContentDbName" DBINSTANCE="DBInstanceName" ADDLOCAL="Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer"

      on both frontend/app servers

      after that i run the SharePoint Management shell commands

      $cdb = Get-SPContentDatabase ""
      $rbss = $cdb.RemoteBlobStorageSettings
      $rbss.Installed()
      $rbss.Enable()
      $rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
      $rbss

      After all this RBS appeared configured for my first DB

      I then attempt to configure RBS for another content database

      by first running the SQL Queries then the RBS.msi installation from command prompt
      but the installation process fails and i've tried on both all the servers it fails on each

      with the error i specified in my initial post thank you

      papa

      Delete
    3. Ok. I'm in holiday mode but I think the problem is that you are trying to install twice. For the second content database, just run the PowerShell script - don't try to reinstall.

      Delete
  2. Hey Steve,

    thanks for outlining this.

    Unfortunately I've a problem enabling the RBS.

    I've an SQL 2012 and a Sharepoint 2013 WFE running on different boxes.

    The MSI installation through the command line went fine. I also found the log entry that read "Installation completed successful!". The "Tables" section of the content DB in SQL Management Studio shows several mssqlrbs tables.

    When I try to enable RBS through Powershell I keep getting the following two errors:

    1) $rbss.Enable()

    SQL remote blob storage must be installed on each web front end server and on the content database before it may be used.

    2) $rbss.SetActiveProviderName($rbss.GetProviderNames()[0])

    The same message as above.

    I tried to activate RBS both ways -> "-WebApplication" and "-ContentDatabase"

    Any idea how to solve this?

    Thanks!

    ReplyDelete
    Replies
    1. So you installed the MSI on both the SQL Server and the SharePoint 2013 WFE, correct? Can you verify that there are no other servers on the farm? It sounds like RBS doesn't think it is installed on all servers.

      Delete
  3. Thanks for your reply!

    Since it's a brandnew farm, I'm pretty sure that there isn't another server on the farm. I installed the servers during the past few days.

    I installed the MSI on both servers, both times the MSI log read "sucesfull", the $rbss.installed() returns true, but i'm not able to enable RBS for some reason.

    ReplyDelete
  4. Hey Steve,

    I torn down everything and rebuild it from scratch last night.

    This time I ran the "first" MSIEXEC on the SQL Box and the second MSIEXEC on the WFE and it worked. Last time I run the first MSIEXEC command on both (SQL and WFE) boxes since all articles read "use this command on the first WFE and the SQL box".

    After doing this I ran into another issue, when trying to enable RBS on another Content DB. Actually it seems to be the same issue "Anonymous" described above. After a bit of research I found that this issue is caused by a bug within the SQL Server 2012, that is described here http://support.microsoft.com/kb/2767183?wa=wsignin1.0 and that was already fixed with the CU5 for the SQL Server 2012 here -> http://support.microsoft.com/kb/2777772.

    I'm currently doing a backup of my DBs and then will install the CU5.

    Best,
    Markus

    ReplyDelete
  5. Hi, thank you for the steps. I have SharePoint 2013 and SQL 2012. 1 wfe and 1 app server. I don't get any errors when I install and configure rbs but when I upload the document the blob store folder remains empty. I can see GUID folders created but they are empty.

    ReplyDelete
    Replies
    1. Make the sure the file is larger than the threshold you configured.

      Delete
    2. Hi Steve,
      Myself I'm not getting any error but the file (a big one) is still going on the DB and not to the file System.
      What could be?

      Delete
    3. There is a 2GB file limitation.

      Delete
  6. Hi Steve,

    Thanks for the Steps. We have 1 WFE and 1 app server. I didn't get any errors when I install and configure RBS but when i try to upload a document , I get the below Error

    Sorry, something went wrong The URL 'Shared Documents/logfile.txt' is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.

    I tried to increase minimum blob storage and tried with different size files, but lo luck.

    ReplyDelete
    Replies
    1. This could be from a number of reasons. Anywhere from the content db being full to third party software issues. This post http://go4answers.webhost4life.com/Example/url-file-name-invalid-may-refer-67794.aspx has some discussion around RBS. If that doesn't help you can see other answers by searching "the url is invalid it may refer"

      Delete
  7. Hello .. please could you check your WSS_CONTENT DB structure ?? .. I don't have AllDocStreams table at all .. (SQL 2012 + SharePoint 2013) ...:(

    ReplyDelete
    Replies
    1. I no longer have the environment I tested RBS with. I'll check a regular environment.

      Delete
  8. I have also second question .. when I use migrate option to flush data from DB to filesystem all files have own size ( eg. 2 mb , 1.2 mb etc). When I use site and upload some file I see on blob multiple 1024 KB .. for example for 50 MB file i have 50 x 1024 KB files ... why ?? Do you know how I can switch it?

    ReplyDelete
    Replies
    1. That is controlled by the file stream. It is not made to be human readable. Remember it is remote blob storage, not remote file storage (although the bytes represent uploaded files).

      Delete
  9. I followed this and when I ran a test, the file size is showing 0kb though the file is 30k in size and when I try to add more then 1 document to my sharepoint doc library, I don't see more then 1 file in the SPBlobStorage Folder

    ReplyDelete
    Replies
    1. What value did you use to set the MinimumBlobStorageSize?

      Delete
    2. I set it to 1mb and that did nothing, then I had to do an IISRESET and now I'm getting this error message:
      Sorry, something went wrong







      The URL 'Shared Documents/SharePoint 2013.pdf' is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.

      Delete
    3. So if you set it to 1MB then only files that are larger than 1MB will be sent to the blob storage. Not sure about your document issue there. Try uploading larger files to insure the RBS is working.

      Delete
    4. Also I'm trying to do a POC and my db server and my WFE are the same server, could that be an issue or no?

      Delete
    5. Yes it very well could be an issue since there are two different RBS.msi installs - one for the DB Server and one for all of the other SharePoint servers. The RBS.msi configuration won't be correct for one or the other.

      Delete
  10. I added this to the script
    $rbss.MinimumBlobStorageSize=1048576

    then I started getting this error as well:
    Sorry, something went wrong







    The URL 'Shared Documents/SharePoint 2013.pdf' is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.

    ReplyDelete
  11. I tried to upload a 10mb file and it didn't work. I'm going to work on it again today to see what happens

    ReplyDelete
  12. Hi Steve,
    Did you try to install it on a second database. I get some errors regarding that.For the first one everything is fine.

    ReplyDelete
    Replies
    1. Haven't tried on multiple databases. Our new stage environment has 5 content databases. We will try it out there soon.

      Delete
    2. But when i try for the second database the rbs tables are not creted in the database. The msi command I ran completed successfully. I was able to enable on multiple databases with the same command for sql 2008r2.

      Delete
    3. The same thing for me.
      Someone sad not need to reinstall the msi for the second DB content; it is true?

      Delete
    4. I still haven't implemented using multiple content databases yet. I would assume you need to install and enable each one.

      Delete
  13. Not sure what I got wrong, but I see the mssqlrbs tables; however, when I run the $rbss.Installed() I get false false true. I have installed the RBS on both WFE and the DB Server. The $rbss.Enabled() fails. I ran the first msiexec script on the DB server instead of my Primary WFE server. I also didn't use the default Content DB for the Web App. I used a Test IT Content DB that is in that Web App...don't really know where to start troubleshooting this one.

    ReplyDelete
  14. What is the error when the enabling fails?

    ReplyDelete
  15. Hi Steve, one question does i have.
    Why do you install the RBS-Binarys on the DB-Server. I think this is not needed for the function.

    I installed RBS only on the SharePoint server in my test enviroment, and it works.

    Thanks for your reply
    Kind Regards

    ReplyDelete
    Replies
    1. The Microsoft documentation states that it is needed and if you do not install the RBS on each server, there could be adverse affects.

      Delete
  16. Hi Steve,

    Followed your article thought it was the best written guide for setting up RBS out there.

    However I do have a problem. I've followed your guide, and although I had a small hiccup installing RBS on my SQL server I managed to get it all enabled.

    I have one SharePoint 2013 server and one SQL 2012 server. One content database (WSS_Content), with multiple site collections contained within.

    There are over a thousand or so documents on the SharePoint install, but only a few Mb in the RBS folder.

    Running a migrate command returns complaints that there are certain things not set and defaults are used. The command then sits for an hour or so outputting nothing to screen. The RBS "filestream.hdr" and "$FSLOG" both have updated modified times, but on a handful of Kb sized files appear in an already existing folder.

    ReplyDelete
    Replies
    1. The only thing I can think of is that your threshold is too high and your documents aren't large enough. I would lower the threshold and try to migrate again. However, it is recommended to keep smaller documents in the content database as it is faster for retrieval.

      Delete
    2. Originally the threshold was at zero. I changed this to 1Mb, and then 100Kb. There are plenty of documents in different site collections (again all on the same database) that are over this limit. In contrast the largest file in the Blob directory is only 1Mb.

      The files in the directory don't seem to update at all on uploading new files.
      And the Filestream is the active provider.

      Delete
  17. Hi Steve,
    Myself I've installed and configure RBS (correctly) in Sharepoint 2013 but doesn't work.
    It didn't put any file in File System (BLOB Storage). The rights seems to be ok.
    Any idea why?
    Other people seems to have the same issue:
    http://social.technet.microsoft.com/Forums/exchange/en-US/e279451f-fe02-4e9f-8e97-30290bdbdc35/rbs-with-sharepoint-2013-and-sql-2012-almost-working?prof=required
    Thanks
    Grig

    ReplyDelete
    Replies
    1. I usually lean towards the file size threshold and the actual document sizes.

      Delete
    2. I don't think it works in a single server install as there are two different installs of rbs - one for db server and one for all others

      Delete
  18. Hi,
    Thank you for your message.
    The size is 1048576 = 1M
    I've uploaded all kind of file size until 1.5G but it didn' go to BLOB folder. It went to content db.
    The WebApplication identity has rights to write also in the content DB also in this BLOB folder.
    $rbss = $cdb.RemoteBlobStorageSettings
    $rbss

    Enabled ActiveProviderName MinimumBlobStorageS UpgradedPersistedPr
    ize operties
    ------- ------------------ ------------------- -------------------
    True FilestreamProvid... 1048576 {}
    So everything seems to be ok but is not putting the file in BLOB store.
    It should work. I did it before. I don`t know why is not working this time.
    Thanks
    Grig

    ReplyDelete
  19. Hi Steve

    What version of SQL Server 2012 were you using? I have been told Microsoft requires Enterprise for RBS.

    Thanks
    Hope

    ReplyDelete
    Replies
    1. Yes! That is correct! You need Enterprise Edition.

      Delete
  20. Is there a query that can be run on the database side that confirms successful externalization of the files?

    ReplyDelete
    Replies
    1. I don't know off hand. If I see physical files on the drive then I know it is working.

      Delete
    2. I don't know off hand. If I see physical files on the drive then I know it is working.

      Delete