Please help regarding the architecuture I am Planning for.

Discussion in 'microsoft.public.sqlserver.clustering' started by Shamshad Ali, Jun 9, 2009.

  1. Shamshad Ali

    Shamshad Ali Guest

    http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/7f0bc946-c9a4-48f9-a668-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_ali74/SQLClusterMReplication.jpg

    Please give your suggestion and thoughts ...

    Shamshad Ali.
     
    Shamshad Ali, Jun 9, 2009
    #1
    1. Advertisements

  2. Shamshad Ali

    Linchi Shea Guest

    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
     
    Linchi Shea, Jun 9, 2009
    #2
    1. Advertisements

  3. Shamshad Ali

    Shamshad Ali Guest

    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.
     
    Shamshad Ali, Jun 9, 2009
    #3
  4. 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
     
    Geoff N. Hiten, Jun 9, 2009
    #4
  5. Shamshad Ali

    Shamshad Ali Guest

    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
     
    Shamshad Ali, Jun 9, 2009
    #5
  6. Shamshad Ali

    Shamshad Ali Guest

    Shamshad Ali, Jun 9, 2009
    #6
  7. Shamshad Ali

    Linchi Shea Guest

    Will Data Partitioning help in this scenario?

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

    Linchi
     
    Linchi Shea, Jun 9, 2009
    #7
  8. Shamshad Ali

    Shamshad Ali Guest

    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
     
    Shamshad Ali, Jun 10, 2009
    #8
  9. Shamshad Ali

    Shamshad Ali Guest

    Shamshad Ali, Jun 10, 2009
    #9
  10. Shamshad Ali

    Linchi Shea Guest

    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
     
    Linchi Shea, Jun 10, 2009
    #10
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.