Updation of data in Active/Active cluster

#11

1. Can't really comment on P2P without knowing more about your requirements.
Persoanlly, I'd keep it simple. If reporting queries are interfering with my
OLTP activities, I'd use transactional replication to offload reporting
queries to a different server. That is a kind of load balancing (though
static) to help with the OLTP performance.

2. If you only keep one week of data, table partitioning would be useful
because you can implemt the so-called sliding window, making archiving a
meta-data operation without having to delete numerous rows all the time.

Linchi

"Shamshad Ali" wrote:

Quote:

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 in message
news:AF5D70A7-6786-494E-9E42-2617B7346866 (AT) microsoft (DOT) com...
3- Table Partitioning for Performance over large tables.

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:

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...
So does that mean I cannot have seperate databases on the two nodes
and
service two different applications?

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.

If, say, N2 dies, then the cluster support in windows will start I2
on
N1.
How is that possible? Can you please elaborate?

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 in message
news:eQtx7ta6JHA.6136 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
Okay. Thanks Tibor.

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

If, say, N2 dies, then the cluster support in windows will start I2
on
N1.
How is that possible? Can you please elaborate?

"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com
wrote
in message news:OM16qoa6JHA.1196 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
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 in message
news:%23aMbmiZ6JHA.3968 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
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

#12

Thanks for your comments.

Please let me know what requirements you want to know. I already explained
that we have to achieve HA, HP and reduce maintenance cost.
I tried to explained my database level activities in diagrams (which i think
is more easy and convinent way to understand) I mentioned i all my recent
posts.
We have one single database and contains data since year 2005. Currently
there are few tables with large amout of data. records like 1000000 rows per
large table.
the site is now opening to other domains for access and we assume every day
or at a time there will be 100000 users connecting online to our site.
We need to design such an architecture that will support all request from
clients in efficient way. online insertions and updations will be frequent.
also online reports are required by every individual user
Plus administrators of each domain/region will be accessing online reports
for their individual domains status/reports. One MIIS server will be
updating users profile and synchronizing on schedule bases.

I wrote my requirement here. If you have any other thing to ask or
confusion - Please feel free to ask.

Shamshad Ali.

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

Quote:

1. Can't really comment on P2P without knowing more about your
requirements.
Persoanlly, I'd keep it simple. If reporting queries are interfering with
my
OLTP activities, I'd use transactional replication to offload reporting
queries to a different server. That is a kind of load balancing (though
static) to help with the OLTP performance.

2. If you only keep one week of data, table partitioning would be useful
because you can implemt the so-called sliding window, making archiving a
meta-data operation without having to delete numerous rows all the time.

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 in message
news:AF5D70A7-6786-494E-9E42-2617B7346866 (AT) microsoft (DOT) com...
3- Table Partitioning for Performance over large tables.

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:

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...
So does that mean I cannot have seperate databases on the two nodes
and
service two different applications?

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.

If, say, N2 dies, then the cluster support in windows will start
I2
on
N1.
How is that possible? Can you please elaborate?

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 in message
news:eQtx7ta6JHA.6136 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
Okay. Thanks Tibor.

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

If, say, N2 dies, then the cluster support in windows will start
I2
on
N1.
How is that possible? Can you please elaborate?

"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com
wrote
in message news:OM16qoa6JHA.1196 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
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 in message
news:%23aMbmiZ6JHA.3968 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
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

#13

Thanks Tibor, Aaron. Just like learning swimming thorugh postal distance
education, I think I need to get hands-on and build a A/A cluster to
understand it fully! :-)

"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in
message news:OKK4KGc6JHA.4100 (AT) TK2MSFTNGP06 (DOT) phx.gbl...

Quote:

My question is about this transition. How does the DB1 data gets updated
in Node2 real-time?

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 in message
news:e%232ITtb6JHA.1432 (AT) TK2MSFTNGP02 (DOT) phx.gbl...
The instances in a cluster is totally separate from each other.

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?

Yes, you *can* because this is what the cluster is all about.
See what Richard mentions here:
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...
So does that mean I cannot have seperate databases on the two nodes and
service two different applications?

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.

If, say, N2 dies, then the cluster support in windows will start I2
on N1.
How is that possible? Can you please elaborate?

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 in message
news:eQtx7ta6JHA.6136 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
Okay. Thanks Tibor.

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

If, say, N2 dies, then the cluster support in windows will start I2
on N1.
How is that possible? Can you please elaborate?

"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com
wrote

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 in message
news:%23aMbmiZ6JHA.3968 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
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

#14

"Aaron Bertrand [SQL Server MVP]" wrote:

Quote:

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.

I myself am not keen on the legacy terms active/active or active/passive. I
have recently built some active/active/active/active/passive clusters - with
room to add some more nodes, but I prefer the term multi-node clusters. It's
pretty cool because I can have four machines fail and still keep running
(albeit at significant performance loss if the databases on their were near
performance capacity).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Updation of data in Active/Active cluster

microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering

Re: Updation of data in Active/Active cluster - 06-10-2009 , 08:04 AM

Re: Updation of data in Active/Active cluster - 06-10-2009 , 08:26 AM

Re: Updation of data in Active/Active cluster - 06-11-2009 , 12:34 AM

Re: Updation of data in Active/Active cluster - 06-11-2009 , 01:50 AM