Workbook is not in a trusted location

I’ve been working with a vendor to setup Microsoft Project Server on a client’s site, and they came across an issue of The Data Connection File … workbook is not in a trusted location.

The simplest answer is to go to Excel Services, and add the URL location to the Trusted Data Connections.  The only problem was that this setting did not seem to work.  Every reference to this error on the web indicated this was all that was required.  After trying a few different things, I attempted to add the trusted data connection library via PowerShell:

$excel = Get-SPExcelServiceApplication
New-SPExcelDataConnectionLibrary -Address"http://projectserver/BICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)/SQLSERVER_AnalysisServices_Database" -Description "Project Server DCL" -ExcelServicesApplication $excel

I attempted to load the spreadsheet and I did not get the error!  It came down to the fact that while looking through all the parameters for the PowerShell cmdlet of New-SPExcelDataConnectionLIbrary that I set a description for the entry for the trusted data connection.

After trying a few other connections, I also discovered that as well as having to set a description, I also needed to ensure I had the the full path to the data connection files including the document library and folder structure.  Also of note, you may need to wait a few minutes for the changes to take affect, there is some caching done by Excel services before it recognises the changes made to its configuration.

For reference, the system build this client was running was SharePoint 2010 SP1 with the June 2012 Cumulative Update applied.  Given that these particualar details are not mentioned elsewhere online, I wonder if this is perhaps a regression.  If what I have described addresses the same problem you are having with the error Data connection file … workbook is not in a trusted location, please leave a comment below with details of your farm build.

Problems with SQL 2012 Reporting Services

I finally had a chance to play with some new technology that one of our progressive clients wanted to adopt for delivering business intelligence to their London SEO company.  However I encountered some problems with SQL 2012 Reporting Services (SSRS) that I wanted to share.  In the past, I had become fairly proficient at the mix of SQL Reporting Services 2008 R2 and SharePoint 2010, and was keen to see how smooth it was for SSRS 2012.  As I discovered, I had a few issues getting this going successfully.

There are a number of excellent write-ups on how to install SSRS 2012.  One of my favourites is Todd Klindt’s guide to installing SSRS 2012 without upgrading your database server.  The process is fairly smooth, everything installs fine and the service application is created with minimal fuss.  So far this is much easier than its predecessor, SSRS 2008 R2.  But now it came time to test.

Problem #1

To test an installation of SSRS, I usually create an Assets Library, associate the Reporting content types and try and launch the Report Builder.  If I can get this far, I am usually fairly confident that everything is working (barring any issues connecting to the underlying data sources).  For SSRS 2012 I followed the same process.  However, when I tried to create a new report and launch Report Builder, I got this error:

SQL 2012 Reporting Services Error
This functionality is not supported because a Report Server URL has not been specified in Central Administration.

This was quite odd as I had followed all instructions to the letter, ensuring that everything was configured properly.  To troubleshoot this issue further I tried the same test on the application server (APP1) where SSRS 2012 was installed.  To my surprise this worked fine!  I went back to my computer and hit one of the web servers (WEB1) and got the error again.  A closer inspection of the content types shows a subtle difference:

SQL 2012 Reporting Services Content Type Comparisons

The image on the left, WEB1, has a different label for the content types (Report Server Content Types) than those on APP1 (SQL Server Reporting Services Content Types) – quite bizarre.  I would have expected that the content types are central to SharePoint, and not dependent on any particular server.  I downloaded the SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint 2010 technologies and installed it on WEB1 (and in this case WEB2, which also exhibited the same issue).  The process recycled the application pools and all of sudden the content types were the same!

This clearly means that these content types are not installed as a solution into the enviornment, and that the add-on is required on all servers in the farm for the correct SSRS content types to be available.

Problem #2

With the same client, I performed a similar configuration on their Test environment.  I was generating the as-built documentation and noticed that the Power View Integration feature was missing.  Having not done much with PowerView, I was a bit stumped as to why it was not showing.  Had I forgotten to install something off the media?

