Tuesday, December 3, 2013

SQL Server and peripheral CMS storage

The Microsoft stack now permits the management and security of NTFS data as if it lived in SQL Server. This approach has a number of business advantages.

I remember getting very excited about the Service Broker feature when SQL Server 2005 was released. That feature just bristled with possibilities, as far as I was concerned. It seems that every release of SQL offers something exciting that most shops just don't tap into.
The latest on that list is Remote Blob Storage - RBS - which combines SharePoint administration and FILESTREAM to make possible NFTS storage within a content management system. When I first got ahold of this, I just went nuts! But I find I have to do a hard sell, every time I participate in a SharePoint deployment or upgrade.

SQL database quality at NTFS prices

RBS in a nutshell: SharePoint can now be configured to store large files of exotic format within the CMS (logically) by actually storing them in the conventional file system (physically), without telling the user. The sleight-of-hand makes it possible for all kinds of very non-standard content types to live within SharePoint, making that content available alongside more conventional content, even though it isn't idea for actual storage within SQL.
What are the reasons you'd want to do this? From the bottom-line perspective, it's just flat-out cheaper. SQL database storage is, byte for byte, far more expensive than the alternatives, and the entire point of an enterprise content management system is putting everything under one roof. Typically, however, the kind of content you'd want to place in peripheral storage would be huge files - content that would take up acres of expensive database storage. Putting it all in the NTFS is less expensive. 

Another reason is to avoid the cost of pushing and pulling these files through SQL's doors. If very large, very exotic files (say, video recordings) are actually stored and retrieved through SQL, that eats up a vast amount of SQL's resources. If the front end of the request is SharePoint, and the push-pull are actually through FILESTREAM, then a large chunk of that resource cost is elsewhere, out of the path of the more conventional CMS traffic.

The cost of compliance

Another very useful justification is to deploy peripheral storage as a compliance solution. In the health and human resources industries, it is not uncommon that compliance requirements will mandate that a great deal of content - much of it the big, exotic stuff like video and audio files - must be kept instantly available for periods of years. This often necessitates a stand-alone content system deployed just for the purpose of satisfying that compliance.

Peripheral storage makes sense in this scenario, too, for a couple of reasons. To begin with, it makes little sense to pay for the storage of that content at SQL database rates, since the content is likely to be accessed far less often than the day-to-day content that a CMS is intended to host. And pulling the compliance content into the same CMS as the current material eliminates the cost of hosting it separately, which in many organizations is considerable.

In this technology's first iteration - SharePoint 2010 + SQL Server 2008R2 - peripheral storage was limited to the same physical server. With SharePoint 2013, it's now possible to deploy remote providers that enable peripheral storage in NAS, or some other convenient real estate.

It's not sexy, it's not glamorous, but peripheral storage is certainly worth serious consideration as a central component of enterprise CMS strategy