Azure SQL Database and SQL Managed instance are Microsoft offerings for PaaS SQL Server in cloud infrastructure. In the case of a traditional on-premises SQL Server, at certain times, we require database system downtime to perform specific operations such as hardware upgrades, OS and SQL Server patching. In a critical production database system, it is challenging to get downtime and schedule these activities.
Like the on-premises infrastructure, Azure also performs planned maintenance for SQL Databases and SQL Managed Instances. Although Azure carries out the patching or maintenance, you might think of possible downtime so that you can plan your application availability.
Azure database and managed instance offer the following guaranteed availability.
-
Azure SQL Database
- Basic, Standard, Business Critical or Premium tiers without Zone Redundant Deployments: 99.99%
- Business Critical or Premium tiers with Zone Redundant Deployments: 99.995%
- Azure SQL Managed instance
|
Monthly uptime percentage |
Service credit |
|
< 99.99% |
10% |
|
<99% |
25% |
|
<95% |
100% |
Azure SQL Database maintenance window (preview)
Therefore, to maintain the commitment and SLA, Azure uses modern, robust service architecture for providing non-impactful and fully transparent service availability. For example, it uses hot patching (dynamic patching or live update) patching to apply updates without restarting the services. However, few updates require service restart, but it is maintained within the defined SLA.
For the high-availability of Azure database infrastructure, it maintains a quorum of database replicas. At a given time, at least one primary and one secondary replica must be healthy. During the planned maintenance, these replicas do not go down altogether. It ensures that there is no client downtime. Once it requires Azure primary replica to be down, it performs a failover, and the secondary takes over the role of a primary. In some specific cases, it might require multiple failovers. If the application initiates a connection to a database while failover is undergoing and the primary replica is not online, it receives 40613 – Database Unavailable error. These maintenance windows information was not available to the Azure DB users. Therefore, recently Microsoft announced the maintenance window for both Azure SQL Database and SQL Managed Instances as per the customer’s feedback. Currently, it is in the preview phase.
By default, Azure performs maintenance daily from 5 PM to 8 AM as per your local time. Here, the local time refers to the Azure region time zone. It avoids the potential peak business hours to prevent minimal interruptions. Azure uses automatic scheduling as well to prevent updates during regions’ peak business hours. It also analyzes database workload patterns to choose the best time for failover resources.
We can further adjust the planned maintenance using the following two additional maintenance window options.
- Weekday (Monday – Thursday) Window: 10 PM – 6 AM
- Weekend (Friday-Sunday) Window: 10 PM – 6 AM
Azure performs maintenance during the configured planned maintenance window.
Configure maintenance window during Azure SQL Database or Managed Instance creation using Azure portal
To configure the maintenance window during the Azure SQL database deployment, navigate to the Additional Settings tab on Create SQL database.
Here by default, it uses system default – 5 PM to 8 AM maintenance window; however, you can configure it per the two additional maintenance window options as shown below.
- Note: Currently, choosing maintenance windows (other than system default) is supported in the specific regions. You can refer to Azure region support for the list of the supported regions
Configure maintenance windows for existing databases using Azure Portal
You can also modify the maintenance window for the existing Azure SQL Database and SQL Managed Instance. In the Azure portal, navigate to Settings and Maintenance.
Currently, you do not get an option to configure the maintenance window in the following cases.
- It is not available for Basic, Standard S0, S1 Service tier, M-services, FsV2 series, DC-series hardware
- The Hyperscale service tier does not support a non-default Azure SQL Maintenance window. It is planned for later in 2021
Configure maintenance window during Azure SQL Database or Managed Instance creation using Azure PowerShell
We can use Azure PowerShell to create a new Azure SQL Database with a specified maintenance window from the options shown earlier.
Check the existing maintenance window options for your Azure region.
|
1 2 3 4 |
$location = "eastus" Write-Host "Available maintenance schedules in ${location}:" $configurations = Get-AzMaintenancePublicConfiguration $configurations | ?{ $_.Location -eq $location -and $_.MaintenanceScope -eq "SQLDB"} |
For the East US region, it returns the following values:
- SQL_EastUS_DB_1: 10:00 PM to 6:00 AM Eastern Standard Time, Monday to Thursday
- SQL_EastUS_DB_2: 10:00 PM to 6:00 AM Eastern Standard Time, Friday to Sunday
The following script creates the database [mytestsqldatabase1] using the maintenance window SQL_EastUS_DB_1. As shown above, this maintenance window is for 10PM to 6 AM EST Monday To Thursday.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$resourceGroupName = "1-33ec9676-playground-sandbox" $serverName = "azureproduction" $databaseName = "mytestsqldatabase1" # Set selected maintenance window $maintenanceConfig = "SQL_EastUS_DB_1" Write-host "Creating an Azure SQL database with maintenance window ${maintenanceConfig} ..." $database = New-AzSqlDatabase ` -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -DatabaseName $databaseName ` -MaintenanceConfigurationId $maintenanceConfig $database |
![creates the database [mytestsqldatabase1] using the maintenance window SQL_EastUS_DB_1.](/wp-content/uploads/2021/07/creates-the-database-mytestsqldatabase1-using-th.png)
Once the script finishes, go to the newly created Azure database and verify the maintenance window.
Configure maintenance window during Azure SQL Database or Managed Instance creation using Azure PowerShell
Suppose you want to modify the maintenance window from the default or custom window. Previously, we updated it using the Azure Portal. The following script uses the cmdlet Set-AzSqlDatabase and modifies the maintenance window to SQL_EastUS_DB_2.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# Select different maintenance window $maintenanceConfig = "SQL_EastUS_DB_2" $resourceGroupName = "1-33ec9676-playground-sandbox" $serverName = "azureproduction" $databaseName = "mytestsqldatabase1" Write-host "Changing database maintenance window to ${maintenanceConfig} ..." $database = Set-AzSqlDatabase ` -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -DatabaseName $databaseName ` -MaintenanceConfigurationId $maintenanceConfig $database |
Refresh Azure console. As shown below, the [mytestsqldatabase1] maintenance window changed to 10 PM to 6 AM EST, Friday To Sunday.
Azure CLI script for defining maintenance window for a new database
Like the Azure PowerShell, we can utilize Azure CLI (command-line interface) to configure the maintenance window during an Azure SQL Database creation.
The following CLI script creates an Azure database with SQL_EastUS_DB_1 maintenance window.
|
1 2 3 4 5 6 7 8 9 10 |
# Set variables for your database $resourceGroupName = "1-80bcfcc7-playground-sandbox" $serverName = "azureproduction" $databaseName="testsqldb" # Set selected maintenance window $maintenanceConfig = "SQL_EastUS_DB_1" # Create database az sql db create --resource-group $resourceGroupName --server $serverName --name $databaseName --maint-config-id $maintenanceConfig |
Validate the configured maintenance window in the Azure portal, as shown below.
Modify maintenance window using Azure CLI
You can modify the maintenance window as per your requirement in the supported regions. The following script modifies it to SQL_EastUS_DB_2 for the [testsqldb] database using CLI script.
|
1 2 3 4 5 6 7 8 |
$resourceGroupName = "1-80bcfcc7-playground-sandbox" $serverName = "azureproduction" $databaseName="testsqldb" # Select different maintenance window $maintenanceConfig = "SQL_EastUS_DB_2" # Update database az sql db update --resource-group $resourceGroupName --server $serverName --name $databaseName --maint-config-id $maintenanceConfig |
Enable alerts on planned maintenance events via advance notification
We can configure alerts for the Azure SQL Database upcoming planned maintenance events 24 hours in advance. It sends the notification for the customers to plan their activities during the scheduled maintenance window to have minimal disruptions. The advanced notification is available for the maintenance window (preview) notification. We can configure text, emails, Azure push notifications, voicemails for the notifications. We can also configure the notification once the maintenance window ends.
- Note: Advanced maintenance notification is not available for Azure SQL Managed Instances
Create an advance notification
To create the advanced notification, search for Service Health in the Azure portal.
In the Service health menu, select Health alerts, then Add service health alert.
In the create an alert rule, do the following changes:
- Services: Select SQL Database from the drop-down services list
- Region: Select global or specific regions such as East US for which you wish to get the notification. You should select the regions in which you have deployed Azure databases
-
Service health criteria: By default, it selects the following health criteria:
- Service issue
- planned maintenance
- Health advisories
- Security Advisory
For example, I choose the East US region and Planned maintenance service health criteria in the following screenshot.
Review the action group configuration.
You can view the configured action group in the following screenshot.
Scroll-down and specify the alert rule details.
- Alert rule name
- Description
- Resource group
- Enable alert rule upon creation.
In the following screenshot, we see a configured alert rule for the DB maintenance window.
The following table gives the information you receive using the advanced notification.
|
Status |
Message |
|
Planned Deployment |
It specifies the maintenance event notification before 24 hours. |
|
In-Progress |
Maintenance for a database is starting. |
|
Complete |
Maintenance of the database is complete. |
|
Extended |
It specifies that the maintenance is in progress, but it didn’t complete for the database. It will continue at the next maintenance window. |
|
Cancelled |
Maintenance for the database is cancelled. It will be rescheduled later. |
|
Blocked |
There was a problem during the maintenance of the database. We’ll notify you when we resume. |
|
Resumed |
The issues are resolved, and maintenance will continue at the next maintenance window. |
- Note:
- You can refer to the article Maintenance window FAQ for questions and their answers on Maintenance Window
- The Azure SQL Database maintenance window, advanced notifications are free of charge for Pay-As-You-Go, Cloud Solution Provider (CSP), Microsoft Enterprise, or Microsoft Customer Agreement subscriptions
Conclusion
In this article, we explored the preview feature of the Azure SQL Database maintenance window. You can choose the maintenance window in the supported region per your database workload requirements. Using the advanced notification, we can receive a notification about the planned maintenance 24 hours in advance.
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