none
How to achieve active active database synchronization in DC and DR side? RRS feed

  • Question

  • There is a client requirement to obtain active-active database replication on both DC and DR side. So that when DC is down; the application can automatically connect to DR database with synchronized database. Both DC and DR are in different subnets and having a single Domain with two different domain controllers at DC and DR side. Can anyone please provide any documentation for these. The client is using enterprise version of sql server 2012.

    What will be the hardware and configuration requirements?


    • Edited by Rijo Raju Sunday, November 18, 2018 7:06 AM
    Sunday, November 18, 2018 7:05 AM

All replies

  •  >>>active-active database replication means a transaction replication? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, November 18, 2018 8:26 AM
  • Hi Rijo Raju,

    According to your description, SQL Server Always On Availability Groups can meet your requirement, the application can connect to the listener, when the DC is down, the database will failover to the DR site.

    To configure a multiple-subnet Always On Availability Groups, please refer to this document: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-multiple-subnet?view=sql-server-2017.

    Here is a blog describing it step-by-step, please refer to it: https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/ .

    Besides, what about the "active active database synchronization"? Alwayson can make the two replicas synced, but only one replica is active (read-write).

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 19, 2018 2:31 AM
    Moderator
  • AlwaysOn Availability Groups seems to be your best bet. Your WSFC can span multi-subnets. Take a look at the links Teige posted. Now, people generally go with Asynchronous mode when configuring AG to DR so that a transaction does not have to wait until it is sent and committed on the DR site before acknowledging the commit to the client. If you still want to go with "Synchronous" then know that there could be some potential network latency making the transactions to take long to commit. 

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, November 19, 2018 4:27 AM
  • Hi,

    I had tried always on availability group. Actually For always on availability group; a quorom is required which is a quorom witness disk or file share witness. But the DC and DR are in two different sites and it may require a third site to configure the file share witness or quorom disk witness which is right now not feasible. As there only two subnets. We cannot place the file share witness either in DC or DR. Because either of them fails; the quorom will also fail resulting in cluster service getting failed. Without quorom also; I guess automatic failover is not possible. So how to achieve automatic failover along with quorom high availability?

    Will database mirroring work? Because it also requires a witness server for automatic failover.

    Monday, November 19, 2018 5:38 AM
  • Hi,

    I had tried always on availability group. Actually For always on availability group; a quorom is required which is a quorom witness disk or file share witness. But the DC and DR are in two different sites and it may require a third site to configure the file share witness or quorom disk witness which is right now not feasible. As there only two subnets. We cannot place the file share witness either in DC or DR. Because either of them fails; the quorom will also fail resulting in cluster service getting failed. Without quorom also; I guess automatic failover is not possible. So how to achieve automatic failover along with quorom high availability?

    Will database mirroring work? Because it also requires a witness server for automatic failover.

    Monday, November 19, 2018 5:39 AM

  • As there only two subnets. We cannot place the file share witness either in DC or DR. Because either of them fails; the quorom will also fail resulting in cluster service getting failed. Without quorom also; I guess automatic failover is not possible. So how to achieve automatic failover along with quorom high availability?

    There are always some pros and cons with every solution. I'd put the file share witness on the primary (DC) site to maintain vote majority on the primary site. You are right, in case the site goes down, the cluster wouldn't survive (due to losing 2 votes). Unfortunately, there's only so much we can do. 

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, November 20, 2018 1:58 AM
  • Is it possible to achieve active-active disaster recovery using always on feature?
    Tuesday, November 20, 2018 5:48 AM
  • Hi Rijo Raju,

    What about your "active-active disaster recovery"? AlwaysOn can provide a real-time disaster recovery, but only one node is read-write.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 20, 2018 7:47 AM
    Moderator
  • hi

    Is possible to mirroring in DC- DR??

    Friday, July 19, 2019 12:36 PM