xSQLServerRSConfig: Reporting Services need to restart for Reports web site to work

[johlju]

Details of the scenario you tried and the problem that is occurring:
After configuring reporting services using xSQLserverRSConfig the 'Reports' web site does not work. After a restart of the 'ReportServer$SQL2016' service the Reports web site works.

Error:

Service Unavailable

HTTP Error 503. The service is unavailable.

The DSC configuration that is using the resource (as detailed as possible):

xSQLServerRSConfig 'InstallSSRS2016'
{
    InstanceName = 'SQL2016'
    RSSQLServer = $Node.NodeName
    RSSQLInstanceName = 'SQL2014'

    PsDscRunAsCredential = $SqlAdministratorCredential
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2012 R2, SQL Server 2016 & 2017, WMF 5.0

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
n/a

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
Dev (PR #575)

[bozho]

I've tried this on Win 2012R2 and SQL Server 2014 (default instance), it works without restarting the server. Are you deliberately using two different SQL instances (InstanceName = 'SQL2016', RSSQLInstanceName = 'SQL2014')?

[johlju]

I have not tried with RS in SQL2014 yet. I can do that.

Yes deliberately using SQL2014 for the database engine, and installing both RS for SQL 2016 and SQL 2017, all on the same box, side by side. Using the SQL2014 instance as database for both RS 2016 and RS 2017.
All of them are named instances.

This is my lab environment.

I will test a pure single server as you did above to verify. Named or default instance should not matter, but unsure.

[johlju]

Also... I have to restart the RS service for both 2016 and 2017 for Reports site to work in each.

[bozho]

Ok, no worries. It doesn't hurt to restart when initialising RS.

[bozho]

Where would you prefer me to fix this? #569 or #570?

[bozho]

Another thought - I've noticed that sometimes the RS website needs a long time to "warm up". For example, my DSC configuration assigns some roles on the report manager site right after initialising SSRS.

I've seen runs where it takes almost 5 minutes to make the connection (could also be my crappy VM :) I'm actually using [System.Net.WebRequest] with a long timeout in my custom DSC resource to "warm up" the site. Once that returns, my DSC resource executes New-WebServiceProxy to get SSRS web service proxy.

[johlju]

I would say it's up to you. 😄 I don't mind you adding it to the PR #575, that would the resource work better when that PR is merged. But if you want, you can send it in as a separate PR together with another change.

Regarding warming up, could you submit a separate issue for that? If you feel it's a problem that we need to address.

[johlju]

Just sent in a PR for integration tests. When this issue is resloved an integration tests should be added to verify that the ReportServer and Reports site can be access without throwing http status code 503.

[johlju]

@bozho I run some test on the build worker for the integration tests - looking for a test to verify that the sites respond and I see around 20 seconds of lag before they respond. So if you see longer that you probably have a slower VM.

Working on this, to get the resource to restart the service.

[johlju]

I added a helper function to restart reporting services that uses Restart-Service (similiar to Restart-SqlService helper function). But another option is to use the ManagedComputer class and the Service class.
I don't know if one of these is better than the other. It's much easier to write tests for the Restart-Service so went that direction - but do we loose something when not using the below code?

Using code partly from xSQLServerServiceAccount (by @nabrond) to illustrate the other option.

$managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $env:COMPUTERNAME

$SQLInstanceName = 'DSCRS2016'

if ($SQLInstanceName -ieq 'MSSQLServer')
{
    $serviceNamePattern = '^MSSQLServer$'
}
else
{
    $serviceNamePattern = ('\${0}$' -f $SQLInstanceName)
}

$serviceTypeFilter = 'ReportServer'

$serviceObject  = $managedComputer.Services | Where-Object -FilterScript {
       ($_.Type -eq $serviceTypeFilter) -and ($_.Name -imatch $serviceNamePattern)
}

$serviceObject.Stop()
$serviceObject.Refresh()
$serviceObject.Start()

[bozho]

@johlju Yes, ~20 seconds sounds ok. We've had some networking issues with our NAS storage for our VMs, so sometimes things would grind to a halt :)