Category Archives: Applications

The apps we use day in and day out.

Configuring RBS for SP2010

Following on from my previous post about list scaling and performance.  The following posts details configuration of Remote Blob Storage for SharePoint 2010 and SQL Server 2008 R2.

First download the RBS provider for SQL Server 2008 (don’t install it yet):

http://go.microsoft.com/fwlink/?LinkId=177388

Configure file stream for the SQL Server Service using the Configuration Manager:

image

Execute the following SQL queries:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

Execute the following SQL to set up a master encryption key and blob store file group:

use WSS_Content

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'

if not exists (select groupname from sysfilegroups where 
groupname=N'RBSFilestreamProvider')alter database WSS_Content
 add filegroup RBSFilestreamProvider contains filestream
 
alter database [WSS_Content] add file (name = RBSFilestreamFile, filename = 'c:Blobstore') 
to filegroup RBSFilestreamProvider

Install the RBS provider with the following command (change DBINSTANCE to your SQL server instance):

msiexec /qn /lvx* rbs_install_log.txt /i RBS_X64.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="WSS_Content" DBINSTANCE="SP2010" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

If installing RBD on production servers, be sure to run on all WFE’s with the following command (again, change the DBINSTANCE):

msiexec /qn /lvx* rbs_install_log.txt /i RBS_X64.msi DBNAME="WSS_Content" DBINSTANCE="SP2010" ADDLOCAL=”Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer”

Run the following Power Shell script from the SP2010 Management Console:

$cdb = Get-SPContentDatabase –WebApplication http://sp2010

$rbss = $cdb.RemoteBlobStorageSettings

$rbss.Installed()

$rbss.Enable()

$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])

$rbss

Now create a document library in SharePoint and upload an image to it.  Next visit the c:blobstore directory and look for the GUID sub folder with recent date.  Keep drilling down until you find a file.  You should see a file with GUID name.  Drop this into IE and you should see that it is the same file you uploaded to your document library.

 

From the SharePoint 2010 book I’m reviewing

List Scaling and Performance in SP2010

It it a well known fact that MOSS 2007 caused some rising opinions on the subject of list scalability and performance.  Many developers operated under the misconception that SharePoint lists only allowed 2000 list items before croaking out with bad performance.  Nothing could be further from the truth.

The following article talks about this issue of large lists in great depth and highlights the point that SharePoint can actually handle many more than 2000 list items in any one list.  However, “query” this data is affected by the item count and SharePoint architects should design their data access and presentation of data accordingly.

http://technet.microsoft.com/en-us/library/cc262813.aspx

Microsoft has added a number of new enhancements to lists in SharePoint 2010 to handle larger capacity and the query of this data, and the following is a short summary of the enhancements:

List Column Indexing

SP2010 now allows list designers to create up to 20 indices (some of multiple columns) on any one list.  These indices allow for faster query of data when the list size exceeds that of typical.

image 

Under list settings and in the columns section; users now see a link to Indexed Columns.

The following is a list of column types usable as indexed columns:

· Single line of text

· Choice field, but not multi choice

· Number

· Currency

· Date/Time

· Look up, but not a multi value look up

· Person or group, but not multi value

· Title, except in a document library

 

List Throttling

SharePoint administrators now have the capability to better control list queries so that developers (or general users) may issue list queries on large lists that may potentially bring down the server.  Specifically:

Administrators may define some limits at the web application level:

– Configure the number of items fetched for queries

– Administrators may receive warnings when thresholds exceeded

– Ability to configure time periods for expensive queries to operate

– Limit the size of list items (default to 8k)

– Limit the number of columns in a join (default to 6)

The following code will display the list throttling limits for the site collection:

