HA, Load Balancing/replication?

SQL Server Administration
1

Hi all.

I am trying to figure out if it is possible (And how exactly?) to LB a SQL Server.

Say we have 1 DB. Not only do other applications access it (read and write) but say if there is a very intense and long running job that runs on the DB today, is there a way to have some kind of LB where the load gets distributed and still have uptime for the SQL DB so clients still can access it at the time the "mega" job(s) run?

What is involved in this?
How does one set this up?

Would this be clustered based or something else?

Thank you

2

SQL Server cannot be load-balanced, only one node can own and access a database at a time. With that said, you could setup and configure an AG (Availability Group) and setup one or more secondary nodes as read-only/read-intent.

Then you would direct your "mega" job to one of the secondary nodes, assuming that "mega" job only performs selects. If the mega job needs to perform read/write operations it needs to run on the primary node.

1 Like