Azure-docs: PATCH to scale data warehouse not working.

[MarkAtAgilliance]

I have used patch on the SQL DWH as described above. No queries are running at the time. The rest API returns success.

This was the body:

    {
      "properties": {
       "requestedServiceObjectiveName": "dw500c"
        }
    }

and this was the return (private info obscured)

{
    "statusCode": 200,
    "headers": {
        "Pragma": "no-cache",
        "Transfer-Encoding": "chunked",
        "Vary": "Accept-Encoding",
        "x-ms-request-id": "41d016d2-f2d1-4916-95f1-efad3ce3dcab",
        "x-ms-ratelimit-remaining-subscription-reads": "11998",
        "x-ms-correlation-request-id": "096858b3-9a68-414a-802a-451057be5e06",
        "x-ms-routing-request-id": "NORTHEUROPE:20190220T214331Z:096858b3-9a68-414a-802a-451057be5e06",
        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
        "X-Content-Type-Options": "nosniff",
        "Cache-Control": "no-cache",
        "Date": "Wed, 20 Feb 2019 21:43:31 GMT",
        "Server": "Microsoft-HTTPAPI/2.0",
        "Content-Type": "application/json; charset=utf-8",
        "Expires": "-1",
        "Content-Length": "645"
    },
    "body": {
        "sku": {
            "name": "DW400c",
            "tier": "DataWarehouse"
        },
        "kind": "v12.0,user,datawarehouse,gen2",
        "properties": {
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "maxSizeBytes": 263882790666240,
            "status": "Online",
            "databaseId": "bfxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxb5",
            "creationDate": "2018-12-21T00:34:21.03Z",
            "currentServiceObjectiveName": "DW400c",
            "defaultSecondaryLocation": "westeurope",
            "catalogCollation": "SQL_Latin1_General_CP1_CI_AS"
        },
        "location": "northeurope",
        "id": "/subscriptions/8dxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx69/resourceGroups/xxxxxxxxxxx/providers/Microsoft.Sql/servers/xxxxxxx/databases/xxxxxxx",
        "name": "xxxxxxx",
        "type": "Microsoft.Sql/servers/databases"
    }
}

note the returned sku was the original and and the status is not "scaling". On subsequent queries, the status did not change from online to scaling at any point and no scaling happened.

I also noticed that scaling via poweshell does not use this PATCH verb. It uses a PUT request with a sku number in the body - not a PATCH with a requestedServiceObjectiveName.

Thanks,
Mark.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 95eb3903-d171-d3c9-180e-a49e14522f27
• Version Independent ID: 44cece14-8e57-4d3b-6e5c-2a8ffe719be5
• Content: Pause, resume, scale with REST in Azure SQL Data Warehouse
• Content Source: articles/sql-data-warehouse/sql-data-warehouse-manage-compute-rest-api.md
• Service: sql-data-warehouse
• GitHub Login: @kevinvngo
• Microsoft Alias: kevin

[KalyanChanumolu-MSFT]

Thank you for the detailed feedback.
We are actively investigating this and will get back to you soon. 

[KalyanChanumolu-MSFT]

@MarkAtAgilliance

I tried to reproduce this and the PATCH request works as expected.

You should receive a HTTP 202 Accepted response for your PATCH request.
And the response to a GET request during scaling should look like the below

{
  "sku": {
    "name": "DataWarehouse",
    "tier": "DataWarehouse",
    "capacity": 9000
  },
  "kind": "v12.0,user,datawarehouse,gen2",
  "properties": {
    "collation": "SQL_Latin1_General_CP1_CI_AS",
    "maxSizeBytes": 263882790666240,
    "status": "Scaling",
    "databaseId": "***",
    "creationDate": "2019-02-24T07:59:21.943Z",
    "currentServiceObjectiveName": "DW400c",      Current SKU
    "requestedServiceObjectiveName": "DW1000c",   Target SKU 
    "defaultSecondaryLocation": "centralus",
    "catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
    "readScale": "Disabled",
    "readReplicaCount": 0,
    "currentSku": {
      "name": "DataWarehouse",
      "tier": "DataWarehouse",
      "capacity": 3600 
    }
  },
  "location": "eastus2",
  "id": "/subscriptions/*****/resourceGroups/***/providers/Microsoft.Sql/servers/***/databases/***",
  "name": "***",
  "type": "Microsoft.Sql/servers/databases"
}

You will get a HTTP 200 OK response to the PATCH request if your target and current SKUs are the same.

The PATCH request was part of API version 2014-04-01-preview.
Newer versions of the API use a PUT request as documented here

Please do let me know if you need any further assistance.

[MarkAtAgilliance]

Hi Kelyan, I’ll give that a try again.

If the recommended way is to use PUT for create or update, I wonder if the page that I commented on should be updated to use PUT as an example rather than the old preview API?

