If we configure 3 nodes in SQL Cluster and keep all active, will these all
nodes work simultaneously, i mean will it share the client's request and all
nodes work togather as load balancing between nodes and share the load among
nodes?

what is the benefit and use of active active terminology?
If i have 6 nodes i would keep all of them active so that they all work
togather and share work load among nodes? is that true?


Please help.

Shamshad Ali.

No, the nodes cannot share the workload in the sense of the traditional
dynamic load balancing. They must be running different SQL instances. If you
can distribute your workload among multiple SQL instances, fine and you can
achieve some type of 'static' load balancing. Otherwise, the instances are
not related other than the fact they share the same Windows failover cluster.

The key benefit is consolidation in that you can make them all work in stead
of keeping a node idle, and even when each node has an instance running, you
still achieve some level of protection in that if a node fails, the SQL
instance running on that node can fail over to some other node and continue
to run. Yes, if that other node already has an instance running, there can be
a performance issue when you now have two instances running on the node. But
that becomes a capacity planning issue.

Linchi

"Shamshad Ali" wrote:

Assuming I have 6 NODES, 3 Active, 3 Passive.

Active: Node1, Node3 & Node5
--------------------------------
Passive: Node2, Node4 & Node6

If I distribute my heavy activity tables in Node3 and Node 5 and keep my
lookup tables on Node1. By dividing them into 3 databases.
May I gain the load balancing in this manner?

Shamshad Ali

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote

I got this as a solution on database level work.
due you think following will work? and scale the application?

2). You can use two node active/active cluster and divide the database into
two database and then install them on each cluster node . e.g install the
first half of database to default instance on first node and other half on
named server on second node , now if one of the node goes done it will fail
over to other . In this way you will get scalability and high availability.
But this approach need database level changes.

Shamshad Ali.


"Shamshad Ali" <shamshad.ali (AT) softech (DOT) us> wrote

You probably won't get any performance gain. And it's likely you'd get
performance degradation if you need to join the data across different SQL
instances constantly.

In general, if your data is tightly related and you need to reference them
frequently in the same queries, you'd be better of put them together in the
same database or in the same SQL instance.

If however, you can minimize the cost of your distributed queries, you may
get a performance boost. So in the end, it's a trade off you must consider.

Linchi

"Shamshad Ali" wrote:

What you described would work if you can statically partition your data. And
in that case, you do not have a hard scalability problem to solve.

Linchi

"Shamshad Ali" wrote:

Could you plz. explain by giving a url with example about statically
partitioning data? how do i do this? need some help in this regards.
I am really messed up with my requirements and the design. Please help me
solving this design.

Thanks.

Shamshad Ali.


"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote

I don't know of any websites specifically for this. I'm sure they are good
ones out there, and someone else ca chime in with a link or two. I just
don't know off the top of my head. But the key is that you have to partition
your data yourself, and allocate computing resources for your partitions.
It's static in that the partition of the load itself is not handled by the
system as load changes. You have to do work to manage the distribution of
the load. If you can anticipate the load patterns and have sufficient time
to accommodate the load changes, it may work well for you. Note that
'partition' as used here is not a technical term such as table partition or
a partitioned view in SQL Server. It's used in a rather conceptual and loose
sense. In fact, you can view having different databases on different servers
for different apps is a type of static partition.

It comes down to the general system design principle of 'loose coupling and
tight cohesion'. You want to design your partitions so that the coupling
between the partitions is relatively loose. Or in SQL, you don;t end up
doing a lot of joins across partitions. At the same time, you want to put
data that is tightly related into the same partition so that when you do
have to reference them together in your code (e.g. SQL queries), you can
take advanatage of locality of reference (e.g. your likelihood of find
another piece of data is cache is improved).

Linchi

"Shamshad Ali" <shamshad.ali (AT) softech (DOT) us> wrote