This article will explore the Azure alerts configuration to audit Azure SQL Database creation, modification, and deletion activities.
Introduction
Microsoft offering Azure SQL Database is a popular cloud infrastructure solution for storing or migrating your relational databases into the cloud. You can create, modify, or drop databases without worrying about the data Centre, server, or hardware requirements.
Suppose in your organization’s Azure subscription, and users regularly deploy databases as per their application requirements. They also remove these databases once their work is done. For example, a user deployed Azure SQL DB for performance testing and dropped them after their activity finishes. As a database administrator, you want to have control over these operations. You want to get notified or able to fetch information whenever someone does the following activities.
- Deploy a new Azure SQL Database
- Modify database such as scales up or down pricing tier
- Drops database
Let’s explore the alerts for Azure SQL Database and see how you can gather information to create, modify, or drop a database.
Configure Alerts in Azure SQL Database
You can create alerts for Azure SQL resources based on the defined activity. These alerts can be configured to notify recipients on their configured email.
The following diagram from Microsoft docs describes the Azure alert rule.
As shown below, to access the alerts, navigate to the Monitoring group in the Azure database menu.
Click on the create the new alert rule and verify the alert scope. By default, it shows the current database from which you launched the alert configuration. Alternatively, you can click on Edit resource and specify the required resource.
Scroll down and define the condition based on which Azure triggers an alert.
Click on Add condition and choose a signal. As shown below, you get two options for signals.
- Metrics: Metrics alerts works on the multi-dimensional metrics for the Azure monitor. For example, you can configure CPU, IO, Memory utilization-related alerts using metrics
- Activity log: The Azure activity log provides details of subscription level events. For example, it logs information such as resource creation, deletion, or modification. This activity log can be retrieved using the Azure portal, Azure PowerShell, and Azure CLI. You can also use Azure diagnostics for sending activity logs to Azure storage or send them to Log Analytics workspace for querying using Kusto query language(KQL)
Select the signal type as Activity Log, and it gives all predefined templates for administrative monitoring service.
As per the requirement of this article, choose the signal name – Create/Update Azure SQL Database (Microsoft.Sql/servers/databases).
In the alert logic, you can configure event level ( Error\Warning\Informational\Verbose), Status(Failed, Started, Succeeded) and Event initiated by (All Services and users).
In the condition preview, Azure shows the condition statement. For example, in our case, it is- Whenever the Activity Log has an event with Category=’Administrative’, Signal name=’Create/Update Azure SQL Database (Microsoft.Sql/servers/databases).’
Click on Done, and it configures the alert condition as shown below.
Actions
In the alert actions, we define Azure’s notification or actions when an alert condition is satisfied.
Click on Add actions groups. If you have any existing groups, it lists those action groups.
For this demo, click on Create action group. In this new action group page, enter the Azure subscription, Resource group, action group names, and Display name.
On the next notification page, select notification type – Email/SMS message/Push/Voice. It opens a new page, and you can specify an email address, SMS number for notification.
Click Ok and enter the name for the notification group.
Actions: On the actions page, we can define Azure’s steps if the alert is raised. It is an optional configuration, and we will skip it in this article.
Review your actions group configuration and click on Create.
It deploys the actions group and navigates back to the create alert rule page.
In the last section, enter a name for the alert rule, description (optional), a resource group for the alert rule. By default, this alert is enabled upon creation.
Click on Create Alert Rule. You get the following notification for the alert rule.
Create a new Azure SQL Database and validate the action log alert
Let’s create a new Azure database in the existing Azure SQL Server. To do so, navigate to Azure SQL Server in the Azure portal and click on Create database.
For this demo, we create a new database named DemoAzureDatabase. As shown below, here, we use the basic service tier.
Deploy the database, and it comes online in a few minutes.
It should trigger an alert and send a notification to the designated email address. Open the email, and you get the following email:
You can view the alert in the Azure portal console for the resource, SQL Server or database level, as shown below:
Alert for pricing tier modification for Azure SQL Database
Azure supports multiple service tiers for SQL databases based on their resources, performance level, and high availability and disaster recovery requirements. You can design the database in a lower pricing tier and scale up resources per application or workload requirements. Similarly, suppose you can scale down resources based on a specific schedule, workload, or metrics ( CPU, IO, Memory utilizations).
Earlier, we had configured Azure alerts for the creation and modification of Azure database resources. In my lab environment, my database is in the basic service tier. Therefore, to generate the sample alert, let me modify the pricing tier to Standard S0: 10 DTU. To modify this pricing tier, you can click on the pricing tier in the Azure portal and choose the desired model – DTU or vCPU. Further, select the pricing tier as per your requirement and cost.
As shown below, the [AzureDemoDatabase] database is in the Standard S0 pricing tier.
Refresh your Azure database portal and validate the modification.
Now, switch to your inbox, and you get the following email notification.
Scroll down, and the properties section shows that the database edition changed from Basic to Standard S0.
Similarly, let’s switch the pricing tier back to basic for Azure SQL Database. It should also trigger another alert notifying about the scale down.
Alerts for delete Azure SQL Database activity log
We have validated the alerts for creating and modifying Azure SQL DB. In many cases, we want a notification if any user drops the database. It is helpful for your database audit, and DBA should be aware of any such activity. If you get the notification on time, you can restore the database with minimum or zero data loss if any accidental deletion.
For this requirement, you can configure the Azure alert, as shown earlier in this article. For database deletion alert, select activity log – Delete Azure SQL Database ( Microsoft.Sql/Servers/databases) as shown below.
To delete a database, navigate to the respective database and click on delete in the Azure portal, as shown below.
Now, enter the database name in the text box as a token of confirmation that you want to drop this database.
It drops the database quickly and generates the following notification.
You get the following alert notification, and it gives the following information.
- Operation name: Microsoft.Sql/servers/databases/delete
- Properties: Delete DemoAzureDatabase
- Caller: It is the user who initiated database deletion
Conclusion
This article explored Azure alerts for database creation, modification, and drop of Azure SQL Database. You should configure these alerts for the Azure database for audit purposes. You can also explore more activity log templates for configuring alerts as per your monitoring requirement.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023