Tuesday, October 23, 2012

Configuring and Implementing Sharepoint 2010 RBS (Remote Blob Storage) with SQL Server 2012 - Part 1

Good day friends, Howdy?

It's been a while since I did some *real* posting. Time to do one..Sorry, this post is not going to be 100% AX focussed, however towards the end, I will share some thoughts/ideas how AX can leverage if RBS is already part of your infrastructure. I will try to cover RBS Implementation in 2 or 3 posts.

You might be wondering what the heck is RBS? Even I was in the same state, a while ago :). Then, started my journey Implementing SQL RBS (Remote Blob Storage) for Sharepoint 2007/2010 in my current company.

Previously, I had setup and configured RBS successfully with SQL Server 2008 R2, now recently we moved all our servers to cloud and we thought it was the right time to upgrade to SQL Server 2012.

So, I had to do the exercise once more, this time with SQL Server 2012. So, Let me give you a quick introduction to RBS. Towards the end, I will share some few references and links where you can get more details about RBS.


Introduction:

In simple terms, RBS (Remote Blob Storage) is used for externalising your storage to a file system instead of bulking your database thereby providing benefits such as:
a. Optimized database storage
b. Performance
c. Distinction between structured and unstructed data

RBS is designed to move the storage of binary large objects (BLOBs) from database servers to commodity storage solutions

Content-addressable storage, also referred to as associative storage or abbreviated CAS, is a mechanism for storing information that can be retrieved based on its content, not its storage location. It is typically used for high-speed storage and retrieval of fixed content

Scenario

Consider the scenario of a custom application that is designed to provide training content to users.  The application will contains less than 1000 documents but they are all training videos that average 200MB in size.  The database for this application would be nearly 200GB in size even though there are relatively few documents.  By enabling RBS, the binary data for these videos can be kept out of the database while the structured metadata in the database remains responsive and easy to manage


When Storage Tiers Need to be Implemented

One of the most significant benefits of RBS lies in extensibility.  RBS doesn’t just have to be for getting BLOB data out of the database.  It can serve other creative purposes such as facilitating highly efficient storage tiers.

Scenario

A document management solution has been or will be deployed for an organization.  A large percentage of the corporate user base will be adding and editing collaborative content on a daily basis.  Over time, a very large number of customer centric documents are created, possibly declared as records for retention, and then archived. 


How RBS Helps

In this scenario, an RBS provider that enables a tiered storage platform could provide tremendous cost savings by intelligently managing the storage location of BLOB data. 

New and frequently accessed content could be stored in high performance storage. 

Older documents that are accessed only occasionally could be automatically moved to lower cost and lower performance storage such as SATA arrays for example. 

Then over time, very old documents that are rarely accessed could be automatically moved again to extremely inexpensive cloud based storage. 

In all cases, end users are able to access content in real time but the responsiveness and cost of the storage is intelligently managed.

When Storage Needs to be Optimized

When BLOB data is allowed to inflate a SQL database, file I/O and processing load is increased on the database server.  If the average size of BLOB data is 80KB or higher, then implementing RBS reduces I/O and processing load which improves the performance of SQL Server


So, Let's jump into some action. I will walkthrough the steps involved for provisioning your blob store.

A. Enable FileStream on the database server

i. Launch SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 2012 > > Configuration Tools > SQL Server Configuration Manager

 
 
ii. Right-click your SQL instance (running MOSS) and then go to Properties, Click FileStream tab
 



 

and click the options below Enable filestream for SQL and I/0 Access and allo remote access to filestream data.
 
 
Enable file stream settings at database level
 
 
B. Provision blob store for each content database in Sharepoint
 
i. Open SQL Server Management Studio and expand your content database for which you want the blob store to be provisioned for and type in the below queries in sequence:
 
Paste the following SQL queries in Query pane, and then execute them in the sequence listed. In each case, replace [WSS_Content_Learn] with the content database name, and replace I:\BlobStore_Learn with the volume\directory in which you want the BLOB store created. The provisioning process creates a folder in the location that you specify. Be aware that you can provision a BLOB store only one time. If you attempt to provision the same BLOB store multiple times, you will receive an error


 

 
 