I notice in the doc tree you pointed to that there is a update only section which still uses the PATCH verb. It has the 2017 preview api version. Is that being retired?

Regards,
Mark.

From: Kalyan Chanumolu [e-mail]
Sent: Sunday, February 24, 2019 1:12 AM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Mark Davies [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] PATCH to scale data warehouse not working. (#25498)

@MarkAtAgilliancehttps://github.com/MarkAtAgilliance

I tried to reproduce this and the PATCH request works as expected.

You should receive a HTTP 202 Accepted response for your PATCH request.
And the response to a GET request during scaling should look like the below

{

"sku": {

"name": "DataWarehouse",

"tier": "DataWarehouse",

"capacity": 9000

},

"kind": "v12.0,user,datawarehouse,gen2",

"properties": {

"collation": "SQL_Latin1_General_CP1_CI_AS",

"maxSizeBytes": 263882790666240,

"status": "Scaling",

"databaseId": "***",

"creationDate": "2019-02-24T07:59:21.943Z",

"currentServiceObjectiveName": "DW400c",      Current SKU

"requestedServiceObjectiveName": "DW1000c",   Target SKU

"defaultSecondaryLocation": "centralus",

"catalogCollation": "SQL_Latin1_General_CP1_CI_AS",

"readScale": "Disabled",

"readReplicaCount": 0,

"currentSku": {

  "name": "DataWarehouse",

  "tier": "DataWarehouse",

  "capacity": 3600

}

},

"location": "eastus2",

"id": "/subscriptions//resourceGroups//providers/Microsoft.Sql/servers//databases/**",

"name": "*",

"type": "Microsoft.Sql/servers/databases"

}

You will get a HTTP 200 OK response if your target and current SKUs are the same.

The PATCH request was part of API version 2014-04-01-preview.
Newer versions of the API use a PUT request as documented herehttps://docs.microsoft.com/en-us/rest/api/sql/databases/createorupdate

Please do let me know if you need any further assistance.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/25498#issuecomment-466753153, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AhfOilAsGAZMdcTt5LJpHijEPGqIcatpks5vQldggaJpZM4bIk2e.

[KalyanChanumolu-MSFT]

Hi Mark,

My sincere apologies if the earlier response was confusing.
Both PUT and PATCH can be used for updating a resource and will be supported.
In case of PUT request you will have to send the complete Request Body for updating a resource.
With a PATCH request however, you can send individual properties that you are trying to modify.

[MarkAtAgilliance]

Thank you very much for the clarification.

One final question. I know this is going beyond the doc bug but hopefully this will get me going.

I am using the PUT in preference because patch is not supported in Azure Data Factory Web REST calls.

When you say “In case of PUT request you will have to send the complete Request Body for updating a resource.”, can you tell me what “complete Request Body” might include? Location is the only field marked as required. I monitored a request from the powershell scale request and it used a body like this:

PUT https://management.azure.com/subscriptions/8dxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx1469/resourceGroups/xxxx/providers/Microsoft.Sql/servers/xxxx/databases/xxxx?api-version=2017-10-01-preview HTTP/1.1",
Body
{
"sku": {
"name": "DW500c"
},
"properties": {
"maxSizeBytes": 0,
"zoneRedundant": false,
"readScale": "Disabled"
},
"location": "northeurope"
}

I am not sure why or which ones of those properties are needed to update the scale. I tried just :
Body
{
"sku": {
"name": "DW500c"
},
"location": "northeurope"
}

and this appeared to work but concerned I am not corrupting something.

Location is marked as a required field and the sku will scale the DWH so these appear to be the minimum but odd that powershell chose to include maxSizeBytes, zoneRedundant and readScale.

Is there any guidance on what are the minimum request body properties for updating an existing DWH?

Regards,
Mark.

From: Kalyan Chanumolu [e-mail]
Sent: Sunday, February 24, 2019 11:59 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Mark Davies [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] PATCH to scale data warehouse not working. (#25498)

Hi Mark,

My sincere apologies if the earlier response was confusing.
Both PUT and PATCH can be used for updating a resource and will be supported.
In case of PUT request you will have to send the complete Request Body for updating a resource.
With a PATCH request however, you can send individual properties that you are trying to modify.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/25498#issuecomment-466907700, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AhfOirpvaPuYcWsQdI6J_5g9Go3z6B3qks5vQ5fZgaJpZM4bIk2e.

[KalyanChanumolu-MSFT]

You will have to include all the mandatory properties in case of a PUT request
(It is just the location for this example).
The request body you are using to scale the data warehouse will work without any side effects.

{
  "sku": {
    "name": "DW500c"
  },
  "location": "northeurope"
}

The mandatory properties are marked as Required on the documentation page.
I will however pass on your feedback to the appropriate team.

[MarkAtAgilliance]

Thank you. That is great. M.

[Mike-Ubezzi-MSFT]

@MarkAtAgilliance We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.