Lesson Learned #517:Connection Timeouts to Azure SQL Database Using Private Endpoint with HikariCP
Recently, we have been working on a support case where our customer reported the following error message: ERROR com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Exception during pool initialization. com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host myserverX.database.windows.net, port 1433 has failed. Error: 'Connection timed out: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.' I would like to share the lessons learned during the troubleshooting and resolution process Initially, what caught our attention were the first two messages: Exception during pool initialization and Connection Timeout out. This indicates that Hikari was unable to create the connection pool during the initialization process (one of the first steps in connection pooling) due to a connection timeout. Therefore, we began working on connectivity options and started investigating, asking if our customer is using public endpoint or private endpoint, they confirmed the private endpoint usage. The following factors could be identified as possible causes: Private Endpoint Misconfiguration: The Private Endpoint was not correctly associated with the Azure SQL Database. DNS Resolution Issues: The database hostname was not resolving to the Private Endpoint's private IP address. Network Security Group (NSG) Restrictions: The NSG attached to the subnet hosting the Private Endpoint was blocking inbound traffic on port 1433. Firewall Settings on Azure SQL Database: Firewall rules were not allowing connections from the source network. Redirect Mode Configuration: Additional ports required for redirect mode were blocked or misconfigured. Our troubleshooting steps started running using nslookup myserverX.database.windows.net to ensure that the database hostname resolves to the Private Endpoint's private IP address. If the IP is public instead of private, we verify the DNS configuration or use Azure DNS. Our second step was to validate the Java Application host can reach Azure SQL Database on port 1433 or the redirect port (if our customer use redirect connection policy) using the command telnet myserverX.database.windows.net 1433 or the Linux command nc -vz myserverX.database.windows.net 1433 and we identified the connections fails. Check this Azure SQL Database and Azure Synapse Analytics connectivity architecture and Azure Private Link - Azure SQL Database & Azure Synapse Analytics | Microsoft Learn for more details. Following we verify the Network Security Group attached to the subnet if allows outbound traffic to the port 1433 and we found that the NSG rule doesn't exist for private endpoint. Once the rule was added the Java Application was able to connect.How to connect to Azure Database for MySQL – Flexible Server in a private network configuration
You might encounter connectivity issues when trying to access your database from different sources and networks. In this blog post, I’ll show you how to troubleshoot and resolve these types of issues based on three common network scenarios, as well as for an on-premises scenario.
Why should you migrate to PostgreSQL 15?
Recently one of my customers shared the below code snippet and I was looking for ways to gain performance. The code snippet A simple function that has evaluated expression in a loop for one billion iterations. function speed_test(p_loops int) returns int language plpgsql as $$ declare v_number bigint; v_multiplier float = 3.14159; loop_cnt bigint; begin for loop_cnt in 1..p_loops * 1000000 loop v_number := 333; v_number := v_number * v_multiplier; end loop; return 0; end;$$; The piece of code shared caught my attention because the code has evaluated expressions, and this was one of the areas where improvements were made after PostgreSQL 12. I ran a simple test to prove this. Test run on PostgreSQL v12: I ran the code run on PostgreSQL v12 in Azure Database for PostgreSQL Flexible Server. Region: West Europe SKU: GP D2ds_v4, 2 vCores 8 GB RAM, 128 GB Storage Test run on PostgreSQL v15: The same piece of code was run on PostgreSQL v15 in Azure Database for PostgreSQL Flexible Server. Region: West Europe SKU: GP D2ds_v4, 2 vCores 8 GB RAM, 128 GB Storage We can clearly see that the code snippet on PostgreSQL 15 is 5x faster than PostgreSQL 12. The rationale behind this is several patches that reduced the overhead of the expression's value in PL/pgSQL. PL/pgSQL was not originally designed for extensive numeric calculations like this but the need was realized as more and more developers started to use it this way, so over time there were several commits by the community that lead to this performance improvement. If you are curious to know more about this, check out this and this commit, which describes the improvements and history of this change. Thanks to Pavel Stehule from the PostgreSQL community to point out the rationale and consolidate out the commits associated with this change. If you are on an older version of PostgreSQL like PostgreSQL 12, this is just one of many other reasons to upgrade to PostgreSQL 15. You can see the feature matrix here. PostgreSQL15 will be released on Azure Database for PostgreSQL Flexible Server soon. If your workload is already running an older version of PostgreSQL, then plan your upgrade using the Major Version Upgrade.New troubleshooting video series for Azure Database for MySQL
We’re pleased to announce our latest video series, focused on troubleshooting potential issues with Azure Database for MySQL. With a new episode releasing every couple of weeks, the Azure Database for MySQL - Troubleshooting Series provides tips and tricks that you can use to anticipate and address potential issues in specific problem areas, such as performance, connectivity, replication latency, database corruption, and more!Spotlight on the ADX Time Pivot Visualization
The Time Pivot chart is a powerful interactive navigation tool that lets you analyze your data over an events timeline, that pivots on a time axis. Time Pivot makes it easy to identify event flows, and beautifully tells the story of “what happened”. It helps you to generate multiple views of the same data by letting you pick multiple levels of data slicing. Time Pivot visualization is your essential tool for analyzing data across hundreds of different tables.
