Using an iRule to route SQL traffic

Question

Here is our setup. 
We have a SharePoint farm, four BISM (SSAS) SQL Servers and 4 ETL Servers, all 2008 R2 Servers. We are trying to setup 2 "pods" each with 2 BISM/2ETL Servers. &nbsp;
THe BISM servers go through an F5. We need to an have iRule route traffic, so we can segregate BISM traffic to the 'pods" according to a certain value, maybe a token, whatever will work. The problem is that between the SP Farm and BISM Server there is TCP (SQL) traffic. &nbsp;
Questions:
1. How can tell what the TCP traffic contains from the SP Farm to the BISM Servers? I have tried TCP::collect and not sure where to look to see the traffic. 
2. I need to use a variable of from the packets to compare to a data group, is that possible?
3. Any help with writting an iRule would be greatly appreciated. &nbsp;
Thank you!  &nbsp;

kevin_stewart · Answer

The new MS SQL profile in 11.5 allows you to balance across SQL pools based on command (read or write) and user (from a defined list). Beyond this there's no built-in SQL parsing function, so you'd necessarily have to build a pretty complex binary-based iRule, which I wouldn't recommend. That said, if 1) you can define a value early on in the SQL data, and b) that data is in every request (or at least 1 per TCP session), then there's still a chance you could read this and do something useful.

nitass · Answer

as Kevin mentioned, TDS command and event are introduced in 11.5.0.&nbsp;
TDS&nbsp;
https://devcentral.f5.com/wiki/iRules.TDS.ashx&nbsp;

dmitry_sherman · Answer

For some reason this feature is missing in both 11.5 and 11.5.1 LTM (VE) GUI, but found it in tmsh, any ideas why it was removed?&nbsp;

nitass · Answer

For some reason this feature is missing in both 11.5 and 11.5.1 LTM (VE) GUI, but found it in tmsh, any ideas why it was removed?&nbsp;

it is fixed in 11.6.0.&nbsp;
ID434414 - MS SQL Proxy Profile is visible in al Virtual Server GUI configuration screens&nbsp;

dmitry_sherman · Answer

thanks! Is it considered stable?
When can I get the 11.6 release?&nbsp;

Forum Discussion

Using an iRule to route SQL traffic

9 Replies

Recent Discussions

ASM instance creation

[ASM] - what is "Browser Challange file" ?

[ASM] - HTML5 Cross-Domain Request Enforcement - CLI command

Reverse Proxy Not Behaving

Stable Firmware for F5

Related Content

LTM Traffic Policies vs iRules: Which runs first?

Using Active/Standby for different traffic groups

Logging TLS traffic less than TLSv1.2

Traffic Shaping and Atlassian Jira

VPN not working when using APM policy via Local Traffic Policy