Rajendra Gupta

Azure Alerts for creating, modifying and deleting Azure SQL Databases

September 13, 2021 by

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.

Azure Alerts diagram representations

As shown below, to access the alerts, navigate to the Monitoring group in the Azure database menu.

Monitoring Alerts

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.

Create Alert Rule

Scroll down and define the condition based on which Azure triggers an alert.

Condition

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 a signal

Select the signal type as Activity Log, and it gives all predefined templates for administrative monitoring service.

Signal type

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).’

Configure Signal Logic

Click on Done, and it configures the alert condition as shown below.

Condition name

Actions

In the alert actions, we define Azure’s notification or actions when an alert condition is satisfied.

Action group

Click on Add actions groups. If you have any existing groups, it lists those action groups.

Add 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.

Enter information in basic section

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.

Email, SMS, Push, Voice configuration.

Click Ok and enter the name for the notification group.

Notification

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.

Action type

Review your actions group configuration and click on Create.

Review alert configuration

It deploys the actions group and navigates back to the create alert rule page.

Action group in alert rule

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.

Alert rule details

Click on Create Alert Rule. You get the following notification for the alert rule.

Alert Notification

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.

Create a new Azure DB

For this demo, we create a new database named DemoAzureDatabase. As shown below, here, we use the basic service tier.

Enter the new database name

Deploy the database, and it comes online in a few minutes.

Database state

It should trigger an alert and send a notification to the designated email address. Open the email, and you get the following email:

View Azure Monitor Alert

View Azure Monitor Alert proprties

You can view the alert in the Azure portal console for the resource, SQL Server or database level, as shown below:

Alert in Azure Portal

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.

Scale up resources

Refresh your Azure database portal and validate the modification.

View modified pricing tier

Now, switch to your inbox, and you get the following email notification.

Email Notification

Scroll down, and the properties section shows that the database edition changed from Basic to Standard S0.

View alerts for change DB edition

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.

Scale down

View alert for scale down resources

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.

Alerts for delete Azure SQL Database activity log

To delete a database, navigate to the respective database and click on delete in the Azure portal, as shown below.

Delete database for demonstration

Now, enter the database name in the text box as a token of confirmation that you want to drop this database.

Enter the database name for confirmation

It drops the database quickly and generates the following notification.

Dropping database

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

Operation name

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.

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

227 Views