Showing posts with label RBS. Show all posts
Showing posts with label RBS. Show all posts

Thursday 9 January 2014

SP 2013 SSRS failing after RBS enabled and disabled

Problem: I have SSRS (SharePoint mode) enabled on my SharePoint 2013 farm using SQL 2012 which was working, I enabled AvePoint's RBS provider on the farm and enabled RBS on 1 out of 2 web applications.  I then disabled RBS on the web applications and assumed all was good, RBS stopped working and threw 1 of 2 errors on the RBS enabled then disabled web application:
"For more information about this error navigate to the report server on the local server machine, or enable remote errors" or ....

Note: Rdl in the system before RBS was enabled work, during RBS don't work and rdl's added after disabling RBS both fail

Initial Hypothesis/Error tracking:
1.> SSRS and WCA errors unfortunately don't get correlationId's, so I turned off all the SSRS SSA instances except 1 so I know which server to find the error on in ULS log. 
2.> I ran the request for the report (rdl, this report just displays a label so I know it is good) again so the error is captured in the ULS log.
3.> I painfully open the latest log using ULS viewer and scan for errors and I find: 
System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'rbs_fn_get_blob_reference', database 'SP_Content_PaulXX', schema 'mssqlrbs'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

4.> Now I am going nuts as RBS has been disabled and I decide to trace the request in SQL Profiler, I can't find the call in SQL Server profiler and while looking for it I realise the function is not in the database.  I also find a post suggesting changing permissions but as I don't have the function, permissions isn't my issue. 
5.> I start looking at RBS on the farm using PowerShell PS>
cls
$cdbs=Get-SPContentDatabase
foreach ($cdb in $cdbs)
{
 $rbs=$cdb.RemoteBlobStorageSettings
 Write-Host "Content DB:" $cdb.Name
 Write-Host "Enabled:" $rbs.Enabled

}
I notice that the content database  'SP_Content_PaulXX' mentioned in the ULS log has the RemoteBlobStorageEnabled flag set to true.

Resolution:

*************************************************
Problem: I have SP2013 + SP2012, I am using SSRS in SP mode.  My app pool accounts for my web app and my SSRS SSA are different and on separate servers.  So for this to occur, you need SP2013, SSRS, RBS Enabled (or the Content database still thinks RBS is enabled), additionally the service account used by the SSRS SSA needs to have minimal permissions.  Existing rdl files display correctly however any rdl files added throw an exception.  The diagram below further explains the scenario:
Initial Hypothesis: Trawling through the ULS logs show the error: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'rbs_fn_get_blob_reference', database...

A snippet of the ULS is shown below:

 
Resolution:  The app pool account used by the SSRS Service Application needs to have permissions to run the function. 
1.> Figure out the app pool account used by the SSRS SSA if you don't know it as shown below:

2.> Give the SP_Services account permissions over the erroring execution calls.  To prove it give the account dbo rights.

 
Thanks to Sam Keytel and Mark Oburoh for looking at this with me.
 

More Info:
 
 


Thursday 22 August 2013

SharePoint BLOB storage for Dummies

Overview: BLOB Storage in SP2007 SP used EBS, SP2010 provided Remote Blob Storage (RBS) capability and SP2010 was backward compatible with EBS, SP2013 does not support EBS.  A common problem with using RBS on SharePoint is orphaned blobs (we need to keep blobs that are outdated by version no or deleted in case you need to do a restore).  This can lead to orphaned blobs – and you need to periodically go and clean-up the blobs.   RBS is enabled at a content database level whereas EBS is per farm. 3rd party RBS providers only need to be installed and configured on the WFE’s not the app servers.

FileStream (local) RBS:  Microsoft SQL server provides Filestream technology which effectively allows SQL to move blobs based on size to remote storage (so no longer stored in the SQL database).  You need to manage backup and recovery yourself and this tends to lead to unused blobs and orphaned blobs.  It also adds overhead to SQL's resources.  The diagram below is how I see Filestream working, the entire SP record is pushed to SQL, SQL adds the stub to the Content database and the blob is moved onto a network file share.



Why Remote RBS?
Microsoft does not have any Remote/externalised blob providers.  All data is stored in your SQL relational databases and not efficient at storing large BLOBs.

With large BLOBs in your content databases:
  • the size becomes harder to manage (think backup and recovery times);
  • database I/O is much more intensive when storing BLOBs; and
  • databases use expensive storage.
Enter the 3rd Party RBS providers : DocAve (AvePoint) has a nice BLOB provider, it can work in real time or retrospectively, backups are cleanly handles and the retention policy is great at cleaning up the old blobs.  This diagram shows how AvePoint provider stores the blob, instead of letting SQL handle the entire record storage, the provider at a SharePoint level, 1st will add the blob to external storage, the location/stub info is sent back to the SP server.  SP then sends the record to SQL including the stub location.  SQL does a lot less work.