A quick bit of Googling told me that PowerView is installed as part of SSRS 2012.  OK, but why isn’t this working?  I decided to check over SSRS 2012 to make sure I hadn’t missed anything.  When I went to do my litmus test for SSRS 2012, there was a key set of content types missing:

SQL 2012 Reporting Services Missing Content Types
(“Report Server Content Types” are SQL 2008 R2 content types and not SQL 2012 contetn types, as we established for Problem #1)

Time to revisit this SEO Agency at SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint 2010 technologies.  Amongst the functionality listed that it provides is:

  • Power View, a feature of Microsoft SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition, is an interactive data exploration, visualization, and presentation experience.

In the Test environment, all of the SharePoint roles and SSRS 2012 was installed on a single server.  I would have imagined that the functionality provided by the add-in would have been installed as part of SSRS 2012, but and on closer inspection of Todd’s article I forgot to select the add-on specifically as an option.  After installing the add-on, I now had the Power View feature available:

SQL 2012 Reporting Services PowerView Feature


Key Learning for SSRS 2012

Having gone through two configurations with SSRS 2012, it is vital that the SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint 2010 technologies is installed – either off the media or from the download.  It was taken for granted that its predecessor was always installed for a SharePoint installation, so it was never an issue in the past.  It is also important to note that these components are not installed as a solution into the farm, and doing it on one server will not guarantee that all servers will have the correct bits installed.  If you are setting up SQL 2012 Reporting services, be sure to install this component on all servers in the farm!

SharePoint Virtualization Performance (and Why Less Is More)

SharePoint virtualization performance is not a new topic, but sizing the virtual resources can be an interesting task.  I was recently working with a client on their SharePoint 2010 environment and we were preparing it to take on an additional workload by installing Project Server.  One of the key discoveries was that their virtual machines were sized to only use one CPU core, and our performance testing indicated that this was a performance bottleneck.

The Microsoft hardware and software requirements for SharePoint 2010are quite clear that the minimum recommendation is to utilise 4 CPU cores.  It is so clear that they also state that if you are not using the recommended minimum requirements that your environment is in an unsupported state.  Despite this very explicit guidance, the client’s infrastructure team were opposed to utilising 4 CPU cores, and only wanted to allocate 2 CPU cores.

The infrastructure team’s main reasoning was that adding additional cores would cause performance issues in their production environment.  The rational was that with four cores, the hypervisor (in this case VMWare ESX) would have to wait for four cores to become available before any CPU tasks could be executed, effectively degrading performance of the virtual machines. (Their team had reference to an article entitled Virtual CPUs – The overprovisioning penalty of vCPU to pCPU ratios) .  From a SharePoint perspective, this put us in a dilemma as we wanted the environment to be in a  supported state.  Our other concern was overall SharePoint performance.  For this we conducted some benchmarking exercises in the client’s test environment.


SharePoint Performance Testing

SharePoint performance testing can be a bit of an art form.  I do not want to detract from the main focus on virtual machine sizing, so wish to summarise some of the key pieces of information about their environment:

  • SharePoint topology
    • 2 x SharePoint servers accepting web requests
    • 2 x SharePoint application tier servers
    • 1 x SQL database server, in process of re-engineering
  • From their existing environment (primarily a publishing intranet), it was determined that they had up to 200 unique users per hour, and this was our initial testing aim for concurrency – 200 users per SharePoint web server.  This also served as a basis for the modelling of the testing scenarios.
  • There were multiple scenarios tested, but for simplicity I am going to focus on side-by-side comparisons for using  1 vCPU, 2 vCPU and 4 vCPU configurations of the SharePoint servers responding to web requests.
  • This client have a number of number of new initiatives in the pipeline (eg collaboration sites, document management), and these are very difficult to estimate for usage and not considered in our tests.
  • Other testing conducted with 4 vCPUs found that 350 users per web server to be the sweet spot before performance degrades (total 700 concurrent users).  This equates to about 40% concurrency for their expected staffing levels.


Performance Testing Metrics

Performance testing can mean a lot of things to a lot of different people.  The testing we had been performing was around two main criteria:

  1. Is the system available to do my task?
  2. How long does it take to perform my task?

From this we devised three simple metrics to rate how the SharePoint environment was performing:

  • Requests per second as a measure of system throughput (we were still interested in system performance).
  • Average response time as a measure of system calculated end user experience.
  • Observed performance, how the system actually performed while under load.

The first two metrics are machine calculated, empirical results of the testing conducted.  The third metric was conducted by a human to measure the page load time, while the system was under the simulated load.

In the test cases below, we used our initial testing aim of 200 users per server to compare the variation with CPU cores.

The first two metrics were calculated from the Web Capacity Analysis Toolkit and the third metric was measured using Fiddler.


Metric 1 – Requests per second (system throughput)

SharePoint Virtualisation Requests per Second

The system throughput shows remarkable differences between 1, 2 and 4 CPU core configurations.  Jumping from 1 to 2 CPU cores is a 47% improvement in throughput, and from 2 to 4 cores is another 53% improvement.  Another observation was that VMWare reported 100% CPU usage with a single core.  When reviewing the system under load with 1 CPU core, it was clear the system was struggling.  Not only was the IIS worker process maxing out, but the SharePoint timer service was trying to execute on a regular basis and even the operating system was struggling to be able to schedule tasks to be processed.

On the other hand, with 2 or 4 CPU cores, the CPU usage did not max out at 100%.  The CPU levels barely rose above 70%, a strong indicator that an absolute minimum of 2 CPU cores are required.  This gets even more interesting when we start looking at response times.


Metric 2 – Average response time (calculated end user experience)

SharePoint Virtualisation Average Response Time

System throughput is nice to measure, as an engineer it gives us some numbers to show whether a system has improved or declined in performance.  The only problem is that end users are not concerned with these numbers, a more humane was to think about system performance is with response times, the time it takes to request an object from SharePoint.  The results shown here are more profound than the first metric of requests per second

With a single CPU core, the system calculated an average response time of 2000ms.  That equates to 2 seconds per request.  This may sounds relatively quick, so lets put that into perspective.

  • Average response time is: 2 seconds
  • Number of objects on a page (assuming an empty client cache): 88
  • Average time to load the page: 2 minutes 56 seconds

Three minutes to load a page!  That is definitely not an indication of good performance.  Lets do that exercise again for 2 and 4 cores with the same number of objects on the page:

[table]2 CPU cores, 4CPU cores

Average response time: 985ms, Average response time: 255ms

Average time to load the page: 1 minute 26 seconds,Average time to load the page: 22 seconds


Using 2 CPU cores is a vast improvement, and 4 cores makes a huge difference, however  22 seconds at a best case is still a long time to load a page.  In the example page I’ve presented, with a populated cache the number of objects to retrieve drops down to less than 10, and in all practicality should be quicker.  Testing of a full page load on an empty cache is an example of an edge case, but not entirely unreasonable to expect from time to time.

This metric gives a good estimate on the performance to expect from the perspective of a machine, so now it is time to look at how the system actually performed from the point of view of a user.


Metric 3 – Observed performance

SharePoint Virtualisation Total Execution Time

Automating load tests are a great way to gauge and measure performance.  The only issue is that these are machine perceived metrics, not human perceived metrics.  To gauge how the system would actually perform under load, the same steps in the scenario were conducted in the browser.  Then using Fiddler, it was possible to measure how long it took to perform each task.

In the model for these performance tests, the time to load all 9 steps without any activity on the server should take about 15 seconds.  This is represented by the green line.  This time is the measurement of elapsed time from loading a page to then end of the last object to be loaded.  Human interaction or wait times have been removed from this metric.

With a single CPU, the time to load the complete scenario was 3 minutes 52 seconds, an average of 25 seconds per task (load a page, open a PDF, perform a search etc).  I would classify this as an unusable system.

For a 2 CPU environment, the total time was 1 minute 45 seconds, an average of 12 seconds per task.  A much better improvement.  If this was the time during peak concurrency, this may be somewhat acceptable, but probably still frustrating.

With 4 CPUs configured, the total time drops down to 23 seconds, an average of 2.5 seconds per task. With this configuration, the system should be relatively quick to use.


Conclusions from Performance Testing

The results gives us some confidence that there is justification in using 4 CPU cores on each server.  Looking purely at the numbers it is fairly clear that 4 CPU cores is the way to go, as Microsoft had intended.

I should also point out that when looking at the performance metrics from VMWare, I didn’t notice any issues around RAM, disk or network for any of the servers in the farm.  I expected that the dedicated SQL server would be serving as a bottleneck, but it was predominately CPU processing on the SharePoint web servers that stood out as an issue.



I have access to a number of virtualization specialists in my organisation, so I put it out them in email the work I was doing and asked for their options on 2 vs 4 CPU cores.  What I received was an education on how hypervisors like VMWare and Hyper-V manage CPU resources.  Here is a summary of this wisdom:

  • Practical experience of the virtualization specialists in other systems shows that performance can be increased by decreasing 4 CPU core systems down to 2 CPU core systems, as the virtual machine does not need to wait until 4 CPU cores are allocated to it.
  • Using more than 1 CPU core will not provide any benefit unless you are running a multi-threaded application (and SharePoint certainly takes advantage of that).
  • The approach to take is to right-size the VMs from the outset, with the potential to scale-up (add virtual resources) or scale out (add SharePoint servers).  Unlike a physical server, a virtual machines requirements may and can change over time.  It is likely to have evolving resource requirements during its life-cycle.
  • Testing and benchmarking can give an estimate of performance behaviour, and even this will not be accurate.  It should be noted that the tests were performed in a non-production environment, and their was no CPU contention, unlike a production environment.
  • Maybe it is time to take a reality check on Microsoft minimum specifications, but it is difficult to ignore what is prescribed in black and white.  Myself and others in our team have been bitten by “non-compliance” in the past.  If you have a serious issue and you need to call Microsoft, you will need to have your SharePoint servers set with a minimum of 4 CPU cores.

Essentially everything I learnt confirmed the client’s beliefs that 2 CPU cores will perform better than 4 cores.  A complete mind shift on everything I previously thought and understood about SharePoint sizing and virtualisation.


Client Implementation

With all the new knowledge on virtualisation and the performance results in hand, I had a sit down meeting with the client’s infrastructure manager to discuss how to move forward.  Their SharePoint team and I were convinced that 4 CPU cores was what we will need at some stage in the future, and their infrastructure manager did not dispute that either.  This client is showing low usage patterns presently, and it is expected that adoption will be increasing over the next six months.  Ultimately we all agreed that 2 CPU cores was better than 1 CPU core, and we would assign each of the SharePoint servers with 2 CPU cores.  This was on the proviso that when we start to see measurable performance issues that we would revisit this topic and review the need to scale up (add CPU cores) or scale out (add an additional SharePoint web server).

Before I finish up I just want to call out that the above scenarios were conducted for a particular client with a particular workload modelled against their particular environment.  We expect the performance results to change when their workload or virtual infrastructure changes.  If you are using the performance results for any guidance, expect this to be different depending on your own workload and usage patterns.  I hope to explore more on the topic of performance benchmarking in a future post.



SharePoint 2013 Preview Unboxing Part 2

The second installment to my unboxing of SharePoint 2013 Preview has now been uploaded to YouTube.  In the first part I demonstrated how to get started from downloading the installation media, up to a configured site waiting for pages to load.

This particular video shows my struggle to get my shiny new site loaded.  I get the opportunity to check that ULS logging is working, attempt to create a web app, and finally get my site to display.  Ultimately the speed of my host caused the most problems, and I finally get some responsiveness once I assign an extra CPU core and bump the RAM from 4GB to 6GB.

The end result is a working team site, and if you cannot wait patiently as I fumble around with my virtual machine, skip ahead to the last couple of minutes.  I appologise if the video appears a bit choppy, I tried to shorten as many of the sequences as possible so you can see more of the action, and less of the progress circles going around.

The two unboxing videos that I have created demonstrate some of the basic steps to get SharePoint 2013 Preview installed.  I hope to get some time soon to stat looking at particular features of Central Administration and the sites themselves.