Frozen Linked Server Connection
If you have written SQL queries using OPENQUERY with a linked server to Oracle or DB2, you might have already experienced frozen connections, which require reboot of the SQL services. If you have not, consider yourself lucky so far.
Here is what somebody from our architecture team said: “in general SSIS will out-perform linked servers. Also SSIS packages don’t get frozen connections.”
However, I still get resistance from developers/analysts/managers when I suggest that we convert those troubled stored procedures with OPENQUERY/LINKED SERVER to a SSIS package, to avoid those frozen connections. The misconception is 1) SSIS package is hard to deploy 2) we don’t have time to convert them to SSIS packages 3) code in SSIS packages are not easy to “see”.
I went ahead and converted one the most notorious stored procedures to a SSIS package, to avoid using OPENQUERY/LINKED SERVER to a DB2 source. The effort has paid off so far. It’s been trouble free for 2 straight days now.