locked
Disaster Recovery Solution RRS feed

  • Question

  • I have 2 copes of the same database on 2 different server at two different locations. I have to synchronize the data 100% between the two databases... Please help me to select an appropriate solution to accomplish this.

    The 2 databases should be always online

    What I can think of is

    - Merge Replication.

    - Transactional  Replication with Updatable subscription..

    If you can point some other solutions that would be really great... how about Mirroring.. If I use this I may need a 2 way mirroring... is this possible.

    Friday, December 27, 2013 8:31 AM

Answers

  • Replication is mainly for scale-out. If you have plenty of time to test your app, verify and possibly make neccesary changes, then replication might be usable for fail-over.

    Mirroring and the successor Availability Groups means that you have one Primary where you can modify data, and the other one can only be read (a bit different technology between mirroring and AG, but the principal applies).


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Sofiya Li Monday, December 30, 2013 5:37 AM
    • Marked as answer by Sofiya Li Monday, January 6, 2014 9:27 AM
    Friday, December 27, 2013 10:57 PM
  • Hi Sudhakar,

    According to your description, there are lots of built-in ways in SQL Server  to make two servers synchronize. Some points you should take into account, for example, how much bandwidth and latency between two server, and the size of two database, how often and how much do data change and so on. you can choose the method most suited for your needs. Merge replication allows various sites to work autonomously and later merge updates into a single. As other post, you can choose replication as a disaster recovery solution. In database mirroring, secondary database is in an offline mode while mirroring is in place. This can ensure integrity in the secondary database in the event of a failover being required.

    For more information, see:
    Selecting the Appropriate Type of Replication: http://technet.microsoft.com/en-us/library/ms152565(v=sql.105).aspx

    Replication VS Mirroring, and what to use for a Disaster Recovery setup: http://simplesql.blogspot.in/2011/01/replication-vs-mirroring-and-what-to.html

    Thanks,
    Sofiya Li

    If you have any feedback on our support, please click here.


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, January 6, 2014 9:27 AM
    Monday, December 30, 2013 6:11 AM

All replies

  • Replication is mainly for scale-out. If you have plenty of time to test your app, verify and possibly make neccesary changes, then replication might be usable for fail-over.

    Mirroring and the successor Availability Groups means that you have one Primary where you can modify data, and the other one can only be read (a bit different technology between mirroring and AG, but the principal applies).


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Sofiya Li Monday, December 30, 2013 5:37 AM
    • Marked as answer by Sofiya Li Monday, January 6, 2014 9:27 AM
    Friday, December 27, 2013 10:57 PM
  • Hi Sudhakar,

    According to your description, there are lots of built-in ways in SQL Server  to make two servers synchronize. Some points you should take into account, for example, how much bandwidth and latency between two server, and the size of two database, how often and how much do data change and so on. you can choose the method most suited for your needs. Merge replication allows various sites to work autonomously and later merge updates into a single. As other post, you can choose replication as a disaster recovery solution. In database mirroring, secondary database is in an offline mode while mirroring is in place. This can ensure integrity in the secondary database in the event of a failover being required.

    For more information, see:
    Selecting the Appropriate Type of Replication: http://technet.microsoft.com/en-us/library/ms152565(v=sql.105).aspx

    Replication VS Mirroring, and what to use for a Disaster Recovery setup: http://simplesql.blogspot.in/2011/01/replication-vs-mirroring-and-what-to.html

    Thanks,
    Sofiya Li

    If you have any feedback on our support, please click here.


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, January 6, 2014 9:27 AM
    Monday, December 30, 2013 6:11 AM