SharePoint Thoughts - A blog centered on Windows® SharePoint® Services

Posted by Maurice Prather

This has been an incredibly fun year. A new version of SharePoint rolled out, quickly followed by a Master certification upgrade class, and now Aptillon.

The short story is a group of 7 friends wanted to work together as a team. Todd Baginski, Darrin Bishop, Dan Holme, Gary Lapointe, David Mann, Matt McDermott, and I collaborated to form a new company that we've called Aptillon. We're all super excited about the new chapter that is unfolding in our lives. Check out the official news post on our site.

-Maurice

Posted @ 11:21 AM on Tuesday, August 24, 2010 | Comments:

Best Practices Conference - August 24-27

Posted by Maurice Prather

Are you attending the Best Practices Conference in Washington, DC?

If so, swing by and say hello! I am realy looking forward to catching up with old friends and, of course, meeting new ones.

I'm really jazzed about the two sessions that I'm presenting. In fact, if I was paying attention I would have signed up to do a third one on PowerPivot. :)

You'll be able to find me presenting or at the Ask the Experts session...

Event	Location
Understanding and Leveraging the Sandbox	Tuesday, August 24 @ 4:15 PM Regency B
Ask the Experts: Development	Wednesday, August @ 4:30 PM UserVersity Coffee Lounge
Best Practices for Upgrading Web Parts	Thursday, August 26 @ 11:00 AM Lake Fairfax B

See ya soon!

-Maurice

Posted @ 6:05 PM on Monday, August 23, 2010 | Comments:

What the Masters think about SharePoint 2010 - PowerPivot

Posted by Maurice Prather

Background: I was originally scheduled to participate in a segment of the Best Practices Conference keynote where a group of MCMs would take some time to describe one of their favorite components of SharePoint 2010. Unfortunately, I scheduled a red-eye flight that landed *after* the keynote. This morning's post is a quick brain dump of what I was planning to share with the audience...

PowerPivot is definitely one of most interesting pieces in SharePoint 2010. Don't get me wrong - there are lot of other things that I really like (services architecture, managed metadata, etc.) but PowerPivot really stands heads and shoulders as the best new "power toy".

Before I dive into some bullet points why... let me frame the conversation a bit...

What is PowerPivot?
In the simplest sense, PowerPivot is three things: an engine, an add-in for Excel, and a SharePoint service application.

The engine is built to operate with large sets of disparate data. It's what makes the add-in and service application possible and also makes it possible for other applications to consume PowerPivot workbooks as data sources.

There is a client side component, PowerPivot for Excel add-in, and a server-side service application, PowerPivot for SharePoint. You can use one without the other, but you'll eventually need the client add-in to design and create workbooks than can be used by the PowerPivot service application.

3 things: engine, Excel add-in, SharePoint service application. It's a key point to remember!

What makes PowerPivot so cool?
Simply put - data.

PowerPivot can allow folks to work with huge amounts of data. I've seen Excel workbooks that contain PowerPivot data numbering will into the hundreds of millions of rows... even seen billions of rows... that's right... millions with a B.

PowerPivot is all about data aggregation.

It allows you to pull in data from multiple data sources and then join it altogether as though it were all from single uniform source. If you get into the details of the process, it truly is from a single source - this is where the technology part kicks in.

PowerPivot has a data engine that allows you to import data from stores like SQL relational databases, Analysis Services, other PowerPivot workbooks, text files, and ADOMD.NET just to name a few. The import process packs all this information into a standard Excel workbook. From there, the data is in fact coming from a single uniform source. The data engine is built on Analysis Services and, once you load your workbook data, PowerPivot lets you build pivot tables and charts as well as expand your data mining opportunities via DAX (a new column-based formula language).

PowerPivot is all about flexibility.

The client piece is basically a completely autonomous application that doesn't have anything to do with SharePoint. If you simply want to work with big data, install the add-in and go. The add-in includes the fully featured PowerPivot engine. Your only limitation is limitation of your client machine (x86 vs. x64, processor, memory). You'll need the Excel client to design the workbooks that will be eventually published to SharePoint.

If you need to move up to a more serious, capable framework for calculation and distribution, move those workbooks over to SharePoint. Here you get the opportunity to leverage the scale and power of servers. PowerPivot for SharePoint is new service application that is available in SQL 2008 R2 and designed for the enterprise sku. The nicest thing about PowerPivot for SharePoint is that it leverages your existing Excel Service infrastructure (much like the client version leverages your existing Excel install!) and is can be managed independently using the services architecture.

PowerPivot bridges the gap between the analyst and the data.

