Forum Discussion

sharpie_79656's avatar
sharpie_79656
Icon for Nimbostratus rankNimbostratus
May 22, 2012

MSSQL Instance/Database Switching

I'm looking at providing HA for a few MSSQL 2008 databases. There will be two servers housing ~3 DB. The DBs will be in mirroring mode, info can be found here.

 

 

I have been using the Oracle 11g deployment guide as a basis. I have the nodes setup and three pools setup with different monitors for each of different the DBs running on the server, but I'm stuck at the virtual service. The Oracle deployment has an iRule for switching service names to different pools.

 

 

Is there a similar rule for MSSQL? I've been searching, but so far come up with nothing. Or is there a more preferred method for MSSQL?

 

 

Thanks!

 

microsoft
partner
  • Nat_Thirasuttakorn's avatar
    Nat_Thirasuttakorn
    Icon for Employee rankEmployee
    May 22, 2012
    Hi sharpie,

     

     

    as far as I know, MS-SQL contains "Database name" and "server name" in "Login" message.

     

    However, typically, "Login" message is encrypted (and it does not work with just simply SSL profile)

     

     

    So you may turn off encryption in MS-SQL or use the following simple work-around.

     

     

    when CLIENT_ACCEPTED {

     

    TCP::collect

     

    }

     

    when CLIENT_DATA {

     

    if { [TCP::payload 1] eq "\x12" } {

     

    reject; return

     

    }

     

    TCP::release

     

    }

     

     

    this is based on assumption that when BIG-IP rejects PRE-LOGIN message from client, it will fallback to LOGIN7 (and with no encryption)

     

    then if client really send unencrypted LOGIN message, we could add the following part to iRule

     

    you can confirm this by packet trace, use Microsoft Network Monitor (it decodes MS-SQL better than other tools)

     

     

    when CLIENT_ACCEPTED {

     

    TCP::collect

     

    }

     

    when CLIENT_DATA {

     

    if { [TCP::payload 1] eq "\x12" } {

     

    reject; return

     

    }

     

    if { [TCP::payload 1] eq "\x10" } {

     

    if { [TCP::payload] contains "D\x00B\x001\x00" } {

     

    log local0. "found DB1"

     

    pool db1

     

    } elseif { [TCP::payload] contains "D\x00B\x002\x00" } {

     

    log local0. "found DB2"

     

    pool db2

     

    } else {

     

    log local0. "found DB3"

     

    pool db3

     

    }

     

    }

     

    TCP::release

     

    }

     

     

    I took shortcut, instead of decoding all the data, I just use "contains" command to detect database name.

     

    note that database name is in Unicode

     

    so I just do a simple string to unicode conversion by append \x00 after each character (feel free to change this to a proper way :) )

     

    so from "DB1" it becomes "D\x00B\x001\x00"

     

     

    hope this help

     

    Nat