How to set DTU for Azure Sql Database via Api?

Question

I am able to create the Azure Sql database by mentioning the pricing tier.

I am trying to set the Memory and DTU for the database.

I am not able to find the Correct Api , Here is what i tried

PUT :    https://management.azure.com/subscriptions/<subscription-ID>/resourceGroups/<Resource-group-Name>/providers/Microsoft.Sql/servers/<Server-name>/databases/<Database-name>/?api-version=2014-04-01

Request Body :

 {
                "location": "East Asia",
                "properties": {
                    "edition": "Premium",
                    "collation":"SQL_Latin1_General_CP1_CI_AS",
                "sampleName": "blank database",
                "serviceTierAdvisors":[
                    {
                        "maxSizeInGB":"150",
                        "maxDtu":"500"
                    }   
                    ]
                }   
    }

I am not getting the proper error message also , Can anyone guide me with the Parameter for setting DTU at Database Level ??

Answer 1

Step 1: You need to create clientId,Token and clientSecret on the azure portal. Use this tutorial (there are many that are just confusing) this one works. how to get a token,clientID and client secret

Step2 : Next you need need to make a choice if you want to use the rest API or SDK. I prefer the SDK but its up to you (you dont need to figure out what all the json values are using the SDK). Step 3 onward assumes you selected the SDK.

To install the pre-requisites for the SDK you need the following nuget packages:

Install-Package Microsoft.Azure.Management.Fluent
Install-Package Microsoft.Azure.Management.ResourceManager.Fluent

And you need the following for SQL:

Microsoft.Azure.Management.Sql.Fluent

You need the tokens in Step 1 to authenticate.

Step3: Next look at this example on how to use the SDK - good to see how to apply. Azure SQL SDK

Finally: Here is a code snippet once you have all the above - does the job brilliantly.

  string tenantId = "9596ecae-xxxxxxx";
  string clientAppId= "51c28b54-xxxxxxxxx";
  string secret = "@w6.Quv--your secret";

  credentials = SdkContext.AzureCredentialsFactory
           .FromServicePrincipal(clientAppId,
           secret,
           tenantId,
           AzureEnvironment.AzureGlobalCloud);

 var azure = Microsoft.Azure.Management.Fluent.Azure
            .Configure()
            .Authenticate(credentials)
            .WithDefaultSubscription();

 var database = azure.SqlServers
        .GetById("/subscriptions/<your specific>/resourceGroups/<your specific>/providers/Microsoft.Sql/servers/<your specific>")
        .Databases.GetById("/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Sql/servers/xxx/databases/xxx");

 var result = database.Update()
            .WithEdition(DatabaseEditions.Standard)
            .WithServiceObjective(ServiceObjectiveName.S0) <-- CHANGE LEVEL OF DB.
            .Apply();

TIP AND HELPER FOR SQL AND DB STRING NAME: Getting the right string to replace the in above code is tricky. A shortcut is to call List first then you can debug and see what to place into the GetById functions you can call this first and see the results:

 azure.SqlServers.List() <-- Debug this and you will see the full names of the Database Servers string names
 Then do azure.SqlServer.Databases.List() <-- an array of objects where you can get the string for the database name.

Hope that helps - the documentation is terrible.

Answer 2

Here is what I did and it works to increase the instance and the DTUs. I had to experiment to get it to work and could not find solid documentation specifically calling out DTUs:

I get my bearer token then call this method to increase or decrease the dtus. Note the call to GetUpdateSettingsJson() returns the following Two Json strings depending on whether I am increasing or decreasing DTU / Instance sizing: Increase JSON: "{"sku": {"name": "Premium","tier":"Premium","capacity": 250} }" Decrease JSON: "{"sku": {"name": "Standard","tier":"Standard","capacity": 50} }"

private static async Task<string> UpdateDatabaseSettings()
    {
        using (HttpClient client = new HttpClient())
        {
            var updateDtuEndpoint = String.Format(@"https://management.azure.com/subscriptions/{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}/databases/{3}?api-version=2017-10-01-preview",
                subscriptionId,
                databaseResourceGroup,
                databaseServer,
                databaseName
                );
            var accept = "application/json; charset=utf-8";

            client.DefaultRequestHeaders.Add("Accept", accept) ;
            client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue ("Bearer", bearerToken);

            //string queryParameters = String.Format(@"resource=https%3A%2F%2Fgraph.microsoft.com%2F&client_id={0}&grant_type=client_credentials&client_secret={1}", clientId, clientSecret);

            string jsonUpdateSetting = GetUpdateSettingsJson();

            using (var response = await client.PatchAsync(updateDtuEndpoint, new StringContent(jsonUpdateSetting, Encoding.UTF8, "application/json")))
            {
                if (response.StatusCode == System.Net.HttpStatusCode.Accepted)
                {
                    WriteLine("Capacity Update is Processing");
                    return "Capacity Update is Processing";
                }
                else
                {
                    Environment.ExitCode = 1;
                    WriteLine("Capacity Update failed.");
                    return "Capacity Update failed. ";
                }
            }


        }
    }