Other vendors include: Metalogix, Stealth Software, StorageEdge from (Alachisoft), SimpleStor

Note: RBS does not change the Content Database size restrictions, the BLOBs are still added to the content DB size calc. The 200GB/4TB limit still use the external BLOB size as the records meta data reports the real/external file size, not the 1 kb stub size.

The thinking behind the 200GB/4TB content DB limit is chosen/used because of database density and not database size, i.e. the average number of objects (docs, items, lists etc…) at 200GB/4TB is approaching a point where indexes and query performance suffer. So externalising does not mean that the density is dropped as the stub is still an object that’s in the DB that’s indexed.  However, using blobs suggest there on average are fewer objects compared to another farm that would have smaller average sized objects within it's content db.  My head hurts just writing this.

The advantage of RBS on your farm is that fragmentation is reduced, the cost of storage is cheaper and performance/stability at larger concurrency is improved. Additionally, DR times are also greatly improved.

High Availability: Blobs are not HA, if the blob store/fileshare goes down you loose the blobs.  DFS can be used to replicate the blobs.  Note: Your blob storage pointers in the SQL Content databases will point to the DFS share.  The image below shows you how to setup a DFS share.  Tip: Use domain based DFS.


Windows Distribute File Share (DFS) provides a logical share for adding files/blobs.  The blobs/files are replicate to all physical disks.  If 1 goes down you don't notice and service continues.


More Info:
http://www.metalogix.com/Libraries/Product_Collateral/Using_Windows_DFS_with_SharePoint_Remoted_BLOBs.pdf
http://technet.microsoft.com/en-us/library/cc771058.aspx

Saturday 28 May 2011

Scanning, Storage & RBS

Problem:  The client has millions of physical documents, that need to be available via SharePoint, additionally documentation still arrives in physical form and needs to be scanned and classified.

Initial Hypothesis: SP2010 can store documents in the SQL database in blob format however, it's not really made for large blob storage performance wise, additionally SQL storage is expensive (RAID, HA). Remote Blob Storage (RBS) helps with storing blobs but does not get around limitations imposed by MS guidanace.  RBS can reduce storage and improve performace if you data storage involves a lot of large blobs (over 256kb  is a good size).  My rough sums show a huge data requirement so for example 600,000 customers transact with the client.  On average each customer has 3 physical documents a year.  So we are talking 1,8 million scanned documents a year.

Documents need to be scanned in at 300 dpi so they can be printed and stored adequately.  With compression and converting these files into tiff/pdf files we are assuming an average of 1 MB per file. So our storage requirement per year would require 1.8 million scanned documents at 1MB per file meaning my storage on 1,800GB

As we have a restriction of 200GB per content database in SP2010 (threshold that MS will support up to).  So we would require 9 new site collections on a new content db per year to meet this requirement. 

Tip: Also worth considering are thresholds and bounderies provide by the SharePoint team.  Site collections max size is 100GB, this scenario has a caviet in that a single Site Collection using a single document library/site supports up to 1TB in the Content Database.  You can have subsites nested in a site collection but 2000 per view is recommended.  Max of 300 content db's per Web Applicaion.  Max 5000 site collections per content database.

Our storage cost is much higher as our disks are RAID so at a minimum we would use 3 times this in actual physical disk space.  On top of this my indexes will be about 25% of the storage requirement.  So price, performance are getting out of control pretty quickly.

Resolution: Using RBS my estimate on these blobs is will will reduce the content database by 90% however content database size is calcualte including RBS so our storage requirement will be cheaper using RBS that is resilient however the content database sizing will not be reduced by using RBS. 

Updated: 21/07/2011 - RBS sizing Calc

Scanning tips for SP:
  • Tiff or pdf are the common base storage file type;
  • 300dpi is good print quality most requirements can be lower;
  • Black and white is far smaller then grey scale scanning.
  • Pdf's if stored correctly can be indexed by the search crawler.
More Info Scanning:
http://www.psigen.com/ - scanning and capture for SP2010.
Capturx from www.adapx.com/sharepoint is a pen that automates data capture on forms.
CoSign does digital signatures and looks to have pretty decent integration with http://www.arx.com/digital-signature/sharepoint
www.kodak.com/go/sharepoint
http://www.goscan.com/connectors-sharepoint.php
http://www.kofax.com/solutions/microsoft.asp

More Info Sizing:
HP Sizer for SP2010
Capacity management for SP2010 - Sw boundries

