In this blog post, we will be sharing the Azure SQL Connection Pooling best practices. Why is it important you ask? It is because connection pooling is a resource hogger, or in order words, expensive.
In order to maintain top performance, connection resiliency, and scalability, you would need to follow the best practices. When a new connection is made, new memory allocations need to happen dynamically by the underlying OS. A highly transactional application typically opens and closes many connections dynamically. If the database system is not optimized correctly, it will result in reduced transactions per second. This will, in turn, increase the application latency.
This is where connection pooling comes to the rescue. It aids in reusing the existing connections and therefore results in lesser latency. It helps the underlying OS to stop allocating & deallocating memory resources dynamically.
Typically, to leverage connection pooling, it is recommended to design your application to take full advantage of it. It helps in keeping a fixed set of connections during startup which is eventually maintained.
Azure SQL Connection Pooling Best Practices
In most cases, the connection pooling the needs to be configured on the application end. The application framework and the driver needs to support it. On several open source databases like PostgreSQL, it leverages a proxy connection pooler service like PgBouncer or Pgpool running between the application and connects to the database server.
In the case of ADO.NET, it uses an in-built optimization technique and is hence called connection pooling. In order to reduce the number of new connection, the in-built pooler service maintains the physical connection and keeps it alive based on the system configuration.
The pooler works like a broker service. Each time when the application opens a new connection, the pooler assigns an active connection. Upon termination from the apps end, the connection is returned to the existing pool. So, a new connection is neither opened nor closed.
For ADO.NET, the following is the expected behavior:
- Connections with the same configuration will be pooled.
- ADO.NET already pools several connections per pool simultaneously.
- Each Windows Identity and connection string will have a separate pooled connection.
- Connections will also be pooled based on whether they are listed in a specific transaction.
- An exact matching algorithm connects a pool with a specific connection string
- When max connection exceeds pool size, newer transactions are queued.
- Idle connections are moved after every 4-8 minutes (approx)
- Connections are assigned based on the transaction context.
In the case of ADO.NET, the pooler service optimizes the pooled connections as they are opened and closed from the application end, unless explicitly disabled. So, the best practices are as follows:
- Always close connection after each transaction to enable the connection to be available for other operations.
- Use ‘Close’ or ‘Dispose’ methods in the connection object.
- Connections which are not explicitly closed are not returned to the pool.
- Avoid using multiple identities (Basic + Windows Integrated Auth)
- Design your multi-tenant application carefully, so that multiple customers cannot exhaust the database connections.
- Take advantage of security mechanisms instead of application roles.
In the case of SQL Azure, it will close an already established connection for specific transactions if:
- An app held an idle connection for more than 30 minutes.
- If you have an open connection in your SQL Server Management Studio (SSMS) connection is freed for anything longer than 30 minutes.
Conclusion
We hope that the above explanation was helpful for you to understand connection pooling best practices for Azure SQL better. Questions and suggestions are welcome in the comment box below. Thanks for your visit!