Friday, August 26, 2011

SharePoint 2010 Independent Market Analysis

Updated - 3/2012 - View new blog posting here

I get asked a lot about reasons to move to SharePoint 2010. I simply state, look at the how the market evaluates SharePoint.
Here is a great link managed by Microsoft that showing Gartner and Forrester reports -

As of right now, I recommend reading the following:
I suggest stay up to date with this site to find the latest reports.

Saturday, August 20, 2011

SharePoint 2010 SQL Server Encryption

I have been asked several times what SharePoint ability to encrypt data that resides the SQL Server databases. I was answering this question when I ran across this awesome presentation that I highly recommend. “SQL Transparent Data Encryption for SharePoint Content Databases” at Just want the first few minutes of the video.

Smaller notes I usually bring up are:

· Row level encryption is not really possible as we do encryption at the database level.

· An important note is that the SQL connection from the SharePoint application layer to SQL server is done through a service account. Users are not directly authenticating and accessing data in the database.

· Documents themselves can have rights management applied so the BLOB is encrypted when it is stored in SharePoint. However metadata associated to the document is not encrypted and documents will not be searchable.

Using Transparent Data Encryption really is a great solution.

SharePoint 2010 Architecture Introduction

I recently had a client ask be about how to get started on understanding the SharePoint 2010 architecture and how they should deploy. Unfortunately the answer is it depends based on your business requirements.
Gartner recognizes the SharePoint platform as a best of breed across all major workloads like web portal, enterprise content management (document management, web content management, records management, etc.), business intelligence, workplace social computing, search/enterprise search, and as an application development platform. Knowing this, the SharePoint platform delivers a single platform that is managed together helping agencies consolidate costs in people, process and technology. Plus SharePoint is tightly aligned to Office and Lync (instant messaging, sharing, meeting, and phone solution).
Now depending on what you will implement will depend on how you scale SharePoint 2010. Plus with many agencies, there is never just on SharePoint farm. There will be multiple SharePoint farms which will be configured to support the business requirements.
If you are trying to get an initial understanding of the SharePoint 2010 architecture, here are some good references:
· SharePoint 2010 Architecture - - this is a good starting place if you are not familiar with SharePoint.
· SharePoint 2010 Technical Diagrams - - all the big picture of both physical and logical architecture.
· Hardware and Software Requirements - - I suggest reading this right off the bat.
Performance, scaling, business continuity topics always come up when starting to learn about SharePoint 2010. Here is a good place to start.
· Performance and capacity technical case studies (SharePoint Server 2010) - More good case studies.
· SharePoint 2010 Performance and Capacity whitepapers - - Whitepapers on specific workloads.
· SharePoint 2010 Capacity boundaries - - This is pretty detailed discussion on testing.
Now if you are familiar with SharePoint 2007 architecture:
· I have written a multiple part series on SharePoint 2010 architecture here-
· I have another blog on scaling SQL Server because this is a critical component to SharePoint -
· Here is a series on SharePoint 2010 Search -
· Here is a series on FAST for SharePoint 2010 -
Finally it is also IMPORTANT to know when reviewing all these architectures, that SharePoint 2010 is the only portal technology on the market that software as a service (SaaS) cloud offering called Office365. This ultimately means major reduction on hardware and software that must be installed and managed, better service level agreements to your users, quicker deployment of solutions, better ability to scale, better ability to support telework and external collaboration, and the list really just goes on. Be in the business of creating business solutions.
I say the best way to learn about SharePoint Online Service is read the service level agreements which I have linked to here -

Thursday, August 18, 2011

BCS Connection to WCF Web Service


I recently needed to connect BCS to some WCF services. The goal was show how I can then use the data out of those WCF services as a data source for FAST. Creating a WCF service and publishing it out through IIS was a pretty simple task. For search you only really need two web methods. First you will need a method that returns a full collection of the dataset which will be used as part of the indexing process. The second method you will need is a get item method that will get the details of a record in the data set.

Issue I ran into

However I ran into a challenge with getting the connection to work initially. When filling in the WCF connection window I would get errors like:



In the Event Logs I had some more detailed errors which really did not help much.

Could not obtain a proxy to WebService for LobSystem 'Default' in App Domain 'ConnectionDomain'. The full exception text is: Metadata contains a reference that cannot be resolved: 'http://demo2010a:8000/SoftwareService/SoftwareService.svc'.. The inner exception text is: <?xml version="1.0" encoding="utf-16"?><Fault xmlns=""><Code><Value>Sender</Value><Subcode><Value xmlns:a="">a:BadContextToken</Value></Subcode></Code><Reason><Text xml:lang="en-US">The message could not be processed. This is most likely because the action '' is incorrect or because the message contains an invalid or expired security context token or because there is a mismatch between bindings. The security context token would be invalid if the service aborted the channel due to inactivity. To prevent the service from aborting idle sessions prematurely increase the Receive timeout on the service endpoint's binding.</Text></Reason></Fault>.

