I have setup runbooks to pause my synapse pool automatically at the end of the day. I was wondering if anyone can give some pointers as to if it is possible to pause after a period of inactivity?

Hi @WZIAFP , Thankyou for using Microsoft Q&A platform. In order to automate pausing and resuming of dedicated SQL pool , these are the steps you need to follow: Azure Synapse managed Identity (same name as Synapse workspace name) needs to have Contributor access in Azure Synapse Analytics. Use Web Activity to get the list of ded sql pool present in your resource group using this GET API URL : https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Synapse/workspaces/{workspace-name}/sqlPools?api-version=2019-06-01-preview Use ForEach activity to iterate for each ded sql pool and run Pause or resume command Use Web activity inside Foreach to send POST API URL for pausing or resuming ded sql pool : https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Synapse/workspaces/{workspace-name}/sqlPools/{database-name}/resume?api-version=2019-06-01-preview Note : In Case of pause same URL can be used , just replace 'resume' with 'pause' In order to make it dynamic for both the commands, i.e. Pause and Resume all SQLpools , you can create parameter to send the command during runtime: https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Synapse/workspaces/{workspace-name}/sqlPools/@{item().name}/@{pipeline().parameters.Command}?api-version=2019-06-01-preview If you only have one ded sql pool in your Synapse workspace, you can skip bullet points 2 and 3 and directly use a Web activity to send the POST command. Please refer to the below provided document and video link in order to understand better: Pause and resume dedicated SQL pools with Synapse Pipelines https://www.youtube.com/watch?v=4Hoye3kPYlU&t=827s Hope this will help. Please let us know if any further queries. ------------------------------ Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how Want a reminder to come back and check responses? Here is how to subscribe to a notification If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Pause Synapse Pool after a period of inactivity

Accepted answer

AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee

2022-01-13T11:07:26.987+00:00
Hi @WZIAFP ,
Thankyou for using Microsoft Q&A platform.
In order to automate pausing and resuming of dedicated SQL pool , these are the steps you need to follow:

Azure Synapse managed Identity (same name as Synapse workspace name) needs to have Contributor access in Azure Synapse Analytics.

Use Web Activity to get the list of ded sql pool present in your resource group using this GET API URL : https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Synapse/workspaces/{workspace-name}/sqlPools?api-version=2019-06-01-preview

Use ForEach activity to iterate for each ded sql pool and run Pause or resume command

Use Web activity inside Foreach to send POST API URL for pausing or resuming ded sql pool : https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Synapse/workspaces/{workspace-name}/sqlPools/{database-name}/resume?api-version=2019-06-01-preview

Note: In Case of pause same URL can be used , just replace 'resume' with 'pause'

In order to make it dynamic for both the commands, i.e. Pause and Resume all SQLpools , you can create parameter to send the command during runtime: https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Synapse/workspaces/{workspace-name}/sqlPools/@{item().name}/@{pipeline().parameters.Command}?api-version=2019-06-01-preview

If you only have one ded sql pool in your Synapse workspace, you can skip bullet points 2 and 3 and directly use a Web activity to send the POST command.

Please refer to the below provided document and video link in order to understand better:
Pause and resume dedicated SQL pools with Synapse Pipelines
https://www.youtube.com/watch?v=4Hoye3kPYlU&t=827s

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.
Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Please sign in to rate this answer.

2 people found this answer helpful.
Jeremy D. Busselman 36 Reputation points

2022-01-13T17:18:19.68+00:00

I have 2 Synapse environments, one more secure using Managed Virtual Network. The non-vnet environment the above workflow runs fine, however I copied the pipeline over to the VNet environment and I'm getting an unknown failure when trying to make the API Call in the first step to get a list of dedicated pools.

Response status code: 'Unknown'. More details:Exception message: 'An error occurred while sending the request.'.\r\n",
"failureType": "UserError",
"target": "GET List",
"details": []
}

When I try calling this Synapse environment from the other where the pipeline is working, it does this call successfully (fails a little later). What kind of permission or approach do I need to apply to get this to function?

AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee

2022-01-20T05:27:18.53+00:00

Hi @Jeremy D. Busselman ,
The Managed identity (having similar name as your synapse workspace name) which you are using for authentication in web activity needs Contributor role in the Virtual network as well. Could you please try adding 'Contributor' role to the managed identity of your synapse workspace which is present in Vnet and try executing the pipeline again.

Jeremy D. Busselman 36 Reputation points

2022-01-20T15:18:55.193+00:00

Actually That didn't work either, I added it to the Virtual Network, Private DNS zone, Network Interface, and the endpoint and still receive the same error.

If there is documentation anywhere on this setup that would be great so I can re-walk through the steps in case there is something else missing.

Thanks

AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee

2022-01-21T12:06:50.097+00:00

Hi @Jeremy D. Busselman ,
Thanks for providing the update. Could you please try creating a service principal under App registration .

I have raised this issue with our internal Product group and trying to seek their help on what other access is required or what else is missing. Till the time, we hear back from them could you please try the approach of using Service principal for Authentication in place of Managed identity.

