4

I have some questions regarding Clustering and AlwaysON.

  1. 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?
  2. For the 2 nodes accessing the same storage, will this lead to more blocking?
  3. 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?
5
  • 3
    You can't load balance writes effectively in SQL Server, no. There's merge replication but good luck with that. You can load balance reads using multiple readable secondaries. Sep 18, 2015 at 20:45
  • @AaronBertrand .. should i understand from this that there is no any kind of load balancing in Active/active cluster? i need to build a server able to Handel around 40,000 connection and 60,000 request per min. what is your suggestion
    – sebeid
    Sep 19, 2015 at 23:48
  • 1
    There is no such thing as active/active. In a SQL Server failover cluster, an instance can only be active on a single node at a time. Clusters are for reliability, not performance. Sep 20, 2015 at 0:37
  • @AaronBertrand thanks .. so what is the solution for best performance ?
    – sebeid
    Sep 20, 2015 at 1:18
  • This is like asking what is the best solution for financial success. There are all kinds of approaches to performance optimization, and which you choose (and in which combination(s)) depend heavily on details of your specific scenario - hardware, table structure, data size, application usage patterns, etc. Sep 20, 2015 at 12:08

1 Answer 1

4
+150

QUICK ANSWERS TL/DR

  1. Sort of, you will double the resources but every instance will only be active on one node at the same time.
  2. 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
  3. 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.

2
  • if i have 2 active nodes,can both run the same application or each node should run a different application ?
    – sebeid
    Sep 22, 2015 at 20:39
  • If the application is SQL Server then each node runs a different instance of the software Full definition here: msdn.microsoft.com/en-us/library/hh231298.aspx
    – Spörri
    Sep 23, 2015 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.