SqlPowerBIRS: New resource proposal

[kungfoome]

Description

SSRS is basically dead and PowerBI is replacing it. They are literally the same product, except PowerBI can do more. You can still publish reports to the PowerBI RS server. With all that said, it makes sense to basically replace SQLRS.

Proposed properties

Copy and paste from SQLRs, SQLRsSetup and add 1 new parameter. Product.

Product is SSRS or PBIRS. Default is PBIRS.

Another option is to try and figure it out, PBIRS setup for instance is PowerBIReportServer.exe and SSRS is SQLServerReportingServices.exe for install. But maybe PBIRS takes precendence of SSRS. So, PBI would be looked for first and then SSRS.

You can even see here:
PBI (Latest release is 3/28)
https://www.microsoft.com/en-us/download/details.aspx?id=55329

and here
SSRS (Latest release is 2/12)
https://www.microsoft.com/en-us/download/details.aspx?id=55252

Special considerations or limitations

If there are actually differences in RS and PowerBI setup that I am unaware of. The only differences I am aware of are between different versions and setting it up for high availability. The 2 products should be the same in setup and configuration, with different registry values and install locations. This is what the Product option would take care of, to ensure we are looking in the right places.

This will also only cover the version as of today, so SQL 2016+

While we can keep SSRS and PBI separate, the code is the same, so trying to maintain duplicate code isn't ideal, but we could potentially just keep them separate.

[johlju]

I rather see these as separate resources as they will potentially grow apart even more, since the last RS version will live for quite a while. Where the resource intersect we should make helper functions to reduce duplicate code.

[kungfoome]

I don't think they ever will tbh. See here:

https://github.com/Microsoft/ReportingServicesTools

I'm also looking at the libraries and it's all built-in to the same thing, hey have product types for both PBI and SSRS

[johlju]

That's cool. Didn't know about that ReportingServicesTools module. Then we could make use of that in the resources, so we don't have to re-invent the wheel. A configuration can use the resource PSModule to download that (part of newest PowerShellGet module).

I'm okay with that the same resources can handle both RS och Power BI RS.

[kungfoome]

A few notes for later:

Edition:

  public enum SkuType
  {
    [SkuStrings, SkuDetails(Product = ProductType.None), SkuSupportsDatabasesCreatedBy, SkuCanUpgradeTo] None = 0,
    [SkuStrings(CommandLineName = "EVAL", FullName = "SQL Server Evaluation", PkConfigName = "EVAL", ShortName = "Evaluation"), SkuDetails(Guid = "18F508AC-AE35-4D36-8C8C-C1AD2B86B9EB", Product = ProductType.SqlServerReportingServices, RequiresKey = false), SkuCanUpgradeTo(new SkuType[] {SkuType.SsrsDeveloper, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore})] SsrsEvaluation = 20, // 0x00000014
    [SkuStrings(CommandLineName = "DEV", FullName = "SQL Server Developer", PkConfigName = "DEVELOPER", ShortName = "Developer"), SkuDetails(Guid = "DEE16405-1594-4D48-90BC-DBDAA97F25E0", Product = ProductType.SqlServerReportingServices, RequiresKey = false), SkuCanUpgradeTo(new SkuType[] {SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore})] SsrsDeveloper = 21, // 0x00000015
    [SkuStrings(CommandLineName = "EXPR", FullName = "SQL Server Express", PkConfigName = "EXPRESS_ADVANCED", ShortName = "Express"), SkuDetails(Guid = "8CD588A6-811C-40AD-B939-24C63CF6C77C", Product = ProductType.SqlServerReportingServices, RequiresKey = false), SkuCanUpgradeTo(new SkuType[] {SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsDeveloper, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress})] SsrsExpress = 22, // 0x00000016
    [SkuStrings(CommandLineName = "WEB", FullName = "SQL Server Web", PkConfigName = "WEB", ShortName = "Web"), SkuDetails(Guid = "ECD8539D-B652-4141-8CFF-B7674C856D8F", Product = ProductType.SqlServerReportingServices, RequiresKey = true), SkuCanUpgradeTo(new SkuType[] {SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb})] SsrsWeb = 23, // 0x00000017
    [SkuStrings(CommandLineName = "STANDARD", FullName = "SQL Server Standard", PkConfigName = "STANDARD", ShortName = "Standard"), SkuDetails(Guid = "F21BFA60-1FAB-42F2-9A8C-4D03C6E98C34", Product = ProductType.SqlServerReportingServices, RequiresKey = true), SkuCanUpgradeTo(new SkuType[] {SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard})] SsrsStandard = 24, // 0x00000018
    [SkuStrings(CommandLineName = "ENTERPRISE", FullName = "SQL Server Enterprise", PkConfigName = "ENTERPRISE", ShortName = "Enterprise"), SkuDetails(Guid = "0145C5C1-D24A-4141-9815-2FF76DDF7CEC", Product = ProductType.SqlServerReportingServices, RequiresKey = true), SkuCanUpgradeTo(new SkuType[] {SkuType.SsrsEnterpriseCore}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore})] SsrsEnterprise = 25, // 0x00000019
    [SkuStrings(CommandLineName = "ENTERPRISECORE", FullName = "SQL Server Enterprise (Core-Based Licensing)", PkConfigName = "ENTERPRISE CORE", ShortName = "Enterprise"), SkuDetails(Guid = "A399186B-AB71-4251-B343-6681EF496FAF", Product = ProductType.SqlServerReportingServices, RequiresKey = true), SkuCanUpgradeTo(new SkuType[] {SkuType.SsrsEnterprise}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore})] SsrsEnterpriseCore = 26, // 0x0000001A
    [SkuStrings(CommandLineName = "EVAL", FullName = "Power BI Report Server - Evaluation", PkConfigName = "EVAL", ShortName = "PBIRS Evaluation"), SkuDetails(Guid = "519A9098-0389-47AB-BA02-25AB120AB706", Product = ProductType.PowerBiReportServer, RequiresKey = false), SkuCanUpgradeTo(new SkuType[] {SkuType.PbirsDeveloper, SkuType.PbirsPremium, SkuType.PbirsSqlServerEeSa}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore, SkuType.PbirsEvaluation, SkuType.PbirsDeveloper, SkuType.PbirsPremium, SkuType.PbirsSqlServerEeSa})] PbirsEvaluation = 30, // 0x0000001E
    [SkuStrings(CommandLineName = "DEV", FullName = "Power BI Report Server - Developer", PkConfigName = "DEVELOPER", ShortName = "PBIRS Developer"), SkuDetails(Guid = "78426786-77FE-462C-B921-6AE8F1AB9062", Product = ProductType.PowerBiReportServer, RequiresKey = false), SkuCanUpgradeTo(new SkuType[] {SkuType.PbirsPremium, SkuType.PbirsSqlServerEeSa}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore, SkuType.PbirsEvaluation, SkuType.PbirsDeveloper, SkuType.PbirsPremium, SkuType.PbirsSqlServerEeSa})] PbirsDeveloper = 31, // 0x0000001F
    [SkuStrings(CommandLineName = "PREMIUM", FullName = "Power BI Report Server - Premium", PkConfigName = "PBI PREMIUM", ShortName = "PBIRS Premium"), SkuDetails(Guid = "6B2E5C11-3AB7-4F3F-88CD-15FD73BF45AB", Product = ProductType.PowerBiReportServer, RequiresKey = true), SkuCanUpgradeTo(new SkuType[] {SkuType.PbirsSqlServerEeSa}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore, SkuType.PbirsEvaluation, SkuType.PbirsDeveloper, SkuType.PbirsPremium, SkuType.PbirsSqlServerEeSa})] PbirsPremium = 32, // 0x00000020
    [SkuStrings(CommandLineName = "SQLEESA", FullName = "Power BI Report Server - SQL Server Enterprise with Software Assurance", PkConfigName = "SQL SERVER EE SA", ShortName = "PBIRS SQL EESA"), SkuDetails(Guid = "0361A3D4-EEAA-4033-9033-4F42BE2ED7AF", Product = ProductType.PowerBiReportServer, RequiresKey = true), SkuCanUpgradeTo(new SkuType[] {SkuType.PbirsPremium}), SkuSupportsDatabasesCreatedBy(new SkuType[] {SkuType.SsrsEvaluation, SkuType.SsrsDeveloper, SkuType.SsrsExpress, SkuType.SsrsWeb, SkuType.SsrsStandard, SkuType.SsrsEnterprise, SkuType.SsrsEnterpriseCore, SkuType.PbirsEvaluation, SkuType.PbirsDeveloper, SkuType.PbirsPremium, SkuType.PbirsSqlServerEeSa})] PbirsSqlServerEeSa = 33, // 0x00000021
  }

[Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft Power BI Report Server\PBIRS\Management\Microsoft.ReportingServices.Editions.dll")

$checksum = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\PBIRS\Setup').checksum

[Microsoft.ReportingServices.Editions.ChecksumUtility]::ConvertRegistryFormatToSkuInfo('PBIRS', $checksum)

Should return something like:

InstanceId  : PBIRS
MachineName : ComputerName
SkuType     : PbirsSqlServerEeSa
SkuTypeId   : 33
Timebomb    : Timebomb does not expire.

For instance, in this case it would be 33 or Power BI Report Server - SQL Server Enterprise with Software Assurance as a long name. Which is what should be shown in the configuration tool

Could potentially get it from the DB as well:

SELECT * FROM [databaseName].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ProductInfoHistory'
SELECT TOP 1 Sku from [databaseName].dbo.ProductInfoHistory order by DateTime DESC

https://docs.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/reporting-services-wmi-provider-library-reference-ssrs?view=sql-server-2017

Get-WmiObject -Namespace 'ROOT\Microsoft\SqlServer\ReportServer\RS_PBIRS\V15' -Class MSReportServer_Instance

Get-WmiObject -Namespace 'ROOT\Microsoft\SqlServer\ReportServer\RS_PBIRS\V15\Admin' -Class MSReportServer_ConfigurationSetting

Finally found the source code the database stuff. Steps are as follows:

1. Verify DB Sku (this is the queries above along with the sku enum)
2. Generate new db script
3. Apply new db script
4. Generate user rights script
5. Apply user rights script
6. SetReportServerDSN (SetDatabaseConnection)

[kungfoome]

That's cool. Didn't know about that ReportingServicesTools module. Then we could make use of that in the resources, so we don't have to re-invent the wheel. A configuration can use the resource PSModule to download that (part of newest PowerShellGet module).

I'm okay with that the same resources can handle both RS och Power BI RS.

I don't think we need to import this module. Me, personally, I'd rather not depend on another module when it's not required. The WMI methods pretty much has everything as far as I can tell.

In fact, looking at the code for SQLRs, I may just be able to use that to support PBI as well. I feel like it's just a few changes that are hard coded currently. I'll probably try to get RS working for PBI instead.

[cnilak]

Hi any eta on this? or should we just try to use SqlRS resource for PowerBI? thanks.

[kungfoome]

@cnilak not really. This is actually extremely difficult to implement properly. In this case, I'd recommend doing it by hand still. SqlRS may work for you, but there are some limitations to it.

[cnilak]

@kungfu71186 thanks for the info, is it possible to use the Package resource for PowerBI?