Post reply

Best approach to achieve high availability and disaster recovery?

  • April 3, 2018 at 8:35 am

    #336852

    We are deploying SQL servers at two geographically distant sites. The bandwidth between two sites is 10Mbps. The requirement is to have both sites writable/readable. Both sites have redundancy requirement within site and at the same time they sync with one another. However, the two sites will not be writing to the same row of a table. What is the best approach here to achieve maximum availability and minimizing any potential data loss? We are thinking about using SQL server 2016 and try to avoid the use of deprecated technologies such as data mirroring.

  • April 3, 2018 at 9:38 am

    #1985318

    You cannot have both sites writeable. SQL's HA features do not scale out writes. There's peer-to-peer replication, but then you really need to localise writes (and at the table level, not the row) to avoid conflicts, It's complex to implement, and complex to manage

    There are multiple options for HA and DR, but picking one suitable would require a lot more analysis and investigation to identify requirements, technical constraints and non-technical constraints.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 3, 2018 at 11:40 am

    #1985338

    GilaMonster - Tuesday, April 3, 2018 9:38 AM

    You cannot have both sites writeable. SQL's HA features do not scale out writes. There's peer-to-peer replication, but then you really need to localise writes (and at the table level, not the row) to avoid conflicts, It's complex to implement, and complex to manage

    There are multiple options for HA and DR, but picking one suitable would require a lot more analysis and investigation to identify requirements, technical constraints and non-technical constraints.

    Thanks for the reply. The system design requires that clients on both sites are able to write to their respective database. So in a sense two sites are the exact replica of each other but they will be updating different data.

    So I was thinking about on each physical site there will be a high availability group. Each site's primary database will also be replicating its unique tables to the other site using transactional replication.

    For example, both sites have table A and table B but only site 1 updates table A and only site 2 updates table B. Then site 1 can publish table A to site 2 and site 2 can publish table B to site 1. The only issues I see is that during unexpected primary database failure, the distributor (not a remote one) can have pending actions that have not been been committed to subscribers yet. As a result, data loss can happen. Do you have any insight on providing redundancy to distributor in such situation? 

    Do you see any other issues on such approach? Thank you for your help once again. 

  • April 5, 2018 at 3:25 am

    #1985604

    Looking at what you desire to achieve have you considered Merge replication?

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication

    https://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply