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.
In the database details page, Click on Locks under the Settings section.
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.
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.
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.
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.
For demo purpose, I am just changing the Data max size from 500 MB to 100 MB. Click on Apply.
Updating the configuration of the database is successful. Please refer to the below image.
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.
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.
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.
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.
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
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.
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.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019