Introduction: If you're managing an Azure SQL database and encounter an issue where the database becomes unresponsive, you must find a solution that will help you restore the service. In this article you will learn how to perform a manual failover to restore service as part of the task to identify the root cause of the problem even though the server is unresponsive.
A working example: One potential cause of an unresponsive Azure SQL database is a query that is fired by an API and consumes 100% of the CPU. In this scenario, it can be difficult to identify the problematic query without connection which allows you to query the data management views (DMVs) and pinpoint the problematic query. Once identified, you can disable the API until a fix is implemented.
One, relatively known solution is to use the dedicated administrator connection (DAC), which allows you to query the DMVs and achieve the goal. This solution is excellent for experienced DBAs but it is not easy to use by Azure administrators or developers.
Another solution is to perform a Manual Failover: you can initiate a manual failover of the Azure SQL database from PowerShell. The command "Invoke-AzSqlDatabaseFailover" can be used to perform the failover. Then, you will be able to do the research needed to find the problematic query in the same way.
To understand why this is possible, it's important to note that every Azure database has three copies behind the scenes for ensuring the high availability of SLA. The purposed solution is to use the same mechanism in a manual way.
If the database is in an elastic pool, this command will failover the specific database without affecting the other databases in the same elastic pool. This command works for both general availability and business-critical tiers.
Limitations: It's worth noting that there is a limitation to this solution - only one failover call is allowed every 15 minutes for each database or elastic pool. This limitation is documented here. Also, the operation might take some time. Data loss is not expected since the
planned failover will ensure that all the committed transactions are persisted before the failover.
Conclusion: In summary, if you encounter an issue where an Azure SQL database becomes unresponsive, you have a few options for restoring service. The dedicated administrator connection (DAC) as well as a manual failover can be initiated using the "Invoke-AzSqlDatabaseFailover" command. While there are some limitations to this solution, it's a useful tool to have in your arsenal.
Microsoft