Could not obtain a proxy to WebService for LobSystem 'Default' in App Domain 'ConnectionDomain'. The full exception text is: There was an error generating the XML document.. The inner exception text is: The type System.Web.Services.Discovery.DiscoveryDocument was not expected. Use the XmlInclude or SoapInclude attribute to specify types that are not known statically..


After playing around with the configuration I finally found the following worked. Notice the “mex” extension added to the end of the Service Metadata URL. Once I had that done, just wire up the web methods like you would anything else in BCS.


Now here is another way to do this. Any service that has a wsdl description can be integrated as well. Notice here how I put the wsdl address for the Service Metadata URL.


Sunday, August 7, 2011

Part 5 – Custom Visio Services Reports with SharePoint 2010 Workflow

· Part 1 - Pattern for Building Stateful Workflows with SharePoint Designer 2010
· Part 2 - Custom Action for Workflow Initiation
· Part 3 - Delays for Workflow Initiation Action
· Part 4 - Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow
· Part 5 - Custom Visio Services Reports with SharePoint 2010 Workflow


This is the last part in this workflow series. In the first part of this series I wrote extensively about how Visio Services allow us to now do visual reporting on the progress of a workflow process instance. More importantly, it allows us to provide a visual report to business users removing all the technical steps that may be needed to implement an end to end workflow.

For example, I below I have a ton steps in a workflow. When SharePoint Designer 2010 publishes this workflow a Visio workflow diagram will be generated.


That Visio diagram will accurately show where it is within its execution however I can guarantee that the business user does not care about all these steps. All they want to know is what the major business process steps are and what the current status is.


We can provide a perfect solution using a custom Visio report.

My solution is to create a completely different Visio diagram to the business reporting. So what I did was take this original diagram I created in this series (below) with the objective of “lighting it up” with the current status of the business process.


The solution is simple.

· I create a column on my list item to store the current step in the business workflow the workflow is in.

· Then during my workflow, I update this column object in the Visio diagram I want to light up.

· Then connect a list and Visio web part together to allow a user to select a running workflow and then visually see the status of the workflow.


To achieve this I need to perform a few steps.

In the workflow I added an Update List Item action. I updated my CurrentAction column with the name of the Visio object I want to light up. In this instance it is “Process.3”. Throughout other workflows I will update the CurrentAction column with other values that correspond to different objects in the Visio diagram.


Next I uploaded the Visio diagram to a location in SharePoint; simple enough.

Next I created a web part page. On that web part page I placed the list where the workflow is running.

Next I added a Visio web part and pointed to the Visio diagram that I had just uploaded. Below is a screenshot.


Next I need to connect the list web part to the Visio web part. I need to do this so that when a user selects a list item, the Visio diagram on the right will highlight the correct Visio object. On the Visio web part I made a connection to the Review list web part. Notice I select “Get Shapes to Highlight From”.


Next in the Configuration Connection screen, I select the CurrentAction column (which is the column I that I update in the workflow) and then select Shape Names. This is how the “Process.3” value is sent from the selected list item to the Visio diagram to be highlighted.


The net result is the following. As you can see a Visio object is now highlighted based on the item that I selected on the left hand side. Really simple and really effective!!!


Highlighting Multiple Items

In the example I just showed you, I am only highlighting the point where the process is in its execution. However, let’s say you want to highlight all the steps the workflow has gone through. Well that is simple too.

In this case I created a different column called “Actions” where I capture a comma delimited string of all the times I want to be highlighted. In the workflow I have the following. Notice in the String Builder window I concatenate the previous value in the column with the new value “,Process.3”.


Other than that, the configuration is 100% identical other than selecting the “Actions” column when connecting the web parts. The final result is the following – all of the Visio object names I concatenated together are highlighted.



This is such a powerful capability. I can create custom visual reports on my business process with no code.

I truly hope this workflow series will be helpful for you when starting to build process automation in SharePoint 2010.

Part 4 – Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow

· Part 1 - Pattern for Building Stateful Workflows with SharePoint Designer 2010
· Part 2 - Custom Action for Workflow Initiation
· Part 3 - Delays for Workflow Initiation Action
· Part 4 - Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow
· Part 5 - Custom Visio Services Reports with SharePoint 2010 Workflow


If you have been reading this blog series closely I mentioned that I will be discussing how we can do reporting for our workflow solutions in SharePoint 2010.

As you may know when you configure a workflow in SharePoint there is a Workflow History list that must be associated which will stored information about the execution of a workflow instance. I have typically viewed this as more of a log. It is not something to be used for reporting. Here is a quick reference about it and I suggest you read it -

The reasons I do not use it for reporting is because:

· This list can potentially store a lot of data from a lot of different workflows.

· It will be purged after a period time. Even though you can disable a job to ensure logs are not deleted, doing this would disable the job for every workflow across an entire web application which is not good practice.

· Personally I like it for its logging capability but I would not use it for a reporting capability.


