Azure-cli: az sql db delete does nothing

[GuyHarwood]

Describe the bug
executing az sql db delete --name mydb --resource-group mygroup --server myserver --yes returns and does nothing. The database remains active.

To Reproduce
At the command line run `az sql db delete --name mydb --resource-group mygroup --server myserver --yes. The behaviour is the same when you omit the confirmation parameter --yes and when you include the --no-wait option also.

Expected behavior
The SQL database should be deleted.

Environment summary
azure-cli (2.0.41)
MacOS
Same behaviour is reproduced when running in VSTS Azure CLI Task

Additional context
none.

[jaredmoo]

Are you able to see the database with az sql db show?

[GuyHarwood]

No. output is...

The Resource 'Microsoft.Sql/servers/xxx.database.secure.windows.net/databases/xxx' under resource group 'xxx' was not found.

[jaredmoo]

I see. The most likely cause of this problem is that the Azure Resource Manager API that CLI is using has a cache of resources such as servers and databases, if you create a database using other entry points such as the old Service Management API or T-SQL create database then on some rare occasions the Resource Manager cache isn't aware of the database, so GET will not show the database and DELETE will be no-op because Resource Manager thinks the database already doesn't exist.

We can mitigate this issue if you contact support, but there is also a self-mitigation possible: you can use sql db create to "create" the database - this will create a cache entry, then the SQL DB backend we will see that the database actually already exists so the database will be updated. How do you make sure this update is a no-op so that it is quick? You can use sql db list to list all databases in the server (in this case the API will use the server's cache entry, so the db should show up), then when you issue the create command make sure you specify the same service level objective that the database already has - this will ensure you don't need to wait for scale operation to complete. When this create is completed, the database should show up with db show and then you should be able to delete it using db delete.

Sorry you were impacted by this issue.

[GuyHarwood]

Hi Jared, thanks for the detail.

To give you a complete picture - during build we create the database using TSQL (which is sent via the npm tedious package inside a node app), run a bunch of migrations to prove they work, then drop the database using the Azure CLI sql db delete command as described in my initial post. So this could explain why we are experiencing this issue. Oddly enough, the sql db show command still can't find it over 18 hours later, so there must be an issue with the cache somewhere.

With the info you have provided i am going to modify it to create the database with the CLI first and see if that mitigates the issue.

Will report back.

[GuyHarwood]

I got this working by removing the fully qualified domain name from the --server- argument of the sql db delete command. I am still creating the database via TSQL.

i discovered this by trying your suggestion of sql db create and it couldn't find the server when it was fully qualified, but could when it was not.

Maybe there are some inconsistencies behind the CLI around this server name, and using FQDN

[jaredmoo]

Oh, that would also explain the problem. There is no inconsistency - an Azure SQL server's name and FQDN are not the same thing. Azure CLI and Azure PowerShell always use the server's name (e.g. myserver), not the FQDN (e.g. myserver.database.windows.net).