using (SPSite site = new SPSite(siteUrl))
            {
                Console.WriteLine("MaxItemsPerThrottledOperation:{0}",
                    site.WebApplication.MaxItemsPerThrottledOperation);
                Console.WriteLine("MaxItemsPerThrottledOperationOverride:{0}",
                    site.WebApplication.MaxItemsPerThrottledOperationOverride);
                Console.WriteLine("MaxItemsPerThrottledOperationWarningLevel:{0}",
                    site.WebApplication.MaxItemsPerThrottledOperationWarningLevel);
            }

To enable list throttling on any list be sure to toggle the setting with the following:

SPList.EnableThrottling = true

MaxItemsPerThrottledOperationWarningLevel – If a list exceeds the number of items specified in this threshold then a warning is displayed on the list settings page.

What MaxItemsPerThrottledOperation – This indicates the number of list items returned to non-administrators.  Administrators can query up to the threshold in What MaxItemsPerThrottledOperationOverride but will receive a warning on the list settings page.

If administrators wish for users to execute expensive operations in specific window of time they can do so by using the following method on the WebApplication object: SetDailyUnthrottledPrivilegedOperationWindow

 

RBS Storage (Remote Blob Storage)

In some cases the use of document libraries to store large files is no longer scalable and causes content databases to become unmanageable.  An example situations where a public web site, hosted in SharePoint, provides users with rich media content – web files and large images – is once such example of the large blob storage issue. 

In MOSS, hosting content in the database provided certain benefits, such as single storage location, versioning, and access of files via the object model.  Whereas file based storage provided better scalability at the cost of orphaned content from the object model.  SP2010 solves this issue with RBS.  Site Architects can now store large files (blobs) in alternate locations to that of the SharePoint content database without relinquishing access via the OM.  From an and developer standpoint, the data is accessed as if it were in the content database, but the content is actually in a remote location.

To enable RBS you’re farm will need to use at least SQL Server 2008 R2.

Marking blobs as external at the content database level enables SharePoint to store the meta-data associated with blobs in the database while storing the actual blob content outside the content database.  Because RBS is handled at the database level, SharePoint is unaware that data is not stored in the content database but in another location.

in a future time, vendors will bring RBS providers for SP2010 to the table, but in the meantime Microsoft has provided RBS for SQL server as an extra download:

http://go.microsoft.com/fwlink/?LinkId=177388

See my next blog post on configuring RBS.

 

From the SharePoint 2010 book I’m reviewing

SP2010 Developer Dashboard

The SP2010 Developer Dashboard allows developers to review object model calls, database queries, web part events – and the timings for these various happenings.

The following code enables the dashboard:

SPPerformanceMonitor SPPerfMon;
SPPerfMon = SPFarm.Local.PerformanceMonitor;
SPPerfMon.DeveloperDashboardLevel = SPPerformanceMonitoringLevel.On;
SPPerfMon.Update();

The following code turns it off again:

SPPerformanceMonitor SPPerfMon;
SPPerfMon = SPFarm.Local.PerformanceMonitor;
SPPerfMon.DeveloperDashboardLevel = SPPerformanceMonitoringLevel.Off;
SPPerfMon.Update();

Development Setup for SP2010

Some important points to remember when developing against SP2010:

  • Make sure your Visual Studio project is set up for .NET 3.5, not .NET 4.0
  • Run Visual Studio as an Administrator to load debugging symbols
  • Make sure your project is set to compile for Any CPU or x64 (not x86 by default), otherwise your code will throw a FileNotFoundException

From the SharePoint 2010 book I’m reviewing

Provision 2010 Farm without the Mess

Anyone who has created a new SP2010 Farm using the SharePoint Technologies Configuration Wizard will know that it makes a huge mess of SQL server, littering new databases with GUID extensions.

Those of you who have anal tendencies, like me, can sleep at night with nice clean database names with a script as follows:

Note: I am working on a script that will create Service Applications, which also like to create many GUID database instances.

@echo off

if "%1" == "" goto usage
if "%2" == "" goto usage

