Ranga Babu
managing locks from resource group

Protecting Azure SQL databases from accidental deletion

July 30, 2019 by

In this article, we will review options called LOCKS in Azure SQL database and Azure SQL Server that are used to protect databases or servers from accidental deletes or configuration changes.

Azure provides a feature called locking that allows us to lock the resource group or the subscription or the resources and prevent from accidental deletes or configuration changes.

Azure provides two types of locks one is the Delete lock that prevents resources, subscriptions and resource groups being deleted. Another one is Read-only that prevents users from updating the resource, resource group, and the subscription.

Creating LOCKS on an Azure SQL database using the Azure portal.

Log in to the Azure portal and navigate to SQL databases. Click on the database.

Azure SQL databases

In the database details page, Click on Locks under the Settings section.

locks on Azure SQL databases

Click on the Add button to add a new lock.

Enter the name of the Lock. In this case, I want to protect the database from accidental deletes. So, I selected the Lock type as Delete. Enter the notes or you can leave it blank and click on OK.

delete lock on Azure SQL databases

Now let us try deleting the database. Navigate to the SQL database page. Select the database on which you created the Delete lock and click on the Delete button.

delete lock on Azure SQL databases

Type Yes in the confirmation text box and click on the Delete. It throws an error saying the database cannot be deleted due to Delete lock placed on the database.

delete lock on Azure SQL databases

Let us try updating the database configuration when we have an active Delete lock on the database.

Navigate to the SQL databases. Click on the database and click on Configure under the settings section.

delete lock on Azure SQL databases

For demo purpose, I am just changing the Data max size from 500 MB to 100 MB. Click on Apply.

change the configuration of Azure SQL database

Updating the configuration of the database is successful. Please refer to the below image.

change the configuration of Azure SQL database

When we place a Delete lock on the database, we can read and update the database but cannot delete the database.

Let us check how the Read-only lock behaves on the database. I deleted the Delete lock that is created above. To create a read-only lock, follow the above steps and select the lock type as Read-only.

Now we have only “Read-only” lock on the Azure SQL database.

Deleting the Azure SQL database with read-only lock: I tried deleting the Azure SQL database that has only one lock i.e. Read-only. The delete operation failed with an error as shown in the below image.

read-only lock on Azure SQL databases

Let us try updating the configuration of the database when the database has a ‘Read-only’ lock. I am scaling the database from Basic 100 MB storage to Basic 50 MB storage. The scaling operation failed with an error as shown in the below image.

read-only lock on Azure SQL databases

When we have Read-only lock on the database we cannot modify or delete the Azure SQL database.

Creating LOCKS on an Azure SQL Server using the Azure portal

We can also create locks at Azure SQL server level. To create locks on the Azure SQL server, navigate to the All Resources page and click on the Azure SQL server.

locks on Azure SQL Server

Creating locks on the Azure SQL server is like creating locks in the Azure SQL database. Please follow the above steps.

When we create lock at the server level, the databases on the server inherits the locks and the rule applies on all the database on the server. Please refer to the below image for locks that are inherited from the server.

inherited locks

We can not modify or delete the parent locks from child resources. i.e. the locks that are created on the Azure SQL server cannot be modified or deleted from the Locks page at the Azure SQL database.

In this case, the Read-only lock is placed at the Azure SQL Server level that will not allow us to modify or delete the Azure SQL server and all the databases on the server.

Locks placed at parent resources are inherited to newly created child resources. For example, if we place a delete lock on the Azure SQL server and if add a new Azure SQL database on the same server, the newly created database will also inherit the delete lock.

Managing the locks from the Resource group

To view all the locks placed on child resources in a resource group. Navigate to the Resource groups page. Click on the Resource group name

managing locks from resource group

Click on Locks under the Settings section. You can view the locks on all the resources in that specific resource group. Please refer to the below image. We can see the lock created on the Azure SQL Server and the lock created on Azure SQL database.

managing locks from resource group

We cannot add locks on the child resources from the Resource group page. But, we can delete the locks on child resources.

Conclusion

In this article, we explored how to configure locks on the Azure SQL database and the Azure SQL Server with different examples to protect databases from accidental deletion on Azure. In case you have any questions, please feel free to ask in the comment section below.

Please refer to the SQL Azure category to learn more about Azure SQL.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
SQL Azure

About Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies

300 Views