Once the queries have been executed, you should be able to see a folder $FSLog and filestream.hdr
 
C. Install RBS Client Library on the Sharepoint Web Server
 
Below are the install steps:
 
 
 
 
 
 
 
 
Once the installation is completed, then Run the below command with elevated permissions, if required.
 
Note: This works only for the first time i.e. Setting up RBS for your first content database, To enable RBS on a second content database, there's a separate command which I will discuss later in my post.
 
msiexec /qn /lvx* rbs_install_log.txt /i "RBS" TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="WSS_Content" DBINSTANCE="DBInstanceName" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1
 
 
How to check if the RBS client library installed successfully:

1. The rbs_install_log.txt log file is created in the same location as the RBS.msi file. Open the rbs_install_log.txt log file by using a text editor and scroll toward the bottom of the file. Within the last 20 lines of the end of the file, an entry should read as follows: Product: SQL Remote Blob
Storage – Installation completed successfully.
 
2.
On the computer that is running SQL Server 2012, verify that the RBS tables were created in the content database. Several tables should be listed under the content database that have names that are preceded by the letters "mssqlrbs"

 


 

 
 
Now, to check RBS in action, You can go your sharepoint site, and upload some docs (say which is > 100 k) and you can see binary files getting created in your blob storage, also you can check the folder size and properties of the folder
 
I will soon come up with Part 2, wherein I will show the steps for enabling a second content database. I had to go through some troubleshooting before I successfully enabled RBS on my second content database.
 

Thursday, October 11, 2012

Inside Microsoft Dynamics AX 2012 Book released!

Dear Friends

Thought to share with you the long awaited book (Inside Dynamics AX 2012 ) is publicly available now to be ordered

Read more about the release in MS Press Blog below...

http://blogs.msdn.com/b/microsoft_press/archive/2012/10/10/rtm-d-today-inside-microsoft-dynamics-ax-2012.aspx

Sunday, October 7, 2012

Dynamics AX Manuals - A good website with videos on AX functionalities

Hi there,

Thought to share with you this website which I stumbled upon as I was tweeting... Looks to me this can go to my favorites as it has some good AX videos..


http://dynamicsaxmanuals.com/

Dynamics AX 2012 - One of the biggest Implementation in Australia with 10,000 user base

Dear All,

I recently heard this news from one of my ex-colleague from Microsoft that this is got to be one of the biggest deal for Dynamics AX in Australia. Read below..

Hastings Deering, a part of the Malaysian Sime Darby Group, is deploying the Microsoft Dynamics AX 2012 solution to support business growth. Microsoft's consulting services division will roll out the system in phases over the next 28 months.

The enteprise resource planning (ERP) system will support 10,000 users when fully implemented. Xapt Corporation, a specialist Dynamics partner, will provide Caterpillar-specific Dynamics modules and heavy equipment domain knowledge

Below is the complete link..

http://www.enterpriseinnovation.net/content/hastings-deering-drives-growth-dynamics-ax


Good to be here and am excited to see new implementations for Dynamics AX gathering momentum in Australia market. Happy Daxing :)

Saturday, October 6, 2012

SSRS Tips and Tricks - Conditionally Summing Group Totals

Dear All,

Hope you are doing good. It's been a while since I have written some posts. I have lots of things to write about, but just not able to manage my time. Have been busy working on different areas:
a. Installing and configuring Sharepoint 2010 Setup with SQL Server 2012
b. Configuring SSRS 2012 Sharepoint Integrated Mode
c. Dynamics AX and Faxing
d. Configuring Remote Blob Storage in SQL Server 2012

I will be soon covering these areas in different posts as I have learned quite interesting things in my journey.

However, thought to start with a small tip on one of the powerful functions in SSRS Reporting. E.g. My requirement was to sum a group total conditionally.

So, we can use a conditional iif() within a sum() within an expression as shown below:

=Sum(iif(Fields!Status.value = "ValueA", Cdbl(Fields!Payment.Value), 0.0))