SqlRS: SQLPS is Required

[rdavisunr]

Details of the scenario you tried and the problem that is occurring:

I have a two server setup:

• Server One running "SQLENGINE" and "SSMS" features
• Server Two running only "RS" feature

xSQLServerRSConfig fails after successfully installing SQL RS feature on Server Two (installation done with xSqlServerSetup). The failure refers to missing SqlServer or SQLPS module(s). So, it appears that installing SQL with only the RS feature does not install either of those modules. When I added the SSMS feature to Server Two, the configuration was able to proceed (it looks like SSMS installs SQLPS).

So, I am suggesting that the documentation for SqlRS be updated to indicate the dependency. It doesn't currently list it as a requirement, while some other resources do.

Also, I would like to ask what is the best way to proceed for this scenario when using only DSC to setup this server with only the "RS" feature? In other words, how do I get that dependency in there without installing SSMS?

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

xSQLServerRSConfig DefaultConfiguration
{
    InstanceName = $SqlData.InstanceName
    RSSQLServer = $NodeName
    RSSQLInstanceName = $SqlData.InstanceName
    ReportServerVirtualDirectory = $SqlData.ReportServerVirtualDirectory
    ReportsVirtualDirectory = $SqlData.ReportsVirtualDirectory
    PsDscRunAsCredential = $LocalAdminCreds
    DependsOn = "[xSqlServerSetup]InstallSql"
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running: Windows Server 2012R2, SQL Server 2014 SP2, PowerShell5.1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running: SQLPS 1.0 (but not really applicable to this issue)

Version of the DSC module you're using, or 'dev' if you're using current dev branch: 9.0.0.0

[johlju]

Thanks for reporting this issue. We should definitively add that SQLPS (or SqlServer) is a prerequisites.

First I would try download the SqlServer module from PowerShell Gallery. You can have that on a share an have that copied to the server with a resource. I think there is an issue proposing we add a resource for this. Let me know if using SqlServer module works, if you test that.

The other option is to add SQLPS like this. This is using the SQL2016 binaries. Maybe backward compatible? Otherwise the same binaries should (?) exist for SQL Server 2014.

Package 'SQLSysClrTypesPackage-SQL2016'
{
    Ensure    = 'Present'
    Path      = Join-Path -Path $node.SqlPsPath -ChildPath 'SQLSysClrTypes.msi'
    Name      = 'Microsoft System CLR Types for SQL Server 2016'
    ProductId = '96EB5054-C775-4BEF-B7B9-AA96A295EDCD'
}

Package 'SharedManagementObjectsPackage-SQL2016'
{
    Ensure    = 'Present'
    Path      = Join-Path -Path $node.SqlPsPath -ChildPath 'SharedManagementObjects.msi'
    Name      = 'Microsoft SQL Server 2016 Management Objects  (x64)' # Extra space after 'Objects' is not a typo here, the Product Name has this typo.
    ProductId = '20EA85AA-2A1D-4F11-B09F-4BA2BF3C8989'
}

Package 'PowerShellToolsPackage-SQL2016'
{
    Ensure    = 'Present'
    Path      = Join-Path -Path $node.SqlPsPath -ChildPath 'PowerShellTools.msi'
    Name      = 'PowerShell Extensions for SQL Server 2016 ' # Extra space at the end is not a typo here, the Product Name has this typo.
    ProductId = '1E19C524-DADE-4587-BE64-D57B1C0BA3C0'
}

[rdavisunr]

@johlju - thanks for the suggestions; I will try those out!

I encountered a couple other issues getting a standalone RS server to work, but I am going to log those separately...

Thanks again

[johlju]

Happy to help. Yes, please log unrelated issues as separate issues.

[randomnote1]

The only reason the SqlServer or SQLPS modules are required is to run the create database and database rights scripts using Invoke-SqlCmd. If another native method natively available that can handle running SQL queries which have multiple GO statements in them, then I think we can remove the requirement for the SqlServer and SQLPS modules.