In this week’s episode of Insight Quest, Brett, a BI consultant from Frontline Analytics, has been assigned tasks related to the programmatic management of Azure Analysis Services and Power BI Embedded resources via existing on-premises tools of SQL Server Integration Services (SSIS) and SQL Server Agent. The previous sprint reviewed in last week’s blog used Azure Automation to execute PowerShell scripts (as Runbooks) to manage these resources.
In the context of both sprints, Azure resource management consists of automating four operations:
- Start (resume) the resource if not already running
- Pause (stop) the resource if it is running
- Scale up the Azure resource one level relative to its current level
- For example, scale up from an A1 Power BI embedded capacity node to an A2 node with more virtual cores and memory.
- Scale down the Azure resource one level relative to its current level
- For example, scale down from an S2 Azure Analysis Services instance to an S1 with fewer QPUs (query processing units) and less memory.
The jobs which execute these operations serve as building blocks to enable the organization to automatically adjust these resources and their associated costs to their changing BI workload requirements such as scaling up on Monday morning but scaling down or even pausing on the weekends.
Here are some of the resource states you may see in either the Azure portal or the capacity settings page in the Power BI Admin portal for the embedded resource:
For this sprint, Brett will create a new solution in Visual Studio containing an SSIS project and a PowerShell project (via PowerShell Tools for Visual Studio 2017) per the following Azure DevOps taskboard:
At the heart of this work are eight (8) PowerShell scripts with defined parameters that will be passed from SSIS packages. In the Azure Automation Runbook scripts featured last week, credentials and resource values were stored in Azure Automation and referenced in each script. For these scripts, SSIS parameters will contain these values and will be passed to a task in an SSIS package. The SSIS packages will then be executed via job schedule(s) in SQL Server Agent.
Requirement Details
- Eight (8) PowerShell scripts with defined parameters which execute management operations (start, stop, scale up, scale down).
- 4 for Azure Analysis Services, 4 for Power BI Embedded
- Eight (8) SSIS packages which individually execute one of the eight (8) PowerShell scripts
- Eight (8) SQL Server Agent jobs which individually execute of the eight (8) SSIS pacakges
- Source control integration for all SSIS packages and PowerShell scripts in Visual Studio
Background
In this scenario, the organization’s data warehouse and its supporting ETL (extract-transform-load) processes are maintained in an on-premises environment and thus the On-Premises Data Gateway is used to facilitate secure data transfer between this source and BI resources in Azure such as Azure Analysis Services. There are plans to migrate these assets to Azure but, for the next 12 to 18 months the organization would like to integrate Azure BI resource management into existing automation and orchestration tools of SSIS and SQL Agent.
Parameterized PowerShell Scripts
The same resource management cmdlets and conditional logic used in the Azure Runbook automation scripts are applicable to these scripts. What’s different is the authentication to Azure and the defined parameters.
All eight (8) parameterized PowerShell scripts supporting this post have been uploaded to GitHub.
Additionally, given the parallels with last week’s scripts, I’ll just show two of them here:
Start (Resume) Azure Analysis Services
Five parameters are defined and then used to authenticate to Azure via the service principal identity. Each script uses cmdlets available for the given resource to retrieve its current state (running or paused, current size/scale) and then, based on this information, perform an action such as scale up (or down) via the Update-AzureRmPowerBIEmbeddedCapacity cmdlet.
#1. Define parameters param( [Parameter(Mandatory=$true,Position=0)] [string]$AppID, [Parameter(Mandatory=$true,Position=1)] [string]$AppIDKey, [Parameter(Mandatory=$true,Position=2)] [string]$TenantID, [Parameter(Mandatory=$true,Position=3)] [string]$ServerName, [Parameter(Mandatory=$true,Position=4)] [string]$ResourceGroupName ) #2. Authenticate $SecurePassword = $AppIDKey | ConvertTo-SecureString -AsPlainText -Force $Cred = new-object -typename System.Management.Automation.PSCredential ` -argumentlist $AppID, $SecurePassword Connect-AzureRmAccount -Credential $Cred -Tenant $TenantID -ServicePrincipal #3. Capture server state $ServerConfig = Get-AzureRmAnalysisServicesServer -ResourceGroupName $ResourceGroupName -Name $ServerName $ServerState = $ServerConfig.State #4. Start Azure AS Server if paused If($ServerState -eq "Paused") {Resume-AzureRmAnalysisServicesServer -Name $Servername -ResourceGroupName $ResourceGroupName} Else {Write-Host "Server is already running"}
Scale Up Power BI Embedded Capacity
The Power BI Embedded capacity scripts use an embedded resource parameter instead of a server name parameter and apply slightly different conditional logic to refer to Azure “A” embedded capacity nodes rather than Analysis Services “S” instances.
#1. Define parameters param( [Parameter(Mandatory=$true,Position=0)] [string]$AppID, [Parameter(Mandatory=$true,Position=1)] [string]$AppIDKey, [Parameter(Mandatory=$true,Position=2)] [string]$TenantID, [Parameter(Mandatory=$true,Position=3)] [string]$EmbeddedResource, [Parameter(Mandatory=$true,Position=4)] [string]$ResourceGroupName ) #2. Authenticate $SecurePassword = $AppIDKey | ConvertTo-SecureString -AsPlainText -Force $Cred = new-object -typename System.Management.Automation.PSCredential ` -argumentlist $AppID, $SecurePassword Connect-AzureRmAccount -Credential $Cred -Tenant $TenantID -ServicePrincipal #3. Capture PBI Embedded capacity state ("Paused" or Succeeded") and node size ("A1"...."A6") $CapacityConfig = Get-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $ResourceGroupName -Name $EmbeddedResource $CapacityState = $CapacityConfig.State $CapacitySKU = $CapacityConfig.Sku #4. Determine scale up node size $ScaleUpSKU = If ($CapacitySKU -eq "A1") {"A2"} ElseIf ($CapacitySKU -eq "A2") {"A3"} ElseIf ($CapacitySKU -eq "A3") {"A4"} ElseIf ($CapacitySKU -eq "A4") {"A5"} Else {"A6"} #5. Scale up one node size if the capacity is running If ($CapacityState -eq "Succeeded") {Update-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $ResourceGroupName -Name $EmbeddedResource -Sku $ScaleUpSKU} Else {Write-Host "Capacity node isn't running"}
The parameterized PowerShell scripts can be tested by passing in parameter values from other PowerShell scripts files. In fact, in some scenarios the organization may prefer to exclusively rely on running PowerShell scripts rather than depending on SSIS and SQL Server Agent. This approach, however, is outside the scope of this post.
As mentioned last week, a simpler but static alternative could be to just define the steady state, scale up, and scale down values and pass these values from a stored location such as Azure Automation variables or SSIS parameters in the case of this sprint.
The PowerShell script files can be easily managed and edited in Visual Studio per the following image:
If you haven’t used PowerShell Tools for Visual Studio, perhaps because you’re comfortable with the PowerShell ISE or the PowerShell extension for Visual Studio Code, you might give it a try. Many BI projects require or benefit from some level of PowerShell scripting and it’s such a convenience to have these scripts in the same solution with other BI projects (ie Analysis Services, Integration Services, Paginated Reports, etc).
SSIS Parameters
In the SSIS project in Visual Studio, the project.params file is edited to store the parameters required by the PowerShell scripts:
In this case, the parameters are all maintained at the project level, which will contain the eight (8) packages. It would be possible to split the parameters between the project scope and the individual package scope but the intent here is to simplify and manage as one single unit if possible.
Note that you won’t be able to execute the packages from Visual Studio with the Sensitive property set to True. Thus, you might keep this set to False to test out the packages and then revise to True prior to deploying to the server.
SSIS Packages
Each of the eight (8) SSIS packages uses the Execute Process Task to execute the parameterized PowerShell scripts:
If you simply need to call a PowerShell script file, you can reference PowerShell.exe as the Executable and the given script file in double quotes following the -File or -F parameter in the Arguments property on the Process page for this task as described in this blog post.
However, in this scenario five (5) project parameters need to be passed to the PowerShell script file and this needs to be a single string to pass to the Arguments property Execute Process Task. The Expressions page of the Execute Process Task Editor provides this ability to customize the argument string:
Clicking the ellipsis opens the expression builder which exposes the parameters and allows for evaluating the expression to confirm it produces the intended result:
The SSIS project parameters are exposed in the Expression Builder window and can be dragged to map into the position required by the PowerShell script. Click ‘Evaluate Expression’ to test/confirm the expression as it’s created.
For now each package just has one task and this task (Execute Process) passes project-scoped parameters into a PowerShell script which executes a resource management operation against an Azure BI resource. At this point, the SSIS packages can be deployed to the server such that they can be executed via SQL Server Agent.
SSIS Environment
The full details on creating and mapping a server environment are available on MS Docs. In this example, a single environment has been created for the project (Azure BI Resource Management) containing the packages and parameters:
Variables and their values are added to the environment and the SSIS project is then configured to reference the environment. Environment variables are then mapped to parameters of the SSIS project:
You can secure environments and their values (which of course can be sensitive) to specific principals.
SQL Agent Jobs
Finally, SQL Server Agent jobs can be added to execute the SSIS packages on a schedule or ad hoc if necessary. Each job will A) use a job step type equal to SQL Server Integration Services B) reference the package at its deployment path in SSISDB on the package tab and C) reference the environment (containing the variables) on the configuration tab per the following image:
You can then just run the SQL Server Agent jobs to confirm the packages and their resource management operations are executing as expected.
Like SSIS, SQL Server Agent has been around for a long time and thus has mature features for scheduling, notifications, logging, and more. I’ve tried to limit this blog post to just the essentials of SSIS and Agent required for this scenario.
A Step Back
This example proves out another method of automating Azure BI resource management which replaces Azure Automation with SSIS and SQL Agent. So if Azure Automation isn’t a viable option at the moment and/or you want to leverage familiar and existing automation and orchestration tools (like SSIS and Agent) you can definitely accomplish this.
A future blog post may take resource management a step further by dynamically triggering these resource management scripts based on various inputs/thresholds.
Wrapping Up
As always, you’re welcome to share thoughts/feedback in the comments. If you think blog posts like this could be useful, you can click ‘Follow’. Next week will likely either (finally) feature dataflows or may take a higher level look at recent announcements, updates and the latest release notes.
Insight Quest 
One comment