Updation of data in Active/Active cluster

Discussion in 'microsoft.public.sqlserver.clustering' started by Abba, Jun 10, 2009.

  1. Abba

    Abba Guest


    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?

    Abba, Jun 10, 2009
    1. Advertisements

  2. 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, Jun 10, 2009
    1. Advertisements

  3. Abba

    Abba Guest

    Okay. Thanks Tibor.

    So does that mean I cannot have seperate databases on the two nodes and
    service two different applications?
    How is that possible? Can you please elaborate?
    Abba, Jun 10, 2009
  4. So does that mean I cannot have seperate databases on the two nodes and service two different
    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, Jun 10, 2009
  5. Abba

    Abba Guest

    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

    See what Richard mentions here:
    http://www.sqlmag.com/Article/ArticleID/44938/sql_server_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.
    Abba, Jun 10, 2009
  6. Abba

    Shamshad Ali Guest


    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.
    Shamshad Ali, Jun 10, 2009
  7. Now, lets say Node1 dies, then as per what you mentioned, the DB1 should be
    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
    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
    Aaron Bertrand [SQL Server MVP], Jun 10, 2009
  8. 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, Jun 10, 2009
  9. Abba

    Linchi Shea Guest

    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

    Linchi Shea, Jun 10, 2009
  10. Abba

    Shamshad Ali Guest

    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.
    Shamshad Ali, Jun 10, 2009
  11. Abba

    Linchi Shea Guest

    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 Shea, Jun 10, 2009
  12. Abba

    Shamshad Ali Guest

    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
    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.
    Shamshad Ali, Jun 10, 2009
  13. Abba

    Abba Guest

    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! :)
    Abba, Jun 11, 2009
  14. Abba

    Mark Allison Guest

    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).
    Mark Allison, Jun 11, 2009
    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.