New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
johlju opened this Issue May 31, 2017 · 11 comments

Comments

Projects
None yet
2 participants
@johlju
Collaborator

johlju commented May 31, 2017

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

This comment has been minimized.

Show comment
Hide comment
@bozho

bozho Jun 1, 2017

Contributor

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')?

Contributor

bozho commented Jun 1, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 1, 2017

Collaborator

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.

Collaborator

johlju commented Jun 1, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 1, 2017

Collaborator

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

Collaborator

johlju commented Jun 1, 2017

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

@bozho

This comment has been minimized.

Show comment
Hide comment
@bozho

bozho Jun 1, 2017

Contributor

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

Contributor

bozho commented Jun 1, 2017

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

@bozho

This comment has been minimized.

Show comment
Hide comment
@bozho

bozho Jun 2, 2017

Contributor

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

Contributor

bozho commented Jun 2, 2017

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

@bozho

This comment has been minimized.

Show comment
Hide comment
@bozho

bozho Jun 2, 2017

Contributor

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.

Contributor

bozho commented Jun 2, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 2, 2017

Collaborator

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.

Collaborator

johlju commented Jun 2, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Sep 20, 2017

Collaborator

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.

Collaborator

johlju commented Sep 20, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Sep 20, 2017

Collaborator

@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.

Collaborator

johlju commented Sep 20, 2017

@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 johlju added in progress and removed help wanted labels Sep 20, 2017

johlju added a commit to johlju/SqlServerDsc that referenced this issue Sep 20, 2017

Changes to xSQLServerRSConfig
- Now the resource will restart the Reporting Services service after
  initializing (issue #592). This will enable the Reports site to work.
@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Sep 21, 2017

Collaborator

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()
Collaborator

johlju commented Sep 21, 2017

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

This comment has been minimized.

Show comment
Hide comment
@bozho

bozho Sep 21, 2017

Contributor

@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 :)

Contributor

bozho commented Sep 21, 2017

@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 :)

johlju added a commit to johlju/SqlServerDsc that referenced this issue Sep 21, 2017

Changes to xSQLServerRSConfig
- Now the resource will restart the Reporting Services service after
  initializing (issue #592). This will enable the Reports site to work.

@johlju johlju closed this in #837 Sep 22, 2017

johlju added a commit that referenced this issue Sep 22, 2017

xSQLServerRSConfig: Restart Reporting Services after initialize (#837)
- Changes to xSQLServerRSConfig
  - Now the resource will restart the Reporting Services service after
    initializing (issue #592). This will enable the Reports site to work.

@johlju johlju removed the in progress label Oct 27, 2017

@johlju johlju removed their assignment Oct 27, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment