Azure & Office 365

Posts Tagged ‘Buster’

Hi All,

 

In this article I would try to share my experience about SharePoint on SQL RBS. This article may help you decide if you should use SQL RBS with SharePoint or any other product.

 

This is my personal opinion about SQL RBS with SharePoint. I would suggest you to cross check below furnished details before taking final decision. Microsoft keeps enhancing its product so some of these may be fixed\resolved\changed.

 

Let’s first discuss what is SQL RBS? Normally when you upload a document, image, video, audio, etc on your SQL server it is stored in MDF (Master database file) of your SQL content database. As per my past experience when you upload anything to a content management system, you would revisit only 20 % of data uploaded, rest 80 % of data is never visited but used for record only purpose. IT pro’s suggest that you should keep your SQL content database on RAID 10 hard disk or SAN which is pretty expensive.

 

To resolve this issue Microsoft came out with SQL RBS which allows you to store your Files\BLOB outside (File system) of SQL database. This means you can use a Raid 0 drive to store BLOB files (image, video, document, etc). RBS is intended to lower storage costs by allowing you to store large read-intensive BLOBs on less expensive drives.

 

1st Myth of SQL RBS: Additional Storage for Content database.

This is the biggest myth about SQL RBS. Microsoft Suggest that your content database should be below 200 GB. Now days when we have a USB stick with 100 GB, a Content management server restricted to 200 GB is not a good deal. To overcome this issue few IT admin plan to use SQL RBS because RBS stores files outside of SQL server hence decrease the size of content database.

Reality: RBS does not increase the storage limits of content databases. All limitations still apply to RBS-enabled content databases. If you are using Remote BLOB Storage (RBS), the total volume of remote BLOB storage and metadata in the content database must not exceed the 200GB limit.

 

2nd Myth of SQL RBS: Files stored on FILESYSTEM through RBS cannot be accessed directly.

IT Pros and lot of other articles says that we cannot open the file directly from BLOB FILESYSTEM. We have to go through SQL database in order to read these files.

Reality: I was able to access FILESYSTEM where the BLOB files were stored and was able to open my txt, bmp, jpg, etc files. SharePoint is known for its Item level Security. So far SharePoint security was never compromised and access is only available on need to know basis. If any users who has access to FILESYSTEM can open any file stored in SharePoint without having access on SharePoint is a security issue. Also encryption is not supported on BLOBs, even if Transparent Data Encryption is enabled.

 

3rd Myth of SQL RBS: Better performance I hear a lot of IT PRO who comment that they would get better performance if FILES\BLOBS are stores outside of SQL Server.

Reality: I would accept this when we are using SharePoint to store Hugh amount of data files (70-80 % of BLOB). When I asked these IT Pros how much data is stored on dbo.docs, dbo.AllDocStreams, dbo.DocStreams, dbo.AllDocVersions, dbo.alldocs, etc table, they are not aware of same or they don’t bother. When you offload 70-80 % of data from your MDF file and store it FILESYSTEM you may feel better performance. If our content database stores BLOB files not more then 30-35 % then it would not make sense to go with RBS. Let me explain you why I say that, when you configure RBS you create number of additional tables on your content database. This means when we upload BLOB to SharePoint it will execute additional query to store data. Here SQL will first import image files, and then it may split the file based on size of file and then store it to FILESYSTEM. Again this process is reversed when we try to open or query the BLOB file. This will increase disk IO, RAM and processing power. Now if consumption of my resources is increased, how can I expect better performance? RBS does not support using data compression but data is compressed when uploaded to SQL MDF file. Microsoft says “Although using RBS with files larger than 1 MB can improve I/O and processor performance, using RBS with files smaller than 256 KB might decrease overall performance. Storing the BLOBs inline in the content database is more efficient with smaller files”

 

4th Myth of SQL RBS: Ease in management

A lot of IT Pro’s also comment that it is easy to manage smaller SQL databases, Better Technology, Ease in configuration, etc.

Reality: Why you want to take an additional Load of configuration anything extra on SQL. If any feature is available does not mean you should use it. Implementation of these features depends on its pros\cons and specific requirement of the feature. Also when you backup the database, it will backup all the files from FILESYSTEM along with SQL MDF\LDF files. Sizes of these databases backup does not decreases but it increase because RBS does not perform any compression. If you plan High availability through Mirroring or Log shipping, you need to follow additional steps to configure the same.

That’s it from my side. Feel free to comment or connect with me if you feel any of the above information is incorrect.

Reference:

https://technet.microsoft.com/en-us/library/cc262787.aspx

https://technet.microsoft.com/en-us/library/ff628583.aspx