By: Koen Verbeeck | Comments (2) | Related: > Upgrades and Migrations
Problem
Recently we were upgrading some SQL Server 2012 service pack 1 instances to the latest cumulative update available at the time. The reason for this upgrade was the added functionality to create Power View reports on top of Analysis Services Multidimensional cubes. We encountered some issues during the upgrade process, such as the set-up wizard failing or not even starting at all. This tip will show you how we resolved these issues.
Solution
Two issues were encountered during the upgrade process and they both had distinct root causes so I'll treat them separately.
The magical SQL Server setup disappearing trick
The upgrade process on the first instance went like a blast: nothing went wrong and it finished in a reasonable amount of time. The second instance - which hosts the SharePoint databases and Reporting Services - had a strange issue during setup. After downloading and extracting the CU file, we launched the upgrade process by double clicking the .exe file. This launches a self-extracting process, which extracts the set-up files to a temporary folder on the drive with the most free disk space.
Normally after a few seconds the following pop-up should appear, telling you to hang on while setup loads.
However, the pop-up screen never appeared. Not even after an hour. Something is wrong. Where is the setup wizard? The first reaction was to check the SQL Server setup log files. These are located in C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log (depending on your installation and version of course). Nothing relevant to find there. The latest log file was from a few months ago. Maybe for some obscure reason the setup didn't have permission to write to the log folder (I've seen it happen with SQL Server Agent accounts). It seems strange as the setup was launched with administrative permissions, but you never know for sure with software, so we temporarily gave write permissions to the log folder to Everyone. Restart setup, still nothing. Remove write permissions again for Everyone and still made sure built-in admins has full control.
Next step was to check the event log, but nothing to find there. So I changed the .exe extension of the update file to .zip and extracted the set-up contents to a local folder (this works, try it out). Run the setup.exe directly. Again, nothing happened. Then I tried to start setup through the command line. It shows two lines of Microsoft copyright, then it finished. Without doing anything!
At this point I'm already going mental, until a colleague stumbled upon this blog post which mentions a little log file created in the %temp% folder (C:\Users\
The error is quite straight forward: Cannot load .NET security policy file
A quick Google search led me to this blog post: Applying SP2 on SQL Server 2008 and it shows black CMD screen and then it disappears. Sounds familiar? The blog post suggests to reset the .NET security policy using caspol.exe with the following command:
And indeed, after issuing this command against the different .NET frameworks on the system, setup started without a problem and the upgrade finished successfully!
SQL Server setup failing and rolling back
On another instance, SQL Server setup started without a problem, but somewhere half way through the upgrade process it failed and rolled back. Important to note is that this server instance is one of the SharePoint servers and doesn't host any real SQL Server services, except the Reporting Services integration with SharePoint. During setup, you can only see a few shared features selected, together with the RS add-in.
Upgrading this server is important, as it hosts Power View and that's what we want to use. Time to check the SQL Server setup logs, which were used this time. The summary log gives the following explanation:
The patch installer has failed to update the shared features.
This doesn't tell us much, as we were only upgrading shared features. On the detail log, where we find the following error:
Error: Failed to run patch request for instance.
Seems like a dead end. However, in the _SHARED_ folder, there was a rather large log file called rsSharePoint_Cpu64_1.log. This log file contained the following message:
Product: Microsoft SQL Server 2012 RS Add-in for SharePoint -- Installation failed.
OK, still no clear error on what happened, but we have the source where it is coming from: the Reporting Services add-in. A quick Google search brought me to this TechNet page: Install or Uninstall the Reporting Services Add-in for SharePoint. This page mentions where the installer puts the log files. You might already guessed it: the %temp% folder! Here we can found a log file called RS_SP_0.log. And this log file reveals us the source of the problem:
An exception of type Microsoft.SharePoint.Upgrade.SPUpgradeException was thrown. Additional exception information: Failed to call GetTypes on assembly Microsoft.AnalysisServices.SPAddin, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91. Could not load file or assembly 'Microsoft.AnalysisServices.SPClient, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
It seems the RS add-in can't locate a specific Analysis Services dll. Another Google search brings us to this TechNet page: Install or Uninstall the PowerPivot for SharePoint Add-in. This paragraph is important:
A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including Application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the PowerPivot for SharePoint 2013 configuration tool.
I checked with the server admin and he confirmed spPowerPivot.msi had not run on that server. After running this .msi, the CU upgrade process finished successfully!
Conclusion
The moral of this story is: check the %temp% folder for recently created log files when something goes wrong during the SQL Server setup, especially when you don't find anything in the regular places.
Upgrading SQL Server can also affect Master Data Services. You can read more about this in the blog post Master Data Services stops working after an upgrade.
Next Steps
- If you haven't already, create a knowledge base of possible issues that can arise during a SQL Server upgrade, such as the problems described in this tip. You want to be prepared when things go awry on production.
- Perform the upgrade first on a development or test environment and document the progress, especially any difficulties you encounter and how you resolved them.
- Read more about Power View for Multidimensional models in this blog post: Power View for Multidimensional Models Released!.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
