none
Inconsistency with Azure SQL Server deletion

    General discussion

  • Disclaimer: that's not a question, but likely to be a bug report. Unfortunately, I do not know a better place to raise such issues.

    Some weeks before I had an issue with re-creating SQL Server.
    I deleted old one, and once I got an acknowledgment that it was deleted - I triggered another deployment. 
    Azure said the name is taken and failed my request. While validation on portal was saying that name is available.
    Also, I tried to connect to the old name with SQL Server Management Studio and it gave the same exception, as for not-existing resources.

    I also found, that in Activity log event about deleted resource appears several minutes later than I've got the initial notification.

    This issue separately is not that harmful, because everything goes back to a consistent state after several minutes.

    But it became very tricky for me when I aborted re-deployment using Terraform.
    Terraform was hanging for a while and I pressed Ctrl-C to stop it. After that, this hanging deployment was lost somewhere in Azure limbo preventing me from creating a resource with this name for few hours in a row.

    That's not a critical issue, but now every time when I want to re-create SQL Server, first I wait for the corresponding event to appear in Activity log and only then trigger a new deployment.


    Monday, February 11, 2019 2:51 PM

All replies

  • Hi Igor,

    This is a great post and one that has initiated several conversations.

    Azure SQL Support Blog: Server name has already been used after you deleted it

    GitHub Issue: SQL Database deploy to Azure fails when re-creating the DB which recommends dropping the database objects and not the database instance itself. This saves the time of having to wait for the specific delete event in the Activity Log. See the Stack Overflow thread below...

    Stack Overflow: How to drop all tables and reset an Azure SQL Database

    Regards,

    Mike

    Monday, February 11, 2019 7:18 PM
    Moderator
  • In our case, we're re-creating the entire environment, but not the only one database, so dropping tables is not a solution at all.

    Looks like we have only two options:
    - Add some randomness into server name when we create our disposable environments
    - Or add some polling logic into scripts to check if the server name is already available...
    These options are feasible, but they are workarounds.

    Three points:

    1. Don't you find it's misleading, that Azure sends notification about successfully deleted resource 5-10 minutes before a resource is actually deleted? Moreover, it's accompanied with corresponding Activity log entries: when deletion was Accepted and Completed

    2. Deployment is not actually failing immediately - it's hanging and only Activity log is populated with the corresponding event.

    3. When I do resource provisioning with ARM templates, at least, I have an option to cancel the deployment when I see that one of related events is unique-name-violation error.
    When I do deployment with other tools like Terraform, as I mentioned above or azure cli, I have no option to cancel it properly (or I don't know this option?) and command interruption, in this case, leads to blocked server-name for hours, not minutes.

    It's frustrating to find out, that some resources are like snowflakes and require special treatment...
    Could you consider this as a feature request, at least?



    Monday, February 11, 2019 8:07 PM
  • I agree, Igor.

    The best channel to address this is via the Azure SQL User voice. Please Up Vote and Comment on the entry that is related to your issue (via the link). The product team is currently reviewing this specific entry. You have a great suggestion and by providing your idea to the item that is currently being reviewed, you pass this information along to the product group for condieration.

    Please ask any additional questions.

    Regards,

    Mike

    Monday, February 11, 2019 8:48 PM
    Moderator
  • Hello,

    There is usually some delay for reusing the name of deleted objects in Azure. Sometimes it could be hours.

    As with all things that rely on the DNS, there is some propagation time upon changing something that it will be reflected throughout the network.


    In addition. when a server is deleted it is soft deleted, which allows support to restore it quickly in exceptional circumstances.  Microsoft retains the server in this state for up to five days, which reserves the name for that period.  If an attempt is made to create a server with the same name under the same subscription then the old server is released and the name can be reused.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, February 11, 2019 9:43 PM
  • Thanks for your replies!

    Alberto Morillo, I'm fine with this behavior in general, but it would be nice to have a predictable user experience. Now, Portal says the name is available, while deployment is hanging because of unique-name-violation.

    The worst part: I don't get any error response at all! Only when resource provisioning is hanging for a while I can go to Activity log to find corresponding error events. And keep in mind, that Activity log option is available only for ARM template users! The rest is left in uncertainty without a clue what's going on

    If reserving names for a while is desired behavior - just return an error response to your users immediately. And here I mean all users: go or python libraries, ARM templates, etc.

    This situation cost me a significant amount of working hours. Now I more or less know how to deal with a similar one in <g class="gr_ gr_687 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="687" id="687">future</g>, but I'm afraid, that I'm not the only one fighting this issue.

    Tuesday, February 12, 2019 7:08 AM