如何通过 API 为 Azure SQL 数据库设置 DTU?

Question

我可以通过提及定价层来创建 Azure Sql 数据库。

我正在尝试为数据库设置内存和 DTU。

我找不到正确的API，这是我尝试过的

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

请求正文 ：

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

我也没有收到正确的错误消息，任何人都可以指导我使用在数据库级别设置 DTU 的参数吗？

Answer 1

谁能指导我使用在数据库级别设置 DTU 的参数？

应请求正确的 DTU 参数服务目标名称。它的类型是枚举。可以为此属性设置以下值。

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

请检查相应的 DTU 值，如下所示。

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 2

您需要使用 API 来更新数据库，如本文中所述。

例如：

{
"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": {}
}
}

在上面的示例中，我正在从标准 S0 缩减到基本层。

希望这有帮助。

Answer 3

这是我所做的，它可以增加实例和 DTU。我不得不进行实验才能让它工作，但找不到专门指出 DTU 的可靠文档：

我得到我的持有者令牌，然后调用此方法来增加或减少 dtus。请注意，对 GetUpdateSettingsJson(( 的调用返回以下两个 Json 字符串，具体取决于我是增加还是减少 DTU/实例大小：增加 JSON："{"sku"： {"name"： "高级"，"层"："高级"，"容量"： 250} }"减少 JSON： "{"sku"： {"名称"： "标准"，"层"："标准"，"容量"： 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：上面的这个方法只把请求放进去，下面的方法被调用，直到它可以确认更改是有效的。上述方法仅请求更改。该方法仅检查 RequestServiceObject = CurrentServiceObject(它不确认 DTU(。

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;
}

希望这可以帮助某人节省比我花在上面的更多时间。

Answer 4

步骤 1：需要在 Azure 门户上创建客户端 ID、令牌和客户端机密。 使用本教程(有很多只是令人困惑(这个有效。如何获取令牌，客户端ID和客户端密钥

Step2： 接下来，如果要使用其余的API或SDK，则需要做出选择。我更喜欢 SDK，但它取决于您(您无需弄清楚使用 SDK 的所有 json 值是什么(。步骤 3 开始假设你选择了 SDK。

若要安装 SDK 的先决条件，需要以下 nuget 包：

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

对于 SQL，您需要以下内容：

Microsoft.Azure.Management.Sql.Fluent

需要步骤 1 中的令牌进行身份验证。

Step3：接下来看看这个如何使用SDK的例子 - 很高兴看到如何应用。 Azure SQL SDK

最后：一旦您具备上述所有内容，这是一个代码片段 - 出色地完成了工作。

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();

SQL 和 DB 字符串名称的提示和帮助程序：获取正确的字符串来替换上面的代码是很棘手的。一个快捷方式是先调用 List，然后你可以调试并查看要放入 GetById 函数的内容，你可以先调用这个并查看结果：

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.

希望有帮助 - 文档很糟糕。