Hello,

Iam familiar with Active/Passive failover where you have only one instance
of database and one active node at a time. So no confusion about fail-over.

However, now I am facing a situation with a A/A cluster. This is my
understanding, please correct me if Iam wrong:

We have node1 SQLServer having a database DB1. We have node2 SQLServer
having database DB2. The applications that need DB1 are connected to Node1
and apps that need DB2 are connected to node2.

Suppose if there is an issue with Node1, we need to failover to Node2. So my
confusion is (1) How will the DB1 data gets real-time updated on Node2? (2)
How will the apps that need DB1 redirect themselves to Node2?


TIA,
AbbA

the term "Active/Active" is misleading.

What yo have is a cluster with two nodes (N1 and N2) and two SQL Server instances (not databases) I1
and I2.

You normally run, say I1 on N1 and I2 on N2.

If, say, N2 dies, then the cluster support in windows will start I2 on N1. Seen from the users
perspective, your SQL Server died and then came alive again (the service was re-started). This is
thanks to the virtual server name that a SQL Server instance have in the cluster.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Abba" <SQL_Help (AT) aaa (DOT) com> wrote

Okay. Thanks Tibor.

So does that mean I cannot have seperate databases on the two nodes and
service two different applications?


"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in
message news:OM16qoa6JHA.1196 (AT) TK2MSFTNGP03 (DOT) phx.gbl...

Yes, you *can* because this is what the cluster is all about. The instances in a cluster is totally
separate from each other. The cluster is not any type of load-balancing, it is all about fail-over.


Windows cluster service monitors the other node and have various rules to see that the other machine
is running and also that the other SQL Server is running. If it detects that the other node died,
then the SQL Server service is started.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Abba" <SQL_Help (AT) aaa (DOT) com> wrote

Yes, I agree. Thus DB1 on Node1 is totally different from DB2 in Node2. And
they service two different applications real-time;both nodes are up and
running. . Bottomline is Active/Active clustering refers to a two-node
cluster, with each node running SQL Server instances but each with a
different set of databases.

Now, lets say Node1 dies, then as per what you mentioned, the DB1 should be
available in Node2. But Node2 does not have a local copy of DB1. My question
is about this transition. How does the DB1 data gets updated in Node2
real-time?


http://www.sqlmag.com/Article/Articl...ver_44938.html ."Note
that only one server at a time can open a SQL Server database. If you want
to implement a system in which you can update data in two places at once,
you need to read about SQL Server's various data-replication options in BOL
...."

Thank you Tibor.


"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in
message news:uvrfsgb6JHA.3688 (AT) TK2MSFTNGP05 (DOT) phx.gbl...

http://www.geocities.com/shamshad_ali74/p2pcluster.jpg

Please have a look on this design and let me know if it works?

1- Database Level Load Balancing using NLB and then P2P replication.
2- HA using clustering
3- Table Partitioning for Performance over large tables.

we have 100000 entries per day in master table, they might be double or 4
times more entries in transaction tables.
considering this as requirement, will this design work or it has flaws?
Please help.


Shamshad Ali.

"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in
message news:uvrfsgb6JHA.3688 (AT) TK2MSFTNGP05 (DOT) phx.gbl...

Then how does Active/Passive work? Let's start in the simple case, which
you said you understood. In Active/Passive you use a shared disk resource
(usually a SAN or otherwise network attached storage) and have two "dumb"
servers, either of which can be running your SQL Server instance at any
given time. If the one that is active goes down, SQL Server essentially
restarts on the other node (and as Tibor explains the cluster service makes
this decision based on various rules and "up" checks). The resources
(including the shared disk where your databases live) are made available to
the newly active node as part of the failover process. So when SQL Server
starts up again, your databases are essentially "local" just like they were
two minutes ago on the other machine, before the failover occurred.

Now in your case with Active/Active it is the exact same thing *except* you
have two instances, one active on each server, and they are kind of mirrored
to one another, each having their own shared disk. Most of the time they
will just sit there running independently, but in the event one fails over,
then the exact same process as above happens: the disk, databases and other
resources become available to the other node. Now in this case the solution
is a little more precarious, because one node needs to be able to handle
both instances of SQL Server.

A more common scenario (well, active/passive is definitely most common, but
after that) is active/active/passive. So you have two nodes that are up
running their own instance(s) of SQL Server, and then a standby server which
can take over for *either* server in the event of a failover. Now this
solution is less "risky" than the active/active case because the only time
you will overload in the event of failover is if *both* active machines
fail.

This is about load balancing, not about clustering. Two very different
solutions to two very different problems. Don't get them confused.

Clustering is big and complex; if you do not fully comprehend it, I strongly
recommend hiring a consultant if your solution is expected to be fault
tolerant and mission critical. Getting it wrong could cost you your job (or
worse).

Aaron, provided the long explanation for this, make sure you read his post thoroghly. I just want to
gove a directed short comment:

An SQL Server instance has a shared disk, typically on a SAN. The cluster support in windows makes
sure that only one of the nodes can access this at a time. This is where all the data sits (database
files etc). When a node fails, the cluster service makes sure that the new node owns and sees this
disk before the database engine starts.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Abba" <SQL_Help (AT) aaa (DOT) com> wrote

Table partitioning may not necessarily give you a performance boost. If you
need to regularly archive/purge older data from the table, table partitioning
can help speed up that process and minimize its impact, and therefore help
with performance. It's slightly more complex to manage than a single plain
table, and there may be subtle issues with using table partitioning with
query plans, etc. So it's not automatically a plus.

The point is that you need to carefully test your apps with table
partitioning.

Linchi

"Shamshad Ali" wrote:

OK, now two options:

1- Assuming if I don't use table partitioning on online database (Cluster A,
B & C). Now may i get performance by using load balancing technique of P2P
replication and users will use Cluster D only for reporting.

2- If I only keep one week's data everyday in my online database and move
from online production to archive/reporting database. Will it be fine? All
my reporting will have to use query from both databases in this manner UNION
etc. Will this technique work? and improve performance?

Thanks for your comments.

Shamshad Ali.



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