locked
High Availability Solution that allows writes to both primary and secondary at the same time RRS feed

  • Question


  • My sys admin group is asking me for a High Availability/DR database solution that will allow writes to both primary and secondary database servers at the same time based on whichever database the front end gateway directs the user to.   I see how Web servers can handle it, but I can't think of any Sql Server type of database solution that will allow that; Merge replication but I don't think that would be a good idea.

    Can the SAN replication products out there handle writes/reads to both geographically distinct locations at the same time and replicate the different info to each other. 

    Is this something Oracle offers?  It just seems so impossible to me with current technology unless there is some sort of merging of the files at some point that a 3rd party product has created.  Thanks for any leads.

    I have found something that is what I was looking for ....xkoto
    http://www.xkoto.com/resources/videos.php
    • Edited by LLittle Tuesday, September 22, 2009 8:02 PM
    Monday, September 21, 2009 9:29 PM

Answers

  • Of all the HA solutions, replication is the only one that allows you to have both databases online and writable at the same time.

    SAN replication wouldn't be an option, as SQL Server internally manages its extent/page allocations, and two online databases would quickly get out of synch, depending on load.

    Merge replication is an option, but has a fairly large management overhead.  The other option would be to scale out to a federation, but this is more of a load-balancing tool than a HA solution.  Along the same lines as a federation is transactional replication with updatable subscriptions.

    In the situation you've described, the usual solution is to use mirroring with automatic failover.  This way the two databases are kept in synch, and only one server can be up at a time, leading to no management of missing rows as the front end will only talk to the active server.  This would be optimal - having two databases could make a maintenance nightmare.  I would guess that your sys admin group isn't keen on mirroring for some reason, or they're worried about performance reasons and would like to load balance - which is a completely different issue to HA/DR.
    • Marked as answer by LLittle Tuesday, September 22, 2009 3:56 PM
    Tuesday, September 22, 2009 1:44 AM
    Answerer

All replies

  • Of all the HA solutions, replication is the only one that allows you to have both databases online and writable at the same time.

    SAN replication wouldn't be an option, as SQL Server internally manages its extent/page allocations, and two online databases would quickly get out of synch, depending on load.

    Merge replication is an option, but has a fairly large management overhead.  The other option would be to scale out to a federation, but this is more of a load-balancing tool than a HA solution.  Along the same lines as a federation is transactional replication with updatable subscriptions.

    In the situation you've described, the usual solution is to use mirroring with automatic failover.  This way the two databases are kept in synch, and only one server can be up at a time, leading to no management of missing rows as the front end will only talk to the active server.  This would be optimal - having two databases could make a maintenance nightmare.  I would guess that your sys admin group isn't keen on mirroring for some reason, or they're worried about performance reasons and would like to load balance - which is a completely different issue to HA/DR.
    • Marked as answer by LLittle Tuesday, September 22, 2009 3:56 PM
    Tuesday, September 22, 2009 1:44 AM
    Answerer
  • High availability is *not* the same thing as scalability if you own data. It is if you *don't* own data (think web farm). Ask your management how they would handle a situation where the same row/order/record (use some term management understand) is modified at the same time, so both nodes now have different information. The answer to that question will allow you to plan further. In general, plan availability separate from scalability. Sometimes you can use the same technology to support both, often not.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, September 22, 2009 7:27 AM