It's been several months since I've been busy with migrating our application from Oracle to MS SQL (the reason is a huge price difference in favor of the latter). As usually one wants to make seven hats from one sheep hide.
After many revelations in terms of performance between the two (update and especially merge) we've finally come close to production.
And here starts the unexpected. Have you ever seen an error like this before?
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host *******, port 1433 has failed. Error: "Cannot assign requested address. 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.
Consider that the server machine is super fast, has hundreds of GiGs of RAM and plenty of spare connections. We are using the native com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource. Number of client database connections is tightly controlled in the application code. Something must be wrong in the OS.
It appears that if you close and open connections frequently enough the OS does not quite catch up, it does not free the ports that were bound by the temporary connections on time. If you close/open connections often (we have 250+ threads doing this simultaneously every second), then you run out of ports and see a cryptical TPC/IP error mentioned above. The pool from Microsoft is not in fact a pool; it closes connections when you call Connection.close(). Why would you call something like that a PoolDataSource is still unclear. By the way, another peculiarity which should have warned my in advance: this pool does not have a max connections, connection timeouts and other properties which are a characteristic of a real connection pool (like that from Oracle or Apache).
The moral: do not use com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource in production, but use org.apache.commons.dbcp.BasicDataSource for example in combination with the com.microsoft.sqlserver.jdbc.SQLServerDriver, which also works on Linux since recently.