The switch back to SSRS Native mode for Performance Reasons

I finally got around to posting a blog entry I had written a couple weeks ago about how to switch from SSRS Native to Integrated Mode. Well... Now I am having to switch everything back to Native Mode due to pitiful performance we were getting when displaying a report (using Integrated mode) in a web part.

My colleague, Melissa Coates did a wonderful job writing up our experiences with poor performance from SSRS, and I would recommend anyone considering a switch to SSRS Integrated mode read it.

Needless to say, I’m quite disappointed in having to revert back, as I was really looking forward to taking advantage some of the native SharePoint benefits of having the report rdl stored directly in a SharePoint library—we were going to have some custom workflows, approval process, an easy place to store metadata, and of course versioning.

No need to go into too much detail here on the reasons why, as Melissa wrote it all up so elegantly.

To sum it up briefly, Integrated mode makes an incredible amount of small requests between the web front end and the SSRS server, and none of the images can be cached because they are created on the fly—even if the same image (such as an up arrow trend indicator) are already displayed on the page 100 other times. Also, many of these requests appear to be done in a single thread, so there is a boatload of time waiting for a response for even the simplest of image or indicator.

And, if all of that extra overhead wasn’t enough, SharePoint 2010 uses AJAX in their new report viewer as opposed to the IFRAME method they used in previous version. While, I do like the AJAX control for other reasons, it has the nasty side affect of more synchronous-type processing when there are multiple report viewers on one page.

So, bottom line, we’re going back to native mode. And, thanks Melissa for your much more verbose and analytical write-up of the issues.

How to Switch from SSRS Native Mode to SharePoint Integrated Mode

I have been utilizing SSRS Native mode for a while, and recently, after setting up numerous environments with SharePoint and SSRS Native Mode, a decision was made to switch everything to SharePoint Integrated mode.

So I started out on my development box uninstalling Reporting Services, and reinstalling. This took forever, and as it turns out was completely unnecessary.

Below are the steps to switch an existing SSRS server to native mode. Note, that this does not include any steps for migrating your reports—you’ll have to do that separately. Also note, that you create a new SQL server instance with Reporting Services and have both Native and Integrated mode, but for my needs, I wanted to do a complete switch.

Switching to SSRS Integrated mode:

1. Open SQL Reporting Services Configuration Manager on your SQL Server:
   
   
2. Click on Databases, and note the current “Report Server Mode” is “Native”
   
   
3. Select Change Database
   
   
4. Select “Create a new report server database,” and click “Next”
   
   
5. Enter your server name and credentials, and select “Next”
   
   
6. Select a new “Database Name” and change “Report Server Mode” to “SharePoint Integrated Mode,” and click “Next”
   
   
7. Enter authentication information, and click “Next”
   
   
8. Verify Summary, and ensure “Report Server Mode:” is set to SharePoint integrated
   
   
9. Hopefully, everything went successfully!! Click Finish
   
   
10. In Reporting Services Configuration Manger, click on Web Service URL, and then click the URL link to open
    
    
11. You should get an error that reads something like this:
    

    Reporting Services Error:

    The report server has encountered a configuration error. If the report server is configured to use SharePoint integrated mode, verify that the server is joined to a SharePoint farm and that the Report Server service account has been granted access to the SharePoint farm. (rsServerConfigurationError)
    
    
    
    This error is expected, because we need to configure Reporting Services Integration in SharePoint. Continue to next step.
    
    If instead you get a something like this:
    
    Reporting Services Error
    
    The configuration parameter SharePointIntegrated is set to True but Share Point Object Model cannot be loaded. The error was: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
    File name: 'Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'
       at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpSecurity.set_StaticCatchAccessDeniedException(Boolean value)
       at Microsoft.ReportingServices.SharePoint.Server.SharePointServiceHelper..ctor()
       at Microsoft.ReportingServices.SharePoint.Server.SharePointServiceHelperFactory.get_ServiceHelper()
    
    
    
    That’s a different issue entirely. That error is due to your SharePoint being configured in Farm mode, and the SQL server is not joined to the SharePoint Farm. That’s a bit out of scope for this post, but essentially, you just need to do a minimal installation of SharePoint on the SQL server and join it to the SharePoint Farm. More information on that can be found on msdn:
    How to: Install and Configure SharePoint Integration on Multiple Servers
    How to: Install a SharePoint Web Front-end on a Report Server Compter
    
    Note that if you did get that error, you must add the server to the farm before continuing to next step.

12. Open up SharePoint Central Administration:
    
    
13. Go to “General Application Settings,” and click “Reporting Services Integration:”
    
    
14. Fill out “Report Server Web Service URL,” and your credentials:
    
    Note that the Web Service URL should be what was specified in the Report Services Configuration Manager:
    
    
15. If All went well, you should get a confirmation with lots of wonderful green success messages:
    
    
16. Just to verify, now try going to the Web Service URL again in a browser, and you should get this:
    
    

That’s It!!  All done. Now your running in Integrated mode, and can start work on migrating your reports to SharePoint Libraries, and utilizing the updated Report Viewer Web Part.

Powershell Pause - Ask to Continue Script

I have been working on some SharePoint PowerShell scripts to automatically restore a SharePoint site collection (often to a different domain), add new users to the site, update master page, etc.

Because often I had one PowerShell script that did many things, sometimes, especially when developing them, I didn't actually want to perform every step of the script. So, one of the first things I did was create a function "AskToProceed" that would prompt me before each step, and I would have the option to perform that step, or skip it.

I have found this script to be particularly useful, and although simple, I thought I would share it.

# function AskToProceed
#   Prompt user to proceed
#   Parameters:
#  $TextQuestion - question to pose
#  $Default - default answer - should be 'y' or 'n'
function AskToProceed {
param ($TextQuestion, $Default)
Write-Host -NoNewline "$TextQuestion [y/n]($default): "
$ans = $host.UI.RawUI.ReadKey("IncludeKeyUp")
Write-Host
if ($ans.character -eq 13) {
if ($Default -eq "y") {
return $true
} else {
if ($Default -eq "n") {
 return $false
}
}
} else {
if ($ans.character.ToString() -eq "y") {
return $true
} else {
if ($ans.character.ToString() -eq "n") {
 return $false
}
}
}
# unknown response, ask again
return AskToProceed $TextQuestion $Default
}

Here's how you would invoke it-below is a script to restore a site collection backup file:

$SiteCollection="http://localhost/MySiteCol"
$SiteCollectionBackupFilename = "d:\backups\MySiteColBackup.bak"
# restore the site
if (AskToProceed "Restore site collection backup '$SiteCollectionBackupFilename' to $SiteCollection" "y") {
Restore-SPSite -Identity $SiteCollection -Path $SiteCollectionBackupFilename -Force
}

When ran in console, the following is displayed:

As you can see, it's waiting on my input. The only valid keys are:

• 'y' - Performs the restore
• 'n' - Skips the restore
• [enter] - Performs default action-in this case, it's 'y' which I specified as a parameter

I also could choose to abort the script entirely by pressing [ctrl]-c.

Now I find myself always throwing an "if AskToProceed" check before all of my commands.