pushd C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14BIN
echo Creating New Farm Instance
psconfig.exe -cmd configdb -create -server %1 -database %2_SharePoint_Config -user DEVMOSS_Farm -password Sharepoint3 -passphrase Sharepoint3 -admincontentdatabase %2_SharePoint_Admin_Content

echo Creating Central Admin Site
psconfig.exe -cmd adminvs -provision -port 8000 -windowsauthprovider onlyusentlm

echo Installing Services
psconfig.exe -cmd services install

echo Securing Resources
psconfig.exe -cmd secureresources

echo Starting Search Service
stsadm.exe -o osearch -action start -role Index -farmcontactemail support@portalsolutions.net -farmperformancelevel maximum -farmserviceaccount "DEVMOSS_Farm" -farmservicepassword Sharepoint3

echo Starting WSS Search Service
stsadm.exe -o spsearch -action start -farmserviceaccount DEVMOSS_Farm -farmservicepassword Sharepoint3 -farmcontentaccessaccount DEVMOSS_Search -farmcontentaccesspassword Sharepoint3 -databaseserver %1 -databasename %2_WSS_App_Search

echo Installing Feature
psconfig.exe -cmd installfeatures

echo Installing Health Rules
psconfig.exe -cmd InstallHealthRules

popd
goto end

:usage
echo "SP2010-Provision.bat <SQLServer> <Project Acronym>"

:end

Pre-Search Facets in MOSS 2007

Search facets offer a powerful entry point into data exploration, especially in cases where data is categorized or tagged effectively.  With modern search and content retrieval mechanisms, such as the search engine in MOSS (and new FAST search engine for SharePoint) the traditional method of browsing for content in SharePoint using static navigation hierarchies can now take on a whole new approach.

The new thinking behind content storage is to put all artifacts in one large bucket, tag the artifacts, and then leverage search and facets to surface relevant content.  Think about how Google revolutionized mail by discarding the folder structure approach in favor of a search and label paradigm.

Anyone who has played with Faceted searching in SharePoint probably knows that, aside of the commercial tools like BA Insight, the only real free option is to use the Codeplex Faceted Additions.

fs3.png

The Codeplex offering assumes “post” search faceting, in that the web parts determine the relevant facet headings and count based on the current executed result set.  This approach makes good for filtering search results and allowing users to drill down on with restricted queries, but what about pre-facet browsing, similar to the functionality on sites like Best Buy?

Here is the problem – to provide the user with a dynamic tree view if hierarchical data based on facet categorization, the hierarchy generation method needs to know about all potential facet values ahead of time.  Take the following example:

An organization tags all their documents with a document type and department.  Let’s assume we wanted to provide a dynamic list of departments, which the user could choose, and then a list of document types available for the selected department.  After selecting the document type we’d like the user to see all documents of the selected type that sourced from the selected department.

Aside of issuing a general search, and then filtering the result set by department and document type, the Codeplex faceted search web parts do not appear to offer a mechanism to provide dynamic table-of-content like behavior.

So I got to thinking – search facets in MOSS are no more than managed properties that exist in the search index.  Surely the object model must enable me a way to query distinct values of a given managed property?  It turns out that you can query the search API for this information, and with a little code magic you can obtain the results desired:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Office.Server.Search;
using Microsoft.SharePoint;
using Microsoft.Office.Server.Search.Query;

namespace SearchFacets
{
    /// <summary>
    /// Faceted search querying.
    /// </summary>
    class Program
    {
        static readonly string SRCURL = "http://server/";

        /// <summary>
        /// Entry point.
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            using (SPSite srcSite = new SPSite(SRCURL))
            {
                string query = "SELECT DocumentType FROM Scope() WHERE "SCOPE" = 'My Scope Documents'";
                string[] values = GetDistinctSearchResults(srcSite, query, 1000);
                foreach (string s in values)
                    Console.WriteLine(s);
            }
        }

