[spacer] [spacer] [spacer]

Sign up with your email address to be the first to know about new publications

[mc4wp_form id="4946"] [spacer]

SQL Maintenance Windows in Azure Synapse Analytics

Posted on

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. 

Planned maintenance activities Azure Service Health

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. 

Maintenance schedule

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

SQL pool

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

Define window time

The change updates immediately. 

Changes update immediately

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

Active schedule

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. 

No Comments Yet.

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *