SQL DNS RE-Direction

Hi Guys,

I basically have 3 SQL servers and a group of 6 web servers.

SQL Servers

SQL-Live

SQL-UAT

SQL-Wip

Web Servers

UAT

Wip

web1

web2

web3

web4

All web servers are in a pool

I have set them up as nodes on my F5 and I have 1 dns entry set up for sql dns entries which is sql-web.domain.com

what I want to do is be able to re-direct traffic to a specific sql server depending on which web server the SQL request comes from.

So in the code all i want to have is sql-web.domain.com and if this code is on the UAT web server i want the F5 to be able to re-direct this automatically to the UAT SQL server.

the web requests for my wip and uat servers dont go through the f5 but the live web servers are all set up on the F5 with sites as virtual servers using a the web pool of 4 live servers.

Any help would be much appreciated.

Pav

I’ve come up with something like this

when CLIENT_ACCEPTED {
   if { [matchclass [IP::remote_addr] equals $::Wip_Server] and [TCP::local_port] != <1433> } {
      pool SQL-Wip
   }

But what would be better is if instead of redirecting on IP if I could just re-direct traffic based on the node?

Interesting concept… Not sure I like it (Sorry. I shudder at the fact that configs and target IP address are the same between test/dev and production), however the iRule looks fine and should accomplish what you require…

But what do you mean ‘direct traffic base on the node’? In the CLIENT_ACCEPTED event you don’t have access to a lot more other than the clients IP address to determine which webserver the traffic is coming from.

H

I assume I dont need to have the nodes set up for my wip and uat servers instead I just need to create two virtual servers and assign the irule to that?

OK so i’ve created a virtual server called Wip and assigned it a different IP

I’ve added the wip server ip to the WiP_Servers address list and how would I modify this rule so that any SQL traffic from the wip server will be re-directed to the SQL-WIP pool and any other requests will just pass traffic normally?

when CLIENT_ACCEPTED {
   if { [matchclass [IP::remote_addr] equals $::WiP_Servers] } {
      pool SQL-WiP
   } else {
   }
}

ok have come up with this but am getting the following errors:

when CLIENT_ACCEPTED {
  if {[matchclass [IP::remote_addr] equals $::Wip_Server]}{
    pool SQL-Wip
  } else {
  elseif {[matchclass [IP::remote_addr] equals $::UAT_Server]}{
    pool SQL-UAT
  elseif {[matchclass [IP::remote_addr] equals $::Live_Servers]{}
    pool SQL-Live
  }
}

line 1: [parse error: missing close-brace] [{

line 4: [parse error: missing close-brace] [{

line 5: [undefined procedure: elseif] [elseif {[matchclass [IP::remote_addr] equals $::UAT_Server]}{

Hello again Pav.

You have some issues with braces and an extra ‘else’ in there. Try this instead:

when CLIENT_ACCEPTED {

if { [matchclass [IP::remote_addr] equals $::Wip_Server] } {

pool SQL-Wip

} elseif { [matchclass [IP::remote_addr] equals $::UAT_Server] } {

pool SQL-UAT

} elseif { [matchclass [IP::remote_addr] equals $::Live_Servers] } {

pool SQL-Live

}

}

Thanks for that Chris the rule now works.

Although at the moment when i access a site from a certain server its only seems to re-direct the SQL traffic and connect if I add a host entry on that particular web server for sql-web.domain.com.

do i need to set the virtual server type to forwarding IP by any chance?

Thanks again for all your help