Azure SQL Server Stored Procedure Debug

Question

Hi :

I was informed that stored procedures on Azure SQL Managed Instances cannot be debugged using the debug method of SSMS or via Visual studio. Answer by the MS support team : " As discussed Azure SQL Managed Instance does not support debugging as SQL Server engine DDL’s which is required by the debugger are not accessible in PaaS environment."

What is the work around solution for this ?

Does this limitation exist on Azure Synapse also ?

Sanjay Shah
sanjay@PROSYS INFOTECH PRIVATE LIMITED .com

Answer 1

On all Azure SQL PaaS database options (DTU model, vCore model, Serverless, Managed Instance, Hyperscale) and Synapse you will have that constraint. Even tools like SQL Server Management Tools (SSMS) that once had a Debugger integrated it no longer has it.

Microsoft Visual Studio Community Edition has a Transact-SQL Debugger you can use. Read here for more information.

In addition, Azure Data Studio has a Profiler Extension that helps a little bit to determine the sequence of instructions that were executed to produce a result set but won't help you to watch the values of variables.

If all this is painful for you maybe your organization should consider choose a SQL Server VM (IaaS) instead of PaaS.

You can also export Azure SQL Databases to a local developer environment and do debugging locally.

Finally, if you need to debug on Production use PRINT or SELECT statements of T-SQL to watch the values of variables or display intermediate result sets. You can also save intermediate results or outputs into a temporary table or log table.