Azure & Office 365

Posts Tagged ‘2012’

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

Hello Everyone,

I was searching a product which has documentation storage & editing like share-point
I know SharePoint do lot of stuff other than document sharing “Own-cloud” is good alternative  for online documents editing as  well as dropbox,sugarsync,google drive,
cloud storage for in-house infra.

Features
* it has an agent for windows,mac & linux
* you can store your data on your local drive & then sync with owncloud server
* File Version no a days most IMP things that system admin need
*  it has agent for all leading mobiles OS
* Open office give you power to edit online .odt files
* & most IMP it’s FREE

Lets setup the Owncloud

I already installed AD in my Virtual Environment
AD details:
Domain : SVM.test.local
group : ocusers
users : oc1, oc2, oc3

above users added in ocusers group

Create one user with delegate control
i have created “owncloudaduser”

My owncloud server is Ubuntu server
owncloud installation instruction find on ownclud site

http://software.opensuse.org/download/package?project=isv:ownCloud:community&package=owncloud

after installation you can access owncloud

owncloud URL : http://IP/owncloud/

when your log in owncloud ask you to create admin account

 

Click on Admin option

SVM.Test.local
CN=owncloudaduser,CN=Users,DC=Test,DC=local
Password
DC=Test,DC=local
Manually enter LDAP filters (recommended for large directories)

(objectClass=*)

(sAMAccountName=%uid)

on left hand site there two options

Click on Advance

600

 

 

 

Hi Friends,

Today we will try to understand how to configure RBS(Remote Blob Service) on single or multiple content databases in SharePoint 2013 with SQL server 2012 or 2014. Steps are nearly same with both SQL server but make sure you use the correct msi file. For SQL serve 2012 size of msi file would be 5120 kb which can be downloaded from here .  For SQL serve 2014 size of msi file would be 4380 kb which can be downloaded from SQL server 2014 feature pack here 

 

Once you have downloaded the file save it on empty folder on c: or d: drive

1. Once database is created from SharePoint server, execute below command on SQL server

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

1. Then we need to select the content database from top and execute below command(password can be changed)

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′

1. Now you need to execute below command on content database(wss_content5 database name needs to be changed)

if not exists (select groupname from sysfilegroups where groupname=N’RBSFilestreamProvider’)alter database [WSS_Content5] add filegroup RBSFilestreamProvider contains filestream

 

1. Now we will add the physical path of RBS datafiles to content database. You can change the database name and filepath

alter database [WSS_Content5] add file (name = RBSFilestreamFile, filename = ‘C:\Blobstore5’) to filegroup RBSFilestreamProvider

 

 

1. Now you need to run below command on Windows Powershell (run as admin). Go to folder which has the rbs.msi file we downloaded.(Make note of FilestreamProvider name. Change SQL servaer and content databasename)

msiexec /qn /lvx* rbs_install_log_db5.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME=”WSS_Content5″ DBINSTANCE=”SQLservername” FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider

If you see the rbs_install_log_db5.txt file to be less then 1000 kb, e.g. 482 kb, it means it didn’t work. If this has worked fine you will see above 1000 kb e.g. 1218 kb.

You can also validate that rbs related tables are added to database as shown below.

Note: if you face issues while running command, try to reboot both SQL and SharePoint server. I know this is not a solution but a workaround to the known issue you might get. You can also try SQL service restart and iisreset on sharepoint server. You may also check eventviewer log to find symptoms of the issue.

6. On SharePoint server run below command on windows powershell:
msiexec /qn /lvx* rbs_install_log.txt /x RBS.msi DBNAME=”WSS_Content5″ DBINSTANCE=”SQLservername” ADDLOCAL=”Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer”

Note: if you face issues while running command, try to reboot both SQL and SharePoint server. I know this is not a solution but a workaround to the known issue you might get. You can also try SQL service restart and iisreset on sharepoint server. You may also check eventviewer log to find symptoms of the issue.

 

1. Now run below command 1 by 1 in SharePoint powershell and update the active providername and enable the RBS on content DB.

$cdb = Get-SPContentDatabase –WebApplication http://testspimgweb01
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])

To migrate existing content (Make sure you run this even if you don’t have any existing data.)
$rbss.Migrate()

Note:  Also every database in web application needs RBS to be enabled or above command will fail.

1. Now if you create site collection and upload a file, you will observe it is moved to this folder you created with SQL command in step 4.

 

Note: You need to run these command for every content database you want enable RBS on.

 

Troubleshooting:  When you run these command, you might face permission issues. To resolve these issue make sure you have full access on C:\Program Files\Common Files\microsoft shared and registry. When you run these command, you may not find the error in the log file so run the rbs.msi file and make sure you find all the permission issue in GUI. Many a time when you assign permission on root folder on drive or key in registry, it will not be inherit to subfolder. Make sure you go to the folder shown in error and assign owner permission.

 

Hope this was helpful.