How to separate write and read for sql database?

huzhiqi

I have four vm,it is three microsoft sql server 2012 and one microsoft AD,three sql sever auto synchronize date with always on mode.

I want to separate write action and read action to different sql server,for example,If I execute select command,A10 will forward the query to read sql server,if I execute write/update/delete/others command, A10 will forward the query to write sql server.

I know we need design a aFlex scrite to achieve the requirement,but how?

mdunn

This is from the SLB admin guide and seems to follow your use-case. Perhaps this can get you started?

Create an aFleX Script for Server Selection (aFlex Script – Optional)
You can create an aFleX script to select servers based on MySQL or MS-SQL traffic. The following
script checks to see if the query is a select statement (read) or an insert statement (write):


when DB_COMMAND {
set ret [ DB::command ]
log "aflex script got command number $ret"
pool mysql_read
}
when DB_QUERY {
	set ret [ DB::query ]
	log "aflex script got query $ret"
	if { ($ret contains "select" ) or ( $ret contains "show" ) or ($ret contains "SELECT" ) }
	{
		log "It is a select!"
		pool mysql_read
	} else {
		log "It is an insert!"
		pool mysql_write }
	}

huzhiqi

I configured VIP with protocol of mssql,did not bind the aFlex,just load balancing.

When I Connected VIP of SQL server with a tool named navicat，it showing 'Protocol error in TDS stream'.

But when I connected VIP of SQL server with a tool named SQLdbx,it showing login success.

I guess,it may be the reason of version,different version can't compatible.

How to fix this issue?

mdunn

Sorry I'm not sure on that one. Perhaps the Navicat connection was sent to a different back end server than SQLdbx?

huzhiqi

Yeah,maybe it is the reason,different tools connected with different way.When login,the SQLdbx just uses query operation,Microsoft management uses query and write operation,navicat maybe uses query and write operation.But how to fix this issue?