SSRS SharePoint Mode Database migration

During the testing phase of the migration of our SSRS SharePoint mode from SharePoint 2013 to SharePoint 2016, I went out through some small issues. Here’s a quick recap.

  • Dismount, mount SPRS database
    After dismounting the fresh new RS database from the service application and mounting the migrated RS database, perform an IISReset in order to refresh the service application proxy.
$DB_OLD = <FreshSSRSDB>
$DB_Migrated = <SSRSDBFromSP2013>
$SSRSServiceApp =
Dismount-SPRSDatabase -Identity $DB_Old -Confirm:$false
Remove-SPRSDatabase -Identity $DB_Old -Force -Confirm:$false
Mount-SPRSDatabase -Name $DB_Migrated -ServiceApplication $SSRSServiceApp -DatabaseServer <SQLServer>
  • Reporting Services Databases status
    Review the status of the migrated databases in SharePoint and make sure they are online.
$DBs_Offline = Get-SPDatabase | Where-Object type -like ‘*Reporting*’ | Where-Object status -eq ‘Online’
if($DBs_Offline) {
foreach($DB_Offin$DBs_Offline) {
Write-Warning”Changing RS database ‘$($DB_Off.Name)’ status to ‘Online'”
$DB_Off.Status=’Online’
$DB_Off.Update()
}
}
  • Grant sufficient permissions on the RS databases
    Once the RS databases are mounted in SharePoint, you should run the SQL script granting permissions.
$sb_DBPermissions_ServiceAct = {
Add-PSSnapIn*sharepoint*
$RS_AppPoolAccount=(Get-SPRSServiceApplication).ApplicationPool.ProcessAccountName
(Get-SPRSDatabaseRightsScript-UserName $RS_AppPoolAccount-DatabaseName (Get-SPRSDatabase)-IsWindowsUser).Replace(‘ReportingServiceDatabase Name=’,”)
}
$sb_DBPermissions_CentralAdminAct = {
Add-PSSnapIn*sharepoint*
$RS_AppPoolAccount=(Get-SPServiceApplicationPool|Where-Object name -like’*security*’).ProcessAccountName
(Get-SPRSDatabaseRightsScript-UserName $RS_AppPoolAccount-DatabaseName (Get-SPRSDatabase)-IsWindowsUser).Replace(‘ReportingServiceDatabase Name=’,”)
}
$RS_SQL_GrantRights_ServiceAct = Invoke-Command -ScriptBlock $sb_DBPermissions_ServiceAct -ComputerName $SPServer_New -Credential $cred -Authentication Credssp
$RS_SQL_GrantRights_CentralAdminAct = Invoke-Command -ScriptBlock $sb_DBPermissions_CentralAdminAct -ComputerName $SPServer_New -Credential $cred -Authentication Credssp
Import-Module ‘SqlServer’
Invoke-Sqlcmd -ServerInstance <SQLServer> -Query $RS_SQL_GrantRights_ServiceAct
Invoke-Sqlcmd -ServerInstance <SQLServer> -Query $RS_SQL_GrantRights_CentralAdminAct
  • SQL Server Agent is running
    Make sure the Central Administration is configured with Kerberos if you want to see the status of the SQL Server Agent in the “Provision Subscriptions and Alerts” page.
  • Review the Reporting Services webservice logs
    Logs are located in the SharePoint Hive :
    c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\WebServices\LogFiles
$Cred = Get-Credential
$SPServer = <SPServer>
$RS_Log = “\\{0}\c$\Program Files\Common Files\microsoft shared\Web Server Extensions\{1}\WebServices\LogFiles\”
$sb_Auth = {
Add-PSSnapIn*sharePoint*
[PSCustomObject] @{
RS_Instances = Get-SPServiceInstance | Where-Object typename -like ‘*Reporting Services*’ | ForEach-Object {$_.Server.address}
RS_SPVersion = (Get-SPFarm).BuildVersion.Major
}
}
$RS_ServiceInstances = Invoke-Command -ScriptBlock $sb_Auth -ComputerName $SPServer -Credential $cred -Authentication Credssp
$RS_ServiceInstances.RS_Instances | ForEach-Object {
$RS_Log_Path=$RS_Log-f$_,$RS_ServiceInstances.RS_SPVersion
$Latest_LogFile=Get-ChildItem-Path $RS_Log_Path-Recurse -File | Sort-Object -Property Name -Descending |Select-Object-First 1
Write-Warning” — Showing logs from server $_ — “
Get-Content-Path $Latest_LogFile.FullName-Tail 15
}

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s