0

I need some help. My company is testing our disaster recovery procedure and after switching our PRD environment to our DR environment we are getting this error with our applications. We are using an MSSQL Always on database setup with a spring boot hikari connection pool, if that is a contributing factor .

The TCP/IP connection to the host , port has failed. Error: "The timeout expired before connecting to the routing destination.

Questions: 1) What is the cause of this error? 2) What is an easy way of replicating this? 3) Is this anyway related to the a command timeout? Any idea how to set that value in a hikari pool with spring boot? 4) Assuming that applications settings are fine? Any other possible reason this can happen (Network, database setup, DNS, hardware)

LOG

2019-04-06 07:03:45:866  INFO [http-nio-50209-exec-9] ph.com.bbb.tbg.aaaa.ws.aop.ValidateHeadersAspect   : Validating channel 3a96b206-d29b-4746-8f90-12048683e5xx
2019-04-06 07:03:45:900  INFO [http-nio-50209-exec-9] ph.com.bbb.tbg.aaaa.ws.controller.InstitutionWebServiceController : Retrieving institutions where CODE= 99999
2019-04-06 07:03:45:900  INFO [http-nio-50209-exec-9] ph.com.bbb.tbg.aaaa.config.ReadOnlyConnectionAspect : Using read-only Database connection.
2019-04-06 07:03:45:902  INFO [http-nio-50209-exec-9] com.zaxxer.hikari.HikariDataSource                 : WS Readonly Connection Pool - Started.
2019-04-06 07:03:55:862  WARN [WS Master Connection Pool housekeeper] com.zaxxer.hikari.pool.ProxyLeakTask               : Connection leak detection triggered for ConnectionID:417 ClientConnectionId: 6649536a-891f-4756-a75b-38fa5b6d7bf9, stack trace follows
java.lang.Exception: Apparent connection leak detected
        at net.ttddyy.dsproxy.support.ProxyDataSource.getConnection(ProxyDataSource.java:63)
        at com.github.gavlyukovskiy.boot.jdbc.decorator.DataSourceDecoratorInterceptor.invoke(DataSourceDecoratorInterceptor.java:53)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$69498630.getConnection(<generated>)
        at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:164)
        at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
        at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:87)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:112)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:230)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:237)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214)
        at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52)
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1512)
        at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:45)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:189)
        at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:430)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at ph.com.bbb.tbg.aaaa.ws.controller.InstitutionWebServiceController$$EnhancerBySpringCGLIB$$fe88c7fb.searchInstitutions(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:119)
        at org.springframework.boot.web.support.ErrorPageFilter.access$000(ErrorPageFilter.java:61)
        at org.springframework.boot.web.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:94)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:112)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)
2019-04-06 07:04:03:908 ERROR [http-nio-50209-exec-9] ph.com.bbb.tbg.aaaa.ws.controller.ExceptionHandlerController : Could not open JPA EntityManager for transaction; nested exception is com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The TCP/IP connection to the host db01.aaaa.bbb.com.ph, port 1433 has failed. Error: "The timeout expired before connecting to the routing destination.". ClientConnectionId:47ecb453-1bec-44d3-8a58-2ad25e9fdd2f org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The TCP/IP connection to the host db01.aaaa.bbb.com.ph, port 1433 has failed. Error: "The timeout expired before connecting to the routing destination.". ClientConnectionId:47ecb453-1bec-44d3-8a58-2ad25e9fdd2f
        at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.handleExistingTransaction(AbstractPlatformTransactionManager.java:429)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:349)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
        at ph.com.bbb.tbg.aaaa.config.ReadOnlyConnectionAspect.around(ReadOnlyConnectionAspect.java:52)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
        at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at ph.com.bbb.tbg.aaaa.ws.service.InstitutionServiceImpl$$EnhancerBySpringCGLIB$$5b01332b.searchInstitutions(<generated>)
        at ph.com.bbb.tbg.aaaa.ws.controller.InstitutionWebServiceController.searchInstitutions(InstitutionWebServiceController.java:172)
        at ph.com.bbb.tbg.aaaa.ws.controller.InstitutionWebServiceController$$FastClassBySpringCGLIB$$8223adbe.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at ph.com.bbb.tbg.aaaa.ws.controller.InstitutionWebServiceController$$EnhancerBySpringCGLIB$$fe88c7fb.searchInstitutions(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:119)
        at org.springframework.boot.web.support.ErrorPageFilter.access$000(ErrorPageFilter.java:61)
        at org.springframework.boot.web.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:94)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:112)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)
Caused by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The TCP/IP connection to the host db01.aaaa.bbb.com.ph, port 1433 has failed. Error: "The timeout expired before connecting to the routing destination.". ClientConnectionId:47ecb453-1bec-44d3-8a58-2ad25e9fdd2f
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:516)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:118)
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:94)
        at net.ttddyy.dsproxy.support.ProxyDataSource.getConnection(ProxyDataSource.java:63)
        at com.github.gavlyukovskiy.boot.jdbc.decorator.DataSourceDecoratorInterceptor.invoke(DataSourceDecoratorInterceptor.java:53)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$69498630.getConnection(<generated>)
        at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:164)
        at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
        at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:87)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:112)
        at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:489)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:216)
        at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:201)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle.doGetConnection(HibernateJpaDialect.java:414)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:177)
        at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)... 80 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host db01.aaaa.bbb.com.ph, port 1433 has failed. Error: "The timeout expired before connecting to the routing destination.". ClientConnectionId:47ecb453-1bec-44d3-8a58-2ad25e9fdd2f
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2670)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2658)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1976)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1762)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1077)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:623)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:95)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:101)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:316)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:506)
Jefrey Valencia
  • 713
  • 3
  • 13
  • 30
  • Where's your DR environment? How do you connect to it? If you connect to it over a WAN, slow link or fast link with high latencies you'll always have networking issues - no matter the database or hardware. Connecting to a cloud database from an on-prem application always has to handle such issues for example. – Panagiotis Kanavos Apr 08 '19 at 08:34
  • Hi thanks for the comment, its in a different location from our PRD. We just need a way to prove that it is not an application problem so we can have our DBAs to check it out. – Jefrey Valencia Apr 08 '19 at 08:36
  • What I meant is that the application should always handle this error. Transient errors *always* happen on distributed environments. That's why Microsoft's drivers added [connection resiliency to its drivers](https://learn.microsoft.com/en-us/sql/connect/odbc/windows/connection-resiliency-in-the-windows-odbc-driver?view=sql-server-2017) some years ago to handle cloud connections. Even when you have a fiber connection to your ISP, stuff happens between them and the cloud provider. – Panagiotis Kanavos Apr 08 '19 at 08:44
  • A DR environment will typically have slower links, weaker servers, weaker *networking hardware* than the primary site. The error you get suggests the network isn't as fast as it the original, which *is* expected. You can ask for a better network, but in the end, if you *do* want the application to stay up, it will have to be able to retry operations – Panagiotis Kanavos Apr 08 '19 at 08:48
  • Finally, Microsoft's JDBC driver [does have connection pooling](https://learn.microsoft.com/en-us/sql/connect/jdbc/using-connection-pooling?view=sql-server-2017). The docs mention connection resiliency only for the Windows versions of the drivers though. – Panagiotis Kanavos Apr 08 '19 at 08:52
  • Hi Panagiotis, Ill keep that in mind. Can this error be simulated / replicated any other way so we can say its not an application issue but an infrastructure issue? – Jefrey Valencia Apr 08 '19 at 09:05
  • Are you interested in how to handle this issue or how to simulate it? The second question isn't exactly on-topic for SO - you'd be asking how to emulate network latencies and transient errors. In a sense, you already *are* simulating this - if you try to connect to the other environment and get such errors, it's the network. The error says exactly that `The TCP/IP connection to the host , port has failed`. The question becomes one of network troubleshooting – Panagiotis Kanavos Apr 08 '19 at 09:13
  • If you search for that error message you'll find many similar questions. Those questions show that the *actual* error message contains the connection steps and their duration, et [this question](https://stackoverflow.com/questions/36056475/azure-sql-frequent-connection-timeouts) and [this one](https://stackoverflow.com/questions/29839609/intermittent-connection-timeout-in-azure-sql-on-ajax-request). What is the full error message in your case? In one of the questions, the initialization and handshake durations are so long that it's clearly a network error. – Panagiotis Kanavos Apr 08 '19 at 09:19
  • [In this case on the other hand](https://stackoverflow.com/questions/31412577/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation) it was an underpowered Azure SQL that couldn't complete the connection process within 1 minute. The delay was registered in the post-login step in that case. – Panagiotis Kanavos Apr 08 '19 at 09:22
  • Hi Panagiotis, actually I'm interested in replicating the issue so we can do it in the UAT environment. And make show the fix will work there before it is deployed in PRD. I saw the 2 question but for the hardware issue I don't think that is the case because we have the same server in both DR and PRD. For the one concerning database index well I am not sure how indexing is different from our primary to our DR server. Sorry Panagiotis, if I sound dense but I appreciate your efforts in helping me. – Jefrey Valencia Apr 08 '19 at 09:31
  • Replicate *which* issue? Bad network or slow hardare? You have to know where to start. Post the *full* error message - what are the timings? This can point you to the right direction. Large delays in initialization mean network. Delays in post-login mean slow server. – Panagiotis Kanavos Apr 08 '19 at 09:37
  • You can try using a stress-test tool or create a simple application (even a command line app) that tries to connect to the remote server and perform some queries. Do you get the error immediatelly? After a while? What happens if you run multiple queries concurrently? Using a stress-test tool takes your application completely out of the picture. – Panagiotis Kanavos Apr 08 '19 at 09:40
  • Sorry for the late reply as it took a while to get the logs from PRD. Based on the logs it fails immediately when using the read only connection (We are also checking on this). – Jefrey Valencia Apr 10 '19 at 03:05

0 Answers0