        /// <summary>
        /// Get some search results using full text.
        /// </summary>
        /// <param name="context">Site.</param>
        /// <param name="searchQuery">Query.</param>
        /// <param name="searchLimit">Limit results.</param>
        /// <returns>Results.</returns>
        static string[] GetDistinctSearchResults(SPSite context, string searchQuery, int searchLimit)
        {
            using (var fullTextQuery = new FullTextSqlQuery(context))
            {
                fullTextQuery.ResultTypes = ResultType.RelevantResults;
                fullTextQuery.QueryText = searchQuery;
                fullTextQuery.KeywordInclusion = KeywordInclusion.AnyKeyword;
                fullTextQuery.EnableStemming = false;
                fullTextQuery.TrimDuplicates = false;
                fullTextQuery.RowLimit = searchLimit;

                ResultTableCollection resultsCollection = fullTextQuery.Execute();
                ResultTable resultsTable = resultsCollection[ResultType.RelevantResults];
                return ReturnDistinct(resultsTable);
            }
        }

        /// <summary>
        /// Return distinct list.
        /// </summary>
        /// <param name="rtWins">Restult set.</param>
        /// <returns>Distinct values.</returns>
        static string[] ReturnDistinct(ResultTable rtWins)
        {
            DataTable dtWins = null;
            Dictionary<String, int> pairs = new Dictionary<string, int>();
            List<String> lstWins = new List<string>();
            dtWins = new DataTable("dtWINS");
            dtWins.Load(rtWins);

            foreach (DataRow drWin in dtWins.Rows)
            {
                string fieldName = drWin[0].ToString();
                if (pairs.ContainsKey(fieldName))
                    pairs[fieldName]++;
                else
                    pairs.Add(fieldName, 0);
            }

            foreach (KeyValuePair<String, int> pair in pairs)
                lstWins.Add(String.Format("{0} ({1})", pair.Key, pair.Value));
            return lstWins.ToArray();
        }
    }
}

You might be thinking “Hey, you’re just executing a search”, and you’d be right.  Since the facet values (managed property values map to crawled properties) live in the search indexes we have no choice but to perform a search to get at these values.

The key in the above code is to limit the search results returned (1000 in above case) and take advantage of relevancy.  In all likelihood; any search results beyond 1000 hits will not likely produce facet values that map to many results of value to the end user.

Clearly, the above code is just a starting point and has potential for many improvements, such as caching, making use of parent child relationships etc, but you get the idea…

Site Collection Restore Error

If you ever run into the following error when performing a site collection restore, via STSADM, and you know space is not an issue, try the steps below before diving deep into troubleshooting mode:

The site collection could not be restored. If this problem persists, please make sure the content databases are available and have sufficient free space.

  • Stop and start the SharePoint Timer Service
  • IISRESET
  • Restore to a new content database

Balsamiq Mockups makes for easy UI design

We’ve been using Balsamiq Mockups for some time at my day job, but until recently I hadn’t used the tool heavily on any of the projects I’d been working.  Today I needed to shell out an example search results page for a project I am architecting in SharePoint.

Think of Balsamiq Mockups as Visio for the layman – it’s light, easy to use, not cluttered with unnecessary functionality, and runs on Adobe Air.  The presentation is something akin to what you might mockup on a whiteboard in the office and the end result, although a signature of the Balsamiq development team, is crisp and ideal for any document deliverable.

What I like about the tool most is that I was able to complete a mockup, which is functional for discussion purposes and doubles for architecture documentation, and it took me a fraction of the time I’d have spent in Visio – this gave me time to write this blog post.

At a price of $79, the product is a steal for the time it’ll save you.

Check out my finished page mockup:

Global Search Results

Working from the Cloud

My employer uses laptops pretty much exclusively (as I do at home) for all employees, with the exception of a few, to promote flexibility and portability in our work environment.  I made the mistake yesterday of forgetting my laptop and turning up to the office with no computer to work with.  As was debating on turning my car around (I got all the way to the office) and heading back home when I got thinking.  In today’s connected environment, did I really have a dependency on a single computer to work?  The short answer is no.

