Skip to main content
F
fedsol
Known Participant
September 4, 2010
Question

CF servers stop trying to send queries to SQL server

  • September 4, 2010
  • 2 replies
  • 2521 views

Multiple times per week, one of my clients loses access to their SQL Server cluster. This is a problem for me because this very large company has a wide variety of applications and languages (Progress 4GL, .Net, Java) interacting with the cluster, and the only servers and apps that lose access are the ColdFusion ones. They have CF 8 Enterprise (fully patched, some standalone, some JRun) on 5 servers, running Windows 2000, 2003, and 2008. All of them experience the issue, but never at the same time. Other applications running on the same servers do not have the issue - it is only the CF across the entire enterprise that periodically becomes unable to see the SQL cluster. (I emphasize this to shortcut the inevitable replies of "check your SQL Server settings".)

The error message received is "Error Executing Database Query. [Macromedia][SQLServer JDBC  Driver][Macromedia][SQLServer JDBC Driver]The requested instance is  either invalid or not running." (Yes, the instance is up and running, and CF was talking to it 5 seconds ago.) Once this occurs, all queries through that DSN fail, though other DSNs connecting to the same instance on the same cluster may still function. Restarting the CF App Server service does not resolve the problem - only restarting the whole server does. Flushing DNS also does not help. Using the IP instead of name does not help.

Further complicating the issue is the bizarre results of Wireshark debugging during these periods of disconnectedness.

  • When queries are made to an unclustered server, CF properly sends the request over the network.
  • When queries are made to the cluster's default instance (i.e. no instance name is specified), CF properly sends the request over the network.
  • When queries are made to an instance on the cluster, CF sends no data over the network at all.

At that point we have reached the end of my technical ability. I'm a Web programmer, I don't have the ability to debug a network driver. Somehow, when an instance name is specified on a cluster, the CF SQL Server driver just occasionally gets stuck in a state where it will not even try to connect to the server anymore.

The client is not happy. They specifically blame CF for the issue. That it happens across 5 different servers running 3 different OS's, that all the other languages and apps never have the problem, and of course the Wireshark results make it basically impossible to argue otherwise. Surely there's some parameter I can specify in the DSN to stop the issue? Or magic hotfix I've never seen? Would really love to hear anything anyone has to suggest on the matter.

Nick Walters

Federated Solutions Group

    This topic has been closed for replies.

    2 replies

    F
    fedsolAuthor
    Known Participant
    January 27, 2011

    This was found to be a DNS issue somehow. CF was using the OS' DNS servers, whereas "the other guys" were using entries in the hosts file. One of the DNS servers was misconfigured, so that when CF sought the cluster location from that DNS server, it bombed out. Hence the extreme difficulty in reproducing it.

    A
    Adam Cameron.
    Inspiring
    September 4, 2010

    Good troubleshooting, and documenting thereof.

    Um... I have no idea what would be causing your issue, but perhaps some more information from you might help:

    * are your DSNs set to maintain connections or not?  Maybe try the opposite of what is currently set for a while;

    * what's your login timeout?

    Have you tried using a different JDBC driver?

    --

    Adam

    F
    fedsolAuthor
    Known Participant
    September 4, 2010

    Login timeout is the default 30, but it only takes ~5 sec before the error is thrown.

    Currently all of the DSNs are set to maintain connections. I will set half of them not to and see if that has any impact.

    Have never used an alternate JDBC driver with CF. Do you have a recommendation?

    Thanks very much for the reply.

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    September 4, 2010

    For any of the servers that are running CF Enterprise (such as the "jrun" ones you mentioned), you can also use the CF Server Monitor and view its Connection Pool Status page, which may offer some information. (Usually, it does not offer much, I'll grant.) To be clear, you do not need to use any of the 3 "start" buttons in the Server Monitor ("monitoring", "profiling", "memory tracking") to see info on this page (as is also true of several other pages, which is a surprise to many.)

    /charlie

    /Charlie (troubleshooter, carehart. org)
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices