Rajendra Gupta
System default - 5 PM to 8 AM

Azure SQL Database Maintenance Window

July 7, 2021 by

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.

Specify DB maintenance window during DB creation

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.

Choose a suitable maintenance window

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

System default - 5 PM to 8 AM

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.

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

Configure maintenance window using PowerShell

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.

creates the database [mytestsqldatabase1] using the maintenance window SQL_EastUS_DB_1.

Once the script finishes, go to the newly created Azure database and verify the maintenance window.

Validate DB maintenance

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.

Configure maintenance window output

Refresh Azure console. As shown below, the [mytestsqldatabase1] maintenance window changed to 10 PM to 6 AM EST, Friday To Sunday.

Configure maintenance window using CLI

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.

CLI script output

Validate the configured maintenance window in the Azure portal, as shown below. Validate the configured maintenance

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.

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.

Modify maintenance window

In the Service health menu, select Health alerts, then Add service health alert.

Service health alerts

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

Alert rules

For example, I choose the East US region and Planned maintenance service health criteria in the following screenshot.

Choose region and health criteria

Create actions groups

Review the action group configuration.

Review

You can view the configured action group in the following screenshot.

View action group

Scroll-down and specify the alert rule details.

  • Alert rule name
  • Description
  • Resource group
  • Enable alert rule upon creation.

Define alerts

In the following screenshot, we see a configured alert rule for the DB maintenance window.

View alert configurations for Azure SQL Database 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.

Rajendra Gupta
Azure, Maintenance, 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

417 Views