Azure & Office 365

Posts Tagged ‘sql’

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

I assume you know if we want to install SharePoint in a Workgroup environment, the only recommended Microsoft solution is to install SharePoint standalone server. Here SQL server embedded edition is installed with SharePoint server and SharePoint databases are created on this Embedded SQL server.

 

Normally we don’t perform any task on these SQL databases but incase if you want to access sharepoint databases for backup, troubleshoot, etc, you need to install SQL server management studio to achieve the same.

 

1. Install SQL server Management studio. You can download the management studio from below link:
   http://www.microsoft.com/en-in/download/confirmation.aspx?id=29062
   

2. Once installation is completed, open SQL Studio management studio
   

Once We have management studio installed, you can either use below registry value or servername\sharepoint to access database. Make sure you use SharePoint farm account when you connect to SQL server.

1. Go to below registry path:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SHAREPOINT\MSSQLServer\SuperSocketNetLib\Np

2. Use the value in key “PipeName” to connect to SharePoint database server. As discussed above you can also use servername\sharepoint to connect to SQL instance

Note: You need to install SQL server management studio on SharePoint server. You cannot remote connect to this SQL instance. Do not manually modify any SharePoint database, if modified, your farm\database would be unsupported

 

Applies to all version of SharePoint servers

• Install SQL, SharePoint. etc. labs as part of this domain

Once we are confirmed using script we are able to add new Virtual Machine to domain, we can use the ready to use SharePoint, SQL, etc. VM’s as part of our Lab environment. Let’s see an example of SharePoint server.

1. Login to your Azure Subscription Portal site https://manage.windowsazure.com
2. Select “Virtual Machines” from left menu and select “Create a Virtual Machine”

3. Under “Virtual Machine” select “From Gallery”

4. On the Choose an Image screen select SharePoint server 2013 Trial. This trial works for 180 days.

5. Type the Machines necessary details like Hardware configuration, Machine name, username and password
6. Now we have an important step in the process. Select the DEMONETWORK you created in Region/Affinity Group/Virtual Network. Make sure our Virtual network name is selected in Virtual Network Subnet. Click on Next

7. Now comes the most important step, we need to select Custom Script on this page and select the Join.ps1 script we just created. Click Finish

8. Now you can see that even SharePoint server is ready within few minutes
9. Advantage here is that you have a single credential stored on Active directory using which you manage all the Virtual Machine and work on your lab.
10. Below you can see that I have created SQL server, SharePoint server and started using them in just couple of minutes.

Issue :

When you logon to some server using Domain account, you cannot access the pre-installed software like SQL server.

 

Solution:

When the VM is created, it is created account you specify using Azure VM creation screen and then added to domain. So by default other account don’t have access to the application installed during VM creation. You have to 1^st Logon to the server using Azure VM account give appropriate permission to domain account and then use it.