PowerShell Script To Configure SQL Server Reporting Services In SharePoint Mode

In this blog you will learn about PowerShell Script to configure SQL Server Reporting Services in SharePoint Mode.

The below mentioned script will take care of the steps listed below,

1. Installs Reporting Services service and service proxy, and starts the service.2
2. Creates a service proxy named “Reporting Services”.
3. Creates a Reporting Services service application named “Reporting Services Application”.
4. Enables the Power View feature for a site collection.

#Script for SSRS Configuration in SharePoint Integrated mode

 

$starttime=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime

Write-Host -ForegroundColor Green "Import the SharePoint PowerShell snappin"
Add-PSSnapin Microsoft.Sharepoint.Powershell –EA 0

Write-Host -ForegroundColor Green "Install SSRS Service and Service Proxy, and start the service"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"

Write-Host -ForegroundColor Green "Install the Reporting Services Shared Service"
Install-SPRSService

Write-Host -ForegroundColor Green " Install the Reporting Services Service Proxy"
Install-SPRSServiceProxy

# Get the ID of the RS Service Instance and start the service
Write-Host -ForegroundColor Green "Start the Reporting Services Service"
$RS = Get-SPServiceInstance | Where {$_.TypeName -eq "SQL Server Reporting Services Service"}
Start-SPServiceInstance -Identity $RS.Id.ToString()

# Wait for the Reporting Services Service to start...
$Status = Get-SPServiceInstance $RS.Id.ToString()
While ($Status.Status -ne "Online")
{
Write-Host -ForegroundColor Green "SSRS Service Not Online...Current Status = " $Status.Status
Start-Sleep -Seconds 2
$Status = Get-SPServiceInstance $RS.Id.ToString()
}

$time=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime
write-host -foregroundcolor DarkGray $time

Write-Host -ForegroundColor Green "Create a new application pool and Reporting Services service application"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
Write-Host -ForegroundColor Green "Create a new application pool"
#!!!! update "-Account" with an existing Managed Service Account
New-SPServiceApplicationPool -Name "Reporting Services" -Account "<domain>\User name>"
$appPool = Get-SPServiceApplicationPool "Reporting Services"

Write-Host -ForegroundColor Green " Create the Reporting Services Service Application"
#!!!! Update "-DatabaseServer", an instance of the SQL Server database engine
$rsService = New-SPRSServiceApplication -Name "Reporting Services Application" -ApplicationPool $appPool -DatabaseName "Reporting_Services_Application" -DatabaseServer "<server name>"

Write-Host -ForegroundColor Green "Create the Reporting Services Service Application Proxy"
$rsServiceProxy = New-SPRSServiceApplicationProxy -Name "Reporting Services Application Proxy" -ServiceApplication $rsService

Write-Host -ForegroundColor Green "Associate service application proxy to default web site and grant web applications rights to SSRS application pool"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
# Associate the Reporting Services Service Applicatoin Proxy to the default web site...
Get-SPServiceApplicationProxyGroup -default | Add-SPServiceApplicationProxyGroupMember -Member $rsServiceProxy

$time=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime
write-host -foregroundcolor DarkGray $time

Write-Host -ForegroundColor Green "Enable the PowerView and reportserver site features"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
#!!!! Update "-url" of the site where you want the features enabled
Enable-SPfeature -identity "powerview" -Url http://server/sites/bi
Enable-SPfeature -identity "reportserver" -Url http://server/sites/bi

####To Verify, you can run the following:
#Get-SPRSServiceApplication
#Get-SPServiceApplicationPool | where {$_.name -like "reporting*"}
#Get-SPRSServiceApplicationProxy