Blog

Azure SQL idle sessions are killed after about 30 minutes

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

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.


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:

 

 

WITHIN AZURE:

Connection Policy: PROXY
The session will be killed by the Azure SQL Gateway after about 30 minutes of being inactive (idle).

Connection Policy: REDIRECT
The session won’t be killed by the Azure SQL Gateway.

Connection Policy: DEFAULT (REDIRECT)
The session won’t be killed by the Azure SQL Gateway.

FROM ON-PREMISES (No VPN/ExpressRoute):

Connection Policy: PROXY
The session will be killed after about 30 minutes of being inactive (idle).

Connection Policy: REDIRECT
The session won’t be killed by the Azure SQL Gateway.

Connection Policy: DEFAULT (PROXY)
The session will be killed after about 30 minutes of being inactive.

 

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 @program varchar(20) = ”;
DECLARE @msg varchar(50) = ”;
DECLARE @session int = 57; — type the correct spid

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

WHILE @program is not null

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

SELECT @msg = @program + ‘ ‘ + 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:

 

No Comments

Leave a Reply

Tigerstaden jentene swingers gangbang & kontaktannonser trondheim swinging osloExciting Times Ahead – Innovating for the Future

Contact Us

We are always here to help. Please reach out to us and we'll get back to you as soon as possible.

Address:

Maruti Court, Ground Floor
Mvuli Road, Nairobi, Kenya

Contacts:

+254716973110

office@digitalexperts.co.ke

Hours:

Mon-Fri 8am – 5pm
Sat 9am – 1am
Sun & Holidays Closed