Join Log in
Score:0
Ernst avatar Server

SQL Server Load Balancing Splitting Read/Write Operations

jp flag
Ernst

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?

1311
0 + 0
Score:-1
avatar Server
cn flag
TomTom

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.

0 + 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.