Thursday 5 May 2011

File Upload Size Limits

Problem: Need to store files in excess of 2GB in SharePoint.
Initial Hypothesis:
  • 50MB is the default upload limit set by SharePoint OOTB.  You can change this on the farm as described here by Dave Coleman up to 2GB or 2047MB. 
  • A common misconception is that by using RBS and not you content database to store the blob you can overcome this 2GB limit.  We it's partly true...  The maximum file size for a file in SQL Server is 2GB however, the next restraint is SharePoint 2010 Server Object Model and this has a hard limit of 2GB for an upload so moving to RBS won't overcome the problem.
  • I believe SharePoint limit's the upload to 2GB due to IIS's worker process w3wp.exe, to upload a file you need to use all the IIS available memory to upload the full stream.  Each w3wp.exe worker process runs well with 2-4GB of memory, this is not a boundary just a good idea (on x64), therefore this makes sense to me that the SP2010 team have limited any file upload to 2GB.
  • Also be aware that increasing you upload file size to 2 GB has performance ramifications so it a user uploads a file and there is no memory available no new requests can be handled until the memory is available again.
Resolution:  Store large files outside SharePoint and surface them in SharePoint.  I believe there is another solution available using a Telerik Silverlight upload control but I haven't tried it.
More Info:
http://blah.winsmarts.com/2010-3-Large_File_Upload_in_SharePoint_2010.aspx

Update 24/07/2013: SharePoint 2013 has the same hard limit of 2 GB for the maximum upload size.  Technet states 50 MB is the default limit for SP2013, the default from an OOTB install is 250MB which is the same value you get with SharePoint Online/Office 365.

Friday 7 January 2011

RBS Primer

Problem: Provide a quick overview of Remote Blob Storage (RBS).

Overview:  When uploading files into a document library or list, the data is stored in the content database, this consists of meta data and the binary blob.  All content database storage is done using SQL Server, the recommended maximum threshold for content databases is 200GB (they can be bigger depending on you IO speed on your SQL Server - this applies to SP2010 (pre SP1), MOSS had a recommended 100GB content db limit from MS) these binaries can make your content database big that results in expensive storage as the disks are usual RAID 10, backup operations can take a long time and speed of data access/write can slow down.
You can configure RBS on a per content database on your farm using 'storage providers'.  There are 3rd party suppliers that provide the storage and the 'storage provider' and they make claims of saving clients up to 90% of their content database size.  You can also store the blobs on files systems.  RBS is useful for medium and large farms, it can be applied retrospectively be setting up RBS on a content db and performing a backup and restore.  EBS was performed at a farm level whereas RBS is content database level.
RBS is only a good candidate if your content database has blobs, the blobs are individually bigger than 256KB and you content database is over 100GB.  RBS is more useful on really big content db's.  Content database size is bizaarly calculated by including the content db size plus the RBS storage.
Tip: RBS is similar to EBD in MOSS except it's applied at a content db level.
Tip: RBS requires SQL Server 2008 R2 (I'm not sure if SQL 2008 will work).  FILESTREAM needs to be activated on SLQ Server for RBS to work.
Tip: SP 2010 has a hard limit of 2GB per item, this is due to SQL server using the varbinary (max) column type for storage and IIS recommended max app pool sizing.  Changing to RBS will still enforce a maximium size of 2GB per file.
Tip: RBS is setup on the farm using PowerShell there is no CA UI interface.

What RBS gives you:
  1. Performant SharePoint farm - the most common bottle neck is SQL Server in SP2010 farms, by reducing the blobs being stored and retrieved within the content database you get better performance on your farm.
  2. Lower cost of disk storage - SQL is normally stores data on your SAN, these disks are expensive and usually RAID10.  Additionally, the backup/HA/mirroring/clustering will also be expensive disk space.  Using RBS moves all your storage to cheaper disk storage.  Furthermore, you normally use an external RBS supplier so if you RBS storage is growing you only pay for the current storage unlike in SQL you would need to have the space to provide for future growth.
  3. Faster DR - Your farm backup and recovery process will generally be faster but more complex.  As the content db's are smaller you can backup and recover quicker however you now have to ensure RBS data is part of your DR planning.
  4. Dead link data - you need to run RBS tools to delete data from RBS that has already been removed by SP2010.
Source: Srini Acharya & Burzin Patel - Externalising BLOB storage in SP2010 slide deck.  This slide shows the process of saving a document using RBS and SP2010.

Providers of RBS:
  • AvePoint
  • OpenText
  • EMC (EBS based I believe)
  • StoragePoint (Metalogix) EBS or RBS based.
More Info:
http://technet.microsoft.com/en-us/library/ff628254.aspx