Using workload management with dedicated SQL Pool and managed identity with synapse pipeline

Question

I'm not sure if this is the right place to ask this or not but here goes.

I've currently set up workload management groups for a dedicated SQL Pool using the Azure portal GUI. I configured the three "pre-built" workload groups that are defined in the portal drop down using the default values provided for each.

Ad-hoc
Dashboard
ELT

I also configured the classifiers for each of the workload groups and assigned each classifier to a specific user in the database.

• Two classifiers for Ad-hoc map to a database user that's actually an Azure Active Directory group - these classifiers are used for developers who support the environment and analysis running ad-hoc queries
• One classifier for Dashboard maps to a standard database user mapped to a database login using standard security - this is used for automated Power BI refreshes
• One classifier for ELT maps to a managed identity login which is the same name as the synapse workspace name - this is used by synapse integration pipelines to run data loads / stored procs associated with the ELT activity

While monitoring the data load activity, I see all of the queries/stored procs being issued by the synapse integration pipelines still mapping to the default resource group "smallrc".

What gives? Does the resource management not correctly identify these queries if it is using the MI of the workspace integration / data factory environment? Do I have to specify a standard login for the pipelines to use and assign that standard login to the classifier for the resource group instead?

It seems kinda silly that I would have to do that - or am I missing something?

Answer 1

Hello @JTBrown ,

Welcome to the MS Q&A platform.

By default, each user is a member of the dynamic resource class smallrc.

The resource class of the service administrator is fixed at smallrc and cannot be changed. The service administrator is the user created during the provisioning process. The service administrator in this context is the login specified for the "Server admin login" when creating a new Synapse SQL pool with a new server.

For more details, refer to Workload management with resource classes in Azure Synapse Analytics

By default, all requests are placed in a workload group called smallrc that grants 3% of system resources per request. You can use workload management to create new workload groups that allocate your desired amount of resources per request. Workload groups can also be used to configure workload isolation. In this user experience, you can also use workload classification to assign workload importance.

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