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?
  • 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. – Aaron Bertrand Sep 18 '15 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 '15 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. – Aaron Bertrand Sep 20 '15 at 0:37
  • @AaronBertrand thanks .. so what is the solution for best performance ? – sebeid Sep 20 '15 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. – Aaron Bertrand Sep 20 '15 at 12:08
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.

  • if i have 2 active nodes,can both run the same application or each node should run a different application ? – sebeid Sep 22 '15 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 '15 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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