Troubleshooting PowerPivot for SharePoint Solution Deployments

The configuration of PowerPivot for SharePoint entails a critical phase concerning the deployment of multiple solution packages (.wsp files) on all servers in the farm. If you run the PowerPivot Configuration Tool for the first time in a farm, you can see the various steps this phase includes under Add and Deploy PowerPivot Solutions, as in the following screenshot.

In a single-server environment, the PowerPivot solution deployment is usually uneventful, but multi-server farms are more complex, providing more opportunities for the solution deployment to fail. There are a number of reasons—a SharePoint server in the farm might be temporarily unavailable, or the SharePoint Timer service isn’t running, or the SharePoint Administration service is stopped. These are the typical causes. But why is an unavailable service on server A causing the PowerPivot Configuration Tool on server B to fail? Let’s take a look.

For illustration purposes, I’m using a test environment with three separate SharePoint servers, as shown in the following figure. The front-end servers (WFE01 and WFE02) only run SharePoint Web applications. The application server (APP01) runs SharePoint shared services as well as the SQL Server database engine hosting the SharePoint configuration and content databases. I also installed PowerPivot in SharePoint mode on APP01 and now I am running the PowerPivot Configuration Tool on this computer. One other thing worth mentioning is that I stopped the Timer and Administration services on WFE01 before I started the PowerPivot Configuration Tool. My config tool now happily begins its work, adds the PowerPivot farm and Web application solutions to SharePoint, but then it reaches the Deploy Farm Solution task, stays there for a while, and eventually times out and fails. It looks quite bad. The farm is now in an inconsistent state. Fortunately, the situation is not as problematic as it seems at a first glance.

So, let’s switch from the PowerPivot Configuration Tool to the standard SharePoint tools and cmdlets to investigate and solve this problem. For SharePoint troubleshooting, I generally prefer to use the Management Shell because the command line provides more capabilities than Central Administration. So, let’s start SharePoint Management Shell with elevated permissions and then copy and paste the following lines:

1. Are the PowerPivot solutions in the SharePoint Solution Store?

Get-SPSolution

Yes, the PowerPivot Configuration Tool has successfully added the PowerPivot solution packages for farm (powerpivotfarm.wsp) and Web applications (powerpivotwebapp.wsp) to the SharePoint solution store. So far so good!

2. What is the deployment status of the PowerPivot solutions?

The solution store is essentially a table in the configuration database that simply stores the .wsp solution packages. Yet, adding a .wsp package to this store doesn’t automatically mean that the solution has been deployed on each server in the farm. The actual deployment of the solution packages is the job of the Timer service running on each SharePoint server. The Timer service retrieves the .wsp packages from the solution store, extracts the files and assemblies from the packages, and deploys them on the local server. You can use the following command line to check the actual deployment status:

Get-SPSolution "powerpivotfarm.wsp" | Format-List

As you can see, the PowerPivot farm solution was deployed successfully on WFE02 and APP01, but not on WFE01, which is not astonishing because I stopped the Timer and Administration services on this computer, as mentioned earlier. If the Timer service isn’t running, it cannot deploy the PowerPivot solutions.

3. Are there any pending deployment jobs for the PowerPivot solution?

Let’s pretend we don’t know the state of the Timer service on WFE01. Even then, you can quickly notice that not all servers in your farm have processed the solution deployment job because the JobStatus still shows Scheduled. This means that at least one Timer service instance has not yet done its work. You can use the following line to list all scheduled deployment jobs:

Get-SPTimerJob -Type Microsoft.SharePoint.Administration.SPSolutionDeploymentJobDefinition | Format-List

The Timer job title reveals that this is the deployment job for the PowerPivot farm solution. Note that the job id in the output is 2d535c93-b838-484e-9528-e3250c2e8318. This is an important parameter for the next step.

3. Did any errors occur during solution deployment on any of the servers?

By using the following command, you can check if the deployment succeeded on all of the servers that already processed the solution deployment job in the farm.

Get-SPTimerJob -Id 2d535c93-b838-484e-9528-e3250c2e8318 | ForEach-Object {$_.HistoryEntries}

Both, APP01 and WFE02 have successfully deployed the solution. No issues here.

4. What’s the status of those servers that haven’t processed the deployment job yet?

The first thing to check on a delinquent SharePoint server is the state of the Timer and Administration services. Note that the Timer service depends on the Administration service to carry out the deployment work because the Timer service usually runs under a low-privileged service account that might not have the required permissions to deploy assemblies in the Global Assembly Cache and so forth, while the Administration service uses the high-privileged Local System account with full access to all local resources. In most cases, restarting these two SharePoint services retriggers the solution deployment and yields a successful completion.

Let’s go one step further in this troubleshooting exercise, disable the Administration service and then restart the Timer service. In this erroneous configuration, the Timer service picks up the deployment job but fails to process it. If you check the status of the deployment job as in the previous step, you can see that the processing was aborted because the administration service on WFE01 is not started. A very clear and actionable error message!

Of course, if you started the Administration service and perhaps restarted the Timer service one more time, the job would complete successfully. The PowerPivot farm solution would be deployed. But let’s not choose this easy route.

5. How do you manually repeat the solution deployment?

Let’s go one step further in the wrong direction and delete the current solution deployment job without completing it on WFE01. Needless to say that you should not do this on a production system!

Get-SPTimerJob -Id 2d535c93-b838-484e-9528-e3250c2e8318 | ForEach-Object {$_.Delete()}

As expected, the PowerPivot farm solution deployment is now in really bad shape. It’s deployed inconsistently across the farm. Some servers have the PowerPivot files, others don’t. And if you run the command:

Get-SPSolution "powerpivotfarm.wsp" | Format-List

You can notice that the DeploymentState is NotDeployed, the JobStatus is empty, and the JobExists status is False. In this or any other case where the solution deployment status across the farm is unclear and there is no deployment job scheduled, I recommend repeating the Install-SPSolution command that the PowerPivot Configuration Tool originally issued to recreate the solution deployment job.

Install-SPSolution -Identity PowerPivotFarm.wsp -GACDeployment –Force

Note that you don’t need to remove or add the PowerPivot solutions again to SharePoint, such as by using the Remove-SPSolution or Add-SPSolution cmdlets, because a failed deployment job does not affect the .wsp files in the solution store. The .wsp files in the solution store are OK.

Make sure all Timer and Administration services are running on all SharePoint servers in your farm. Check the solution and deployment job status as illustrated in the previous steps, and if a SharePoint server still stubbornly refuses to deploy, restart the server or the SharePoint services, check network connectivity and access permissions to the configuration database, consult the Windows Event Log and SharePoint ULS logs, and resolve any SharePoint issues you discover.

In the majority of cases, restarting the SharePoint services will do the trick. Once the PowerPivot farm solution is deployed, you can return to the PowerPivot Configuration Tool and click on Validate again. The tool will detect the farm’s new configuration state so that you can continue at the right point with the remaining steps, as in the following figure.

And that’s it! In summary, the PowerPivot Configuration Tool cannot compensate for configuration problems and operational issues at the SharePoint level. If a farm is not fully functional, if servers are down, or if important SharePoint services are stopped for any reason, then the PowerPivot solution deployment will fail. However, the PowerPivot Configuration Tool is designed to accommodate these situations by letting you switch to standard SharePoint tools and cmdlets for troubleshooting. Once the problem is resolved, you can switch back and continue with the remaining steps to finish the PowerPivot configuration successfully. In subsequent blog posts, I will show you how to troubleshoot other common issues that you might encounter at later stages in the configuration tool. Stay tuned!