When I thought about it some more, having no laptop didn’t mean I couldn’t continue my work day as normal.  I spoke nicely to one of our IT people and asked for a temporary laptop for the morning, hooked it up to the network, logged in, and continued working as normal – how?

The answer is in the tools that I use.  Granted, I’ve moved on from localized development and no longer require a host of specialized tools to work, which makes life easier. Also, I’ve always had a healthy paranoia about keeping work files on portable devices that may inadvertently fall in the parking lot and break into a million pieces, so wove redundancy into my personal workspace some time ago, meaning I was already in great shape for using another computer for work. 

With Internet speeds getting faster and online storage becoming cheaper, there is a definite shift in mentality to store files in the cloud.  I realized this about a year ago.  The following is a list of applications and approaches I use to enable portability in the my day-to-day work:

Hosted Virtual Machines

My job involves SharePoint development, so I cannot escape the need for a development environment.  Many of us still develop on Virtual Machine images using portable devices.  Fortunately, my employer saw this as non-scalable solution and set up virtual servers for all out development.  Our IT infrastructure includes backups, and I can access the servers from any location using secure VPN.

Outlook Web Access and Gmail

All my company email sits on an Exchange server, which comes complete with a web client for accessing my email from any web browser.  If I insist on the thick client, Outlook is installed on most of the company laptops and configuration of my account is 5 minute exercise.  I use Gmail for all my personal mail and never have to worry about loosing my email or servers going down.  On the rare occasion that the company Exchange server goes down, I have my personal email to fall back on if I need to (who doesn’t?).

GTD with ClearContext

I use ClearContext to arrange my inbox within Outlook.  CC uses folders within my inbox, so I don’t have to worry about carting around backups of my settings.  If I choose not to install CC on a loaner laptop, I can still work with my email because filed messages live in Exchange folders and I can put aside new inbox email for filing later when I get back to my laptop – left at home.

Evernote

I am never ever caught out talking to a client without notes from previous meetings.  I know a lot of people like to use One Note, but if you use EN your notes are available on the web, phone, or any other computer (Windows and Mac) that you choose to install the application.  My notes synchronize in a few minutes and I’m up and running.

Drop Box

A well thought out product that synchronizes files across computers and in the cloud.  I use this application on all my computers, and the UI is a simple folder on my desktop – I drag all my files to the special drop box folder and have peace of mind that my files are available on all other computers, or via the web interface.

IM

Using both Communicator (corporate) and MSN (personal), I am able to stay in touch with clients, colleagues and friends.  Both applications install in minutes and require no setup for me to get back online.

SharePoint and Colligo Contributor

My work primarily involves SharePoint, so I would be amiss if I didn’t eat my own dog food.  My employer has a nice extranet where I can always access client work in progress, RFP work etc – it’s policy that all work is stored on our extranet.  With Colligo Contributor – an application that works much like Groove, only better – I can keep a cached version of files on any PC, so if I loose network access I can carry on working on a local copy of my files stored in SharePoint.

Pandora

Say what?

A work day in the office is a little dull if I cannot listen to my favorite tunes whilst working.  Using Pandora – an Internet streaming radio service – I can continue listening from any web enabled computer.

X-Lite

X-Lite is a SIP VOIP client, an my employer uses VOIP.  So if I want to take a call from Starbucks, the road (using mobile broadband), abroad, or a client office, it’s no big deal.  The recipient of my call doesn’t know I’m not calling from the office.

So… Flexibility in a nutshell.  If you’ve not done so already, it’s time to cut the chord from your working computer and get into a portable mentality.  You’ll need support from your employer (something to consider asking in your next job interview), but if you can convince them and it’ll make you more productive – it’s worth any overhead.