question

JimMauck-2192 avatar image
0 Votes"
JimMauck-2192 asked HassanRahamathullah-0087 commented

Automate pause/resume Synapse Workspace (Preview) SQL Pool with Powershell

I have a Synapse Workspace with SQL Pool that I would like to automate resume and suspend with PowerShell. I am following this MS doc as a guide: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/pause-and-resume-compute-powershell

I am using this command after authenticating and setting proper subscription context:

 Suspend-AzSqlDatabase –ResourceGroupName "workspacemanagedrg-********-****-****-****-************" –ServerName "syndatawarehouse" –DatabaseName "sqlpooldw"

(The only way I can get this command to work is by referencing the SQL Pool with managed resource group name as the ResourceGroupName parameter. The SQL Pool is not found when referencing it using the primary resource group name.)

With this, the suspend command is able to reference my SQL Pool but I get this error message:

 Suspend-AzSqlDatabase: DenyAssignmentAuthorizationFailed: The client '****@******.onmicrosoft.com' with object id '********-****-****-****-************' has permission to perform action 'Microsoft.Sql/servers/databases/pause/action' on scope '/subscriptions/********-****-****-****-************/resourceGroups/workspacemanagedrg-********-****-****-****-************/providers/Microsoft.Sql/servers/syndatawarehouse/databases/sqlpooldw/pause'; however, the access is denied because of the deny assignment with name '********-****-****-****-************' and Id '********************************' at scope '/subscriptions/********-****-****-****-************/resourceGroups/workspacemanagedrg-********-****-****-****-************'.

I can see the deny assignment is placed on the SQL Pool from the managed resource group through Azure Blueprint that was applied during SQL Pool creation.

Do I need to remove the deny assignment?
Or is there another way to reference the SQL Pool database to get around the deny assignment?

Thank you,
Jim


azure-synapse-analytics
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey @JimMauck-2192,

Deny assignments block users from performing specific Azure resource actions even if a role assignment grants them access.

Could you please remove the deny assignment and try to run the PowerShell cmdlet?


0 Votes 0 ·

Hey @JimMauck-2192, Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.


0 Votes 0 ·

Hi Pradeep,

Thanks for following up!

No, I am not able to remove the deny assignment. I cannot find any solid documentation on how to remove deny assignments from Azure Blueprints where I did not create the blueprint itself. I cannot find any where to assign myself RBAC privileges to work with this particular blueprint which contains the deny assignment. In fact, I cannot even read the blueprint itself.

Was the blueprint created by Microsoft Azure administrators that was automatically applied (per design) to my managed resource group upon creation? I ask this because the SQL Pool I created, named "sqlpooldw", within my Synapse workspace was automatically assigned to the managed resource group.

Regards,
Jim

0 Votes 0 ·

Hello @JimMauck-2192,
If it becomes necessary to modify or delete a resource protected by an assignment, there are two ways to do so.

  • Updating the blueprint assignment to a locking mode of Don't Lock

  • Delete the blueprint assignment

When the assignment is removed, the locks created by Azure Blueprints are removed. However, the resource is left behind and would need to be deleted through normal means.

Reference: Understand resource locking in Azure Blueprints

Hope this helps. Do let us know if you any further queries.


Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.

0 Votes 0 ·

1 Answer

JimMauck-2192 avatar image
1 Vote"
JimMauck-2192 answered HassanRahamathullah-0087 commented

Hi Pradeep,

I figured it out I was using wrong product documentation set. I was following "Azure Synapse Analytics (formerly SQL DW)".
I needed to follow "Azure Synapse Analytics (workspaces preview)". They have completely different set of commands. This happened easily because searching for "pause resume Synapse Analytics with PowerShell" in MS documentation sites finds the formerly SQL DW and not the workspaces preview in the results.

Going back through the workspaces preview documentation again I noticed that distinction and the PowerShell command Update-AzSynapseSqlPool.

So, for my instance, these commands work in PowerShell:

 Update-AzSynapseSqlPool -WorkspaceName syndatawarehouse -Name sqlpooldw -Resume
 Update-AzSynapseSqlPool -WorkspaceName syndatawarehouse -Name sqlpooldw -Suspend

I am also able the schedule these as PowerShell runbooks in Azure Automation.

Regards,
Jim

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi everyone, now the document has been updated. Please refer the commands here: https://docs.microsoft.com/en-us/powershell/module/az.synapse/?view=azps-6.3.0#synapse-analytics
Below are the updated command to resume/pause synapse workspace:

 Suspend-AzSynapseSqlPool -WorkspaceName $servername -Name $databasename
    
 Resume-AzSynapseSqlPool  -WorkspaceName $servername -Name $databasename

Anyway thanks for sharing the solution @JimMauck-2192.

1 Vote 1 ·

I want to emphasize this was my fault due to not paying enough attention to the differences between SQL DW and Workspaces.

0 Votes 0 ·

Hi @JimMauck-2192,
Glad to know that your issue has resolved. And thanks for sharing the solution, which might be beneficial to other community members reading this thread.

0 Votes 0 ·