EXTRA: This method above only puts the request in, the method below is called until it can confirm that the change is EFFECTIVE. The method above only requests the change. The method just checks that the RequestServiceObject = the CurrentServiceObject (it does not confirm DTUs).

private static async Task<bool> CheckDatabaseSettng()
    {
        using (HttpClient client = new HttpClient())
        {
            var checkDatabaseSettings = String.Format(@"https://management.azure.com/subscriptions/{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}/databases/{3}?api-version=2017-10-01-preview",
                subscriptionId,
                databaseResourceGroup,
                databaseServer,
                databaseName
                );
            var accept = "application/json; charset=utf-8";

            client.DefaultRequestHeaders.Add("Accept", accept);
            client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", bearerToken);

            using (var response = await client.GetAsync(checkDatabaseSettings))
            {
                if (response.StatusCode == System.Net.HttpStatusCode.OK)
                {
                    var jsonresult = JObject.Parse(await response.Content.ReadAsStringAsync());
                    var jsonProperties = jsonresult["properties"];
                    if (jsonProperties == null)
                    {
                        throw new Exception("Could not find properties that are supposed to be returned in this call.");
                    }
                    var currentServiceObject = (string)jsonProperties["currentServiceObjectiveName"];
                    var requestedServiceObject = (string)jsonProperties["requestedServiceObjectiveName"];

                    string msg = string.Format("currentServiceObjectiveName = {0}; requestedServiceObjectiveName = {1}", currentServiceObject, requestedServiceObject);
                    WriteLine(msg);
                    if (currentServiceObject == requestedServiceObject)
                    {
                        return true;
                    }
                }
            }
        }

        return false;
    }

Hope this helps someone save more time than I spent on it.

Answer 3

Can anyone guide me with the Parameter for setting DTU at Database Level ??

The correct DTU parameter should be requestedServiceObjectiveName. Its type is enum. You could set following values for this property.

Basic, 
S0, S1, S2, S3
P1, P2, P4, P6, P11, P15
System, System2
ElasticPool

Please check the corresponding DTU values as following.

Basic(5DTU), 
S0(10DTU), S1(20DTU), S2(50DTU), S3(100DTU)
P1(125DTU), P2(250DTU), P4(500DTU), P6(1000DTU), P11(1750DTU), P15(4000DTU)
System, System2
ElasticPool

Answer 4

You need to use the API for updating a database as explained in this article.

For example:

{
    "parameters": {
        "subscriptionId": "00000000-1111-2222-3333-444444444444",
        "resourceGroupName": "sqlcrudtest-4799",
        "serverName": "sqlcrudtest-5961",
        "databaseName": "testdb",
        "api-version": "2014-04-01",
        "parameters": {
            "properties": {
                "edition": "Standard",
                "status": "Online",
                "createMode": "Default",
                "serviceLevelObjective": "S0",
                "collation": "SQL_Latin1_General_CP1_CI_AS",
                "maxSizeBytes": "268435456000",
                "currentServiceObjectiveId": "f1173c43-91bd-4aaa-973c-54e79e15235b",
                        "requestedServiceObjectiveId": "dd6d99bb-f193-4ec1-86f2-43d3bccbc49c",
                                            "requestedServiceObjectiveName": "Basic",
                "defaultSecondaryLocation": "Japan West",
                "earliestRestoreDate": "2017-02-10T01:52:52.923Z",
                "containmentState": 2,
                "readScale": "Disabled"
            }
        }
    },
    "responses": {
        "200": {
            "body": {
                "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-4799/providers/Microsoft.Sql/servers/sqlcrudtest-5961/databases/testdb",
                "name": "testdb",
                "type": "Microsoft.Sql/servers/databases",
                "location": "Japan East",
                "kind": "v12.0,user",
                "properties": {
                    "edition": "Standard",
                    "status": "Online",
                    "serviceLevelObjective": "S0",
                    "collation": "SQL_Latin1_General_CP1_CI_AS",
                    "creationDate": "2017-02-24T22:39:46.547Z",
                    "maxSizeBytes": "268435456000",
                    "currentServiceObjectiveId": "f1173c43-91bd-4aaa-973c-54e79e15235b",
                    "requestedServiceObjectiveId": "dd6d99bb-f193-4ec1-86f2-43d3bccbc49c",
                    "requestedServiceObjectiveName": "Basic",                   "sampleName": null,
                    "defaultSecondaryLocation": "Japan West",
                    "earliestRestoreDate": "2017-02-10T01:52:52.923Z",
                    "elasticPoolName": null,
                    "containmentState": 2,
                    "readScale": "Disabled",
                    "failoverGroupId": null
                }
            }
        },
        "202": {}
    }
}

On above example I am scaling down from Standard S0 to Basic tier.

Hope this helps.