http://social.msdn.microsoft.com/For...8-d14de245e267


We have clusterA with 6 Nodes, 3 active and 3 Passive, all passive will be
available for any active nodez for Failover. All online database activity
will be done on this clusterA, then we have one another clusterB with 4
nodes, the database on ClusterA will setup merge replication with ClusterB.
My question is:
1- Is Merge replication available on Cluster environment?
2- Is Table Partitioning possible on Cluster environment?
3- All three technologies can be gathered in to give High Performance and
High Availability?
4- Also could you please tell me what is the advantage of using windows
server 2008 cluster over windows server 2003


We will be doing some data level maintenance and updates (fixing data
changes from other database) on ClusterB and also the reports will be served
from ClusterB. This will automatically give a Load Balancing environment.

Please help me if this can be achieved or it might has any flaws. We will
distribute Database level activity Load among two Clusters via replication
and to face large tables we may us benefit from table partitioning. How it
would look like?

We have 100000 users online at a time and we have 0 downtime with max.
performance is target to achieve. Also the maintenance cost should be
considere3e as much we can reduced as possible.


You can find my designed architecture here:


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

------------------------------------------------------------------------------------
http://www.geocities.com/shamshad_al...eplication.jpg

Please give your suggestion and thoughts ...

Shamshad Ali.

What is the rationale for having a six-node cluster with only three SQL
instances? You may be better off with three two-node clusters, and that would
give you better isolation. But you have a specific reason for your current
configuration.

Linchi

"Shamshad Ali" wrote:

Thanks for your reply. I am new to clustering and never worked on it before.
Your suggestions would land me on right path.
What you suggest how can i achieve high availability with load balancing,
low maintenance cost?
could you please guide me and suggest best option. I made some diagrams
could you plz. look them and understand my scenario to which i want to have
max. of throughput. and make corrections in design ?



Shamshad Ali.


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

Merge replication does not achieve load balancing. The overhead for merge
is usually close to the processing load the node would take directly. In
other words, the transactions still have to happen on all nodes. You don't
get any scale-out benefits.

HA and Low maintenance are opposing concepts. You spend more time, effort,
and energy on maintenance so the system spends more time processing
transactions. The actual maintenance windows are short, but planned very
heavily.

Scale-out as a native component of SQL Server does not exist.. Yet. I am
sure the team in Redmond is working on something that direction. When we
will see it in the retail product is the big question.

Right now, bigger boxes is the answer, not more boxes.

As for clustering Windows 2008 vs 2003, the benefits are way too numerous to
list in a short answer. Lets just say I recommend 2008 absolutely for
clustering.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP





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

I have one another design i implemented earlier. and that has more
maintenance costly so i thougth about the clustering; also it has some
drawbacks i mentioned in note below.
here we are moving online data to reporting/archivint database weekly, some
meintenance cost and overhead on other depending servers raise here. also if
Server A get down we lost replication with reporting and so on.

Can we merge this design with earlier one with HA and make some precised
solution? Please help

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


Shamshad Ali



"Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote

Furthermore,

Will Data Partitioning help in this scenario?
http://www.geocities.com/shamshad_ali74/SQLCluster.jpg

Can we have only data partitioning only implemented, will it work


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

What is 'this scenario'? It would help if you can be specific.

Linchi

"Shamshad Ali" wrote:

Actually i wanted to show you the diagram in which i tried to explain that
in a cluster environment, can we make table partitioning on large tables?

and the design of partitioning is also described in diagram:
http://www.geocities.com/shamshad_ali74/SQLCluster.jpg

Actually we have few site tracking tables in our database, each day we have
more than 100000 users transaction in few tracking tables, due to large
tables, users are face performance issue when browsing site. We wanted to
distribute load among servers using Peer to Peer replication and keep only
one weeks data into online database rest would be moved on another box (say
archive db or reporting db) but that has many drawbacks i mentioned in
diagram.
http://www.geocities.com/shamshad_ali74/p2p.jpg


so we decided to have table partitioning. in which we keep partition per
week data. montly for past months since begining of year and rest partitions
will be for yearly data from Janurary to december for all past years since
begining of project.

Please help me figureout the solution for High Availability + High
Performance

Shamshad Ali


"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> 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.



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

Failover clustering and table partitioning are two orthogonal concepts.
Whether or not you use failover clustering should have no impact on whether
or not you use table partitioning.

Linchi

"Shamshad Ali" wrote: