How To Scale Azure SQL Database In Azure Data Factory Using A Web Activity

Azure SQL Database is a very flexible service that can be easily scaled to fit the needs of the moment. In most solutions, I like to orchestrate the up and down scaling right from within Azure Data Factory, and I’m always interested in more efficient approaches to do this.

<— Update 4-7-2019:
I’ve learned from a colleague (thanks Roland ✌) that you can find the correct values with the SQL query below, after you have manually set the database to the desired tier and size:

SELECT DATABASEPROPERTYEX(db_name(),'edition') AS ServiceTier , DATABASEPROPERTYEX(db_name(),'serviceobjective') AS ComputeSize

Until today, to scale up an Azure SQL Database before a particular workload in Azure Data Factory, I had quite some options to choose from to achieve this:

  • Create a LogicApp, and trigger it with a Web Activity in Azure Data Factory.
  • Create an Azure Function, and invoke the function using the new Azure Function Activity in Azure Data Factory.
  • Create an Azure Automation workbook, add a webhook to it and call that with a Web Activity in Azure Data Factory.
  • Create a T-SQL script with an ALTER DATABASE statement and call that with a Stored Procedure Activity in Azure Data Factory.

New kid in town (well, at least for me)

But today, my dear colleague Simon Zeinstra pointed out that the Azure Data Factory Web Activity supports managed identity (MSI) authentication, thanks 👊🏻. And I successfully put together another really good option: using the Azure Management REST API with a Web Activity in Azure Data Factory. And it is so simple and easy to set up, this probably is my favorite option from now on 😎.
The Web Activity seems to call the REST API synchronously, hence it does not finish before the scale operation is completed.

ADF Web Activity
Less is more: we only need a Web Activity.


The following configuration for the Web Activity is what worked in my set up.

  • URL: Use the following URL, where you replace the stuff between {brackets}. Be sure to specify the Azure SQL Server without the “” part.{your SubscriptionID}/resourceGroups/{your RG}/providers/Microsoft.Sql/servers/{your sqlservername}/databases/{your databasename}?api-version=2017-10-01-preview
  • Methode: PUT
  • Headers: Add a header with name Content-Type, and value application/json.
  • Body: Use the following json body, where you specify the sku name and tier that you want your database to be scaled to, and the region of the database:
    {"sku":{"name":"P1","tier":"Premium"}, "location": "West Europe"}
  • Authentication: MSI
  • Resource


If you have completed your configuration and debug the Web Activity, you may get this error message:

"errorCode": "2108",
"message": "{\"error\":{\"code\":\"AuthorizationFailed\",\"message\":\"The client '{GUID}' with object id '{GUID}' does not have authorization to perform action 'Microsoft.Sql/servers/databases/write' over scope '/subscriptions/{subid}/resourceGroups/{rgid}/providers/Microsoft.Sql/servers/{sqlservername}/databases/{dbname}'.\"}}",
"failureType": "UserError",
"target": "Scale up ASQL DB"

This means the managed identity (MSI) of our Azure Data Factory does not yet have the correct permissions on our Azure SQL Server. Follow these steps to do this:

  1. In the Azure Portal, go to the SQL Server.
  2. Open the ‘Access control (IAM)’ option.
  3. Click ‘Add role assignment’.
  4. Choose role ‘Contributor’.
  5. In the search box for ‘Select’, type the exact name of your Azure Data Factory resource, and select it.
  6. Click Save.

Now the managed identity (MSI) of your Azure Data Factory is able to change the options of your database!

Quickstart ADF pipeline example

For your convenience, I’ve shared an example Azure Data Factory pipeline on GitHub.


You now know how to effortlessly use a Web Activity in Azure Data Factory to scale your Azure SQL Database.

The ease-of-use of this solution is awesome, and largely due to how the authentication between Azure Data Factory and Azure SQL is taken care of via managed identity (MSI). Keeping any credentials out of my code, and I don’t even have to use Azure Key Vault to accomplish this.

The other options I mentioned like LogicApps, Functions and Runbooks sure provide more features and options. Time will tell if this simple option is going to be my new default, but it sure seems like it 👍!

More info:

Pass the sauce

This Post Has 39 Comments

  1. Derek

    Will the same work for AAS?

  2. Joe Abbott

    Hi Dave!

    I just fired this up for a project I’m working on and it’s pretty fantastic and simple. Thank you for sharing and the documentation/notes/JSON code. Much appreciated!

    Thanks again!

  3. Sam

    I get error The client xxx with object id xxx does not have authorization to perform action ‘Microsoft.Sql/servers/databases/write’

    I HAVE inserted the datafactory as Contributor as instructed (Even tried owner). Has something changed explaining that this does not work? Or do I miss some step? Also I have double checked the other parameters.

  4. Sam

    I must comment that this was my error. I used FQDN server name and it should be the short one.

    1. Dave Ruijter

      Hi Sam,
      Great you got it solved! Indeed it should be the short name of the server, without the rest of the ‘URL’ of the server name.

  5. DC

    works very nice for scaling between basic, standard and premium but I’m having trouble scaling up vcore. I would like to scale to Gen4 – General Purpose (GP_Gen4_4) (inlcuding the azure hybrid benefit saving).

    What would the json body be for that

    1. Dave Ruijter

      Hi! Great question, thanks for asking. In your case the ServiceTier would be ‘GeneralPurpose’ and the ComputeSize would be ‘GP_Gen4_4’.
      I’ve learned from a colleague that you can find the correct values with the SQL query below, after you have manually set the database to the desired tier and size (thanks Roland! ✌)

      SELECT DATABASEPROPERTYEX(db_name(),'edition') AS ServiceTier , DATABASEPROPERTYEX(db_name(),'serviceobjective') AS ComputeSize

      I’ll update my blog post to note this.

      1. DC

        hi, thanks for the reply. what I’m really interested to know is how to include the licence hybrid benifit saving.

        so maybe the json would look like this…but I cant find the values to put

        {“BasePrice”:”??”,”LicenseIncluded “:”??”}
        ,”location”:”West Europe”

        1. Dave Ruijter

          It’s been a while, have you found the solution? If so, please feel free to share it!

  6. Tim

    nice article! Have you tried this with an azure sql managed instance? i can successfully call the get method from the api, but on calling put/patch i get an unauthorized error – the downscaling however worked… upscaling (with the same parameterized activity) didn’t work…

      1. Tim

        Hi Dave,

        I’ve got it working:
        For the managed instance, you have to add the data factory as contributor to the resource group!


  7. Andrey

    Thanks Dave, you saved my day!

  8. Vipin Saini

    Not able to authorize, can you please share what would be the the authorization header. Do we need to get access token or what i need to provide in authorization header

    1. Dave Ruijter

      Please double check the Authentication type.. is it MSI?

  9. Vipin Saini

    I am getting this in response
    {“error”:{“code”:”AuthenticationFailed”,”message”:”Authentication failed. The ‘Authorization’ header is missing.”}

  10. Henrik

    Great tip! Will this work when using basic authentication instead of managed identity?

    1. Dave Ruijter

      I don’t think so. It might also work with some form of oauth token authentication. But basic, no.

  11. Sanjeev Jaladi

    Thanks for your post.

    I want to scale Azure SQL database DTUs. Your post doesn’t mention any method to change DTUs for a specific Edition. For example what if I want to change DTUs to 200/800 for Edition “S4” Is this possible? Please Suggest..

    1. Sanjeev Jaladi

      Got it, Thanks

      1. Jos Haemers

        How did you fix this?

        1. Dave Ruijter

          You can change the tier to get more DTUs. You can’t set the DTU level.

  12. Niel

    It worked fine when i ran in test / debug mode but when i moved it to production/datafactory, it failed with below message. It looks like URL API version is in preview which you used ?api-version=2017-10-01-preview
    Is there a GA version out?

    Operation on target dbscale failed: {“error”:{“code”:”InvalidRequestContent”,”message”:”The request content was invalid and could not be deserialized:
    ‘Could not find member ‘datasets’ on object of type ‘ResourceDefinition’. Path ‘datasets’.’.”}}

    1. Dave Ruijter

      The API version is no issue, you can use the preview version.
      There must be something else going wrong.
      Can you explain more about when this error comes up?

  13. Marc

    Hi dave,

    I am bumping into the same issue, in debug mode it works like a charm but when published and executed via a trigger I get the following error:

    Operation on target dbscale failed: {“error”:{“code”:”InvalidRequestContent”,”message”:”The request content was invalid and could not be deserialized:
    ‘Could not find member ‘datasets’ on object of type ‘ResourceDefinition’. Path ‘datasets’.’.”}}

    I can’t find any documentation regarding this error, do you have any idea?

    Kind regards,


    1. Dave Ruijter

      Hi Marc, do you have more information on the issue? When does it happen, what is the input for the web activity, what is the output, those kinds of things.. Maybe it provides a hint of what is going wrong..

  14. bhaskar

    Can we do the same for Azure Sql POOL

    1. Dave Ruijter

      Yes, that should be possible with some minor adjustments

  15. Bas

    Hi Dave, this looked very promising when I tried it out and it worked immediately. But when I trigger the pipeline the web activity times out after 1 min. Have you seen that issue before, and maybe can you suggest a solution?

  16. Bas

    Hi Dave, this is an addition to my previous message (can’t respond to that one yet, still waiting moderation).
    It now seems that sometimes the pipeline works, sometimes it fails. I can imagine that for larger databases this operation will fail more often – as Microsoft are replicating your database (which takes time depending on data volume) in a different service tier when you’re scaling the DB.

    1. Dave Ruijter

      Hi Bas,

      Sorry I missed your questions.
      By default, the web activity in Azure Data Factory has a timeout of 1min for POST requests.
      So that is what is causing this issue.
      And, it seems that this POST request is run synchronous, so it waits until it completes..
      Maybe you can think of a solution that is asynchronous?

  17. David

    Thank you sir! that worked like a charm !

  18. Robert

    Hi Dave,
    I have this set up, however the higher ups at my company feel that Contributor may be too permissive. They even feel that SQL DB Contributor may still be too permissive. I’m thinking a custom RBAC role with the following permissions may work.

    Read List/Get Azure SQL Database(s) “Microsoft.Sql/servers/databases/read”
    Write Create/Update Azure SQL Database “Microsoft.Sql/servers/databases/write”
    Other Pause a Datawarehouse database. “Microsoft.Sql/servers/databases/pause/action”
    Other Resume a Datawarehouse database. “Microsoft.Sql/servers/databases/resume/action”
    Read List/Get Azure SQL Server(s) “Microsoft.Sql/servers/read”
    Read Gets the status of a database operation “Microsoft.Sql/servers/databases/operationResults/read”

    Can you provide any feedback on this?

  19. Kumar

    Hi Dave,

    I am getting the below error message. Can you please help me out?

    {“error”:{“code”:”ParentResourceNotFound”,”message”:”Can not perform requested operation on nested resource. Parent resource ‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ not found.”}}

Leave a Reply