Cluster synchronize

I have an architecture which has 4 DB servers on the whole; two will
participate in load balancing and other two in fail over clustering.
My question is how will the two DB servers that is used for load
balancing synchronize?
Hi
To synchronize the data between the 2 clusters, you need to use something
like replication. Clustering is not a load-balancing technology.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<mukesh.thiru@.gmail.com> wrote in message
news:1135764099.960391.227140@.g43g2000cwa.googlegr oups.com...
>I have an architecture which has 4 DB servers on the whole; two will
> participate in load balancing and other two in fail over clustering.
> My question is how will the two DB servers that is used for load
> balancing synchronize?
>
|||Clustering is also not compatible with Windows Load Balancing, so you would
need to use a hardware based load balancer.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eQ%23n4Y6CGHA.620@.TK2MSFTNGP10.phx.gbl...
> Hi
> To synchronize the data between the 2 clusters, you need to use something
> like replication. Clustering is not a load-balancing technology.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> <mukesh.thiru@.gmail.com> wrote in message
> news:1135764099.960391.227140@.g43g2000cwa.googlegr oups.com...
>
|||mukesh.thiru@.gmail.com wrote:
> I have an architecture which has 4 DB servers on the whole; two will
> participate in load balancing and other two in fail over clustering.
> My question is how will the two DB servers that is used for load
> balancing synchronize?
If you are using SQL server 2005, you might want to consider using read
only shared databases for your reporting . Using a shared file system,
all the nodes can see the same database files which are read only, and
mount the database as read only. You could run reporting quaries
against up to 16 nodes archiving close to 16X speedup.
If your update cycle and reporting cycle do not overlap, switching
between the read write to read only (and back) takes about 20 seconds.
Note that no replication is needed and no storage space is wasted.
Tomer Meshorer
Database Engineer
Polyserve Inc (http://www.polyserve.com)
Email:tmeshorer@.polyserve.com
|||I'd very strongly disagree with that assessment. It isn't a shared file
system, it requires a SAN.
Additionally, just because you point 2 SQL Server instances at the same
database does not mean you get 2X performance improvement, just like
pointing 16 instances at a single database will not give you 16X
performance. You could in fact see your performance degrade. The only way
that your performance would improve is if you have queries running on each
instance that are not competing with each other for disk throughput. If
there is disk contention, your performance will degrade since a single set
of disks are now servicing requests from multiple instances at the same
time.
Read only, shared databases is a nice new feature, but any performance
benefit you may get is very highly dependent upon your query patterns, the
volume of data in your active working set, and the amount of physical data
you can cache in memory on each instance.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"tmeshorer" <tmeshorer@.polyserve.com> wrote in message
news:1135935720.275938.306680@.g43g2000cwa.googlegr oups.com...
> mukesh.thiru@.gmail.com wrote:
> If you are using SQL server 2005, you might want to consider using read
> only shared databases for your reporting . Using a shared file system,
> all the nodes can see the same database files which are read only, and
> mount the database as read only. You could run reporting quaries
> against up to 16 nodes archiving close to 16X speedup.
> If your update cycle and reporting cycle do not overlap, switching
> between the read write to read only (and back) takes about 20 seconds.
> Note that no replication is needed and no storage space is wasted.
>
> Tomer Meshorer
> Database Engineer
> Polyserve Inc (http://www.polyserve.com)
> Email:tmeshorer@.polyserve.com
>
|||Your scenario is correct on a specific context. Vis you have an I/O
bottleneck
However, this is customer specific.
In cases where you have a CPU bottleneck (usually occurs in DW type of
quaries,which tends to do sequential I/O) the suggested solution will
scale close to linear(at least as observed in internal benchmarks where
I/O was not a bottleneck)
What I am suggesting, and specifically in the context of reporting, is
an alternative way for scaling SQL server.
Currently the only way to scale is up, which become very costly when
you go to the 8,16,32 CPU boxes.
In addition, you cannot scale economically since you move in increments
of 2^n each time you want to decrease your reporting time (and move
exponentially in price)
Note that in both scenarios (scale up to big SMP, or scale out on a
shared file system cluster) if you have an I/O bottleneck you reach the
state as described in your email.
However scaling out is more economically sound (assuming that you do
not have I/O bottle neck) since:
1. It is much cheaper
2. Pay as you go
3. No storage duplication
4. No changes to the database configuration(I.e. no need for
replication).
Best,
Tomer Meshorer
Database Engineer
Polyserve Inc
|||My only point was that you stated that you would get a 16X performance
improvement by using this when you deploy on 16 machines. That statement is
100% false and we both know it. You get ZERO performance improvement. Not
a single, solitary second of improved performance. A query running on the
operational system and taking 10 seconds is going to take 10 seconds to
complete if you are using a scalable shared database model.
Do not mix improved performance with the ability to execute more read only
queries per unit time. It is NOT the same thing and is an incredibly
misleading statement. It is misleading statements like that which lead to
people implementing things and then saying that "SQL Server can't scale" or
"SQL Server can't perform", because they are trying to make a feature do
something that it was not designed to do and never had the possibility of
doing in the first place.
You CAN be increased throughput and you CAN get increased capacity by being
able to run many more of these 10 second queries simultaneously when they
are executed against multiple machines. The ONLY time that you get
increased throughput (more queries per second) or increased capacity (more
queries per second) is if you hae a VERY specific situation where memory or
CPU is the ONLY bottleneck. In those situations, you can get increased
throughput or capacity, BUT, you do NOT and never will get a linear
increase in query capacity. The amount of increased capacity is VERY highly
dependent upon your database design, the volume of data you are working
with, and query patterns.
You cited DW type of queries. There is no such thing. You either have
queries that perform sequential I/O or queries performing random I/O.
Giving it a fancy name doesn't change it, because you run both types of
queries in every operational system. So, let's look at a CPU contrained
query. We have system where we have 4 processors and 4 GB of RAM. Queries
executing against the 400GB of data within the database normally take 15
minutes to execute and pull as much as 3 GB of data. We have isolated the
issue to CPU bottlenecks and moved the system to an 8 processor machine,
thereby doubling the CPU capacity. We observed that the execution time of
this query drops to 11 minutes whereby, we found that memory became the
bottleneck in the system. We doubled the RAM to 8GB and found that the
query execution only dropped to 10 minutes. Both memory and CPU became
bottlenecks at that point, because we were all of a sudden executing more
queries per unit time and 3 simultaneously executing queries would saturate
our memory. This would cause pages to be ejected from the cache (incurring
additional processor overhead). More pages would have to be read from disk
(more processor overhead). We quickly found out that it if we increased the
processor or memory capacity, we would then shift the bottleneck to the I/O
subsystem.
Sure, you can craft testing matrices and benchmarks that will say that
scalable, shared databases will get you a nearly linear scaling. (VERY
important to note that the word is SCALING, NOT performance improvement.)
For every scenario that you come up which exhibits this behavior, I can
change it a hundred different ways to cause the performance to degrade
either by changing the query pattern, introducing a new query, changing the
indexing, increasing the data volume, changing query parameters, etc.
It is a nice technology that has a VERY specific use in an environment
experiencing VERY specific scalability issues. Unless you have done a full
analysis on the system to determine whether it is appropriate, implementing
this technology would be a very big mistake. It most definitely isn't going
to produce a linear scaling and there is a reason that Microsoft is not
giving any specific numbers. (The instant they publish numbers like you
have, there are going to be several thousand people handing evidence that
the findings are not correct.)
This very specific issue is what makes all of the Oracle marketing and sales
surrounding RAC a complete joke. Oracle RAC won't produce a linear scaling
for exactly the same reasons that Scalable Shared Databases won't produce a
linear scaling. If your query pattern, active data volumes, and bottlenecks
meet a VERY specific profile then these types of technologies will get you
increased SCALABILITY. (That does NOT mean you get improved performance.)
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"tmeshorer" <tmeshorer@.polyserve.com> wrote in message
news:1136012125.047011.237960@.g47g2000cwa.googlegr oups.com...
> Your scenario is correct on a specific context. Vis you have an I/O
> bottleneck
> However, this is customer specific.
> In cases where you have a CPU bottleneck (usually occurs in DW type of
> quaries,which tends to do sequential I/O) the suggested solution will
> scale close to linear(at least as observed in internal benchmarks where
> I/O was not a bottleneck)
> What I am suggesting, and specifically in the context of reporting, is
> an alternative way for scaling SQL server.
> Currently the only way to scale is up, which become very costly when
> you go to the 8,16,32 CPU boxes.
> In addition, you cannot scale economically since you move in increments
> of 2^n each time you want to decrease your reporting time (and move
> exponentially in price)
> Note that in both scenarios (scale up to big SMP, or scale out on a
> shared file system cluster) if you have an I/O bottleneck you reach the
> state as described in your email.
> However scaling out is more economically sound (assuming that you do
> not have I/O bottle neck) since:
> 1. It is much cheaper
> 2. Pay as you go
> 3. No storage duplication
> 4. No changes to the database configuration(I.e. no need for
> replication).
> Best,
> Tomer Meshorer
> Database Engineer
> Polyserve Inc
>
|||I should have clarified my point. I am looking at the actual wall clock
time that it takes to run a reporting job. I also assume that you have
more queries than servers, which I think would be the case for a
typical reporting job.
So from a wall clock time perspective, you will be able to get close to
X#server execution time improvement. I.e. if you are CPU constrained, a
6 hour job with 6 queries on one server can be reduced to 1 hour job on
6 servers.
If you have an I/O bottleneck there are two cases. If you are storage
constrained that this is a natural case. Vis, it does not matter if you
have 4*2 way servers or one 8 way server. If you are internal I/O BUS
constrains (e.g. PCI Express) moving to 4*2 way might improve your
situation since now you have 4 internal buses in your disposal.
However, I do not agree with your other point that this is very
specific situation. I think that you will encounter this situation at
the same probability that you will encounter any other kind of
bottleneck (which is about 1/3 of the cases). However, as your example
describe, to move out of the "CPU as a bottleneck" state into "I/O as
bottleneck state" you have to scale up. Based on your example, your
customer had to scale up from 4 way to 8 way.
Now lets say that you paid the money and now you are I/O constrained.
Ok, you buy more disks, change your RAID method, etc. Time passes and
you are again constrained by the CPU... are you going to move to 16
way?, 32 way? 64 way ?
My point is that if you do not have CPU bottleneck now, you will be
after you solve your current bottleneck. I.e. this is not VERY specific
scenario but rather a common one.
Tomer Meshorer
Database Engineer
Polyserve Inc (www.polyserve.com)
|||So that means that I can put 9 women to work and produce a baby in 1 month?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"tmeshorer" <tmeshorer@.polyserve.com> wrote in message
news:1136319106.372823.46070@.o13g2000cwo.googlegro ups.com...
>I should have clarified my point. I am looking at the actual wall clock
> time that it takes to run a reporting job. I also assume that you have
> more queries than servers, which I think would be the case for a
> typical reporting job.
> So from a wall clock time perspective, you will be able to get close to
> X#server execution time improvement. I.e. if you are CPU constrained, a
> 6 hour job with 6 queries on one server can be reduced to 1 hour job on
> 6 servers.
> If you have an I/O bottleneck there are two cases. If you are storage
> constrained that this is a natural case. Vis, it does not matter if you
> have 4*2 way servers or one 8 way server. If you are internal I/O BUS
> constrains (e.g. PCI Express) moving to 4*2 way might improve your
> situation since now you have 4 internal buses in your disposal.
> However, I do not agree with your other point that this is very
> specific situation. I think that you will encounter this situation at
> the same probability that you will encounter any other kind of
> bottleneck (which is about 1/3 of the cases). However, as your example
> describe, to move out of the "CPU as a bottleneck" state into "I/O as
> bottleneck state" you have to scale up. Based on your example, your
> customer had to scale up from 4 way to 8 way.
> Now lets say that you paid the money and now you are I/O constrained.
> Ok, you buy more disks, change your RAID method, etc. Time passes and
> you are again constrained by the CPU... are you going to move to 16
> way?, 32 way? 64 way ?
> My point is that if you do not have CPU bottleneck now, you will be
> after you solve your current bottleneck. I.e. this is not VERY specific
> scenario but rather a common one.
> Tomer Meshorer
> Database Engineer
> Polyserve Inc (www.polyserve.com)
>
|||No. What I suggest is "Concurrent " execution, I.e. wall time speed up.
However, I cannot formulate and answer using babies and women while
staying
politically correct :-)
Tomer Meshorer
Database Engineer
Polyserve Inc (www.polyserve.com)