Forum Discussion

Ted_Waller_01_1's avatar
Ted_Waller_01_1
Icon for Altocumulus rankAltocumulus
Feb 28, 2014

MSSQL Health Monitor Issue

We're currently using the F5 to load balance across read-only shards for a specific feature within our infrastructure. I have had success getting a MSSQL monitor to work initially, but every time the backend servers are rebooted, the monitor stops functioning. If I make enough cosmetic changes to the health monitor, it eventually works again.

 

What's odd is if I connect via management studio to the backend nodes and run the exact query in the monitor (with same credentials), the receive string is what is expected. If I change the pool's monitor to a basic TCP connect, and then connect via management studio to the VS's IP, I can execute the health monitor successfully. But if I set the actual monitor on the pool, it's still not functional.

 

The monitor is doing a simple select to a function that returns a single row/column with a 0 or 1 to signify whether the service is available for use.

 

Any thoughts on what might be the issue here?

 

F5 Software: BIG-IP 11.4.0 Build 2425.0 Hotfix HF4 F5 Platform: BIG-IP 5000

 

application delivery
design
ihealth
LTM
ms sql
  • Ian_124377's avatar
    Ian_124377
    Icon for Nimbostratus rankNimbostratus
    Jan 13, 2015

    @Ted Waller - I ran into this same issue on v11.4.1 HF5. Thanks for posting the response. I changed the 'Count' field from 0 to 1 and it made my health monitor work again.

     

  • Ted_Waller_01_1's avatar
    Ted_Waller_01_1
    Icon for Altocumulus rankAltocumulus
    Feb 28, 2014

    Amusingly enough...or more like how things always go in technology...I think I figured it out. It looks like (at least in this case), if you have the "Count" option for the monitor set to 0 (ie. permanent connection), it gets itself confused when SQL is bounced (reboot or service restart). It doesn't seem to reopen the connection, but tries to re-use the old one despite SQL having cut all connections on the restart. Not sure why I didn't think of this before, but I tried setting it to 1 and after ~10 instances of a restart or reboot, I'm not seeing the problem anymore. Sorry for the needless question. Though I am interested if this is by design or a bug.

     

  • PeteWhite's avatar
    PeteWhite
    Icon for Employee rankEmployee
    Feb 28, 2014
    Can you post the monitor config and have you tried capturing it via tcpdump to see what response it actually receives?