locked
Need Solution- Changes at DR should be replicated at DC RRS feed

  • Question

  • Hi,

    I have SQL Server 2014 Standard Edition 

    Active/Passive Server at DC and Standalone at DR

    5 database need to be replicated to DR

    Once Switch-over / Fail-over happens Application will point to DR. Changes will be done at DR. 
    Then again Switchback/Fail-back will happen and Application will point to DC. The changes happen at DR databases should be reflected at DC. 

    1. Log-Shipping is One-way replication from DC to DR. Before Switchback/Fail-back Log-Shipping has to be configured again
    2. Database Mirroring - Changes will be done at DR will be synch with DC during Switchback/ Fail-back. There are 5 databases.
       The database size is around Terabytes there can be performance issue.
    3. Merge and Tran with update subscription are object level technology. Also a new column is added in table which might create      connectivity  issue when application communicating with Databases.

    So which technology should I prefer for SQL Server 2014 Standard Edition Active/Passive Server at DC and Standalone at DR. Is there any other solution then please let me know

    Regard,
    Nikhil P Desai




    Thursday, August 25, 2016 6:43 AM

Answers

  • Hi Nikhil P Desai,

    As per your requirement, I would say you need to upgrade to Enterprise Edition to setup an Availability Group between DC site and DR site. On DC site you could configure your Active/Passive cluster as primary replica of the Availability Group and configure the standalone instance on DR site as secondary replica. For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Thursday, August 25, 2016 7:18 AM
  • Note however, that if you upgrade to SQL 2016 Std Edition then each database has to be in its own AG.   If you need to keep multiple databases in sync and on the same server at the same time then this will not work for you.

    Upgrading to SQL 2016 Enterprise may be too expensive for you - however with your databases being around 1TB in size and you mentioning performance issues then I think you are certainly in the realms of re-architecting your solution and you may also want to consider the enhanced columnstore indexes and perhaps also partitioning to improve your performance at the same time.

    It will not be a trivial solutions.


    Martin Cairney SQL Server MVP

    Friday, August 26, 2016 6:20 AM

All replies

  • Hi Nikhil P Desai,

    As per your requirement, I would say you need to upgrade to Enterprise Edition to setup an Availability Group between DC site and DR site. On DC site you could configure your Active/Passive cluster as primary replica of the Availability Group and configure the standalone instance on DR site as secondary replica. For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Thursday, August 25, 2016 7:18 AM
  • Hello,

    If you want to stay in 2014 standard Edition you have to use Database mirroring.

    Regards

    Thursday, August 25, 2016 1:09 PM
  • Hi There,

    your only options are (2. Database Mirroring) & (3. Merge and Tran) with standard edition Active/Passive and a DR standard.

    If you change the design with Always on you can achieve this easily.  either FCI in DC and always on with DR.

    or once cluster across DC & DR with sync commit.

    So you need Enterprise edition.

    if you consider SQl 2016 standard your options are limited but more than SQl 2014 standard. As SQl 2016 standard provide always on with availability groups with one database and a maximum of 10 I think.

    Good luck

    Kumar

    Friday, August 26, 2016 2:17 AM
  • Note however, that if you upgrade to SQL 2016 Std Edition then each database has to be in its own AG.   If you need to keep multiple databases in sync and on the same server at the same time then this will not work for you.

    Upgrading to SQL 2016 Enterprise may be too expensive for you - however with your databases being around 1TB in size and you mentioning performance issues then I think you are certainly in the realms of re-architecting your solution and you may also want to consider the enhanced columnstore indexes and perhaps also partitioning to improve your performance at the same time.

    It will not be a trivial solutions.


    Martin Cairney SQL Server MVP

    Friday, August 26, 2016 6:20 AM