patternsqlMinor
Load balancing with Windows Clustering and AlwaysOn Availability Groups
Viewed 0 times
groupswithandwindowsbalancingclusteringavailabilityloadalwayson
Problem
I have some questions regarding Clustering and AlwaysON.
blocking?
get some kind on load balancing. If yes how will the data be
synchronized?
- If I need to build a 3 node cluster - 2 active and one passive. Each node will be 16 CPU and 128 GB RAM. Does this mean that I will have double resources. Will I get the max thread number (704) for each node?
- For the 2 nodes accessing the same storage, will this lead to more
blocking?
- For AlwaysOn, can I set 2 primary servers instead of one so I can
get some kind on load balancing. If yes how will the data be
synchronized?
Solution
QUICK ANSWERS TL/DR
You can create a 3 node cluster. But in SQL Server terminology active/active clustering means that you can run separate instances on any of the cluster nodes which then can fail back to any of the other nodes as clustering is for redundancy or high availability and nothing is shared between the database services.
You can use Always On the create read only replicas of the data which can be used to take some read only load of the server. All of this is explained in details in the High availability whitepaper
Now if you want or need to create a distributed load balanced setup you can try to implement peer to Peer transactional replication replication, you can create multiple distributed databases, using in memory objects in SQL Server to handle the transactional load and offload the data to other objects/databases. But the only transactional RDBMS that can, out of the box, run a single database in shared everything mode on multiple hosts is Oracle RAC (or maybe ScaleDB) and that comes with it's own set of problems.
- Sort of, you will double the resources but every instance will only be active on one node at the same time.
- Any SQL Server instance will have exclusive access to it's storage on the node where it's currently active - If all the storage for multiple instances comes from the same SAN one instance can affect the others
- No you cant, sorry
You can create a 3 node cluster. But in SQL Server terminology active/active clustering means that you can run separate instances on any of the cluster nodes which then can fail back to any of the other nodes as clustering is for redundancy or high availability and nothing is shared between the database services.
You can use Always On the create read only replicas of the data which can be used to take some read only load of the server. All of this is explained in details in the High availability whitepaper
Now if you want or need to create a distributed load balanced setup you can try to implement peer to Peer transactional replication replication, you can create multiple distributed databases, using in memory objects in SQL Server to handle the transactional load and offload the data to other objects/databases. But the only transactional RDBMS that can, out of the box, run a single database in shared everything mode on multiple hosts is Oracle RAC (or maybe ScaleDB) and that comes with it's own set of problems.
Context
StackExchange Database Administrators Q#115539, answer score: 4
Revisions (0)
No revisions yet.