Server migration, redundancy and database failure / timeout

Ignition
1

Using 8.1.25. Already have a ticket in with tech support as it's urgent but want to know if anyone has run into this.

Have two redundant servers in a datacenter in the east coast. SQL Server is in the same datacenter.

They're planning to migrate to another datacenter in south west of US. We moved redundant server, brought it back up, redundancy connection re-established and db connections live.

I then attempted to fail over to the redundant so we could move the primary over to the new datacenter - for this one - the database connection error's out regularly and I also noticed the connections climbs much higher than the master has. The error was

java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object

The ping time between the new server in the new datacenter to the original is fast - sub 40 ms so it feels like that should not be a problem. We've restarted the service on the redundancy server that was already moved and tried again to the same error.

Any ideas?

For my database it is SQL Server, the connection pooling settings -
Initial Size - 0
Max Active - 16
Max Idle - 16
Min Idle - 0
Max Wait - 5000

2

Just increasing Max Active and Max Idle worked. For some reason it was peaking above 16 connections now randomly, but allowing 32 connections gave enough buffer room. I did increase Initial Size/Min Idle to 3 and Max Wait to 10000 but I don't think these were the issues I think it was just the connections getting above the max allowed.

3

Did you move the SQL Server too? If not, then comms latency would make all of your quick queries appear to take many multiples longer, running up the pool usage.

1 Like
4

We have a planned outage to do that next, that was my intuition here - data payloads from SQL to Ignition were taking longer, increasing the connection count. Will be interesting to see, and I expect it to be the case, the connection count decrease when the SQL Server migrates to be more local to the Ignition server.