Go to App registration. Click on new registration and provide necessary details and click on register.

Within the newly created Service principal. Go to Certificates & Secrets, and create new Client secret.

Make sure to save the service key somewhere as it will appear only once.

Give this Service principal, contributor role in subscription level.

In the Web activity, instead of using managed identity, select Service principal and provide Service Principal ApplicationID in Service Principal ID option and provide the Service principal key that you saved and try running the pipeline.

Jeremy D. Busselman 36 Reputation points

2022-01-21T16:21:49.957+00:00

I created the Service Principal, tested using SHIR and was successful (make sure I had the SP set up correctly).

I then switched to the Azure runtime and it took a while to get this error:
"errorCode": "2108",
"message": "GetSpnAuthenticationToken: Failed while processing request for access token with error: Object reference not set to an instance of an object.",
"failureType": "UserError",
"target": "GET List",
"details": []

Jeremy D. Busselman 36 Reputation points

2022-01-21T18:43:35.837+00:00

The following is from a consultant we are working with:

The issue is networking related to the IR.
When we use a self hosted IR (on a on-prem VM) for the GET request it works using the Managed Identity
When we use any Azure IR within the managed VNet it can't connect to any public API. (I've tried https://api.publicapis.org/entries)

This documentation seems to say that outbound should be allowed: https://learn.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint#outbound-communications-through-public-endpoint-from-adf-managed-virtual-network

This documentation says that with exfil protection on all traffic goes over private endpoints: https://learn.microsoft.com/en-us/azure/synapse-analytics/security/workspace-data-exfiltration-protection#securing-data-egress-from-synapse-workspaces

@AnnuKumari-MSFT Does setting preventDataExfiltration: true trump the outbound calls?

Here is the bicep template:
resource syn 'Microsoft.Synapse/workspaces@2021-06-01' = {
name: synapseName
location: location
identity: {
type: 'SystemAssigned'
}
properties: {
cspWorkspaceAdminProperties: {
initialWorkspaceAdminObjectId: //BI Data Engineering Admin AD group
}
defaultDataLakeStorage: {
...
}
managedResourceGroupName: '${resourceGroupName}-managed'
managedVirtualNetwork: 'default'
managedVirtualNetworkSettings: {
preventDataExfiltration: true
}
publicNetworkAccess: 'Enabled'
sqlAdministratorLogin: sqlServerAdministratorLogin
sqlAdministratorLoginPassword: sqlServerAdministratorPassword
}
}

Jeremy D. Busselman 36 Reputation points

2022-01-24T13:25:37.153+00:00

@AnnuKumari-MSFT , we did try re-creating an environment with this setting and it appears to have worked.

Before we re-build our base environment, can you confirm this setting change is the solution, and possibly if there's a way to do it without re-building the environment?

Thanks

AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee

2022-01-24T16:24:27.12+00:00

@Jeremy D. Busselman ,
Thanks for the updates . I have raised the query with internal team. I will keep you posted once I hear back from the team. Thanks!

AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee

2022-01-25T14:46:53.527+00:00

Hi @Jeremy D. Busselman ,

I got the response from PG team . Here is the response in their words:

Yes, the setting preventDataExfiltration disallows any calls not made via PEs, which should be in approved AAD tenants.
You can find more info here: Data exfiltration protection for Azure Synapse Analytics workspaces

Also , to your question whether this setting can be changed after the workspace has been created pr not , it's clearly mentioned in the above document as a note that You cannot change the workspace configuration for managed virtual network and data exfiltration protection after the workspace is created.

Kindly do click Accept Answer and Up-Vote for the same. In case you have any further query please do let us know.

Jeremy D. Busselman 36 Reputation points

2022-01-25T16:03:45.767+00:00

Thanks for that confirmation, and FYI for anyone else, I did have to use the Service Principal ID instead of Managed ID for access, and I could put the Service Principal ID as contributor on the Resource group instead of Subscription.

AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee

2022-01-25T16:24:52.453+00:00

@Jeremy D. Busselman ,
Appreciate you for helping the community by sharing your approach on finding the end to end solution. Please consider hitting Accept Answer and kindly do upvote for the same as accepted answers helps community as well.

Sasikanth Prabha 41 Reputation points

2022-04-27T04:45:23.11+00:00

Hi there, I am also looking for the same as described in the question. The solution above doesn't answer the actual need.
More importantly, the need is to find the 'Inactivity' status or inactivity duration on DB pool. Once, as a user it can be found, above techniques for pause and resume can be implemented. the crux of the problem is to find the inactive duration of DB Pool. some thing in lines such as be able to find if there are no queries for DB pool and is currently idel

Leslie Andrews 1 Reputation point

2022-11-28T15:00:44.683+00:00

Were you ever able to figure out how to determine the inactivity and pause your dedicated pool? I am trying to do the same thing. Thanks!

AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee

2022-12-01T10:18:19.82+00:00

Hi @Leslie Andrews ,
Please check out this thread link . Thanks
Sign in to comment

Pause Synapse Pool after a period of inactivity

0 additional answers