How many folks know how to use Excel? (imagine you are at the front of room, you'd probably see every hand go up) What makes Excel so popular? It's powerful. It's easy.

How many folks have ever requested some type of BI report? (fewer hands)

How many folks think there is a fast turnaround time for BI reports? (probably no hands)

Why? Traditional BI has often required large, specialized projects that quite frankly take a lot of time and money to complete.

If you can get the data out to your users, why not let them create their own dynamic reports? If they know how to use Excel, leveraging PowerPivot (in either form - client or server) is a logical next step. As with any well-planned SharePoint deployment, work on finding your trusted PowerPivot authors - not everyone should be allowed to publish PowerPivot workbooks. As with any service, you need to consider the workloads and behaviors to ensure the service works as expected for all users.

PowerPivot bridges the gap like no other Microsoft tool can... It is an engine that uses the familiar Excel experience for ui elements and it is not a completely different developer toolset like other BI tools.

PowerPivot is industrial strength BI that can be delivered directly to your business units.

See you on the next post!
-Maurice

Posted @ 4:00 PM on Monday, August 23, 2010 | Comments:

Sandbox Tiers

Posted by Maurice Prather

How many of you have started using Sandboxed Solutions? What's the first thing you do in order to make them work? Start the Sandboxed Code Service.

For the most part, once the service is started, most folks don't think twice about the configuration. That's pretty nice in many ways - it clearly shows the service is pretty easy to activate and use.

What about optimizing the service?

Tweaking the configuration is rarely done. Why? Quite honestly, it's probably due to the following:

a. Little information on what can be changed and how it should be
b. No clear guidance on how to collect metrics to validate those changes

Today we'll dive into the details of the service so that we can understand what can be changed and why you may want to consider changing the default values. Validation of those changes (i.e. metrics gathering) will be saved for another post.

Let's look at the basic building blocks of the Sandboxed Code Service. There are primarily 3 constructs that you should be aware of…

Sandboxed Code Service can be run on any member of the SharePoint farm.
The Sandboxed Code Service uses Tiers to define where code is run. By default, SharePoint creates one tier where all code is run.
A Sandbox Tier determines how many resources (# of worker processes, connections, appdomains) are provided for code execution.

Let's dive into the details of each of these elements...

Service

The first and easiest piece of the puzzle is the fact that the Sandboxed Code Service is in fact a service. This effectively means the service can run on any member of the SharePoint farm. Starting and stopping the service is fairly straight forward.

The key point to take away here is isolation. The server that is running the sandboxed code can be different than the web server that is actually serving the page request.

The service will load balance requests for sandbox execution amongst all the servers that are running the service.

Tiers

Once the service is up and running, the Sandboxed Code Service relies on Tiers to determine where code is executed.

Think of Sandbox Tiers as a resource pool. A Tier defines the following characteristics:

         • Maximum worker processes
         • Maximum application domains
         • Maximum connections per process
         • Maximum resource value

By default, SharePoint creates 1 tier which will serve all sandboxed execution requests (per the maximum resource value). The default tier also defines 1 worker process and 1 connection per process.

Once a load balanced request is sent to server N, the Sandboxed Code Service on server N will then determine which Tier the execution request will serve that Tier. The tier is chosen based on the maximum resource value (reference: ResourceMaxValue property). Please note, the current msdn text indicates the max value should be less than or equal to 1 but that is not the case.

Resources
As mentioned in the preceding section, Tiers can be conceptually thought of as resource pools. Each Tier allows administrators to how sandboxed solutions are executed.
In short, adjusting the resource values of a Tier is an exercise in risk management. What do you provide to "good" solutions versus "bad" solutions?  How much are willing to loose if something forces the sandbox worker processes to crash?

Before we get too deep into why/when/where of Tiers, let me throw out an important fact - the only way to manage the Sandbox configuration is via PowerShell. The Central Admin UI only allows you to start and stop the service. As a reference, the remainder of this blog post will reference the following PowerShell script…

$sandbox = [microsoft.sharepoint.administration.spusercodeservice]::local
 
## Delete the default tier
$sandbox.Tiers.Add("myTier")
$sandbox.Tiers["myTier"].MaximumWorkerProcesses = <TBD>
$sandbox.Tiers["myTier"].MaximumConnectionsPerProcess = <TBD>
$sandbox.Tiers["myTier"].MaximumAppDomainsPerProcess = <TBD>
$sandbox.Tiers["myTier"].ResourceMaxValue = <TBD>
$sandbox.Tiers["myTier"].Update()

Why are Tiers important? In other words, what is wrong with 1 tier?
Technically, there is nothing wrong with using only 1 Tier. As I mentioned earlier, the default configuration sets up 1 tier that is used by all user solutions.

However, if you really want to scale up your sandboxed operations, creating and managing multiple Tiers is a requirement. Tiers were specifically designed to address "bad code" scenarios. It will be your job, as an administrator, to ensure "bad code" has the least impact on your users and system.

Let's try illustrate the risk management problem is by looking at a few different examples…

Scenario 1: You simply bump up the value of MaximumConnectionsPerProcess from the default value of 1, but leave everything else as is. What happens if the worker process crashes and all available connections are being used when the crash occurs? Well, whatever those users were doing is now completely non-operational and/or has been left in a bad state (some work was done before the crash).

Scenario 2: You have code that historically takes a long time complete and is highly likely to time out. The Sandbox will time out the process, but what happens if the Maximum Worker Process and Maximum Connections per Process values are low? The user experience is probably going to be sluggish especially with high user requests. Not only will existing users have to wait for the existing processes to complete but new users may run into a scenario where there is no opportunity to execute because all processes and connections are taken.

Understanding how a Tier's ResourceMaxValue comes into play is crucial. Once a server has received a request to run code in the sandbox (i.e. the load balancer has already made a decision who is running the code), the target server must then determine which tier is actually responsible for executing the code. SharePoint keeps a tally on the average # of resource points that each user solution consumes. Tiers effectively allow you to balance "good" solutions into what is considered "high density" configurations (tiers with lots of worker processes, connections, app domains) and "bad" solutions can be served by "low density" configurations (tiers with fewer worker processes, connections, app domains).

For example, let's say that you have 3 Tiers called A, B, C. Those tiers have ResourceMaxValue set to .25, .50, and Int32.MaxValue, respectively. A solution with an average resource value of .10 points per request is queued, it will run in Tier A. A solution with .40 points per request will run in Tier B. Any solution with more than .50 points per request would be served by Tier C. Heavy operations drive up your average resource consumption; in other words, smaller values represent solutions that have least impact. In this case, we'd configure Tier A to have the highest density, Tier C would have the lowest density, and Tier B would probably be somewhere in between A and C.

Where should you start? The current line of thought is to at least increase the worker process count to the # of cpu cores plus 1, especially for those servers that are dedicated sandbox servers. From there, the task of defining how many tiers and how they are individually configured is much more involved.

In an upcoming post, I'll try to tackle how best to monitor solutions and the Sandboxed Code Service to help you effectively setup your Tiers.

-Maurice

Posted @ 10:09 PM on Monday, August 16, 2010 | Comments:

PowerPivot's service redirector cannot handle managed paths

Posted by Maurice Prather

If you're familiar with PowerPivot's default view of a PowerPivot gallery, you will see 2 buttons on the right hand side. The first button allows you to create what is known as a thin workbook. The second button takes you the scheduling page. Today's discussion is around the first button...

When you create a new linked workbook, an ODC file is created and passed back to the client. The ODC file is then used to call into PowerPivot's ServiceRedirector via msolap 10. The redirector allows the system to serve up the request for the linked workbook.

This works like a charm until your site lives under a managed path. If your site lives under a managed path, the following basically occurs:

An ODC file is created and handed off to the Excel client.
Excel attempts to open the data source by calling into PowerPivot's service redirector. (steps 1 and 2 are normal)
The service redirector falsely checks the root site of the web app (note: not the root of the site collection) for the existence of the workbook. This is a big problem!
On failing to existence check, the client is sent an authorization failed notification and, in the case of Excel, a terribly old looking authentication dialog is presented.
The dialog is called "Multidimensional Connection 10.0" and is designed to prompt the user for credentials.

Well, at this point in time, you're pretty much done. No matter what you enter for credentials, you will never be able to go further than that dialog.

The basic problem starts with the call to the redirector. Even though my workbook may live in a managed path, the call to the service redirector does not reflect that structure.

For example, if my workbook lives at http://projects.example.com/sites/DepartmentProject1/documents/test.xlsx, where my managed path is /sites, most experienced SharePoint folks would expect the following service redirector call:

http://projects.example.com/sites/DepartmentProject1/_vti_bin/PowerPivot/SecureRedirector.svc

Instead, the call is made to ...

http://projects.example.com/_vti_bin/PowerPivot/SecureRedirector.svc

I've gone through this scenario a few different times and varying levels of detail. In each test case, I can see that PowerPivot's service redirector is in fact querying the wrong SPWeb during the existence checks. Instead of looking at /sites/DepartmentProject1 for the target workbook, it looks in the root ("/"). Since the workbook clearly does not live in the root, the check fails.

Best I can tell, there is no way around this problem. This has fairly large ramifications when planning your topology. If I figure something out or hear an update, I'll post an update.

-Maurice

Posted @ 8:25 AM on Tuesday, August 10, 2010 | Comments:

(Items 1 to 5)