SQL Maintenance Windows in Azure Synapse Analytics

In this blog post, you will learn what Azure Synapse Analytics database (SQL pool) maintenance windows are and how to monitor and configure them. This blog post applies to Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse) and Azure Synapse Analytics Workspaces SQL pools. 

What are the maintenance windows? 

Azure Synapse Analytics is a platform-as-a-service (PaaS) cloud model service. Microsoft manages the infrastructure (including networking/storage/servers). This also means that Microsoft engineers apply upgrades and include new features at a specific time.  

In an Azure Synapse Analytics SQL pool, you are allowed to define primary and secondary time maintenance windows to apply any changes. Be careful not to have critical workloads during these time windows. Microsoft will do their best to use these time windows to perform upkeep activities.  

The documentation says that maintenance windows last between 3 and 8 hours, but don’t worry! This doesn’t mean that your SQL pool will be offline for up to 8 hours. You can expect to have a single loss of connectivity of around 6 minutes. Most times, the new product version will be applied after pausing and starting the service, so you still have control on when to apply the upgrades.

If you are using less than DW400c (data warehouse units), you might experience upkeep outside of these maintenance windows. Microsoft will send you a notification and there could be multiple losses of connectivity. 

You can see planned activities in the Azure Service Health section. 

In the following example, you can see previous upkeep activity. 

It’s possible to create Azure Monitor alerts to be notified of these events. 

What to expect once the maintenance starts 

Once the maintenance starts: 

• Cancellation of all active sessions
• Roll-back of non-committed transactions

Defining the maintenance time window 

In your SQL pool, you can find the maintenance window on the summary page. 

By accessing the maintenance window, define your preferred time window. 

Set up your windows to be on any day but Monday and Friday. You can also decrease the window time to 3 hours. 

The change updates immediately. 

If you are using less than DW400c (data warehouse units), you might experience upkeep outside the pre-defined maintenance windows. 

Summary 

Today, you looked at one configuration option that you must take into account during the initial configuration of Azure Synapse Analytics SQL pools maintenance windows. 

While this is a platform-as-a-service offering, upgrades to include new features are still required. 

Final Thoughts 

I have seen many instances where users don’t modify or take into account this important configuration. In any product environment, you  want to be on top of this configuration option. 

I also believe that you shouldn’t upgrade any low tier service (less than 400 DWU) outside the window. 

What’s Next? 

In upcoming blog posts, we’ll continue to explore some of the features within Azure Synapse Analytics. 

Check out my other posts