Azure SQL Database idle sessions are killed after about 30 minutes when "Proxy" connection policy
Published Sep 29 2022 01:16 AM 9,492 Views
Microsoft

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 | Micr...

  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. If you are connecting through a Private Endpoint Connection (Private Link), the port range is 1433 to 65535.

    networking.png

     

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.

 

In addition to these scenarios, we need to consider another variable "Private Links" (Private Endpoint Connections). If a "Private endpoint connection" is enabled on our Azure SQL Server, the default connection policy within Azure, Site-to-Site VPN, and ExpressRoute is set to "Proxy", so all sessions will be killed after approximately 30 minutes of being inactive (idle). However, you have the option to select the "Redirect" to force this connection policy. In the "Private EndPoint Connection (Private Link)" + "Redirect" scenario the port range is 1433 to 65535.

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.

OhDPzFL4Tl.png

These scenarios are easy to test.

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

hugo_sql_2-1648463771298.png

 

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

 

-- Run this script in your user database

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'

 

LZfHonEBEc.png

 

Finally, the session was killed after 32 minutes:

Q7Dp4oij7j.png

 

Let's see how it works when we connect to the Azure SQL Database through a Private Endpoint Connection (Private Link)

 

I have enabled "Private Access" at the Azure SQL Server level:

hugo_sql_0-1710702997562.png

 

The private IP address is 172.170.0.6

 

hugo_sql_2-1710703268647.png

 

 

And then we force "Redirect" connection policy:

hugo_sql_1-1710703115960.png

 

Now, we will connect from an Azure Virtual Machine (VM):

 

We will connect to the Azure SQL Server through SSMS and then run the following networking commands:

 

nslookup srv-sqldb-test.database.windows.net

 

With this command we observe that the Azure DNS (168.63.129.16) is resolving the private IP address of the Private Endpoint Connection instead of the Public IP address of the Public Endpoint.

 

netstat -ano | findstr 172.17.0.6

 

With this command we observe that the Azure VM's private IP address(172.17.0.4) is connecting to the Private Endpoint Connection's private IP address (172.17.0.6) and port 22906 from the ports range 1433 to  65535.

 

 

hugo_sql_0-1710704834399.png

 

Let's run the test T-SQL code:

 

hugo_sql_0-1710705659898.png

 

We are going to check if the idle spid 78 will be killed after 30 minutes without any activity:

 

hugo_sql_1-1710705872010.png

 

After 30 minutes we observe that the Azure SQL Gateway doesn't kill the idle session 78:

 

hugo_sql_0-1710708190630.png

 

hugo_sql_1-1710708586578.png

 

What happen when we select "Default" Connection Policy at Azure SQL Server level? in this case all connections will use the "Proxy" option, therefore the Gateways will kill idle sessions after 30 minutes:

 

hugo_sql_0-1710707537158.png

 

We run again the T-SQL test (idle spid = 82):

 

hugo_sql_0-1710709789416.png

 

The Azure SQL Gateway killed the session after 30 minutes:

hugo_sql_0-1710711473326.png

 

 

 

 




 

4 Comments
Co-Authors
Version history
Last update:
‎Mar 18 2024 02:27 AM
Updated by: