Hello We have 3 nodes of SQL Server 2014 Standard Edition one of them as primary and the other one as Replication of the Primary using Transactional Replication database
We want to use the other 2 nodes to read operations and reduce the load of the primary node.
How could we configure in a load balancer like HA Proxy to split read operation from writes and send to node 2 and 3 only read operations? or Should We use other load balancer that allow this?
How could we configure in a load balancer like HA Proxy to split read operation from writes and send to node 2 and 3 only read operations?
Not at all. There is no way that a load balancer would know what happens in a SQL Server connection and understand logically what can and what can not be considered a read only or a write only or a mixed (which is a write), especially not as the load balancer can not look forward in time. I can connect to sql server, start a transaction, do a read operation AND THEN START A WRITE - how would you know I intend to write when deciding on load balancing? There is no logical way to handle this without looking forward in time.
The way this is normally done is by having different connections on the application level and have the application decide which one to use. After all, one would assume the programmers are smart enough to know whether they intend a connection only for reading or actually maybe write on the same connection/transaction at a later stage.
