[QUERY] How do you scale an Azure SQL database using the SDK?

[Aileron79]

Library name and version

Azure.ResourceManager.Sql 1.0.0

Query/Question

Is there a way to scale a database up or down using the SDK? I am aware I could directly call the REST API or use Transact SQL, but I would assume that the point of an SDK is to avoid those... Could someone please point me in the right direction? I know I can do it in PowerShell using Set-AzSqlDatabase, but as I manage some resources through the SDK it would be appreciated to have this functionality there.

I assumed there would be some method on the SqlDatabaseResource class, but obviously that's not the case.

Environment

.NET 6, Visual Studio 2022, Win 10, developing an Azure App Service

PM> dotnet --info .NET SDK (reflecting any global.json): Version: 6.0.402 Commit: 6862418796

Runtime Environment: OS Name: Windows OS Version: 10.0.19043 OS Platform: Windows RID: win10-x64 Base Path: C:\Program Files\dotnet\sdk\6.0.402\

global.json file: Not found

Host: Version: 6.0.10 Architecture: x64 Commit: 5a400c212a

.NET SDKs installed: 2.1.617 [C:\Program Files\dotnet\sdk] 3.1.201 [C:\Program Files\dotnet\sdk] 3.1.424 [C:\Program Files\dotnet\sdk] 5.0.104 [C:\Program Files\dotnet\sdk] 6.0.203 [C:\Program Files\dotnet\sdk] 6.0.402 [C:\Program Files\dotnet\sdk]

.NET runtimes installed: Microsoft.AspNetCore.All 2.1.17 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All] Microsoft.AspNetCore.All 2.1.24 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All] Microsoft.AspNetCore.All 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All] Microsoft.AspNetCore.App 2.1.17 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 2.1.24 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 3.1.29 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 3.1.30 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 5.0.4 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 6.0.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 6.0.9 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 6.0.10 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.NETCore.App 2.1.17 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 2.1.24 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 3.1.29 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 3.1.30 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 5.0.4 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 6.0.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 6.0.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 6.0.10 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.WindowsDesktop.App 3.1.29 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 3.1.30 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 5.0.4 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 6.0.5 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 6.0.9 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 6.0.10 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

[azure-sdk]

Label prediction was below confidence level 0.6 for Model:ServiceLabels: 'Extensions:0.25444126,Storage:0.15804517,App Services:0.061986733'

[jsquire]

Thank you for your feedback. Tagging and routing to the team member best able to assist.

[xboxeer]

@Aileron79 I see you mentioned you are able to do this using PowerShell, can you enable PowerShell debug mode to check what rest API call it calls so that we can help find out the corresponding SDK call?

[Aileron79]

Not entirely sure what you mean with debug mode, but I tried

Set-PSDebug -Trace 2 -Strict

with different parameters (With and without Strict, Trace 1 or 0), but none of them revealed anything. This would scale my database, here is the output:

DEBUG:    1+  >>>> Set-PSDebug -Trace 0
DEBUG:     ! CALL function '<ScriptBlock>'
PS >         Set-AzSqlDatabase -ResourceGroupName myresourcegroup `
>>             -ServerName mydbserver `
>>             -DatabaseName mydatabase `
>>             -Edition Standard `
>>             -RequestedServiceObjectiveName S1
>>

ResourceGroupName             : myresourcegroup
ServerName                    : mydbserver
DatabaseName                  : mydatabase
Location                      : northeurope
DatabaseId                    : 6xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx
Edition                       : Standard
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : 32212254720
Status                        : Online
CreationDate                  : 12.04.2021 06:59:47
CurrentServiceObjectiveId     : 00000000-0000-0000-0000-000000000000
CurrentServiceObjectiveName   : S1
RequestedServiceObjectiveName : S1
RequestedServiceObjectiveId   :
ElasticPoolName               :
EarliestRestoreDate           : 11.10.2022 09:44:36
Tags                          :
ResourceId                    : /subscriptions/blah/..../..../databases/mydatabase
CreateMode                    :
ReadScale                     : Disabled
ZoneRedundant                 : False
Capacity                      : 20
Family                        :
SkuName                       : Standard
LicenseType                   :
AutoPauseDelayInMinutes       :
MinimumCapacity               :
ReadReplicaCount              : 0

[xboxeer]

Hi @Aileron79 You can add -Debug option at the end of your PS command to let powershell export the raw rest api call it uses. Please note this debug output includes credential information so please remove those information before you share, and the overall trace would be very long so you might need to put it somewhere else instead of direct paste in this chat chain

[Aileron79]

Oh, sorry I thought turning on Debug globally would do the same. Here is the complete log output, I hope I have anonymized all relevant fields.

Guess it was too long to paste here, moved it here https://pastebin.com/CMvgnmh7

[Aileron79]

As there does not seem to be any useable documentation I continued to experiment. I discovered the DatabaseResource.UpdateAsync() method which takes an SqlDatabasePatch as an argument. This is what I call very poor documentation as it does not give any clues on how to properly use it. I just tried creating a new SqlSku and passing it to the UpdateAsync() method like this:

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

However, this is what result looks like:

{Azure.ResourceManager.Sql.SqlArmOperation`1[Azure.ResourceManager.Sql.SqlDatabaseResource]}
    HasCompleted: false
    HasValue: false
    Id: '((Azure.ResourceManager.Sql.SqlArmOperation<Azure.ResourceManager.Sql.SqlDatabaseResource>)result).Id' threw an exception of type 'System.NotImplementedException'
    Value: '((Azure.ResourceManager.Sql.SqlArmOperation<Azure.ResourceManager.Sql.SqlDatabaseResource>)result).Value' threw an exception of type 'System.InvalidOperationException'

I'd highly appreciate any help.

[fengzhou-msft]

@Aileron79 the result in your code is a long running operation object. You can do this to get the real result:

var lro = await sqlDatabaseResource.UpdateAsync(Azure.WaitUntil.Completed, patch);
var result = lro.Value;

[ghost]

Hi, we're sending this friendly reminder because we haven't heard back from you in 7 days. We need more information about this issue to help address it. Please be sure to give us your input. If we don't hear back from you within 14 days of this comment the issue will be automatically closed. Thank you!