Rob Garrett – Blog

Following on from my previous post about list scaling and performance.  The following posts details configuration of Remote Blob Storage for SharePoint 2010 and SQL Server 2008 R2.

First download the RBS provider for SQL Server 2008 (don’t install it yet):

http://go.microsoft.com/fwlink/?LinkId=177388

Configure file stream for the SQL Server Service using the Configuration Manager:

Execute the following SQL queries:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

Execute the following SQL to set up a master encryption key and blob store file group:

use WSS_Content

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'

if not exists (select groupname from sysfilegroups where 

groupname=N'RBSFilestreamProvider')alter database WSS_Content
 add filegroup RBSFilestreamProvider contains filestream

alter database [WSS_Content] add file (name = RBSFilestreamFile, filename = 'c:\Blobstore') 

to filegroup RBSFilestreamProvider

Install the RBS provider with the following command (change DBINSTANCE to your SQL server instance):

msiexec /qn /lvx* rbs_install_log.txt /i RBS_X64.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="WSS_Content" DBINSTANCE="SP2010" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

If installing RBD on production servers, be sure to run on all WFE’s with the following command (again, change the DBINSTANCE):

msiexec /qn /lvx* rbs_install_log.txt /i RBS_X64.msi DBNAME="WSS_Content" DBINSTANCE="SP2010" ADDLOCAL=”Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer”

Run the following Power Shell script from the SP2010 Management Console:

$cdb = Get-SPContentDatabase –WebApplication http://sp2010

$rbss = $cdb.RemoteBlobStorageSettings

$rbss.Installed()

$rbss.Enable()

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

$rbss

Now create a document library in SharePoint and upload an image to it.  Next visit the c:\blobstore directory and look for the GUID sub folder with recent date.  Keep drilling down until you find a file.  You should see a file with GUID name.  Drop this into IE and you should see that it is the same file you uploaded to your document library.

From the SharePoint 2010 book I’m reviewing

It it a well known fact that MOSS 2007 caused some rising opinions on the subject of list scalability and performance.  Many developers operated under the misconception that SharePoint lists only allowed 2000 list items before croaking out with bad performance.  Nothing could be further from the truth.

The following article talks about this issue of large lists in great depth and highlights the point that SharePoint can actually handle many more than 2000 list items in any one list.  However, “query” this data is affected by the item count and SharePoint architects should design their data access and presentation of data accordingly.

http://technet.microsoft.com/en-us/library/cc262813.aspx

Microsoft has added a number of new enhancements to lists in SharePoint 2010 to handle larger capacity and the query of this data, and the following is a short summary of the enhancements:

List Column Indexing

SP2010 now allows list designers to create up to 20 indices (some of multiple columns) on any one list.  These indices allow for faster query of data when the list size exceeds that of typical.

Under list settings and in the columns section; users now see a link to Indexed Columns.

The following is a list of column types usable as indexed columns:

· Single line of text

· Choice field, but not multi choice

· Number

· Currency

· Date/Time

· Look up, but not a multi value look up

· Person or group, but not multi value

· Title, except in a document library

List Throttling

SharePoint administrators now have the capability to better control list queries so that developers (or general users) may issue list queries on large lists that may potentially bring down the server.  Specifically:

Administrators may define some limits at the web application level:

- Configure the number of items fetched for queries

- Administrators may receive warnings when thresholds exceeded

- Ability to configure time periods for expensive queries to operate

- Limit the size of list items (default to 8k)

- Limit the number of columns in a join (default to 6)

The following code will display the list throttling limits for the site collection:

using (SPSite site = new SPSite(siteUrl))
            {
                Console.WriteLine("MaxItemsPerThrottledOperation:{0}",
                    site.WebApplication.MaxItemsPerThrottledOperation);
                Console.WriteLine("MaxItemsPerThrottledOperationOverride:{0}",
                    site.WebApplication.MaxItemsPerThrottledOperationOverride);
                Console.WriteLine("MaxItemsPerThrottledOperationWarningLevel:{0}",
                    site.WebApplication.MaxItemsPerThrottledOperationWarningLevel);
            }

To enable list throttling on any list be sure to toggle the setting with the following:

SPList.EnableThrottling = true

MaxItemsPerThrottledOperationWarningLevel – If a list exceeds the number of items specified in this threshold then a warning is displayed on the list settings page.

What MaxItemsPerThrottledOperation – This indicates the number of list items returned to non-administrators.  Administrators can query up to the threshold in What MaxItemsPerThrottledOperationOverride but will receive a warning on the list settings page.

If administrators wish for users to execute expensive operations in specific window of time they can do so by using the following method on the WebApplication object: SetDailyUnthrottledPrivilegedOperationWindow

RBS Storage (Remote Blob Storage)

In some cases the use of document libraries to store large files is no longer scalable and causes content databases to become unmanageable.  An example situations where a public web site, hosted in SharePoint, provides users with rich media content – web files and large images – is once such example of the large blob storage issue. 

In MOSS, hosting content in the database provided certain benefits, such as single storage location, versioning, and access of files via the object model.  Whereas file based storage provided better scalability at the cost of orphaned content from the object model.  SP2010 solves this issue with RBS.  Site Architects can now store large files (blobs) in alternate locations to that of the SharePoint content database without relinquishing access via the OM.  From an and developer standpoint, the data is accessed as if it were in the content database, but the content is actually in a remote location.

To enable RBS you’re farm will need to use at least SQL Server 2008 R2.

Marking blobs as external at the content database level enables SharePoint to store the meta-data associated with blobs in the database while storing the actual blob content outside the content database.  Because RBS is handled at the database level, SharePoint is unaware that data is not stored in the content database but in another location.

in a future time, vendors will bring RBS providers for SP2010 to the table, but in the meantime Microsoft has provided RBS for SQL server as an extra download:

http://go.microsoft.com/fwlink/?LinkId=177388

See my next blog post on configuring RBS.

From the SharePoint 2010 book I’m reviewing