Azure SQL idle sessions are killed after about 30 minutes

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

When we connect to our Azure SQL Databases or Azure SQL Managed Instances databases using the "Proxy" connection policy and the session is still open without any activity, the Azure SQL Gateway will kill the session after being idle for more than 30 minutes.

When we use the connection policy "Redirect" the Gateway won't kill the idle sessions after about 30 minutes.

In Azure SQL we have two connections policies:

Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs

1. Proxy: the default option when you connect from on-premises without any ExpressRoute or Site-to-Site VPN. This policy requires port 1433 to open to connect to the Azure SQL  Database gateway.
   
   
2. Redirect: the default option when you connect within Azure or from On-premises if you have implemented an ExpressRoute or Site-to-Site VPN. This policy requires ports 1433 to connect to the Azure SQL  Database gateway and 11000 to 11999 to establish connections directly to the node hosting the database.
   
   
   

    

With this information we have six different scenarios:

To these scenarios, we need to add another variable "Private Links" (Private Endpoint Connections). If we have enabled a "Private endpoint connection" on our Azure SQL Server, the only supported connection policy within Azure, Site-to-Site VPN and ExpressRoute are "Proxy", so all sessions will be killed after about 30 minutes of being inactive (idle).

Only when you connect from on-premises without Site-to-Site VPN and ExpressRoute you can force it to use "Redirect", and the connections will be handled directly by the node that hosts the database, and then your session won't be killed by the Azure SQL Gateway.

These scenarios are easy to test.

You can open a Command Prompt (cmd) and run the following command on your Azure SQL Server:

And then monitor the idle session on your SSMS for example:

DECLARE varchar(20) = '';
DECLARE @msg varchar(50) = '';
DECLARE @session int = 57; -- type the correct spid

Set = (SELECT [program_name]
FROM sys.dm_exec_sessions
WHERE session_id=@session and program_name='SQLCMD');

WHILE is not null

BEGIN
Set = ( SELECT [program_name]
FROM sys.dm_exec_sessions
WHERE session_id=@session and program_name='SQLCMD'
);

SELECT @msg = + ' ' + convert(varchar(20),getdate());
RAISERROR(@msg,10,1) WITH NOWAIT

-- Wait for 1 minute
WAITFOR DELAY '00:01:00';
END

print '*** END TIME: ' + convert(varchar(20),getdate());
SELECT [program_name],session_id, DATEDIFF(minute,last_request_start_time,GETDATE()) [idle_minutes], last_request_start_time
FROM sys.dm_exec_sessions
WHERE session_id=@session and program_name='SQLCMD'

Finally, the session was killed after 32 minutes: