I've just started using Microsoft's .NET SDK for Azure to manage some resources like SQL databases. I try using the Azure.ResourceManager.Sql package for that purpose. I could not find out how to scale up and down regular SQL databases or trigger that process using the SDK - does anyone know?
I would expect this to be a method on the SqlDatabaseResource class where I can also access the current scaling level at the .data.sku properties.
As far as I understand it, the SDK covers the functionality of the Azure API, so I guess I could do it through an API call, but as there is an SDK I would prefer to use it.
On this Microsoft Learn page it says
Use the Azure SQL Database management library to create, manage, and scale Azure SQL Database server instances.
refering to the package Microsoft.Azure.Management.Sql.Fluent which is deprecated:
The recommended replacement is exactly what I am trying to use - so how is it possible to scale databases using this SDK?
Ok I finally figured out how it works. MS support suggested to set the .RequestedServiceObjectiveName
property of an SqlDatabasePatch
and call the Update
method on the SqlDatabaseResource
. Unfortunately, that does not work as that property is read only.
However, I tried it like I described below and it works for me. Lacking any documentation and the MS support not being particularly helpful I cannot tell if this is the "official" or recommended way to do it or if it will continue to work in future releases of the SDK, so I'm still open for better suggestions or confirmation.
So what you have to do is walk through Azure's hierarchy to access the database:
ArmClient
SubscriptionResource
ResourceGroupResource
within that subscriptionSqlServerResource
within that resource groupSqlDatabasePatch
and set its Sku
property to a new instance of SqlSku
with the Capacity
, Family
and Tier
properties set accordinglysqlDatabaseResource
's Database ID to SqlDatabasePatch.SourceDatabaseId
Update
or UpdateAsync
on the sqlDatabaseResource
providing the SqlDatabasePatch
as a parameter.Azure.WaitUntil.Started
) or wait for it to be finished (Azure.WaitUntil.Completed
)Here's the code:
ArmClient client = new ArmClient(new DefaultAzureCredential());
SubscriptionResource subscription = await client.GetDefaultSubscriptionAsync();
ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();
ResourceGroupResource resourceGroup = await resourceGroups.GetAsync("My_resourceGroup");
SqlServerResource sqlServerResource = await resourceGroup.GetSqlServerAsync("MySqlServerName");
SqlDatabaseResource sqlDatabaseResource = await sqlServerResource.GetSqlDatabaseAsync("MyDatabaseName");
SqlDatabasePatch patch = new SqlDatabasePatch();
SqlSku sku = new SqlSku("GP_Gen5");
sku.Capacity = 2;
sku.Family = "Gen5";
sku.Tier = "GeneralPurpose";
patch.Sku = sku;
patch.SourceDatabaseId = sqlDatabaseResource.Id;
var result = await sqlDatabaseResource.UpdateAsync(Azure.WaitUntil.Started, patch);
How to add/remove a Database from an ElasticPool:
Works the same way as above, just set the ElasticPoolId
property of the patch instead of the Sku
:
patch.ElasticPoolId = new ResourceIdentifier("/subscriptions/<subscriptionID>/resourceGroups/<resourcegropuname>/providers/Microsoft.Sql/servers/<servername>/elasticPools/<elasticpoolname>");
patch.SourceDatabaseId = sqlDatabaseResource.Id;
var result = await sqlDatabaseResource.UpdateAsync(Azure.WaitUntil.Started, patch);
Unfortunately, the result
variable does not contain any success information. While I am aware that the WaitUntil.Started
will not return the final result, it would be nice if the UpdateAsync
would return the results of an initial check - like if it's even possible to scale the database to the requested tier.