question

WZIAFP-7807 avatar image
1 Vote"
WZIAFP-7807 asked SasikanthPrabha-3295 commented

Pause Synapse Pool after a period of inactivity

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?

azure-synapse-analytics
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.

1 Answer

AnnuKumari-MSFT avatar image
1 Vote"
AnnuKumari-MSFT answered SasikanthPrabha-3295 commented

Hi @WZIAFP-7807 ,
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:

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
164801-image.png

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 130616-image.png or upvote 130671-image.png 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





image.png (73.4 KiB)
· 12
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 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

1 Vote 1 ·

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?

0 Votes 0 ·
AnnuKumari-MSFT avatar image AnnuKumari-MSFT JeremyDBusselman-8934 ·

Hi @JeremyDBusselman-8934 ,
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.

0 Votes 0 ·

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

0 Votes 0 ·
AnnuKumari-MSFT avatar image AnnuKumari-MSFT JeremyDBusselman-8934 ·

Hi @JeremyDBusselman-8934 ,
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.

0 Votes 0 ·

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": []

0 Votes 0 ·
Show more comments