SQL Elevated Configuration: The fail-safe for maintenance

Many years ago, in the company I was working for, one junior DBA started a reindex operation in a SQL Server Standard Edition on the most busy day of the month. Do I need to explain what happened next? It’s easy to use the imagination on this one.

What’s the option to solve this? Online index creation or rebuild. This would allow index maintenance to not block the database at all.

SQL Server 2019 and Azure SQL brought two database scoped configurations to work as a fail-safe for this kind of situation: Elevated_Online and Elavated_Resumable

Database Scoped Configurations are in some ways similar to server configurations, but we can set them on database level. On the beginning, they were server configurations we could set on the database level. After a while some configurations that work only as Database Scoped Configuration started to appear.

These two configuration options work as a fail-safe in case a junior DBA executes a statement that will block the server: When enabled, these configurations automatically convert the statements to online execution or resumable execution.

They are not enabled by default. I strongly recommend to enable them, avoiding blocking your production database when you less expect.

Managing the Configurations

You can check if these statements are enabled using the following query:

SELECT *
FROM   sys.database_scoped_configurations
WHERE  NAME IN ( ‘ELEVATE_ONLINE’, ‘ELEVATE_RESUMABLE’ ) 

 

These options are not a simple ON/OFF switch. They have 3 configuration options:

OFF: It’s the default. The configuration is disabled.

WHEN_SUPPORTED: It’s the one I most recommend. SQL Server will change the statement to online/resumable when possible, but if for some reason it’s not possible, the statement will be executed anyway.

FAIL_UNSUPPORTED: This is the most strict option. The execution will fail If for some reason the statement can’t run online. I recommend to be very careful when using this option. If we could set time windows for this option, defining when the statement can or can’t be executed, it would be way better.

Example to set these configurations:

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = WHEN_SUPPORTED;

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;

Recommendation for Production Environments

In critical production environments, one option is to keep both configurations set with FAIL_UNSUPPORTED. Every maintenance requiring to execute something offline or not resumable would need to change the configuration first, make the execution and revert the change.

The first security blocking production issues is that someone who wants to execute a dangerous task for production will need to be aware about this configuration and really know what to do.

We can add one more security level using permission management. In order to change a database scoped configuration the user needs the ALTER ANY DATABASE SCOPED CONFIGURATION permission. The server administrator will need to manage permissions in such a way that only a limited set of administrators will have these permissions, while junior administrators will have limited permissions to manage the database.

 

 

 

 

The post SQL Elevated Configuration: The fail-safe for maintenance appeared first on Simple Talk.

from Simple Talk https://ift.tt/3mSEZZU
via