Hey Data Champions and Integration Gurus,
Does anyone know of a SQL statement with which I can instruct the MS SQL Server to send TCP keepalives to the client through the duration of the current connection? Is there some argument to one of the many built-in stored procedures, maybe?
Here’s the context: I have a routine that executes a query on Illuminate’s PostgreSQL server and then writes the results into a table in our custom schema on Schoolzilla’s SQL Server. Without keepalive packets, some intervening network equipment (affecting routes to both servers) drops the connection if the query takes a long time to start returning results. It’s solved elegantly on the PostgreSQL side by executing
SET tcp_keepalives_idle = 180;
after connecting. But the software executing the routine connects to both servers before executing the PostgreSQL query, so that when the result does arrive, the connection to the MS SQL server is defunct–and it gives up without trying to reconnect. All my Googling has failed to yield a keepalive setting for Microsoft’s SQL Server JDBC driver. It would be nice to keep the export-import routine I have, rather than rebuild it on some other platform that might or might not keep a connection alive.
A request to the SQL Server would be ideal because it would be driver-agnostic. If there’s a JDBC driver for SQL Server that can be configured to send keepalives, I might be able to use that.
Thanks to all!