My approach will be to store data in a SQL Server table using Business Connectivity Services (BCS). With SharePoint 2010 it is so easy to support this from a workflow without doing a ton of code. Now I can do inserts and updates operations to reporting tables and then build reports off of them using the either Excel Services, SSRS, etc.

I am not going to go over the steps on how to create a SQL Table or how to create an external content type using BCS. What I will cover is how to use the external content type from a workflow and discuss one tricky configuration you may not know about.

For this solution you need to:

· Create any sort of SQL tables you need.

· Create external content types to connect to those tables.

· Create external lists on the site to use the external content types.

· Finally I will cover how the workflow will call the external list as a proxy to make transactions against the SQL tables that you will use for reporting.

Insert a Record from a Workflow

There is really nothing special to this at all. Add a Create List Item action into my workflow that references the external list. In my case, the external list I created was called “Review Actions Table”.


Note that I created a variable called ReviewActionID, where I store the SQL generated ID of the new item that was created. If you need to update this SQL record as part of a different workflow process, I recommend updating this value into a column on the item where the workflow is running against.

Updating a Record

Now this is the tricky part. Let’s say later on in the same workflow process you want to update the same SQL record you just created. To accomplish this just add an update action like the following.


The tricky part is the configuration of the update – look at the screen shot below. Notice how I have to select the BDC Identity Field to get the primary key value to perform the update. If you do not do this the update will fail (sorry do not have the error log handy).

Initially I had set it to use the ID Field on the external content type thinking it would have the generated key from the SQL table as part of the original insert I did early. However that was not the case.

Note you do not need to create this BDC Identity column; it is auto-generated for you.


One more note, I had to do this for a SQL Server table with an auto-generated primary key. I have not tested this out for other scenarios like a web service or another DB platform.

Finally let’s say I need to update an external list item on a record that was created prior to the workflow being started. Add an Update List Item action to the external list like the following. Notice here for the Field I use the ID column from the external content type. We do not have to do what we did earlier.



Why do I get so excited about this? Now I am able to start dumping data out into SQL server to do reporting with SharePoint 2010 Business Intelligence solutions. I can create reports with Excel Services or SSRS. In this example I used SSRS using Report Builder 3.0 to create reports about the execution of my business process.

For instance I created an SSRS report that shows the average duration of steps in the business process. I embedded the reporting into SharePoint and I was off and running.


I then created another report that shows the duration by business process instance.


I even created another report that shows how employees perform.


I just embedded these reports into SharePoint 2010 and I was done. Now I can provide some real business insight into process execution.

Part 3 – Delays for Workflow Initiation Action

· Part 1 - Pattern for Building Stateful Workflows with SharePoint Designer 2010
· Part 2 - Custom Action for Workflow Initiation
· Part 3 - Delays for Workflow Initiation Action
· Part 4 - Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow
· Part 5 - Custom Visio Services Reports with SharePoint 2010 Workflow


In the previous part of this blog series I provided an approach for chaining together a bunch of sequential workflows together so that I can create a “state like” workflow without having to use Visual Studio. However one thing I did not go over was a few configurations you will have to do to actually start working with data.

One important issue I would run into with my approach was that workflows would start to trip over each other. For instance I have workflow A and workflow B associated to a list. In this case, workflow A would start workflow B. Every once and a while the workflows would fail for no apparent reason. Digging through the logs I would find errors like this which would not tell me much.

OWSTIMER.EXE (0x0A00) 0x0968 SharePoint Foundation Workflow Infrastructure 88xr Unexpected WinWF Internal Error, terminating workflow Id# 13531682-5bef-4194-aaf6-8cf32b31345d

OWSTIMER.EXE (0x0A00) 0x0968 SharePoint Foundation Workflow Infrastructure 98d4 Unexpected System.ArgumentException: listItemKey at Microsoft.SharePoint.WorkflowActions.WaitForDocumentUnlockActivity.Initialize(WorkflowContext context, Guid listID, SPItemKey listItemKey) at Microsoft.SharePoint.WorkflowActions.WithKey.UpdateItemActivity.Execute(ActivityExecutionContext provider) at System.Workflow.ComponentModel.ActivityExecutor`1.Execute(T activity, ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutor`1.Execute(Activity activity, ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutorOperation.Run(IWorkflowCoreRuntime workflowCoreRuntime) at System.Workflow.Runtime.Scheduler.Run()


The solution is simple; if you are going to use the approach that I outlined earlier in this series you will need to introduce some delays into your workflows.

For instance in Workflow A, I put in the following delay before my custom action to initiate a workflow.


Then in Workflow B, I put another delay.


This way I am ensured that if there are any lagging transactions are completed against the list item that both workflows are working on.

You will also have to configure the interval workflow timer job in Central Admin. The default is 5 minutes and I upped it to one minute in my development environment.


There is no perfect configuration however you will need to make the correct configuration to make sure everything works well. You need to introduce enough delays and configure the workflow service to make sure you do not get these locking issues.