As SharePoint usage grows, many organisations are learning there are challenges to planning an effective storage platform for their SharePoint environment, writes AvePoint's Garth Luke.

On of the main issues to face when planning a SharePoint storage architecture involves deciding whether to store content within the SharePoint SQL database or have it point to an external data store.

This decision will have major implications for the cost and performance of your SharePoint platform.

While SQL is an efficient database technology, its use as SharePoint’s backend can pose unique challenges for organisations that are looking to centralise terabytes worth of legacy data. SQL is a relatively expensive storage media compared to file- and cloud-based storage, and SQL’s performance can also be compromised when burdened with unstructured, non-relational data.

Microsoft built the SQL database as a transactional database. SQL database storage needs high IOPS (input/output operations per second) performance and and low latency. This requires Tier 1 storage hardware which can cost up to 10 times that of other storage options.

If you want to get an idea of how that works in practice, try opening up a new file in Microsoft Excel, then putting 100 5MB files into separate cells in the spreadsheet. Performance will take a big hit, just as will happen to a SharePoint installation if you fill up the SQL database with thousands of large files.

Any file stored in the SharePoint database is referred to as a BLOB (Binary Large OBject). The BLOB can be a word document, PDF, email or audio or video file, stored in the database with its associated metadata.

There is actually no need to store BLOBs in the SharePoint database as they do not participate in query operations, so moving them to an external data store will not affect to speed of queries or information retrieval.

Microsoft helped address this issue by providing an External BLOB Store (EBS) Provider with Microsoft Office SharePoint Server (MOSS) 2007 SP2, which enabled organisations to extend SharePoint storage to other media. The EBS provider can take ownership of BLOBs and move them off to cheaper, more efficient file-based storage while leaving a token or stub in the SQL Server, so SharePoint can retrieve the object if necessary. One problem, though, is that EBS isn’t granular – only deployable at the farm-level.

Consequently, administrators had to deploy the EBS solution on every SharePoint web front end server. It also required deep knowledge of scripting and coding in order to successfully deploy the provider.

SharePoint Server 2010 has improved upon this with the support of the Remote Blob Storage (RBS) API, a SQL-based API. More flexible than EBS, RBS enables storage of all content in a Site Collection on the file system (with metadata retained in the SQL content database). However, it also requires significant coding in order to utilize the RBS effectively.

Whether an organisation decides to do custom coding or looks to a third-party provider, it is imperative it utilises Microsoft’s EBS and RBS APIs. Keep in mind that the SQL Server is the engine that powers the SharePoint platform. If it is overburdened with unstructured data it is not natively equipped to efficiently handle, it can have potentially disastrous consequences at the precise time your business needs the platform most.

To avoid having your SharePoint SQL database grow too large and unwieldy, it pays to limit overall content DB size and split content DBs if they get “too big.” You also don’t want to have a vast repository of content in your main SharePoint database that is not being regularly accessed.

It can help to set content limits for MySites, for instance 10GB, with an alert given when they exceed 8GB.

In our experience it is advisable to place any file that is larger than 256KB outside of the SQL database.

There are limits to the size of a database able to be addressed by SharePoint natively, Microsoft recommends 90GB for SharePoint 2007 and 200GB for SharePoint 2010.

We have seen examples in Australia of organisations that have over 1TB.
AvePoint’s DocAve Storage Optimization suite offers a comprehensive solution for the offloading of BLOBs from SQL Server including the free tool DocAve Extender for SharePoint

To ensure seamless access to any network or cloud file-share content directly through SharePoint – without the need for migration – organisations can utilise DocAve Connector for SharePoint, enabling the migration-free SharePoint presentation, management, and streaming of legacy content.

Garth Luke is Vice President of Sales at Avepoint.